Re: [HACKERS] Autoanalyze and OldestXmin
On Wed, Jun 8, 2011 at 10:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Pavan Deolasee pavan.deola...@gmail.com writes: I first thought that analyze and vacuum can not run concurrently on the same table since they take a conflicting lock on the table. So even if we ignore the analyze process while calculating the OldestXmin for vacuum, we should be fine since we know they are working on different tables. But I see analyze also acquires sample rows from the inherited tables with a non-conflicting lock. I probably do not understand the analyze code well, but is that the reason why we can't ignore analyze snapshot while determining OldestXmin for vacuum ? The reason why we can't ignore that snapshot is that it's being set for the use of user-defined functions, which might do practically anything. They definitely could access tables other than the one under analysis. (I believe that PostGIS does such things, for example --- it wants to look at its auxiliary tables for metadata.) Also keep in mind that we allow ANALYZE to be run inside a transaction block, which might contain other operations sharing the same snapshot. Ah, I see. Would there will be benefits if we can do some special handling for cases where we know that ANALYZE is running outside a transaction block and that its not going to invoke any user-defined functions ? If user is running ANALYZE inside a transaction block, he is probably already aware and ready to handle long-running transaction. But running them under the covers as part of auto-analyze does not see quite right. The pgbench test already shows the severe bloat that a long running analyze may cause for small tables and many wasteful vacuum runs on those tables. Another idea would be to split the ANALYZE into multiple small transactions, each taking a new snapshot. That might result in bad statistics if the table is undergoing huge change, but in that case, the stats will be outdated soon anyways if we run with a old snapshot. I understand there could be issues like counting the same tuple twice or more, but would that be a common case to worry about ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
[HACKERS] Parameterized aggregate subquery (was: Pull up aggregate subquery)
step. I didn't measure the additional cost in planner stage. BTW, as I changed title and design from the previous post, should I throw away the old commit fest entry and make the new one? Regards, -- Hitoshi Harada aggjoin-20110609.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] postgresql 9.0.4 source compilation issue on OSX
I am installing postgresql using homebrew on OSX. I am getting strange compilation errors. Googling did not help me in getting any lead to the resolution. When I compile it on a standalone basis (without using brew) I still get the same error. Compiling 9.1beta1 source package also gave me the same set of errors. Any lead/help will be highly appreciated. - Bhavin Here's the brief gist of errors along with environment details and here's the full error log https://gist.github.com/1014987. make -C ../../../src/port all /usr/bin/cc -O3 -march=core2 -msse4.1 -w -pipe -I/usr/include/libxml2 -I/usr/local/Cellar/ossp-uuid/1.6.2/include -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -arch x86_64 -I../../../src/include -I/usr/local/Cellar/readline/6.2.1/include -I/usr/include/libxml2 -I../../../src/port '-DHOST_TUPLE=x86_64-apple-darwin10.7.0' '-DMAKEPROG=make' '-DSHELLPROG=/bin/sh' '-DDLSUFFIX=.so' -c -o pg_regress.o pg_regress.c make[3]: Nothing to be done for `all'. /usr/bin/cc -O3 -march=core2 -msse4.1 -w -pipe -I/usr/include/libxml2 -I/usr/local/Cellar/ossp-uuid/1.6.2/include -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -arch x86_64 -I../../../src/include -I/usr/local/Cellar/readline/6.2.1/include -I/usr/include/libxml2 -c -o pg_regress_main.o pg_regress_main.c /usr/bin/cc -O3 -march=core2 -msse4.1 -w -pipe -I/usr/include/libxml2 -I/usr/local/Cellar/ossp-uuid/1.6.2/include -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -arch x86_64 -I../../../src/include -I/usr/local/Cellar/readline/6.2.1/include -I/usr/include/libxml2 -c -o regress.o regress.c make -C ../../../contrib/spi refint.so make[3]: pg_config: Command not found make[3]: *** No rule to make target `refint.so'. Stop. make[2]: *** [../../../contrib/spi/refint.so] Error 2 make[2]: *** Waiting for unfinished jobs make[1]: *** [install] Error 2 make: *** [install] Error 2 == Exit Status: 2 http://github.com/mxcl/homebrew/blob/master/Library/Formula/postgresql.rb#L55 == Environment HOMEBREW_VERSION: 0.8 HEAD: 3c7142478ab167c79e0d30a5d1a580ce6bce596f HOMEBREW_PREFIX: /usr/local HOMEBREW_CELLAR: /usr/local/Cellar HOMEBREW_REPOSITORY: /usr/local HOMEBREW_LIBRARY_PATH: /usr/local/Library/Homebrew Hardware: dual-core 64-bit penryn OS X: 10.6.7 Kernel Architecture: x86_64 Ruby: 1.8.7-174 /usr/bin/ruby = /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/bin/ruby Xcode: 3.2.6 GCC-4.0: build 5494 GCC-4.2: build 5666 LLVM: build 2335 MacPorts or Fink? false X11 installed? true == Build Flags CC: /usr/bin/cc = /usr/bin/gcc-4.2 CXX: /usr/bin/c++ = /usr/bin/c++-4.2 LD: /usr/bin/cc = /usr/bin/gcc-4.2 CFLAGS: -O3 -march=core2 -msse4.1 -w -pipe -I/usr/include/libxml2 -I/usr/local/Cellar/ossp-uuid/1.6.2/include CXXFLAGS: -O3 -march=core2 -msse4.1 -w -pipe -I/usr/include/libxml2 CPPFLAGS: -I/usr/local/Cellar/readline/6.2.1/include LDFLAGS: -L/usr/local/Cellar/readline/6.2.1/lib -L/usr/local/Cellar/ossp-uuid/1.6.2/lib MAKEFLAGS: -j2 PKG_CONFIG_PATH: /usr/local/Cellar/readline/6.2.1/lib/pkgconfig
Re: [HACKERS] [Pgbuildfarm-members] CREATE FUNCTION hang on test machine polecat on HEAD
On Jun 8, 2011, at 9:13 AM, Robert Creager wrote:I've renamed /opt/local so it's not picked up, and change HEAD to build every 6 hours. Won't prove it doesn't happen though... If it appears to work for a bit, I can move /opt/local back and see what happens.Gack. ccache is in /opt/local/bin... I've reverted for now, keep the 6 hour force build, and will re-work my config and put ccache somewhere else.Sigh,Rob --Robert Creager, Principal Software EngineerOracleServer Technologies500 Eldorado Blvd, Bldg 5Broomfield, CO, 80021Phone: 303-272-6830Email: robert.crea...@oracle.comOracle is committed to developing practices and products that help protect the environment
Re: [HACKERS] [Pgbuildfarm-members] CREATE FUNCTION hang on test machine polecat on HEAD
On Jun 7, 2011, at 11:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: But I tried make installcheck in plperl quite a few times with no problems. (And yes, I tried some assorted settings of PERL_HASH_SEED, as well as none at all.) At this point I'm thinking that the perl you've got in /opt/local must be bollixing the works somehow, though it's not clear how. It's also really strange that it evidently only fails some of the time ... I've renamed /opt/local so it's not picked up, and change HEAD to build every 6 hours. Won't prove it doesn't happen though... If it appears to work for a bit, I can move /opt/local back and see what happens. I'll see about setting up my iMac at home for the build farm for another Apple machine. Anything you'd like to change, or run, or something else? Later, Rob
[HACKERS] [v9.1] sepgsql - userspace access vector cache
The attached patch adds contrib/sepgsql a cache mechanism for access control decision of SELinux. It shall reduce the total number of system call invocations to improve the performance on its access controls. In the current implementation, the sepgsql always raises a query to SELinux in-kernel. However, same answer shall be returned for some pair of security labels and object class, unless the security policy got reloaded. It is a situation caching mechanism works well. Of course, we don't assume the security policy is reloaded so frequently. I tried to measure the performance to run sepgsql_restorecon(NULL) that is used to assign initial labels of schemas, relations, columns and procedures. It also invokes massive number of relabelfrom and relabelto permission checks. $ time -p psql -c 'SELECT sepgsql_restorecon(NULL);' postgres without patch real 2.73 real 2.70 real 2.72 real 2.67 real 2.68 with patch real 0.67 real 0.61 real 0.63 real 0.63 real 0.63 The improvement is obvious. From the viewpoint of implementation, this patch replaces sepgsql_check_perms() by sepgsql_avc_check_perms(), from non-cache interface to cached interface. Every cached items are hashed using a pair of security labels and object class, so, even if different objects have same security label, system call invocation shall happen only once for an identical combination. The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp sepgsql-uavc.1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Allow star syntax in GROUP BY, as a shorthand for all table columns
On Wed, Jun 8, 2011 at 20:22, Tom Lane t...@sss.pgh.pa.us wrote: Is this really necessary now that we know about GROUP BY primary key? You're right. I was just looking for something easy to hack on and didn't put much thought into usefulness. I'll try to do better next time. :) Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] could not truncate directory pg_serial: apparent wraparound
While testing this, I noticed another serious bug in the OldSerXidSLRU handling: we never set the dirty-flag on any page. I believe the reason we haven't bumped into this in testing before is that when a new page is initialized, it's marked as dirty, so everything goes smoothly when we modify recently-zeroed pages. But if a page falls out of the cache, and is later read back in and modified, the modifications are lost. The comments in SLRU could be more explicit about this. It was coincidental that I started to wonder where the pages are marked as dirty, I somehow thought the SLRU functions do that for you. Fortunately the fix is very simple, we just need to set the page_dirty flag whenever we modify an slru page. But clearly this slru stuff needs more testing. It's pretty hard to write good repeatable test cases for these things, though. -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Wed, Jun 8, 2011 at 6:43 PM, Joshua Berkus j...@agliodbs.com wrote: Simon, The point I have made is that I disagree with a feature freeze date fixed ahead of time without regard to the content of the forthcoming release. I've not said I disagree with feature freezes altogether, which would be utterly ridiculous. Fixed dates are IMHO much less important than a sensible and useful feature set for our users. This is such a non-argument it's silly. We have so many new major features for 9.1 that I'm having trouble writing sensible press releases which don't sound like a laundry list. You're right this is a non-argument. I am not continuing this debate using the above point. I am merely correcting people's assertions about what I think, which is a little tiresome for all of us and it would be much better if people didn't foolishly put words in my mouth, as multiple people have done on this thread. I'm also quite happy with the feature set for 9.1. MySQL repeatedly delivered releases with half-finished features and earned much disrespect. We have never done that previously and I am against doing so in the future. This is also total BS. I worked on the MySQL team. Before Sun/Oracle, MySQL specifically had feature-driven releases, where Marketing decided what features 5.0, 5.1 and 5.2 would have. They also accepted new features during beta if Marketing liked them enough. This resulted in the 5.1 release being *three years late*, and 5.3 being cancelled altogether. And let's talk about the legendary instability of 5.0, because they decided that they couldn't cancel partitioning and stored procedures, whether they were ready for prime time or not and because they kept changing the API during beta. MySQL never had time-based releases before Oracle took them over. And Oracle has been having feature-free releases because they're trying to work through MySQL's list of thousands of unfixed bugs which dates back to 2003. I claimed they delivered half-finished features. You clearly agree with me on that. I'm not sure which part you see as BS? An argument for feature-driven releases is in fact an argument for the MySQL AB development model. And that's not a company I want to emulate. Yes, I've also experienced totally marketing-driven software development, and that's why I'm *here*. I've spoken at length about how good our process is and have considerable respect for it and the people that have made it work. I am not advocating any changes to it at all, especially not to the model used by MYSQL AB. I have asked that we maintain the Reasonableness we have always had about how the feature freeze date was applied. An example of such reasonableness is that if a feature is a few days late and it is important, then it would still go into the release. An example of unreasonableness would be to close the feature freeze on a predetermined date, without regard to the state of the feature set in the release. To date, we have always been reasonable and I don't want to change the process in the way Robert has suggested we should change. I was one of a number of developers making that point at the developer meeting and I would say I was part of the majority view. -- 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] Autoanalyze and OldestXmin
On Thu, Jun 9, 2011 at 11:50 AM, Pavan Deolasee pavan.deola...@gmail.comwrote: Ah, I see. Would there will be benefits if we can do some special handling for cases where we know that ANALYZE is running outside a transaction block and that its not going to invoke any user-defined functions ? If user is running ANALYZE inside a transaction block, he is probably already aware and ready to handle long-running transaction. But running them under the covers as part of auto-analyze does not see quite right. The pgbench test already shows the severe bloat that a long running analyze may cause for small tables and many wasteful vacuum runs on those tables. Another idea would be to split the ANALYZE into multiple small transactions, each taking a new snapshot. That might result in bad statistics if the table is undergoing huge change, but in that case, the stats will be outdated soon anyways if we run with a old snapshot. I understand there could be issues like counting the same tuple twice or more, but would that be a common case to worry about ? FWIW I searched the archives again and seems like ITAGAKI Takahiro complained about the same issue in the past and had some ideas (including splitting one long transaction). We did not conclude the discussions that time, but I hope we make some progress this time unless we are certain that there are no low-hanging fruits here. http://archives.postgresql.org/pgsql-hackers/2008-02/msg00574.php Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
[HACKERS] FOREIGN TABLE doc fix
Hi hackers,
At first I've posted to pgsql-docs but there is no reply at present.
http://archives.postgresql.org/pgsql-docs/2011-06/msg6.php
So I post revised patch to pgsql-hackers, because the patch is for
documents under development for 9.1 beta2. Please let me know if this
was wrong list to discuss these issues.
Attached patch includes fixes for FOREIGN TABLE documents:
1) NOT NULL should be added to syntax of ALTER FOREIGN TABLE ADD
COLUMN command.
2) Mentions about unsupported features should be removed from ALTER
FOREIGN TABLE document. They had been proposed but haven't been
committed for 9.1. These unsupported features are not mentioned in
CREATE FOREIGN TABLE document.
- table inheritance
- oid system column
- CHECK constraint
- per-column FDW options
3) It would be useful to mention differences between ordinary tables and
foreign tables in CREATE FOREIGN TABLE document.
- NOT NULL constraint is just for optimization, at least at present
- serial/bigserial can't be used because they need default value
The description in the patch are based on the advice which were posted
by Thom Brown and Robert Haas in the thread:
http://archives.postgresql.org/pgsql-hackers/2011-03/msg01949.php
Regards,
--
Shigeru Hanada
diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml
b/doc/src/sgml/ref/alter_foreign_table.sgml
index c2ebdac..af7d5fa 100644
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
*** ALTER FOREIGN TABLE replaceable class=
*** 32,38
phrasewhere replaceable class=PARAMETERaction/replaceable is one
of:/phrase
! ADD [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable
replaceable class=PARAMETERtype/replaceable
DROP [ COLUMN ] [ IF EXISTS ] replaceable
class=PARAMETERcolumn/replaceable [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [
SET DATA ] TYPE replaceable class=PARAMETERtype/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable {
SET | DROP } NOT NULL
--- 32,38
phrasewhere replaceable class=PARAMETERaction/replaceable is one
of:/phrase
! ADD [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable
replaceable class=PARAMETERtype/replaceable [ NULL | NOT NULL ]
DROP [ COLUMN ] [ IF EXISTS ] replaceable
class=PARAMETERcolumn/replaceable [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [
SET DATA ] TYPE replaceable class=PARAMETERtype/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable {
SET | DROP } NOT NULL
*** ALTER FOREIGN TABLE replaceable class=
*** 125,131
termliteralOPTIONS ( [ ADD | SET | DROP ] replaceable
class=PARAMETERoption/replaceable ['replaceable
class=PARAMETERvalue/replaceable'] [, ... ] )/literal/term
listitem
para
! Change options for the foreign table or the column of the foreign table.
literalADD/, literalSET/, and literalDROP/
specify the action to be performed. literalADD/ is assumed
if no operation is explicitly specified. Option names must be
--- 125,131
termliteralOPTIONS ( [ ADD | SET | DROP ] replaceable
class=PARAMETERoption/replaceable ['replaceable
class=PARAMETERvalue/replaceable'] [, ... ] )/literal/term
listitem
para
! Change options for the foreign table.
literalADD/, literalSET/, and literalDROP/
specify the action to be performed. literalADD/ is assumed
if no operation is explicitly specified. Option names must be
*** ALTER FOREIGN TABLE replaceable class=
*** 150,157
You must own the table to use commandALTER FOREIGN TABLE/.
To change the schema of a foreign table, you must also have
literalCREATE/literal privilege on the new schema.
-To add the table as a new child of a parent table, you must own the
-parent table as well.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have literalCREATE/literal privilege on
the table's schema. (These restrictions enforce that altering the owner
--- 150,155
*** ALTER FOREIGN TABLE replaceable class=
*** 260,269
/para
para
! Consistency with the foreign server is not checked when a column is
! added or removed with literalADD COLUMN/literal or
! literalDROP COLUMN/literal, a system literaloid/ column is added
! or removed, a literalCHECK/ or literalNOT NULL/ constraint is
added, or column type is changed with literalSET DATA TYPE/. It is
the
user's responsibility to ensure that the table definition matches the
remote side.
--- 258,266
/para
para
! Consistency with the foreign server is not checked when a column is added
! or removed with literalADD COLUMN/literal or
! literalDROP
[HACKERS] hot standby startup, visibility map, clog
Hello list, A little while ago time ago I posted about how my ... exciting backup procedure caused occasional problems starting due to clog not being big enough. (http://archives.postgresql.org/pgsql-hackers/2011-04/msg01148.php) I recently had a reproduction and a little bit of luck, and I think I have a slightly better idea of what may be causing this. The first fact is that turning off hot standby will let the cluster start up, but only after seeing a spate of messages like these (dozen or dozens, not thousands): 2011-06-09 08:02:32 UTC LOG: restored log file 0002002C00C0 from archive 2011-06-09 08:02:33 UTC WARNING: xlog min recovery request 2C/C1F09658 is past current point 2C/C037B278 2011-06-09 08:02:33 UTC CONTEXT: writing block 0 of relation base/16385/16784_vm xlog redo insert: rel 1663/16385/128029; tid 114321/63 2011-06-09 08:02:33 UTC LOG: restartpoint starting: xlog Most importantly, *all* such messages are in visibility map forks (_vm). I reasonably confident that my code does not start reading data until pg_start_backup() has returned, and blocks on pg_stop_backup() after having read all the data. Also, the mailing list correspondence at http://archives.postgresql.org/pgsql-hackers/2010-11/msg02034.php suggests that the visibility map is not flushed at checkpoints, so perhaps with some poor timing an old page can wander onto disk even after a checkpoint barrier that pg_start_backup waits for. (I have not yet found the critical section that makes visibilitymap buffers immune to checkpoint though). Given all that, if the smgr's generic read path that checks the LSN and possibly the clog (but apparently only in hot standby mode, since pre-hot-standby the clog's intermediate states were not so interesting...) has a problem with such uncheckpointed pages, then it would seem reasonable that the system refuses to start vs. the way it once did. FWIW, letting recovery run without hot standby for a little while, canceling, and then starting again after the danger zone had passed would allow recovery to proceed correctly, as one might expect. Thoughts? -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [v9.2] sepgsql - userspace access vector cache (Re: [v9.1] sepgsql - userspace access vector cache)
Oops, subject was incorrect. It should be [v9.2], not [v9.1] 2011/6/9 Kohei KaiGai kai...@kaigai.gr.jp: The attached patch adds contrib/sepgsql a cache mechanism for access control decision of SELinux. It shall reduce the total number of system call invocations to improve the performance on its access controls. In the current implementation, the sepgsql always raises a query to SELinux in-kernel. However, same answer shall be returned for some pair of security labels and object class, unless the security policy got reloaded. It is a situation caching mechanism works well. Of course, we don't assume the security policy is reloaded so frequently. I tried to measure the performance to run sepgsql_restorecon(NULL) that is used to assign initial labels of schemas, relations, columns and procedures. It also invokes massive number of relabelfrom and relabelto permission checks. $ time -p psql -c 'SELECT sepgsql_restorecon(NULL);' postgres without patch real 2.73 real 2.70 real 2.72 real 2.67 real 2.68 with patch real 0.67 real 0.61 real 0.63 real 0.63 real 0.63 The improvement is obvious. From the viewpoint of implementation, this patch replaces sepgsql_check_perms() by sepgsql_avc_check_perms(), from non-cache interface to cached interface. Every cached items are hashed using a pair of security labels and object class, so, even if different objects have same security label, system call invocation shall happen only once for an identical combination. The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SLRU limits
While reviewing the SLRU code in predicate.c again, I remembered this old thread: http://archives.postgresql.org/pgsql-hackers/2010-12/msg02374.php SLRU has a limit of 64k segment files, because the files are named using four hex digits like 00CE. Kevin's math shows that that's just enough to store 2^32 four-byte integers, which wasn't enough for predicate.c, which needs to store uint64s. Kevin worked around that by simply limiting the max range of open xids to fit the SLRU limit, ie. 2^31. However, that math was based on 8k block size, and the situation is worse for smaller block sizes. If you set BLCKSZ to 2048 or less, pg_subtrans can only hold 1 billion transactions. With 1024 block size, only half a billion. It's awfully late in the release cycle, but how about we add another digit to the filenames used by SLRU, to up the limit? At a quick glance, I don't see any protection against wrapping around page numbers in subtrans.c, so that ought to be fixed somehow. And it would make the SLRU code in predicate.c simpler (I note that the warning logic at least is wrong as it is - it doesn't take XID wrap-around into account). -- 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
On Thu, Jun 9, 2011 at 12:39 AM, Jeevan Chalke jeevan.cha...@enterprisedb.com wrote: It's a problem, but without an efficient algorithm for Unicode case folding, any fix we attempt to implement seems like it'll just be moving the problem around. Agree. I read on other mail thread that str_tolower() is a wide-character-aware lower function but it is also a collation-aware and hence might change its behaviour wrt change in locale. However, Tom suggested that we need to have non-locale-dependent case folding algorithm. But still for same locale on same machine, where we can able to create a table, insert some data, we cannot retrieve it. Don't you think it is more serious and we need a quick solution here? As said earlier it may even lead to pg_dump failures. Given that str_tolower() functionality is locale dependent but still it will resolve this particular issue. Not sure, there might be a performance issue but at-least we are not giving an error. Well, as I understand it, the problem here is that if someone goes and changes the locale, then you might massively break the user's application. For example, if the user says: CREATE TABLE FOO (...); SELECT * FROM FOO; ...that'll work, of course, because whatever you get when you downcase FOO will be the same both times. But if the locale now changes, then the next... SELECT * FROM FOO; ...might fail, because the new downcasing of FOO might not match the old one. You could argue that that's better than the current situation, but it's not clear-cut. But now that I re-think about it, I guess what I'm confused about is this code here: if (ch = 'A' ch = 'Z') ch += 'a' - 'A'; else if (IS_HIGHBIT_SET(ch) isupper(ch)) ch = tolower(ch); result[i] = (char) ch; It seems to me that we're downcasing the first byte of each wide character and ignoring the rest... which seems like it can't possibly be a good idea in a multi-byte encoding. Perhaps we could keep that approach for single-byte encodings and just pass through multi-byte characters untouched? -- 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] SSI work for 9.1
Dan Ports wrote: On Wed, Jun 08, 2011 at 09:17:04PM -0500, Kevin Grittner wrote: A patch is attached which just covers the predicate lock acquisition, where a snapshot is available without too much pain. There are two functions which acquire predicate locks where a snapshot was not readily available: _bt_search() and _bt_get_endpoint(). Not only was it not clear how to get a snapshot in, it was not entirely clear from reading the code that we need to acquire predicate locks here. Now, I suspect that we probably do, because I spent many long hours stepping through gdb to pick the spots where they are, but that was about a year ago and my memory of the details has faded. For _bt_search(), the lock calls should move to _bt_first() where the ScanDesc is available. This also keeps us from trying to take locks during _bt_pagedel(), which is only called during vacuum and recovery. Sounds reasonable, but why did you pass the snapshot to the PredicateLockPage() call but not the PredicateLockRelation() call? Oversight? The call in _bt_get_endpoint() seems unnecessary, because after it returns, _bt_endpoint() takes the same lock. The only other callers of _bt_get_endpoint() are _bt_pagedel() and _bt_insert_parent(), neither of which should take predicate locks. That also sounds reasonable. I've updated the patch, attached. I've confirmed that it passes the usual regression tests (including isolation tests and the normal regression tests at serializable). I'll take a closer look once I wake up and get the caffeine going. Thanks for following up on this! -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] could not truncate directory pg_serial: apparent wraparound
Heikki Linnakangas wrote: While testing this, I noticed another serious bug in the OldSerXidSLRU handling: we never set the dirty-flag on any page. Arg. I never noticed that there was such a thing, although in retrospect I should have suspected it and gone hunting for it. I believe the reason we haven't bumped into this in testing before is that when a new page is initialized, it's marked as dirty, so everything goes smoothly when we modify recently-zeroed pages. Sounds plausible. But if a page falls out of the cache, and is later read back in and modified, the modifications are lost. The comments in SLRU could be more explicit about this. It was coincidental that I started to wonder where the pages are marked as dirty, I somehow thought the SLRU functions do that for you. Yeah -- me, too. Fortunately the fix is very simple, we just need to set the page_dirty flag whenever we modify an slru page. OK. But clearly this slru stuff needs more testing. It's pretty hard to write good repeatable test cases for these things, though. Yeah, that is the problem. Thanks for finding this. Is there anything you would like me to do in this area right now, or are you on it? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
On Wed, Jun 8, 2011 at 12:34, Radosław Smogura rsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? What's nbproject? As for the patch itself, we don't normally keep comments in the .gitignore files, I'm not sure if we want to start doing that... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SLRU limits
On Thu, Jun 9, 2011 at 7:46 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: While reviewing the SLRU code in predicate.c again, I remembered this old thread: http://archives.postgresql.org/pgsql-hackers/2010-12/msg02374.php SLRU has a limit of 64k segment files, because the files are named using four hex digits like 00CE. Kevin's math shows that that's just enough to store 2^32 four-byte integers, which wasn't enough for predicate.c, which needs to store uint64s. Kevin worked around that by simply limiting the max range of open xids to fit the SLRU limit, ie. 2^31. However, that math was based on 8k block size, and the situation is worse for smaller block sizes. If you set BLCKSZ to 2048 or less, pg_subtrans can only hold 1 billion transactions. With 1024 block size, only half a billion. I'm pretty unexcited about this. It's not terribly sane to keep a transaction open for half a billion XIDs anyway, because of VACUUM. And I would guess that there's a lot more interest in raising BLCKSZ than lowering it. It might not be a bad idea to adopt the fix you propose anyway, but it doesn't seem urgent. -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Thu, Jun 9, 2011 at 5:09 AM, Simon Riggs si...@2ndquadrant.com wrote: I have asked that we maintain the Reasonableness we have always had about how the feature freeze date was applied. An example of such reasonableness is that if a feature is a few days late and it is important, then it would still go into the release. An example of unreasonableness would be to close the feature freeze on a predetermined date, without regard to the state of the feature set in the release. To date, we have always been reasonable and I don't want to change the process in the way Robert has suggested we should change. Now you're putting words in my mouth. I wouldn't want to put out a release without a good feature set, either, but we don't have that problem. Getting them out on a fairly regular schedule without a really long feature freeze has traditionally been a bit harder. I believe that over the last few releases we've actually gotten better at integrating larger patches while also sticking closer to the schedule; and I'd like to continue to get better at both of those things. I don't advocate blind adherence to the feature freeze date either, but I do prefer to see deviations measured in days or at most weeks rather than months; and I have a lot more sympathy for the patch submitted and no one got around to reviewing it situation than I do for the patch just plain got here late case. -- 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] reducing the overhead of frequent table locks - now, with WIP patch
On Thu, Jun 9, 2011 at 2:13 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jun 9, 2011 at 5:09 AM, Simon Riggs si...@2ndquadrant.com wrote: I have asked that we maintain the Reasonableness we have always had about how the feature freeze date was applied. An example of such reasonableness is that if a feature is a few days late and it is important, then it would still go into the release. An example of unreasonableness would be to close the feature freeze on a predetermined date, without regard to the state of the feature set in the release. To date, we have always been reasonable and I don't want to change the process in the way Robert has suggested we should change. Now you're putting words in my mouth. I wouldn't want to put out a release without a good feature set, either, but we don't have that problem. Getting them out on a fairly regular schedule without a really long feature freeze has traditionally been a bit harder. I believe that over the last few releases we've actually gotten better at integrating larger patches while also sticking closer to the schedule; and I'd like to continue to get better at both of those things. I don't advocate blind adherence to the feature freeze date either, but I do prefer to see deviations measured in days or at most weeks rather than months; and I have a lot more sympathy for the patch submitted and no one got around to reviewing it situation than I do for the patch just plain got here late case. Can we make this the last post on this topic please? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing the overhead of frequent table locks - now, with WIP patch
Can we make this the last post on this topic please? +1 :) Thanks, Pavan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
On Thu, 9 Jun 2011 14:12:59 +0200, Magnus Hagander wrote: On Wed, Jun 8, 2011 at 12:34, Radosław Smogura rsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? Actually I called compilation from make in cygwin env, as I can't run build on Windows7/VisualStudio 2010. I don't know if this is preferred method. From configure script checking for ld used by GCC... /usr/i686-pc-cygwin/bin/ld.exe checking if the linker (/usr/i686-pc-cygwin/bin/ld.exe) is GNU ld... yes CC runs as GNU C (GCC) version 4.3.4 20090804 (release) 1 (i686-pc-cygwin) compiled by GNU C version 4.3.4 20090804 (release) 1, GMP version 4.3.1, MPFR version 2.4.1-p5. GGC heuristics: --param ggc-min-expand=100 --param ggc-min-heapsize=131072 After compilation I got many of *dll.def and others. What's nbproject? Just configuration from some editor. It looks like any move in project creates this entry in .gitignore As for the patch itself, we don't normally keep comments in the .gitignore files, I'm not sure if we want to start doing that... This is not my part - just wanted to comment what and why. Regards, Radek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
On 06/09/2011 08:12 AM, Magnus Hagander wrote: On Wed, Jun 8, 2011 at 12:34, Radosław Smogurarsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? Yes, indeed. I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? The .exe files go exactly where linked executables go on Unix. We could add .gitignore lines for them just as we have for Unix, e.g. by adding /initdb.exe to src/bin/initdb.gitignore, or more simply by adding a global rule for *.exe as the patch proposes. 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] .gitignore for some of cygwin files
On Thu, 9 Jun 2011 14:12:59 +0200, Magnus Hagander wrote: On Wed, Jun 8, 2011 at 12:34, Radosław Smogura rsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? What's nbproject? As for the patch itself, we don't normally keep comments in the .gitignore files, I'm not sure if we want to start doing that... Sorry for trash this is `git status` after compilation on branch # .gitignore-for-cygwin.diff # blob_20110601.patch # nbproject/ # src/backend/postgres.def # src/backend/postgres.exe # src/backend/replication/libpqwalreceiver/liblibpqwalreceiverdll.def # src/backend/snowball/libdict_snowballdll.def # src/backend/utils/adt/blob.c # src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_micdll.def # src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_micdll.def # src/backend/utils/mb/conversion_procs/euc2004_sjis2004/libeuc2004_sjis2004dll.def # src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_micdll.def # src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjisdll.def # src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_micdll.def # src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5dll.def # src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250dll.def # src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_micdll.def # src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_asciidll.def # src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5dll.def # src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillicdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc2004/libutf8_and_euc2004dll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cndll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jpdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_krdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_twdll.def # src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030dll.def # src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbkdll.def # src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859dll.def # src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1dll.def # src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johabdll.def # src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjisdll.def # src/backend/utils/mb/conversion_procs/utf8_and_sjis2004/libutf8_and_sjis2004dll.def # src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhcdll.def # src/backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_windll.def # src/bin/initdb/initdb.exe # src/bin/pg_basebackup/pg_basebackup.exe # src/bin/pg_config/pg_config.exe # src/bin/pg_controldata/pg_controldata.exe # src/bin/pg_ctl/pg_ctl.exe # src/bin/pg_dump/pg_dump.exe # src/bin/pg_dump/pg_dumpall.exe # src/bin/pg_dump/pg_restore.exe # src/bin/pg_resetxlog/pg_resetxlog.exe # src/bin/psql/psql.exe # src/bin/scripts/clusterdb.exe # src/bin/scripts/createdb.exe # src/bin/scripts/createlang.exe # src/bin/scripts/createuser.exe # src/bin/scripts/dropdb.exe # src/bin/scripts/droplang.exe # src/bin/scripts/dropuser.exe # src/bin/scripts/reindexdb.exe # src/bin/scripts/vacuumdb.exe # src/include/utils/blob.h # src/interfaces/ecpg/preproc/ecpg.exe # src/pl/plpgsql/src/libplpgsqldll.def # src/test/regress/libregressdll.def # src/test/regress/pg_regress.exe # src/timezone/zic.exe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
=?UTF-8?Q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes: On Thu, 9 Jun 2011 14:12:59 +0200, Magnus Hagander wrote: What's nbproject? Just configuration from some editor. It looks like any move in project creates this entry in .gitignore If you've got random third-party tools that clutter the source tree, you should use a personal .gitignore file to ignore them. We already established the principle that emacs backup files have to be ignored on a personal level, and I don't see why we'd do it differently for Windows tools. 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] postgresql 9.0.4 source compilation issue on OSX
On Thu, Jun 9, 2011 at 2:59 AM, Bhavin Kamani bhavinkam...@gmail.com wrote: I am installing postgresql using homebrew on OSX. I am getting strange compilation errors. Googling did not help me in getting any lead to the resolution. When I compile it on a standalone basis (without using brew) I still get the same error. Compiling 9.1beta1 source package also gave me the same set of errors. Any lead/help will be highly appreciated. I'm not exactly sure what's going on here, but contrib modules can be built in two ways: with PGXS, or without PGXS. Typically, you compile without PGXS when you're building the server and it's contrib modules, and with PGXS if the server is already installed (and thus pg_config is present) and you just want to compile one more module. Here it looks like you're doing an in-tree compile but somehow PGXS is getting enabled anyway - not sure what's going on there. -- 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] SLRU limits
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: It's awfully late in the release cycle, but how about we add another digit to the filenames used by SLRU, to up the limit? It's way too late for that kind of thing, unless you are saying that SSI in and of itself is going to cause a release slip. (Which I'm getting the uncomfortable feeling may be true anyway.) That is not a one-line kind of fix --- it is likely to have a lot of unforeseen consequences. And who builds with smaller-than-default BLCKSZ anyway? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
Robert Haas robertmh...@gmail.com writes: But now that I re-think about it, I guess what I'm confused about is this code here: if (ch = 'A' ch = 'Z') ch += 'a' - 'A'; else if (IS_HIGHBIT_SET(ch) isupper(ch)) ch = tolower(ch); result[i] = (char) ch; The expected behavior there is that case-folding of non-ASCII characters will occur in single-byte encodings but nothing will happen to multi-byte characters. We are relying on isupper() to not return true when presented with a character fragment in a multibyte locale. 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
On Thu, Jun 9, 2011 at 10:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: But now that I re-think about it, I guess what I'm confused about is this code here: if (ch = 'A' ch = 'Z') ch += 'a' - 'A'; else if (IS_HIGHBIT_SET(ch) isupper(ch)) ch = tolower(ch); result[i] = (char) ch; The expected behavior there is that case-folding of non-ASCII characters will occur in single-byte encodings but nothing will happen to multi-byte characters. We are relying on isupper() to not return true when presented with a character fragment in a multibyte locale. Based on Jeevan's original message, it seems like that's not always the case, at least on Windows. -- 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: We are relying on isupper() to not return true when presented with a character fragment in a multibyte locale. Based on Jeevan's original message, it seems like that's not always the case, at least on Windows. Hmm. Now that you mention it, I think the same has been said about OSX. If we need to work around brain-dead isupper() tests, maybe the best thing is to implement two versions of the loop: if (encoding is single byte) ... loop as it stands ... else ... loop without the else if part 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] release slippage
On Thu, Jun 9, 2011 at 10:03 AM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: It's awfully late in the release cycle, but how about we add another digit to the filenames used by SLRU, to up the limit? It's way too late for that kind of thing, unless you are saying that SSI in and of itself is going to cause a release slip. (Which I'm getting the uncomfortable feeling may be true anyway.) So, speaking of that, aren't we supposed to wrap beta2 any minute now? There are two open items listed on the open items page as blockers for beta2: - error in information_schema.element_types view definition (bug 5926) - more SSI loose ends We really ought to fix #1, because we've already bumped catversion since beta1 (several times, actually) and we'd probably like very much not to do it again before final. With respect to #2, it would be nice to fix that, but I'm disinclined to hold up beta2 for it. Kevin and Dan don't seem to have reached agreement on all the details of the patch, and even if they had, it's far from obvious that this will be the last SSI related change we'll need to make. I'd vote for pushing out beta2 even if we can't get that one in. -- 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] literature on write-ahead logging
Excerpts from Robert Haas's message of mié jun 08 23:24:34 -0400 2011: I did a brief literature search for papers on breaking the WAL-serialization bottleneck today and hit upon this: Aether: A Scalable Approach to Logging, Ryan Johnson, Ippokratis Pandis, et al. http://infoscience.epfl.ch/record/149436/files/vldb10aether.pdf Great. 1. Subdivide XLOG insertion into three operations: (1) allocate space in the log buffer, (2) copy the log records into the allocated space, and (3) release the space to the buffer manager for eventual write to disk. AIUI, WALInsertLock currently covers all three phases of this operation, but phase 2 can proceed in parallel. It's pretty easy to imagine maintain one pointer that references the next available byte of log space (let's call this the next insert pointer), and a second pointer that references the byte following the last byte known to be written (let's call this the insert done pointer). I think this can be done more simply if instead of a single insert done pointer you have an array of them, one per backend; there's also a global pointer that can be advanced per the minimum of the bunch, which you can calculate with some quick locking of the array. You don't need to sleep at all, except to update the array and calculate the global ptr, so this is probably also faster. Second, they aren't really using locks, unless you count bus locks - they appear to have implemented most or all of it via CAS-based lock-free algorithms, which is probably well-justified optimization effort. Oh, hmm ... -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
On Thu, Jun 9, 2011 at 15:40, Andrew Dunstan and...@dunslane.net wrote: On 06/09/2011 08:12 AM, Magnus Hagander wrote: On Wed, Jun 8, 2011 at 12:34, Radosław Smogurarsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? Yes, indeed. I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? The .exe files go exactly where linked executables go on Unix. We could add .gitignore lines for them just as we have for Unix, e.g. by adding /initdb.exe to src/bin/initdb.gitignore, or more simply by adding a global rule for *.exe as the patch proposes. I think a global exclude for *.exe makes perfect sense. I don't see why we would ever want to commit a .exe file to the repository... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
On Thu, Jun 9, 2011 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: We are relying on isupper() to not return true when presented with a character fragment in a multibyte locale. Based on Jeevan's original message, it seems like that's not always the case, at least on Windows. Hmm. Now that you mention it, I think the same has been said about OSX. If we need to work around brain-dead isupper() tests, maybe the best thing is to implement two versions of the loop: if (encoding is single byte) ... loop as it stands ... else ... loop without the else if part That seems like a clear improvement. It's a long way from perfect, but still worthwhile. Would we back-patch that? Just do it in master? Wait for 9.2? -- 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] .gitignore for some of cygwin files
On Thu, Jun 9, 2011 at 15:40, Radosław Smogura rsmog...@softperience.eu wrote: On Thu, 9 Jun 2011 14:12:59 +0200, Magnus Hagander wrote: On Wed, Jun 8, 2011 at 12:34, Radosław Smogura rsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? What's nbproject? As for the patch itself, we don't normally keep comments in the .gitignore files, I'm not sure if we want to start doing that... Sorry for trash this is `git status` after compilation on branch # .gitignore-for-cygwin.diff # blob_20110601.patch # nbproject/ # src/backend/postgres.def # src/backend/postgres.exe # src/backend/replication/libpqwalreceiver/liblibpqwalreceiverdll.def # src/backend/snowball/libdict_snowballdll.def # src/backend/utils/adt/blob.c # src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_micdll.def # src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_micdll.def # src/backend/utils/mb/conversion_procs/euc2004_sjis2004/libeuc2004_sjis2004dll.def # src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_micdll.def # src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjisdll.def # src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_micdll.def # src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5dll.def # src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250dll.def # src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_micdll.def # src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_asciidll.def # src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5dll.def # src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillicdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc2004/libutf8_and_euc2004dll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cndll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jpdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_krdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_twdll.def # src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030dll.def # src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbkdll.def # src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859dll.def # src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1dll.def # src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johabdll.def # src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjisdll.def # src/backend/utils/mb/conversion_procs/utf8_and_sjis2004/libutf8_and_sjis2004dll.def # src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhcdll.def # src/backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_windll.def # src/bin/initdb/initdb.exe # src/bin/pg_basebackup/pg_basebackup.exe # src/bin/pg_config/pg_config.exe # src/bin/pg_controldata/pg_controldata.exe # src/bin/pg_ctl/pg_ctl.exe # src/bin/pg_dump/pg_dump.exe # src/bin/pg_dump/pg_dumpall.exe # src/bin/pg_dump/pg_restore.exe # src/bin/pg_resetxlog/pg_resetxlog.exe # src/bin/psql/psql.exe # src/bin/scripts/clusterdb.exe # src/bin/scripts/createdb.exe # src/bin/scripts/createlang.exe # src/bin/scripts/createuser.exe # src/bin/scripts/dropdb.exe # src/bin/scripts/droplang.exe # src/bin/scripts/dropuser.exe # src/bin/scripts/reindexdb.exe # src/bin/scripts/vacuumdb.exe # src/include/utils/blob.h # src/interfaces/ecpg/preproc/ecpg.exe # src/pl/plpgsql/src/libplpgsqldll.def # src/test/regress/libregressdll.def # src/test/regress/pg_regress.exe # src/timezone/zic.exe Based on this list, a global exclude for *.exe and lib*dll.def seems reasonable. Andrew, does that seem right to you as well? I definitely want to keep the nbproject thing out of there, that belongs in a personal exclude. utils/adt/blob.c and include/utils/blob.h - what is that? That's something that's actually from your development work and should not be ignored, right? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] release slippage
Robert Haas robertmh...@gmail.com writes: So, speaking of that, aren't we supposed to wrap beta2 any minute now? Yes. I'm expecting to do it this evening. There are two open items listed on the open items page as blockers for beta2: - error in information_schema.element_types view definition (bug 5926) - more SSI loose ends Peter concluded that #1 was really only a documentation problem, and fixed it in his latest commit. I'm not entirely convinced that he's reading the spec correctly, but will defer to his judgment. With respect to #2, it would be nice to fix that, but I'm disinclined to hold up beta2 for it. I agree. From what I can see, SSI is nowhere near RC-ready. We may as well push out beta2 to get the fixes accumulated so far into the hands of testers. (In particular, judging by the reports that have been coming in, we really need to push the timezone-abbreviations Windows crash fix...) 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] release slippage
On Thu, Jun 9, 2011 at 16:27, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So, speaking of that, aren't we supposed to wrap beta2 any minute now? Yes. I'm expecting to do it this evening. There are two open items listed on the open items page as blockers for beta2: - error in information_schema.element_types view definition (bug 5926) - more SSI loose ends Peter concluded that #1 was really only a documentation problem, and fixed it in his latest commit. I'm not entirely convinced that he's reading the spec correctly, but will defer to his judgment. With respect to #2, it would be nice to fix that, but I'm disinclined to hold up beta2 for it. I agree. From what I can see, SSI is nowhere near RC-ready. We may as well push out beta2 to get the fixes accumulated so far into the hands of testers. (In particular, judging by the reports that have been coming in, we really need to push the timezone-abbreviations Windows crash fix...) Yeah, we definitely need that one out. I say let's get beta2 out, and then plan to have a beta3 as well before rc. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] literature on write-ahead logging
On Thu, Jun 9, 2011 at 10:22 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: 1. Subdivide XLOG insertion into three operations: (1) allocate space in the log buffer, (2) copy the log records into the allocated space, and (3) release the space to the buffer manager for eventual write to disk. AIUI, WALInsertLock currently covers all three phases of this operation, but phase 2 can proceed in parallel. It's pretty easy to imagine maintain one pointer that references the next available byte of log space (let's call this the next insert pointer), and a second pointer that references the byte following the last byte known to be written (let's call this the insert done pointer). I think this can be done more simply if instead of a single insert done pointer you have an array of them, one per backend; there's also a global pointer that can be advanced per the minimum of the bunch, which you can calculate with some quick locking of the array. You don't need to sleep at all, except to update the array and calculate the global ptr, so this is probably also faster. I think looping over an array with one entry per backend is going to be intolerably slow... but it's possible I'm wrong. -- 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] Parameterized aggregate subquery (was: Pull up aggregate subquery)
On Thu, Jun 9, 2011 at 2:28 AM, Hitoshi Harada umi.tan...@gmail.com wrote: BTW, as I changed title and design from the previous post, should I throw away the old commit fest entry and make the new one? Nah, just edit the existing entry and change the title. Also add a link to the new patch, of course. -- 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] literature on write-ahead logging
Excerpts from Robert Haas's message of jue jun 09 10:28:39 -0400 2011: On Thu, Jun 9, 2011 at 10:22 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: 1. Subdivide XLOG insertion into three operations: (1) allocate space in the log buffer, (2) copy the log records into the allocated space, and (3) release the space to the buffer manager for eventual write to disk. AIUI, WALInsertLock currently covers all three phases of this operation, but phase 2 can proceed in parallel. It's pretty easy to imagine maintain one pointer that references the next available byte of log space (let's call this the next insert pointer), and a second pointer that references the byte following the last byte known to be written (let's call this the insert done pointer). I think this can be done more simply if instead of a single insert done pointer you have an array of them, one per backend; there's also a global pointer that can be advanced per the minimum of the bunch, which you can calculate with some quick locking of the array. You don't need to sleep at all, except to update the array and calculate the global ptr, so this is probably also faster. I think looping over an array with one entry per backend is going to be intolerably slow... but it's possible I'm wrong. Slower than sleeping? Consider that this doesn't need to be done for each record insertion, only when you need to flush (maybe more than that, but I think that's the lower limit). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Autoanalyze and OldestXmin
On Thu, Jun 9, 2011 at 2:20 AM, Pavan Deolasee pavan.deola...@gmail.com wrote: Ah, I see. Would there will be benefits if we can do some special handling for cases where we know that ANALYZE is running outside a transaction block and that its not going to invoke any user-defined functions ? We'd have to distinguish between user-defined typanalyze functions and system-defined typanalyze functions, which doesn't seem to appealing, or robust. If user is running ANALYZE inside a transaction block, he is probably already aware and ready to handle long-running transaction. But running them under the covers as part of auto-analyze does not see quite right. The pgbench test already shows the severe bloat that a long running analyze may cause for small tables and many wasteful vacuum runs on those tables. Another idea would be to split the ANALYZE into multiple small transactions, each taking a new snapshot. That might result in bad statistics if the table is undergoing huge change, but in that case, the stats will be outdated soon anyways if we run with a old snapshot. I understand there could be issues like counting the same tuple twice or more, but would that be a common case to worry about ? I am wondering if we shouldn't be asking ourselves a different question: why is ANALYZE running long enough on your tables for this to become an issue? How long is it taking? -- 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] release slippage
Robert Haas robertmh...@gmail.com wrote: Tom Lane t...@sss.pgh.pa.us wrote: aren't we supposed to wrap beta2 any minute now? There are two open items listed on the open items page as blockers for beta2: - error in information_schema.element_types view definition (bug 5926) - more SSI loose ends We really ought to fix #1, because we've already bumped catversion since beta1 (several times, actually) and we'd probably like very much not to do it again before final. With respect to #2, it would be nice to fix that, but I'm disinclined to hold up beta2 for it. Kevin and Dan don't seem to have reached agreement on all the details of the patch, and even if they had, it's far from obvious that this will be the last SSI related change we'll need to make. I'd vote for pushing out beta2 even if we can't get that one in. There is this pending patch, without which there are infrequent conditions under which the users could get a LOG level message and accumulate files in pg_serial: http://archives.postgresql.org/message-id/4dee7be402250003e...@gw.wicourts.gov The other pending patch relates to the false positives serialization failures and funny-looking pg_lock entries from not ignoring non-MVCC snapshots in SSI. I don't think Dan had any problem with the patch I offered, but I wasn't sure what to do about two functions. He researched that and proposed a way to handle those. That looked good to me on a first pass except that there was one line he left at NULL instead of plugging in the snapshot. That looks like an easily-fixed oversight. It will take me about an hour to review his changes in detail and re-test everything, although I don't expect any other issues. It seems odd not to include a change that was requested by Tom, Robert, and Heikki (and which Dan and I both put in the hours to have a patch before beta2) in beta2. It does fix the issue which people were concerned about (the predicate locks acquired on the table heap by the transaction when doing a REINDEX within a serializable transaction is no longer occuring.) The only other pending SSI issues I know of are minor documentation tweaks. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we need to work around brain-dead isupper() tests, maybe the best thing is to implement two versions of the loop: if (encoding is single byte) ... loop as it stands ... else ... loop without the else if part That seems like a clear improvement. It's a long way from perfect, but still worthwhile. Would we back-patch that? Just do it in master? Wait for 9.2? It looks to me like a portability bug fix, so I'd say back-patch. I'll take care of it, unless you're hot to do so? 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] libpq SSL with non-blocking sockets
I believe I have found a bug in libpq COPY handling with non-blocking
SSL connections. The bug manifests itself by dropping the connection
in PGputCopyData() with server closed the connection unexpectedly
message. The connection drop only occurs with nonblocking connections -
blocking connections work as expected.
I'll skip a lot of debugging details, but what I found out was that
the connection drop was caused by a SSL bad write retry from
SSL_write(). Which in turn appears to be caused by bad data passed to
SSL_write() retry. So, what happened was that at some point SSL_write()
returned SSL_ERROR_WANT_WRITE meaning that the write needs to be
retried. Now, according to OpenSSL manual the retry SSL_write needs to
be passed the same arguments as the previous call:
http://www.openssl.org/docs/ssl/SSL_write.html#WARNING
However the next SSL_write was passed another buffer with a different
length and it failed with SSL_ERROR_SSL bad write retry. Which in
turn caused the pqSendSome to drop the connection and failed the
PQputCopyData. Actually, now that I think of it, this is probably not
only related to COPY handling ...
The connection drop can be reproduced by introducing some network
problems to the connection, so that the SSL_write needs to be retried.
This can be most easily accomplished by passing a huge buffer to
PQputCopyData. For instance on my laptop I can do about 3 PQputCopyData
with a 100MB buffer before the connection is dropped. Others results may
vary. Also, it doesn't seem to matter what the libpq version is
used - I initially started on 8.3, but later confirmed that the same
problem exists on 8.4, 9.0 and 9.1. Tested with OpenSSL 0.9.8g and
0.9.8o, no idea about other versions.
The following is a log excerpt from my test program (attached) with some
extra logging added to libpq:
PQputCopyData #0 buf:1 total:0
pqFlush()
pqFlush() = 0
pqPutMsgEnd()
pqSendSome(9744)
SSL_write(0x7e59d60, 0x7ff5a7eac010, 9744) = 9744:
SSL_error: 0, errno: 0
pqPutMsgEnd() = 0
PQputCopyData #1 buf:1 total:1
pqPutMsgEnd()
pqSendSome(9744)
SSL_write(0x7e59d60, 0x7ff5a7eac010, 9744) = -1: SSL_error: 3,
errno: 11
pqPutMsgEnd() = 0
PQputCopyData #2 buf:1 total:2
pqFlush()
pqSendSome(10266)
SSL_write(0x7e59d60, 0x7ff5a7eac010, 10266) = -1: SSL_error:
3, errno: 11
pqFlush() = 1
pqPutMsgEnd()
pqSendSome(19488)
SSL_write(0x7e59d60, 0x7ff597eab010, 19488) = -1: SSL_error:
1, errno: 0
SSL error: bad write retry
pqPutMsgEnd() = -1
PQputCopyData() = -1: server closed the connection unexpectedly
In PQputCopyData #2 it is visible that the first SSL_write called from
pqFlush failed with SSL_ERROR_WANT_WRITE. The next SSL_write should
have been a retry with the same parameters, but instead was passed a
buffer with a different address and length. Hence the bad write
retry. Some googling turned out similar issues for other projects
using SSL with non-blocking sockets.
The possible workarounds are to disable SSL or to disable non-blocking
libpq connections. Both are not always possible - security reasons, 3rd
party applications, drivers, etc. So I think this should be fixed in
libpq. Not sure exactly how though. It would seem that for the
PQputCopyData the best would be to return 0 to indicate that the
operation should be retried. No idea for the other possible cases of
SSL_write() retry though. What do you think?
regards,
Martin
/* Debugging libpq SSL connection crashes. */
#include stdio.h
#include stdlib.h
#include stdarg.h
#include libpq-fe.h
#define BUFSIZE 1
#define CONNINFO host=localhost sslmode=require
static char buf[BUFSIZE];
void die(char *fmt, ...)
{
va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
exit(1);
}
void exec_sql(PGconn *conn, const char *sql, int expected_status)
{
PGresult *res;
fprintf(stderr, sql: %s\n, sql);
res = PQexec(conn, sql);
if (PQresultStatus(res) != expected_status)
die(failed: %s\n, sql, PQerrorMessage(conn));
}
int main(void)
{
PGconn *conn;
PGresult *res;
unsigned long bytes = 0;
int count = 0;
conn = PQconnectdb(CONNINFO);
if (PQstatus(conn) != CONNECTION_OK)
die(connection to database failed: %s, PQerrorMessage(conn));
fprintf(stderr, Connected to: %s\n, CONNINFO);
exec_sql(conn, BEGIN, PGRES_COMMAND_OK);
exec_sql(conn, DROP TABLE IF exists test_ssl_copy, PGRES_COMMAND_OK);
exec_sql(conn, CREATE TABLE test_ssl_copy(t text), PGRES_COMMAND_OK);
exec_sql(conn, COPY test_ssl_copy(t) FROM stdin, PGRES_COPY_IN);
/* Set the connection to non-blocking to enable crashing */
if (PQsetnonblocking(conn, 1) != 0)
die(PQsetnonblocking failed: %s, PQerrorMessage(conn));
while (1) {
int rc;
fprintf(stderr, PQputCopyData #%d buf:%lu total:%lu\n, count, sizeof(buf), bytes);
rc =
Re: [HACKERS] .gitignore for some of cygwin files
On 06/09/2011 10:25 AM, Magnus Hagander wrote: Based on this list, a global exclude for *.exe and lib*dll.def seems reasonable. Andrew, does that seem right to you as well? That plus an entry in src/backend/.gitignore for /postgres.def. 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] could not truncate directory pg_serial: apparent wraparound
Excerpts from Heikki Linnakangas's message of jue jun 09 04:56:41 -0400 2011: Fortunately the fix is very simple, we just need to set the page_dirty flag whenever we modify an slru page. But clearly this slru stuff needs more testing. It's pretty hard to write good repeatable test cases for these things, though. Maybe reduce the number of SLRU buffers used? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] release slippage
Kevin Grittner kevin.gritt...@wicourts.gov writes: The other pending patch relates to the false positives serialization failures and funny-looking pg_lock entries from not ignoring non-MVCC snapshots in SSI. I don't think Dan had any problem with the patch I offered, but I wasn't sure what to do about two functions. He researched that and proposed a way to handle those. That looked good to me on a first pass except that there was one line he left at NULL instead of plugging in the snapshot. That looks like an easily-fixed oversight. It will take me about an hour to review his changes in detail and re-test everything, although I don't expect any other issues. It seems odd not to include a change that was requested by Tom, Robert, and Heikki (and which Dan and I both put in the hours to have a patch before beta2) in beta2. Well, Heikki is lead committer for SSI; anything he's comfortable with pushing in today, he should push. Just keep in mind that it is too late to get a full buildfarm cycle in before beta2 wraps... so anything that goes in today had better be low-risk. 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] Autoanalyze and OldestXmin
I am wondering if we shouldn't be asking ourselves a different question: why is ANALYZE running long enough on your tables for this to become an issue? How long is it taking? The log file attached in the first post has the details; it's taking around 5 mins for the accounts table with 50 scale factor and 50 clients Thanks, Pavan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] literature on write-ahead logging
On Thu, Jun 9, 2011 at 10:34 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of jue jun 09 10:28:39 -0400 2011: On Thu, Jun 9, 2011 at 10:22 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: 1. Subdivide XLOG insertion into three operations: (1) allocate space in the log buffer, (2) copy the log records into the allocated space, and (3) release the space to the buffer manager for eventual write to disk. AIUI, WALInsertLock currently covers all three phases of this operation, but phase 2 can proceed in parallel. It's pretty easy to imagine maintain one pointer that references the next available byte of log space (let's call this the next insert pointer), and a second pointer that references the byte following the last byte known to be written (let's call this the insert done pointer). I think this can be done more simply if instead of a single insert done pointer you have an array of them, one per backend; there's also a global pointer that can be advanced per the minimum of the bunch, which you can calculate with some quick locking of the array. You don't need to sleep at all, except to update the array and calculate the global ptr, so this is probably also faster. I think looping over an array with one entry per backend is going to be intolerably slow... but it's possible I'm wrong. Slower than sleeping? Consider that this doesn't need to be done for each record insertion, only when you need to flush (maybe more than that, but I think that's the lower limit). Maybe. I'm worried that if someone jacks up max_connections to 1000 or 5000 or somesuch it could get pretty slow. -- 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] Autoanalyze and OldestXmin
On Thu, Jun 9, 2011 at 10:52 AM, Pavan Deolasee pavan.deola...@gmail.com wrote: I am wondering if we shouldn't be asking ourselves a different question: why is ANALYZE running long enough on your tables for this to become an issue? How long is it taking? The log file attached in the first post has the details; it's taking around 5 mins for the accounts table with 50 scale factor and 50 clients Wow, that's slow. Still, what if the user were doing a transaction of comparable size? It's not like ANALYZE is doing a gigantic amount of work. -- 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
On Thu, Jun 9, 2011 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we need to work around brain-dead isupper() tests, maybe the best thing is to implement two versions of the loop: if (encoding is single byte) ... loop as it stands ... else ... loop without the else if part That seems like a clear improvement. It's a long way from perfect, but still worthwhile. Would we back-patch that? Just do it in master? Wait for 9.2? It looks to me like a portability bug fix, so I'd say back-patch. I'll take care of it, unless you're hot to do so? Nope, have at it. -- 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] Autoanalyze and OldestXmin
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:52 AM, Pavan Deolasee pavan.deola...@gmail.com wrote: I am wondering if we shouldn't be asking ourselves a different question: why is ANALYZE running long enough on your tables for this to become an issue? How long is it taking? The log file attached in the first post has the details; it's taking around 5 mins for the accounts table with 50 scale factor and 50 clients Wow, that's slow. Still, what if the user were doing a transaction of comparable size? It's not like ANALYZE is doing a gigantic amount of work. I wonder what vacuum cost delay settings are in use ... 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] Autoanalyze and OldestXmin
On 09-Jun-2011, at 8:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:52 AM, Pavan Deolasee pavan.deola...@gmail.com wrote: I am wondering if we shouldn't be asking ourselves a different question: why is ANALYZE running long enough on your tables for this to become an issue? How long is it taking? The log file attached in the first post has the details; it's taking around 5 mins for the accounts table with 50 scale factor and 50 clients Wow, that's slow. Still, what if the user were doing a transaction of comparable size? It's not like ANALYZE is doing a gigantic amount of work. I wonder what vacuum cost delay settings are in use ... Default settings with 512Mb shared buffers Thanks. Pavan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] release slippage
On Thu, Jun 9, 2011 at 10:37 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: There is this pending patch, without which there are infrequent conditions under which the users could get a LOG level message and accumulate files in pg_serial: http://archives.postgresql.org/message-id/4dee7be402250003e...@gw.wicourts.gov That seems important to fix. The other pending patch relates to the false positives serialization failures and funny-looking pg_lock entries from not ignoring non-MVCC snapshots in SSI. I don't think Dan had any problem with the patch I offered, but I wasn't sure what to do about two functions. He researched that and proposed a way to handle those. That looked good to me on a first pass except that there was one line he left at NULL instead of plugging in the snapshot. That looks like an easily-fixed oversight. It will take me about an hour to review his changes in detail and re-test everything, although I don't expect any other issues. It seems odd not to include a change that was requested by Tom, Robert, and Heikki (and which Dan and I both put in the hours to have a patch before beta2) in beta2. It does fix the issue which people were concerned about (the predicate locks acquired on the table heap by the transaction when doing a REINDEX within a serializable transaction is no longer occuring.) I didn't realize I had requested to include this, but I've concluded after looking over it that I'm not qualified to commit it. Based on a quick IM session with Heikki I am doubtful he'll be able to get to it before the wrap, but we'll see, I guess. In any case, if this misses beta2, I'm not feeling like we'd need to push beta3 just for that reason. I'm not even 100% convinced that it needs to be in 9.1. We're talking about fixing a relatively rare false positive from a system which by its design necessitates that users be prepared for the possibility of false positives. If getting this fix in is going to mean that we have to wait another month and push another beta when that otherwise wouldn't be necessary, I'd vote to ship what we have. But I doubt that's really the decision we have to make anyway. One other problem I'm realizing: didn't Peter ask Bruce to revert the change to make pg_upgrade ignore case differences in locale names, on the grounds that it was not safe? -- 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] could not truncate directory pg_serial: apparent wraparound
Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Heikki Linnakangas's message of jue jun 09 04:56:41 -0400 2011: Fortunately the fix is very simple, we just need to set the page_dirty flag whenever we modify an slru page. But clearly this slru stuff needs more testing. It's pretty hard to write good repeatable test cases for these things, though. Maybe reduce the number of SLRU buffers used? That's a thought. I'll see about getting a build with TEST_OLDSERXID defined and just one or two SLRU buffers for this, and see if anything pops out of that. after I finish reviewing Dan's patch from last night. Thanks, -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] literature on write-ahead logging
Excerpts from Robert Haas's message of jue jun 09 10:55:45 -0400 2011: On Thu, Jun 9, 2011 at 10:34 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Slower than sleeping? Consider that this doesn't need to be done for each record insertion, only when you need to flush (maybe more than that, but I think that's the lower limit). Maybe. I'm worried that if someone jacks up max_connections to 1000 or 5000 or somesuch it could get pretty slow. Well, other things are going to get pretty slow as well, not just this one, which is why we suggest using a connection pooler with a reasonable limit. On the other hand, maybe those are things we ought to address sometime, so perhaps we don't want to be designing the old limitation into a new feature. A possibly crazy idea: instead of having a MaxBackends-sized array, how about some smaller array of insert-done-pointer-updating backends (a couple dozen or so), and if it's full, the next one has to sleep a bit until one of them becomes available. We could protect this with a PGSemaphore having as many counts as items are in the array. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Core Extensions relocation
Hello Greg, hello All, This is my first post at Hackers, so sorry if I am been a noob here, but I am pretty confused about how to create the extension pg_buffercache. First of all, I was trying to create using the old method by calling the pg_buffercache--1.0.sql directly. Then I discover the change that occurs recently to use CREATE EXTENSION, but even now I am getting the weird error: # select * from pg_available_extensions; name | default_version | installed_version | comment +-+---+- plpgsql| 1.0 | 1.0 | PL/pgSQL procedural language pg_buffercache | 1.0 | | examine the shared buffer cache (2 rows) postgres=# CREATE EXTENSION pg_buffercache SCHEMA pg_catalog; ERROR: syntax error at or near NO Right now, talking with some fellows at #postgresql they tell that the error is NOT occurring for they. This was about 9.1beta from git. But even so, I need to ask, because my production is on another versions: What is the right way to install this contrib at 9.0.1, 9.0.2 and 9.0.4 ? Many thanks, Best regards, vinnix On Thu, Jun 9, 2011 at 1:14 AM, Greg Smith g...@2ndquadrant.com wrote: Following up on the idea we've been exploring for making some extensions more prominent, attached is the first rev that I think may be worth considering seriously. Main improvement from the last is that I reorganized the docs to break out what I decided to tentatively name Core Extensions into their own chapter. No longer mixed in with the rest of the contrib modules, and I introduce them a bit differently. If you want to take a quick look at the new page, I copied it to http://www.2ndquadrant.us/docs/html/extensions.html I'm not completely happy on the wordering there yet. The use of both modules and extensions is probably worth eliminating, and maybe that continues on to doing that against the language I swiped from the contrib intro too. There's also a lot of shared text at the end there, common wording from that and the contrib page about how to install and migrate these extensions. Not sure how to refactor it out into another section cleanly though. Regression tests came up last time I posted this. Doesn't look like there are any for the modules I'm suggesting should be promoted. Only code issue I noticed during another self-review here is that I didn't rename contrib/pgrowlocks/pgrowlocks--unpackaged--1.0.sql cleanly, may need to do that one over again to get the commits as clean as possible. Updated code is at https://github.com/greg2ndQuadrant/postgres/tree/move-contrib too, and since this is painful as a patch the compare view at https://github.com/greg2ndQuadrant/postgres/compare/master...move-contribwill be easier for browsing the code changes. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Vinícius Abrahão Bazana Schmidt Desenvolvimento Dextra Sistemas www.dextra.com.br +55 19 3256-6722 Ramal 246 Este email é confidencial. Mais informações em: This message is confidential. More information at: www.dextra.com.br/confidencial.htm -- vi[nnix]™ aka: Vinícius Abrahão Bazana Schmidt vischmidt.wordpress.com twitter.com/vischmidt
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
On Thu, Jun 9, 2011 at 3:59 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. I believe we decided against that previously on the grounds that we don't want to add syscaches that might get really really big. In particular, there could be a LOT of labelled large objects floating around. -- 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] literature on write-ahead logging
On Thu, Jun 9, 2011 at 11:13 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of jue jun 09 10:55:45 -0400 2011: On Thu, Jun 9, 2011 at 10:34 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Slower than sleeping? Consider that this doesn't need to be done for each record insertion, only when you need to flush (maybe more than that, but I think that's the lower limit). Maybe. I'm worried that if someone jacks up max_connections to 1000 or 5000 or somesuch it could get pretty slow. Well, other things are going to get pretty slow as well, not just this one, which is why we suggest using a connection pooler with a reasonable limit. On the other hand, maybe those are things we ought to address sometime, so perhaps we don't want to be designing the old limitation into a new feature. A possibly crazy idea: instead of having a MaxBackends-sized array, how about some smaller array of insert-done-pointer-updating backends (a couple dozen or so), and if it's full, the next one has to sleep a bit until one of them becomes available. We could protect this with a PGSemaphore having as many counts as items are in the array. Maybe. It would have to be structured in such a way that you didn't perform a system call in the common case, I think. -- 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we need to work around brain-dead isupper() tests, maybe the best thing is to implement two versions of the loop: if (encoding is single byte) That seems like a clear improvement. It's a long way from perfect, but still worthwhile. Hmm ... while the above is easy enough to do in the backend, where we can look at pg_database_encoding_max_length, we have also got instances of this coding pattern in src/port/pgstrcasecmp.c. It's a lot less obvious how to make the test in frontend environments. Thoughts anyone? 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
On Thu, Jun 9, 2011 at 11:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we need to work around brain-dead isupper() tests, maybe the best thing is to implement two versions of the loop: if (encoding is single byte) That seems like a clear improvement. It's a long way from perfect, but still worthwhile. Hmm ... while the above is easy enough to do in the backend, where we can look at pg_database_encoding_max_length, we have also got instances of this coding pattern in src/port/pgstrcasecmp.c. It's a lot less obvious how to make the test in frontend environments. Thoughts anyone? I'm not sure if this helps at all, but an awful lot of those tests are against hard-coded strings that are known to contain only ASCII characters. Is there some way we can optimize this for that case? -- 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] release slippage
On 09.06.2011 17:46, Tom Lane wrote: Kevin Grittnerkevin.gritt...@wicourts.gov writes: The other pending patch relates to the false positives serialization failures and funny-looking pg_lock entries from not ignoring non-MVCC snapshots in SSI. I don't think Dan had any problem with the patch I offered, but I wasn't sure what to do about two functions. He researched that and proposed a way to handle those. That looked good to me on a first pass except that there was one line he left at NULL instead of plugging in the snapshot. That looks like an easily-fixed oversight. It will take me about an hour to review his changes in detail and re-test everything, although I don't expect any other issues. It seems odd not to include a change that was requested by Tom, Robert, and Heikki (and which Dan and I both put in the hours to have a patch before beta2) in beta2. Well, Heikki is lead committer for SSI; anything he's comfortable with pushing in today, he should push. Just keep in mind that it is too late to get a full buildfarm cycle in before beta2 wraps... so anything that goes in today had better be low-risk. I'm not planning to push anything before beta2 anymore. There is a bunch of SSI related issues pending now, some of which have patches floating around but I haven't had enough time to review them in depth. -- 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] release slippage
Robert Haas robertmh...@gmail.com writes: One other problem I'm realizing: didn't Peter ask Bruce to revert the change to make pg_upgrade ignore case differences in locale names, on the grounds that it was not safe? There was a hypothesis that it wasn't safe, but no very clear evidence either way. And we did have the field report of a platform where it'd be useful to allow a case discrepancy. I'm not uncomfortable with leaving that as-is pending further evidence. 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] .gitignore for some of cygwin files
Magnus Hagander mag...@hagander.net Thursday 09 of June 2011 16:25:07 On Thu, Jun 9, 2011 at 15:40, Radosław Smogura rsmog...@softperience.eu wrote: On Thu, 9 Jun 2011 14:12:59 +0200, Magnus Hagander wrote: On Wed, Jun 8, 2011 at 12:34, Radosław Smogura rsmog...@softperience.eu wrote: After compilation I got big list of differences about binaries and other files. You may see it in patch. Hmm. Does this really affect just cygwin, and not also mingw? I know msvc builds out-of-tree, but mingw doesn't, iirc? WHere do the EXEs go there? What's nbproject? As for the patch itself, we don't normally keep comments in the .gitignore files, I'm not sure if we want to start doing that... Sorry for trash this is `git status` after compilation on branch # .gitignore-for-cygwin.diff # blob_20110601.patch # nbproject/ # src/backend/postgres.def # src/backend/postgres.exe # src/backend/replication/libpqwalreceiver/liblibpqwalreceiverdll.def # src/backend/snowball/libdict_snowballdll.def # src/backend/utils/adt/blob.c # src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_micdll.d ef # src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mi cdll.def # src/backend/utils/mb/conversion_procs/euc2004_sjis2004/libeuc2004_sjis200 4dll.def # src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_micdll .def # src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjisd ll.def # src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_micdll .def # src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5d ll.def # src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_wi n1250dll.def # src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_micdll.d ef # src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_asciidll .def # src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5dll.d ef # src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyril licdll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc2004/libutf8_and_euc200 4dll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cnd ll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jpd ll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_krd ll.def # src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_twd ll.def # src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb1803 0dll.def # src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbkdll.def # src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso885 9dll.def # src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8 859_1dll.def # src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johabdll .def # src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjisdll.d ef # src/backend/utils/mb/conversion_procs/utf8_and_sjis2004/libutf8_and_sjis2 004dll.def # src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhcdll.def # src/backend/utils/mb/conversion_procs/utf8_and_win/libutf8_and_windll.def # src/bin/initdb/initdb.exe # src/bin/pg_basebackup/pg_basebackup.exe # src/bin/pg_config/pg_config.exe # src/bin/pg_controldata/pg_controldata.exe # src/bin/pg_ctl/pg_ctl.exe # src/bin/pg_dump/pg_dump.exe # src/bin/pg_dump/pg_dumpall.exe # src/bin/pg_dump/pg_restore.exe # src/bin/pg_resetxlog/pg_resetxlog.exe # src/bin/psql/psql.exe # src/bin/scripts/clusterdb.exe # src/bin/scripts/createdb.exe # src/bin/scripts/createlang.exe # src/bin/scripts/createuser.exe # src/bin/scripts/dropdb.exe # src/bin/scripts/droplang.exe # src/bin/scripts/dropuser.exe # src/bin/scripts/reindexdb.exe # src/bin/scripts/vacuumdb.exe # src/include/utils/blob.h # src/interfaces/ecpg/preproc/ecpg.exe # src/pl/plpgsql/src/libplpgsqldll.def # src/test/regress/libregressdll.def # src/test/regress/pg_regress.exe # src/timezone/zic.exe Based on this list, a global exclude for *.exe and lib*dll.def seems reasonable. Andrew, does that seem right to you as well? I definitely want to keep the nbproject thing out of there, that belongs in a personal exclude. utils/adt/blob.c and include/utils/blob.h - what is that? That's something that's actually from your development work and should not be ignored, right? Thats mine, sorry, looks I didn't get diff directly from master. Regards, Radek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
Re: [HACKERS] SLRU limits
On 09.06.2011 15:50, Robert Haas wrote: On Thu, Jun 9, 2011 at 7:46 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: While reviewing the SLRU code in predicate.c again, I remembered this old thread: http://archives.postgresql.org/pgsql-hackers/2010-12/msg02374.php SLRU has a limit of 64k segment files, because the files are named using four hex digits like 00CE. Kevin's math shows that that's just enough to store 2^32 four-byte integers, which wasn't enough for predicate.c, which needs to store uint64s. Kevin worked around that by simply limiting the max range of open xids to fit the SLRU limit, ie. 2^31. However, that math was based on 8k block size, and the situation is worse for smaller block sizes. If you set BLCKSZ to 2048 or less, pg_subtrans can only hold 1 billion transactions. With 1024 block size, only half a billion. I'm pretty unexcited about this. It's not terribly sane to keep a transaction open for half a billion XIDs anyway, because of VACUUM. I agree, but if you or your application is insane enough to do that anyway, it's not appropriate response for the system to give warnings about apparent XID wrap-around, and in the worst case mix up subtransactions belonging to different transactions, possibly leading to data loss. I have not actually tested that, but I don't see any safeguards to stop it from happening. Of course, another alternative is to add such safeguards. And I would guess that there's a lot more interest in raising BLCKSZ than lowering it. It might not be a bad idea to adopt the fix you propose anyway, but it doesn't seem urgent. I guess we could fix pg_subtrans by not allowing BLCKSZ 8k. That leaves the problem with pg_serial. Kevin has already worked around, but I'm not very happy with that workaround. If we don't want to change it wholesale, one option would be to support different lengths of filenames in slru.c for different slrus. At a quick glance, it seems pretty easy. That would allow keeping clog unchanged - that's the one that's most likely to have unforeseen consequences if changed. pg_subtrans and pg_serial are more ephemeral, they don't need to be retained over shutdown, so they seem less likely to cause trouble. That seems like the best option to me. -- 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] Core Extensions relocation
Vinicius Abrahao wrote: This is my first post at Hackers, so sorry if I am been a noob here, but I am pretty confused about how to create the extension pg_buffercache. This list is for talking about development of new features, normally on the latest development version of the software (right now 9.1). There is no such thing as CREATE EXTENSION in versions before that. A question like how do I install pg_buffercache for 9.0? should normally get sent to one of the other mailing lists; any of pgsql-performance, pgsql-admin, or pgsql-general would be appropriate to ask that at. This one really isn't. It's also better to avoid taking someone else's discussion and replying to it with your questions. But even so, I need to ask, because my production is on another versions: What is the right way to install this contrib at 9.0.1, 9.0.2 and 9.0.4 ? But since I happen to know this answer, here's an example from a RedHat derived Linux system running PostgreSQL 9.0.4, logged in as the postgres user: -bash-3.2$ locate pg_buffercache.sql /usr/pgsql-9.0/share/contrib/pg_buffercache.sql /usr/pgsql-9.0/share/contrib/uninstall_pg_buffercache.sql -bash-3.2$ psql -d pgbench -f /usr/pgsql-9.0/share/contrib/pg_buffercache.sql SET CREATE FUNCTION CREATE VIEW REVOKE REVOKE -bash-3.2$ psql -d pgbench -c select count(*) from pg_buffercache count --- 4096 The location of the file will be different on other platforms, but that's the basic idea of how you install it. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tuning autovacuum
Robert Haas wrote: Well, if there are more tables that need vacuuming than there are workers available at any given time, there will be a delay. We probably don't keep track of that delay at present, but we could. There are at least four interesting numbers to collect each time autovacuum runs: 1) This one, when was the threshold crossed. I believe one of the AV workers would have to pause periodically to update these if they're all busy doing work. 2) What time did the last autovacuum start at 3) How many dead rows were there at the point when it started 4) When did the last autovacuum end (currently the only value stored) There may be a 5th piece of state I haven't looked at yet worth exposing/saving, something related to how much work was skipped by the partial vacuum logic introduced in 8.4. I haven't looked at that code enough to know which is the right metric to measure its effectiveness by, but I have tis gut feel it's eventually going to be critical for distinguishing between the various common types of vacuum-heavy workloads that show up. All of these need to be stored in a system table/view, so that an admin can run a query to answer questions like: -What is AV doing right now? -How far behind is AV on tables it needs to clean but hasn't even started on? -How long is the average AV taking on my big tables? -As I change the AV parameters, what does it do to the runtimes against my big tables? As someone who is found by a lot of people whose problems revolve around databases with heavy writes or update churn, limitations in the current state of tracking what autovacuum does have been moving way up my priority list the last year. I now have someone who is always running autovacuum on the same table, 24x7. It finishes every two days, and when it does the 20% threshold is already crossed for it to start again. The wait until a worker was available problem isn't there, but I need a good wasy to track all of the other three things to have a hope of improving their situation. Right now getting the data I could use takes parsing log file output and periodic dumps of pg_stat_user_tables, then stitching the whole mess together. You can't run a heavily updated database in the TB+ range and make sense of what autovacuum is doing without a large effort matching output from log_autovacuum_min_duration and the stats that are visible in pg_stat_user_tables. It must get easier than that to support the sort of bigger tables it's possible to build now. And if this data starts getting tracked, we can start to move toward AV parameters that are actually aiming at real-world units, too. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Core Extensions relocation
Please do not piggyback on an unrelated thread to ask a question. Start a new thread. Vinicius Abrahao vinnix@gmail.com writes: postgres=# CREATE EXTENSION pg_buffercache SCHEMA pg_catalog; ERROR: syntax error at or near NO This looks like a syntax error in the pg_buffercache--1.0.sql file ... have you tampered with that at all? I believe BTW that you cannot specify pg_catalog as the target schema here. When I try that, I get: regression=# CREATE EXTENSION pg_buffercache SCHEMA pg_catalog; ERROR: permission denied to create pg_catalog.pg_buffercache DETAIL: System catalog modifications are currently disallowed. but it goes through fine without the SCHEMA clause. 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] procpid?
Can someone explain why pg_stat_activity has a column named procpid and not simply pid? 'pid' is that pg_locks uses, and 'procpid' is redundant (proc-process-id). A mistake? -- 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] Parameterized aggregate subquery (was: Pull up aggregate subquery)
2011/6/9 Robert Haas robertmh...@gmail.com: On Thu, Jun 9, 2011 at 2:28 AM, Hitoshi Harada umi.tan...@gmail.com wrote: BTW, as I changed title and design from the previous post, should I throw away the old commit fest entry and make the new one? Nah, just edit the existing entry and change the title. Also add a link to the new patch, of course. Ok, done. -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] release slippage
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: There is this pending patch, without which there are infrequent conditions under which the users could get a LOG level message and accumulate files in pg_serial: http://archives.postgresql.org/message-id/4dee7be402250003e...@gw.wicourts.gov That seems important to fix. It seems a lot more important to me than the other SSI issues. In any case, if this misses beta2, I'm not feeling like we'd need to push beta3 just for that reason. I'm not even 100% convinced that it needs to be in 9.1. We're talking about fixing a relatively rare false positive from a system which by its design necessitates that users be prepared for the possibility of false positives. Yeah, I was actually surprised that people felt it needed to go into 9.1, rather than being on a list of 9.2 follow-up improvements. Of course, now that it's done, I'd like to see it get into the release; but not at the cost of slipping the date.. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another issue with invalid XML values
On Jun2, 2011, at 01:34 , Florian Pflug wrote: On Jun2, 2011, at 00:02 , Noah Misch wrote: On Wed, Jun 01, 2011 at 06:16:21PM +0200, Florian Pflug wrote: Anyway, I'll try to come up with a patch that replaces xmlSetGenericErrorFunc() with xmlSetStructuredErrorFunc(). Sounds sensible. Will this impose any new libxml2 version dependency? xmlSetStructuredErrorFunc() seems to be available starting with libxml 2.6.0, release on Oct 20, 2003. Since we already require the version to be = 2.6.23, we should be OK. I won't have access to my PC the next few days, but I'll try to come up with a patch some time next week. Phew... I did manage to produce a patch, but it was way more work than I had intended to put into this. As it turns out, you loose the nicely formatted context information that libxml2 provides via the generic error func once you switch to structured error reporting. Registering handlers for both doesn't help either, since the generic error handler isn't called once you register a structured one. Fortunately, libxml does export xmlParserPrintFileContext() which generates these context messages. It, however, doesn't return a string, but instead passes them to the generic error handler (this time, independent from whether a structural error handler is registered or not). As it stood, the code assumed that all third-party library re-install their libxml error handlers before each library call, and thus didn't bother to restore the old error handler itself. Since I revamped the error handling anyway, I removed that requirement. There is now a function pg_xml_done() which restores the original error handler that we overwrote in pg_xml_init(). I also realized that some libxml error (like undefined namespace prefixes) must be ignored during xmlparse() and friends. Otherwise, it becomes impossible to build XML documents from individual fragments. pg_xml_init() therefore now takes an argument which specifies how strict the error checking is supposed to be. For the moment, only XPATH() uses the strict mode in which we report all errors. XMLPARSE() and friends only report parse errors, not namespace errors. Finally, I had to adjust contrib/xml2 because it uses some parts of the core XML support like pg_xml_init(). Heres the indended behaviour with the patch applied: We always use structured error handling. For now, the error messages pretty much resemble the old ones, but it's now easy to add additional information. XMLPARSE() and casting to XML check for parse errors only, like they do without the patch. They're also capable of reporting warnings, but I didn't find a case where the libxml parser generates a warning. XPATH() reports all errors and warnings. Trying to use XPATH() on a document with e.g. inconsistent namespace usage or invalid namespace URIs therefore now raises an error. This is *necessary* because libxml's XPath evaluator gets confused if it encounters e.g. invalid namespace URI and outputs invalid XML in response. contrib/xml2's behaviour hasn't changed. Patch is attached, and comments are welcome. best regards, Florian Pflug pg_xml_errorhandling.v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
On Thu, Jun 9, 2011 at 16:40, Andrew Dunstan and...@dunslane.net wrote: On 06/09/2011 10:25 AM, Magnus Hagander wrote: Based on this list, a global exclude for *.exe and lib*dll.def seems reasonable. Andrew, does that seem right to you as well? That plus an entry in src/backend/.gitignore for /postgres.def. Right. Agreed and committed. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SLRU limits
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 09.06.2011 15:50, Robert Haas wrote: And I would guess that there's a lot more interest in raising BLCKSZ than lowering it. It might not be a bad idea to adopt the fix you propose anyway, but it doesn't seem urgent. I guess we could fix pg_subtrans by not allowing BLCKSZ 8k. That leaves the problem with pg_serial. Kevin has already worked around, but I'm not very happy with that workaround. If we don't want to change it wholesale, one option would be to support different lengths of filenames in slru.c for different slrus. At a quick glance, it seems pretty easy. That would allow keeping clog unchanged - that's the one that's most likely to have unforeseen consequences if changed. pg_subtrans and pg_serial are more ephemeral, they don't need to be retained over shutdown, so they seem less likely to cause trouble. That seems like the best option to me. I agree with Robert that this is completely not urgent. If you want to fool with it for 9.2, fine, but let's not destabilize 9.1 for it. (BTW, while I've not looked at the SLRU code in several years, I'm quite unconvinced that this is only a matter of filename lengths.) 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] tuning autovacuum
Excerpts from Tom Lane's message of mié jun 08 21:50:22 -0400 2011: Robert Haas robertmh...@gmail.com writes: I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: As far as I recall that logic, there is no delay between when we know that a table needs vacuumed and when we do it. I don't see the point of introducing any such delay, either. Autovacuum checks each table twice. When it first connects to a database it grabs a complete list of relations needing vacuum. Then it starts vacuuming, and before processing each relation, it rechecks. So there *is* a delay, which corresponds to how long it took to process the tables that preceded it in the list. Robert's suggestion would seem to make sense. I'm not sure how to implement it: do we want some more (highly volatile) data points in pgstat? Do we need some other mechanism? This seems like a use case for pg_class_nt (see http://archives.postgresql.org/pgsql-patches/2006-06/msg00114.php) In any case, given the rebalancing feature of vacuum_cost_delay (which increases the delay the more workers there are), the only solution to the problem of falling behind is reducing the delay parameter. If you just add more workers, they start working more slowly. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
Bruce Momjian br...@momjian.us writes: Can someone explain why pg_stat_activity has a column named procpid and not simply pid? 'pid' is that pg_locks uses, and 'procpid' is redundant (proc-process-id). A mistake? Mistake or not, it's about half a dozen releases too late to change it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
* Kohei KaiGai (kai...@kaigai.gr.jp) wrote: The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. Perhaps I'm missing it, but.. why is this necessary to implement such a cache? Also, I thought the SELinux userspace libraries provided a cache solution? This issue is hardly unique to SELinux in PostgreSQL... THanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] WALInsertLock contention
On Wed, Jun 8, 2011 at 11:30 PM, Merlin Moncure mmonc...@gmail.com wrote: The heap pages that have been marked this way may or may not have to be off limits from the backend other than the one that did the marking, and if they have to be off limits logically, there may be no realistic path to make them so. After some more thought, plus a bit of off-list coaching from Haas, I see now the whole approach is basically a non-starter due to the above. Heap pages *are* off limits, because once deferred they can't be scribbled on and committed by other transactions -- that would violate the 'wal before data' rule. To make it 'work', you'd have to implement shared memory machinery to do cooperative flushing as suggested upthread (complex, nasty) or simply block on deferred pages...which would be a deadlock factory. Oh well. :( merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
2011/6/9 Robert Haas robertmh...@gmail.com: On Thu, Jun 9, 2011 at 3:59 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. I believe we decided against that previously on the grounds that we don't want to add syscaches that might get really really big. In particular, there could be a LOT of labelled large objects floating around. (Sorry, I missed to Cc: pgsql-hackers, so send again) As long as we use syscache mechanism to hold security label of relation or other cached objects, do you think it cause no troubles? If so, it may be a good idea to distinct cases when we try to reference the security label of blobs and others. -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
On Thu, Jun 9, 2011 at 11:54 AM, Bruce Momjian br...@momjian.us wrote: Can someone explain why pg_stat_activity has a column named procpid and not simply pid? 'pid' is that pg_locks uses, and 'procpid' is redundant (proc-process-id). A mistake? Well, we refer to the slots that backends use as procs (really PGPROC), so I'm guessing that this was intended to mean the pid associated with the proc. It might not be the greatest name but I can't see changing it 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] tuning autovacuum
Alvaro Herrera alvhe...@commandprompt.com writes: In any case, given the rebalancing feature of vacuum_cost_delay (which increases the delay the more workers there are), the only solution to the problem of falling behind is reducing the delay parameter. If you just add more workers, they start working more slowly. Yeah. Note also that if you're not running a pretty recent minor release, you're exposed to this bug: Author: Tom Lane t...@sss.pgh.pa.us Branch: master [b58c25055] 2010-11-19 22:28:20 -0500 Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500 Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500 Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500 Fix leakage of cost_limit when multiple autovacuum workers are active. When using default autovacuum_vac_cost_limit, autovac_balance_cost relied on VacuumCostLimit to contain the correct global value ... but after the first time through in a particular worker process, it didn't, because we'd trashed it in previous iterations. Depending on the state of other autovac workers, this could result in a steady reduction of the effective cost_limit setting as a particular worker processed more and more tables, causing it to go slower and slower. Spotted by Simon Poole (bug #5759). Fix by saving and restoring the GUC variables in the loop in do_autovacuum. In passing, improve a few comments. Back-patch to 8.3 ... the cost rebalancing code has been buggy since it was put in. 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] literature on write-ahead logging
Robert Haas robertmh...@gmail.com writes: [ lots of interesting stuff about WAL optimization snipped ] ... Second, they aren't really using locks, unless you count bus locks - they appear to have implemented most or all of it via CAS-based lock-free algorithms, which is probably well-justified optimization effort. FWIW, I'm pretty suspicious of claims that lock-free data structures will be some kind of magic bullet. As far as I can tell, a very large part of our contention problems on many-core machines stem from the CPUs fighting over cache line ownership. Avoiding an explicit lock in favor of hardware primitive test-and-modify instructions isn't going to do a darn thing to improve that. And contorting the algorithms until they fit into what's portably available in that line could easily be a net loss. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
2011/6/9 Stephen Frost sfr...@snowman.net: * Kohei KaiGai (kai...@kaigai.gr.jp) wrote: The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. Perhaps I'm missing it, but.. why is this necessary to implement such a cache? Also, I thought the SELinux userspace libraries provided a cache solution? This issue is hardly unique to SELinux in PostgreSQL... I'm concerned about its interface, although it might be suitable for X-Windows... Its avc interface identifies security context using a pointer of malloc()'ed cstring. In our case, we need to look up this security context on the hash managed by libselinux using the result of syscache lookup. It is quite nonsense. In addition, avc of libselinux confirms whether the security policy is reloaded for each avc lookup, unless we launch a system state monitoring thread. But, it is not a suitable design to launch a worker thread for each pgsql backend. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore for some of cygwin files
Excerpts from Tom Lane's message of jue jun 09 09:42:02 -0400 2011: =?UTF-8?Q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes: On Thu, 9 Jun 2011 14:12:59 +0200, Magnus Hagander wrote: What's nbproject? Just configuration from some editor. It looks like any move in project creates this entry in .gitignore If you've got random third-party tools that clutter the source tree, you should use a personal .gitignore file to ignore them. We already established the principle that emacs backup files have to be ignored on a personal level, and I don't see why we'd do it differently for Windows tools. I agree with that, though the *dll.def files are ours and probably deserve a global .gitignore entry. As for executables, I think the local .gitignore files in each subdir should be tweaked so that they catch the .exe extension, so src/backend/.gitignore which currently includes /postgres should also have /postgres.exe, and so on. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fw: [BUGS] BUG #6011: Some extra messages are output in the event log at PostgreSQL startup
On Mon, May 23, 2011 at 16:49, MauMau maumau...@gmail.com wrote: From: Kevin Grittner kevin.gritt...@wicourts.gov MauMau maumau...@gmail.com wrote: Make pg_ctl's -s option suppress informational event logging. This will ultimately be up to a committer (and I'm not one), but to me it seems reasonable to back-patch if it is addressed this way. the PostgreSQL Windows service must be registered by pg_ctl register -s to make use of this patch. However, according to the current manual, pg_ctl register does not take -s option. Actually, pg_ctl does not refuse to take -s, so this is not a big problem. pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-w] [-t seconds] [-o options] When you write the patch, be sure to include a fix for the docs here, please. I attached a patch to fix this bug. I performed the following tests successfully on Windows Vista (32-bit). snip test cases I wish this will be back-patched in the next minor release. Thanks, sorry about the delay, patch applied. I backpatched it back to 8.3 which is as far as it applied cleanly - I'm not excited enough about it to bother a manual backpatch to 8.2. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tuning autovacuum
On Thu, Jun 9, 2011 at 12:25 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Tom Lane's message of mié jun 08 21:50:22 -0400 2011: Robert Haas robertmh...@gmail.com writes: I think it'd be really useful to expose some more data in this area though. One random idea is - remember the time at which a table was first observed to need vacuuming. Clear the timestamp when it gets vacuumed. Then you can do: As far as I recall that logic, there is no delay between when we know that a table needs vacuumed and when we do it. I don't see the point of introducing any such delay, either. Autovacuum checks each table twice. When it first connects to a database it grabs a complete list of relations needing vacuum. Then it starts vacuuming, and before processing each relation, it rechecks. So there *is* a delay, which corresponds to how long it took to process the tables that preceded it in the list. There's that, plus as of 9.1 autovacuum will skip tables that it can't immediately take ShareUpdateExclusiveLock on (except in case of impending wraparound). So in the worst case we might have tables left over from a previous run of the autovacuum worker that never got processed. And, of course, in any version, it's also possible for autovacuum to get booted off the table by a conflicting lock request that arrives midway through; the system might get shut down in the middle of the run; autovacuum might error out in medias res; etc. Robert's suggestion would seem to make sense. I'm not sure how to implement it: do we want some more (highly volatile) data points in pgstat? Do we need some other mechanism? This seems like a use case for pg_class_nt (see http://archives.postgresql.org/pgsql-patches/2006-06/msg00114.php) I haven't looked at the implementation, but I like the concept. It's tempting to just shove everything in pgstat, but we already have scalability limits there. In any case, given the rebalancing feature of vacuum_cost_delay (which increases the delay the more workers there are), the only solution to the problem of falling behind is reducing the delay parameter. If you just add more workers, they start working more slowly. Unless, of course, you already have the delay set to zero, in which case starting more workers might be all you can do. The case where this really matters is with big tables, I think. If you have #(big tables) #(autovacuum workers), it seems like you could end up in a situation where the big tables pin down all the workers and no small tables can get vacuumed for a long time. Perhaps we can tune the algorithm to compensate for that in some way, but I think we need more raw data to work with first. -- 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] .gitignore for some of cygwin files
Alvaro Herrera alvhe...@commandprompt.com writes: I agree with that, though the *dll.def files are ours and probably deserve a global .gitignore entry. Agreed, and that's done. As for executables, I think the local .gitignore files in each subdir should be tweaked so that they catch the .exe extension, so src/backend/.gitignore which currently includes /postgres should also have /postgres.exe, and so on. I agree with the committed patch, which just added one global *.exe pattern. I don't see the value of having to maintain a second .gitignore entry in every subdirectory that builds an executable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad UI design: pg_ctl and data_directory
Robert Haas wrote: On Wed, Jun 1, 2011 at 7:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mi? jun 01 18:22:56 -0400 2011: ISTM that it would be useful to run postgres in a mode where it doesn't actually try to start up the database, but parses postgresql.conf and then exits, perhaps printing out the value of a certain GUC as it does so. ?In this case, data_directory. I had the same thought, and wondered if we could use the feature elsewhere. This was suggested quite some time ago, IIRC, but we never got round to it. The main problem in the current context is that it only fixes the issue so long as you ignore the possibility that relevant values were specified on the command line or via environment variables, rather than coming directly from the config file. ?PGDATA is thus a particular hazard here: all you need is to be running with a different PGDATA setting in your environment than was used when pg_ctl start was issued, and you're hosed. I guess I'm missing something here. If you change PGDATA, you're going to be working on a different cluster, but that's what you asked for. I guess there could be a problem if you used pg_ctl -D dir start, and postgres --tell-me-the-data-dir relied on PGDATA in telling you what the data directory should be, but that seems simple to work around: just have -D dir set $PGDATA before invoking postgres. Is this just part of our existing TODOs? -- 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] Another pgindent run before beta2?
Andrew Dunstan wrote: On 06/08/2011 02:29 PM, Tom Lane wrote: Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. OK, I have made sure the list on the buildfarm is up to date. OK, can I run it in one hour? 1800 GMT? -- 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] Another pgindent run before beta2?
Bruce Momjian br...@momjian.us writes: Andrew Dunstan wrote: On 06/08/2011 02:29 PM, Tom Lane wrote: Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. OK, I have made sure the list on the buildfarm is up to date. OK, can I run it in one hour? 1800 GMT? I have nothing else planned for today except a pass over the release notes, so go for it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] literature on write-ahead logging
On Thu, Jun 9, 2011 at 12:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: [ lots of interesting stuff about WAL optimization snipped ] ... Second, they aren't really using locks, unless you count bus locks - they appear to have implemented most or all of it via CAS-based lock-free algorithms, which is probably well-justified optimization effort. FWIW, I'm pretty suspicious of claims that lock-free data structures will be some kind of magic bullet. As far as I can tell, a very large part of our contention problems on many-core machines stem from the CPUs fighting over cache line ownership. Avoiding an explicit lock in favor of hardware primitive test-and-modify instructions isn't going to do a darn thing to improve that. And contorting the algorithms until they fit into what's portably available in that line could easily be a net loss. That's possible. It would definitely be possible to get slap-happy with CAS, and I'm not eager to go there just because we can. On the other hand, these lock-free algorithms seem to be springing up like kudzu, so I doubt we'll be able to avoid them forever. People wouldn't keep doing it if it didn't solve some problem that they have. My suspicion is that in many cases there are better ways to optimize that avoid the need to serialize things altogether (as the fastlock patch does). But WAL insertion might be an exception: nobody seems to have any good ideas about how to do that without serial locking; unless someone does, don't see another alternative other than to compress the critical section down to as few machine language instructions as possible. Anyway it's all speculation at this point: when I or someone else has time to write some actual code, then we'll benchmark it and see what happens. -- 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] Another pgindent run before beta2?
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Andrew Dunstan wrote: On 06/08/2011 02:29 PM, Tom Lane wrote: Should we consider doing $SUBJECT? Given the plan to branch off 9.1 after we make the beta release, I think it's now or never for a second pgindent run for 9.1. OK, I have made sure the list on the buildfarm is up to date. OK, can I run it in one hour? 1800 GMT? I have nothing else planned for today except a pass over the release notes, so go for it. OK, unless I hear objections ... -- 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] .gitignore for some of cygwin files
Excerpts from Tom Lane's message of jue jun 09 12:57:46 -0400 2011: Alvaro Herrera alvhe...@commandprompt.com writes: I agree with that, though the *dll.def files are ours and probably deserve a global .gitignore entry. Agreed, and that's done. Oh, I see ... I haven't gotten the commit message yet. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
On Thu, Jun 9, 2011 at 12:39 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/6/9 Robert Haas robertmh...@gmail.com: On Thu, Jun 9, 2011 at 3:59 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. I believe we decided against that previously on the grounds that we don't want to add syscaches that might get really really big. In particular, there could be a LOT of labelled large objects floating around. (Sorry, I missed to Cc: pgsql-hackers, so send again) As long as we use syscache mechanism to hold security label of relation or other cached objects, do you think it cause no troubles? Maybe, but why do we need it? -- 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 Thu, Jun 9, 2011 at 12:54 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/6/9 Stephen Frost sfr...@snowman.net: * Kohei KaiGai (kai...@kaigai.gr.jp) wrote: The only modification by this patch to the core routine is a new syscache for pg_seclabel system catalog. The SECLABELOID enables to reference security label of the object using syscache interface. Perhaps I'm missing it, but.. why is this necessary to implement such a cache? Also, I thought the SELinux userspace libraries provided a cache solution? This issue is hardly unique to SELinux in PostgreSQL... I'm concerned about its interface, although it might be suitable for X-Windows... Its avc interface identifies security context using a pointer of malloc()'ed cstring. In our case, we need to look up this security context on the hash managed by libselinux using the result of syscache lookup. It is quite nonsense. So you're going to depend on the syscache not to move the pointers around? Yikes. In addition, avc of libselinux confirms whether the security policy is reloaded for each avc lookup, unless we launch a system state monitoring thread. But, it is not a suitable design to launch a worker thread for each pgsql backend. I thought there was something you could mmap() into each backend...? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
Robert Haas wrote: On Thu, Jun 9, 2011 at 11:54 AM, Bruce Momjian br...@momjian.us wrote: Can someone explain why pg_stat_activity has a column named procpid and not simply pid? ?'pid' is that pg_locks uses, and 'procpid' is redundant (proc-process-id). ?A mistake? Well, we refer to the slots that backends use as procs (really PGPROC), so I'm guessing that this was intended to mean the pid associated with the proc. It might not be the greatest name but I can't see changing it now. Agreed. Just pointing out this mistake slipped through. -- 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] Invalid byte sequence for encoding UTF8, caused due to non wide-char-aware downcase_truncate_identifier() function on WINDOWS
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 9, 2011 at 11:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hmm ... while the above is easy enough to do in the backend, where we can look at pg_database_encoding_max_length, we have also got instances of this coding pattern in src/port/pgstrcasecmp.c. It's a lot less obvious how to make the test in frontend environments. Thoughts anyone? I'm not sure if this helps at all, but an awful lot of those tests are against hard-coded strings that are known to contain only ASCII characters. Is there some way we can optimize this for that case? For the places where we're just looking for a match to a fixed all-ASCII string, an ASCII-only downcasing would be sufficient, and would eliminate the whole problem. But I doubt all the callers fall into that class. What I'm particularly worried about at the moment is whether we are assuming anywhere that the frontend side can duplicate the backend's identifier downcasing behavior. That seems like a complete morass, because (1) they might not have the same locale, (2) they might not have the same encoding, (3) even if they do, the same locale is known to behave differently on different platforms. 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] wrong message on REASSIGN OWNED
Hi, on shdepReassignOwned() we have this message, which is obviously wrong we are not dropping objects just reassigning them... ereport(ERROR, (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST), errmsg(cannot drop objects owned by %s because they are required by the database system, getObjectDescription(obj; but haven't thought of a good way of rephrase it -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI work for 9.1
On Thu, Jun 09, 2011 at 07:06:18AM -0500, Kevin Grittner wrote: Sounds reasonable, but why did you pass the snapshot to the PredicateLockPage() call but not the PredicateLockRelation() call? Oversight? Yep, just an oversight; long day yesterday. I'll fix the patch shortly (unless you can get to it first, in which case I wouldn't complain) Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
