[HACKERS] postgresql-9.5alpha1 packaging
As there are additional or moved binaries in comparison to 9.4 -usr/bin/pg_archivecleanup.exe -usr/bin/pg_rewind.exe -usr/bin/pg_test_fsync.exe -usr/bin/pg_test_timing.exe -usr/bin/pg_upgrade.exe -usr/bin/pg_xlogdump.exe -usr/bin/pgbench.exe any suggestion where to fit them ? Current split on cygwin packages is: $ cygcheck -l postgresql |grep exe /usr/sbin/initdb.exe /usr/sbin/pg_controldata.exe /usr/sbin/pg_ctl.exe /usr/sbin/pg_resetxlog.exe /usr/sbin/postgres.exe $ cygcheck -l postgresql-client |grep exe /usr/bin/clusterdb.exe /usr/bin/createdb.exe /usr/bin/dropdb.exe /usr/bin/pg_dump.exe /usr/bin/pg_dumpall.exe /usr/bin/pg_basebackup.exe /usr/bin/pg_isready.exe /usr/bin/pg_receivexlog.exe /usr/bin/pg_recvlogical.exe /usr/bin/psql.exe /usr/bin/reindexdb.exe /usr/sbin/createlang.exe /usr/sbin/createuser.exe /usr/sbin/droplang.exe /usr/sbin/dropuser.exe /usr/sbin/pg_restore.exe /usr/sbin/vacuumdb.exe cygcheck -l postgresql-contrib |grep exe /usr/lib/postgresql/bin/oid2name.exe /usr/lib/postgresql/bin/pgbench.exe /usr/lib/postgresql/bin/pg_archivecleanup.exe /usr/lib/postgresql/bin/pg_standby.exe /usr/lib/postgresql/bin/pg_test_fsync.exe /usr/lib/postgresql/bin/pg_test_timing.exe /usr/lib/postgresql/bin/pg_upgrade.exe /usr/lib/postgresql/bin/pg_xlogdump.exe /usr/lib/postgresql/bin/vacuumlo.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] PostgreSQL 9.5 Alpha 1 build fail with perl 5.22
On 7/3/2015 2:31 PM, Marco Atzeri wrote: On 7/3/2015 8:19 AM, Michael Paquier wrote: On Fri, Jul 3, 2015 at 2:47 PM, Marco Atzeri marco.atz...@gmail.com wrote: On 7/2/2015 5:16 PM, Dave Page wrote: -lldap hstore_plperl.o: In function `hstore_to_plperl': /pub/devel/postgresql/prova/postgresql-9.5alpha1-1.i686/src/postgresql-9.5alpha1 /contrib/hstore_plperl/hstore_plperl.c:16: undefined reference to `hstoreUpgrade ' for what I see the hstore_plperl link has a double problem. It requires a link to hstore as it also requires a link to perl. Attached patch for solving this and a similar issue with python. Regards MArco --- origsrc/postgresql-9.5alpha1/contrib/hstore_plperl/Makefile 2015-06-29 21:42:18.0 +0200 +++ src/postgresql-9.5alpha1/contrib/hstore_plperl/Makefile 2015-07-04 08:20:54.077873800 +0200 @@ -30,6 +30,12 @@ override CPPFLAGS += -DPLPERL_HAVE_UID_G SHLIB_LINK += ../hstore/libhstore.a $(wildcard ../../src/pl/plperl/libperl*.a) endif +ifeq ($(PORTNAME), cygwin) +# This means we need an in-tree build on Windows, not a pgxs build +SHLIB_LINK += -L../hstore -lhstore -L$(perl_archlibexp)/CORE -lperl +endif + + # As with plperl we need to make sure that the CORE directory is included # last, probably because it sometimes contains some header files with names # that clash with some of ours, or with some that we include, notably on --- origsrc/postgresql-9.5alpha1/contrib/hstore_plpython/Makefile 2015-06-29 21:42:18.0 +0200 +++ src/postgresql-9.5alpha1/contrib/hstore_plpython/Makefile 2015-07-04 08:39:30.343835200 +0200 @@ -28,6 +28,12 @@ ifeq ($(PORTNAME), win32) SHLIB_LINK += ../hstore/libhstore.a $(wildcard ../../src/pl/plpython/libpython*.a) $(wildcard ../../src/pl/plpython/libplpython*.a) endif +ifeq ($(PORTNAME), cygwin) +# This means we need an in-tree build on Windows, not a pgxs build +SHLIB_LINK += -L../hstore -lhstore -L../../src/pl/plpython -lplpython2 $(python_libspec) +endif + + REGRESS_OPTS += --load-extension=hstore ifeq ($(python_majorversion),2) REGRESS_OPTS += --load-extension=plpythonu --load-extension=hstore_plpythonu --- origsrc/postgresql-9.5alpha1/contrib/ltree_plpython/Makefile 2015-06-29 21:42:18.0 +0200 +++ src/postgresql-9.5alpha1/contrib/ltree_plpython/Makefile2015-07-04 08:40:09.328303700 +0200 @@ -28,6 +28,12 @@ ifeq ($(PORTNAME), win32) SHLIB_LINK += $(wildcard ../../src/pl/plpython/libpython*.a) endif +ifeq ($(PORTNAME), cygwin) +# This means we need an in-tree build on Windows, not a pgxs build +SHLIB_LINK += -L../../src/pl/plpython -lplpython2 $(python_libspec) +endif + + REGRESS_OPTS += --load-extension=ltree ifeq ($(python_majorversion),2) REGRESS_OPTS += --load-extension=plpythonu --load-extension=ltree_plpythonu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] C# reading result from a function
Hi guys, I have a problem with a sipmle function in postgresql. The func()
calculates two integers to a result and returns it. The result is in the DB,
but not in the Console Line in C#. So for example when i have par1 = 2 and
par2 = 5 I get an DB entry with 7 in the result row, but the ConsoleLine is
empty.
What is wrong here?
BEGIN
Result := par1 + par2;
insert into t1 (par1, par2, res) values (par1, par2, Result);
RETURN Result;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_func(integer, integer)
OWNER TO Test;
*
c#:
.
DbParameter giveBack = cmd.CreateParameter();
giveBack.DbType = DbType.Int32;
giveBack.ParameterName = Result;
giveBack.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(giveBack);
cmd.Parameters[par1].Value = 2;
cmd.Parameters[par2].Value = 11;
connection.Open();
if (connection.State == System.Data.ConnectionState.Open) {
Console.WriteLine(cmd.Parameters[par1].Value);
Int32 result = cmd.ExecuteNonQuery();
Console.WriteLine(Status: {0}: Result: {1}, result,
cmd.Parameters[Result].Value);
}
Console.WriteLine for par1 returns 2. Console.Writline for Result returns
nothing.
--
View this message in context:
http://postgresql.nabble.com/C-reading-result-from-a-function-tp5856560.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] multivariate statistics / patch v7
Hello Horiguchi-san! On 07/03/2015 07:30 AM, Kyotaro HORIGUCHI wrote: Hello, I started to work on this patch. attached is v7 of the multivariate stats patch. The main improvement is major refactoring of the clausesel.c portion - splitting the awfully long spaghetti-style functions into smaller pieces, making it much more understandable etc. Thank you, it looks clearer. I have some comment for the brief look at this. This patchset is relatively large so I will comment on per-notice basis.. which means I'll send comment before examining the entire of this patchset. Sorry in advance for the desultory comments. Sure. If you run into something that's not clear enough, I'm happy to explain that (I tried to cover all the important details in the comments, but it's a large patch, indeed.) === General comments: - You included unnecessary stuffs such like regression.diffs in these patches. A :-/ Will fix. - Now OID 3307 is used by pg_stat_file. I moved pg_mv_stats_dependencies_info/show to 3311/3312. Will fix while rebasing to current master. - Single-variate stats have a mechanism to inject arbitrary values as statistics, that is, get_relation_stats_hook and the similar stuffs. I want the similar mechanism for multivariate statistics, too. Fair point, although I'm not sure where should we place the hook, how exactly should it be defined and how useful that would be in the end. Can you give an example of how you'd use such hook? I've never used get_relation_stats_hook, but if I get it right, the plugins can use the hook to create the stats (for each column), either from scratch or tweaking the existing stats. I'm not sure how this should work with multivariate stats, though, because there can be arbitrary number of stats for a column, and it really depends on all the clauses (so examine_variable() seems a bit inappropriate, as it only sees a single variable at a time). Moreover, with multivariate stats (a) there may be arbitrary number of stats for a column (b) only some of the stats end up being used for the estimation I see two or three possible places for calling such hook: (a) at the very beginning, after fetching the list of stats - sees all the existing stats on a table - may add entirely new stats or tweak the existing ones (b) after collecting the list of variables compatible with multivariate stats - like (a) and additionally knows which columns are interesting for the query (but only with respect to the existing stats) (c) after optimization (selection of the right combination if stats) - like (b), but can't affect the optimization But I can't really imagine anyone building multivariate stats on the fly, in the hook. It's more complicated, though, because the query may call clauselist_selectivity multiple times, depending on how complex the WHERE clauses are. 0001: - I also don't think it is right thing for expression_tree_walker to recognize RestrictInfo since it is not a part of expression. Yes. In my working git repo, I've reworked this to use the second option, i.e. adding RestrictInfo pull_(varno|varattno)_walker: https://github.com/tvondra/postgres/commit/2dc79b914c759d31becd8ae670b37b79663a595f Do you think this is the correct solution? If not, how to fix it? 0003: - In clauselist_selectivity, find_stats is uselessly called for single clause. This should be called after the clauselist found to consist more than one clause. Ok, will fix. - Searching vars to be compared with mv-stat columns which find_stats does should stop at disjunctions. But this patch doesn't behave so and it should be an unwanted behavior. The following steps shows that. Why should it stop at disjunctions? There's nothing wrong with using multivariate stats to estimate OR-clauses, IMHO. =# CREATE TABLE t1 (a int, b int, c int); =# INSERT INTO t1 (SELECT a, a * 2, a * 3 FROM generate_series(0, ) a); =# EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b = 2 OR c = 3; Seq Scan on t1 (cost=0.00..230.00 rows=1 width=12) =# ALTER TABLE t1 ADD STATISTICS (HISTOGRAM) ON (a, b, c); =# ANALZYE t1; =# EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b = 2 OR c = 3; Seq Scan on t1 (cost=0.00..230.00 rows=268 width=12) Rows changed unwantedly. That has nothing to do with OR clauses, but rather with using a type of statistics that does not fit the data and queries. Histograms are quite inaccurate for discrete data and equality conditions - in this case the clauses probably match one bucket, and so we use 1/2 the bucket as an estimate. There's nothing wrong with that. So let's use MCV instead: ALTER TABLE t1 ADD STATISTICS (MCV) ON (a, b, c); ANALYZE t1; EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b = 2 OR c = 3; QUERY PLAN - Seq Scan on t1 (cost=0.00..230.00 rows=1
Re: [HACKERS] PostgreSQL 9.5 Alpha 1 build fail with perl 5.22
Marco Atzeri marco.atz...@gmail.com writes: for what I see the hstore_plperl link has a double problem. It requires a link to hstore as it also requires a link to perl. Attached patch for solving this and a similar issue with python. +ifeq ($(PORTNAME), cygwin) +# This means we need an in-tree build on Windows, not a pgxs build +SHLIB_LINK += -L../hstore -lhstore -L$(perl_archlibexp)/CORE -lperl +endif [ and likewise for the other contrib transform modules ] I wondered how come we had not seen this problem in the buildfarm, but the answer appears to be that our only working Cygwin critter (brolga) doesn't build any of the optional PLs, so it skips these modules altogether. Seems like we need to improve that situation. Also, I noted that the regular win32 path in these makefiles says, eg, ifeq ($(PORTNAME), win32) # these settings are the same as for plperl override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment # This means we need an in-tree build on Windows, not a pgxs build SHLIB_LINK += ../hstore/libhstore.a $(wildcard ../../src/pl/plperl/libperl*.a) endif It's not apparent to me how that works at all. It seems to specify hard-linking a copy of hstore as well as a copy of plperl into the shlib for hstore_plperl. Then at runtime, there will *also* be the hstore and plperl shlibs in memory. At best that means substantial memory bloat, but it seems likely to me that it would fail altogether, particular for plperl which has got a substantial amount of semantically- important static storage. Two copies of that storage will not end well. 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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?
In summary, the X^1.5 correction seems to work pretty well. It doesn't completely eliminate the problem, but it makes it a lot better. I've looked at the maths. I think that the load is distributed as the derivative of this function, that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that is the finishing load is 1.5 the average load, just before fsyncing files. This looks like a recipee for a bad time: I would say this is too large an overload. I would suggest a much lower value, say around 1.1... The other issue with this function is that it should only degrade performance by disrupting the write distribution if someone has WAL on a different disk. As I understand it this thing does only make sense if the WAL the data are on the samee disk. This really suggest a guc. I have ran some tests with this patch and the detailed results of the runs are attached with this mail. I do not understand really the aggregated figures in the files attached. I guess that maybe between end markers there is a summary of figures collected for 28 backends over 300-second runs (?), but I do not know what the min/max/avg/sum/count figures are about. I thought the patch should show difference if I keep max_wal_size to somewhat lower or moderate value so that checkpoint should get triggered due to wal size, but I am not seeing any major difference in the writes spreading. I'm not sure I understand your point. I would say that at full speed pgbench the disk is always busy writing as much as possible, either checkpoint writes or wal writes, so the write load as such should not be that different anyway? I understood that the point of the patch is to check whether there is a tps dip or not when the checkpoint begins, but I'm not sure how this can be infered from the many aggregated data you sent, and from my recent tests the tps is very variable anyway on HDD. -- Fabien. -- Sent 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.5 Alpha 1 build fail with perl 5.22
On 07/04/2015 11:02 AM, Tom Lane wrote: Marco Atzeri marco.atz...@gmail.com writes: for what I see the hstore_plperl link has a double problem. It requires a link to hstore as it also requires a link to perl. Attached patch for solving this and a similar issue with python. +ifeq ($(PORTNAME), cygwin) +# This means we need an in-tree build on Windows, not a pgxs build +SHLIB_LINK += -L../hstore -lhstore -L$(perl_archlibexp)/CORE -lperl +endif [ and likewise for the other contrib transform modules ] I wondered how come we had not seen this problem in the buildfarm, but the answer appears to be that our only working Cygwin critter (brolga) doesn't build any of the optional PLs, so it skips these modules altogether. Seems like we need to improve that situation. Also, I noted that the regular win32 path in these makefiles says, eg, ifeq ($(PORTNAME), win32) # these settings are the same as for plperl override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment # This means we need an in-tree build on Windows, not a pgxs build SHLIB_LINK += ../hstore/libhstore.a $(wildcard ../../src/pl/plperl/libperl*.a) endif It's not apparent to me how that works at all. It seems to specify hard-linking a copy of hstore as well as a copy of plperl into the shlib for hstore_plperl. Then at runtime, there will *also* be the hstore and plperl shlibs in memory. At best that means substantial memory bloat, but it seems likely to me that it would fail altogether, particular for plperl which has got a substantial amount of semantically- important static storage. Two copies of that storage will not end well. Windows finds the DLL in its path. I just tested this by removing the intree pl DLLs after installing and then running contrib installcheck, and it worked fine. Whether or not it actually loads the DLL twice when it can find the intree DLL I don't know for sure, maybe someone with more Windows-fu than me can inform our ignorance. 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
[HACKERS] xlc atomics
On Wed, Jun 25, 2014 at 07:14:34PM +0200, Andres Freund wrote:
* gcc, msvc work. acc, xlc, sunpro have blindly written support which
should be relatively easy to fix up.
I tried this on three xlc configurations.
(1) IBM XL C/C++ for AIX, V12.1 (5765-J02, 5725-C72). Getting it working
required the attached patch. None of my xlc configurations have an atomic.h
header, and a web search turned up no evidence of one in connection with xlc
platforms. Did you learn of a configuration needing atomic.h under xlc? The
rest of the changes are hopefully self-explanatory in light of the
documentation cited in generic-xlc.h. (Building on AIX has regressed in other
ways unrelated to atomics; I will write more about that in due course.)
(2) IBM XL C/C++ for Linux, V13.1.2 (5725-C73, 5765-J08) for ppc64le,
http://www-01.ibm.com/support/docview.wss?uid=swg27044056aid=1. This
compiler has a Clang-derived C frontend. It defines __GNUC__ and offers
GCC-style __sync_* atomics. Therefore, PostgreSQL selects generic-gcc.h.
test_atomic_ops() fails because __sync_lock_test_and_set() of one-byte types
segfaults at runtime. I have reported this to the vendor. Adding
pgac_cv_gcc_sync_char_tas=no to the configure invocation is a good
workaround. I could add a comment about that to src/test/regress/sql/lock.sql
for affected folks to see in regression.diffs. To do better, we could make
PGAC_HAVE_GCC__SYNC_CHAR_TAS perform a runtime test where possible. Yet
another option is to force use of generic-xlc.h on this compiler.
(3) IBM XL C/C++ for Linux, V13.1.2 (5725-C73, 5765-J08) for ppc64le,
modifying atomics.h to force use of generic-xlc.h. While not a supported
PostgreSQL configuration, I felt this would make an interesting data point.
It worked fine after applying the patch developed for the AIX configuration.
Thanks,
nm
diff --git a/src/include/port/atomics/generic-xlc.h
b/src/include/port/atomics/generic-xlc.h
index 1c743f2..0ad9168 100644
--- a/src/include/port/atomics/generic-xlc.h
+++ b/src/include/port/atomics/generic-xlc.h
@@ -18,8 +18,6 @@
#if defined(HAVE_ATOMICS)
-#include atomic.h
-
#define PG_HAVE_ATOMIC_U32_SUPPORT
typedef struct pg_atomic_uint32
{
@@ -48,9 +46,6 @@ static inline bool
pg_atomic_compare_exchange_u32_impl(volatile pg_atomic_uint32 *ptr,
uint32
*expected, uint32 newval)
{
- boolret;
- uint64 current;
-
/*
* xlc's documentation tells us:
* If __compare_and_swap is used as a locking primitive, insert a call
to
@@ -62,18 +57,15 @@ pg_atomic_compare_exchange_u32_impl(volatile
pg_atomic_uint32 *ptr,
* XXX: __compare_and_swap is defined to take signed parameters, but
that
* shouldn't matter since we don't perform any arithmetic operations.
*/
- current = (uint32)__compare_and_swap((volatile int*)ptr-value,
-
(int)*expected, (int)newval);
- ret = current == *expected;
- *expected = current;
- return ret;
+ return __compare_and_swap((volatile int*)ptr-value,
+ (int *)expected,
(int)newval);
}
#define PG_HAVE_ATOMIC_FETCH_ADD_U32
static inline uint32
pg_atomic_fetch_add_u32_impl(volatile pg_atomic_uint32 *ptr, int32 add_)
{
- return __fetch_and_add(ptr-value, add_);
+ return __fetch_and_add((volatile int *)ptr-value, add_);
}
#ifdef PG_HAVE_ATOMIC_U64_SUPPORT
@@ -83,23 +75,17 @@ static inline bool
pg_atomic_compare_exchange_u64_impl(volatile pg_atomic_uint64 *ptr,
uint64
*expected, uint64 newval)
{
- boolret;
- uint64 current;
-
__isync();
- current = (uint64)__compare_and_swaplp((volatile long*)ptr-value,
-
(long)*expected, (long)newval);
- ret = current == *expected;
- *expected = current;
- return ret;
+ return __compare_and_swaplp((volatile long*)ptr-value,
+ (long
*)expected, (long)newval);;
}
#define PG_HAVE_ATOMIC_FETCH_ADD_U64
static inline uint64
pg_atomic_fetch_add_u64_impl(volatile pg_atomic_uint64 *ptr, int64 add_)
{
- return __fetch_and_addlp(ptr-value, add_);
+ return __fetch_and_addlp((volatile long *)ptr-value, add_);
}
#endif /* PG_HAVE_ATOMIC_U64_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] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?
On Jul 4, 2015, at 11:34 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: In summary, the X^1.5 correction seems to work pretty well. It doesn't completely eliminate the problem, but it makes it a lot better. I've looked at the maths. I think that the load is distributed as the derivative of this function, that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that is the finishing load is 1.5 the average load, just before fsyncing files. This looks like a recipee for a bad time: I would say this is too large an overload. I would suggest a much lower value, say around 1.1... The other issue with this function is that it should only degrade performance by disrupting the write distribution if someone has WAL on a different disk. As I understand it this thing does only make sense if the WAL the data are on the samee disk. This really suggest a guc. I am a bit skeptical about this. We need test scenarios that clearly show the benefit of having and of not having this behavior. It might be that doing this always is fine for everyone. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xlc atomics
On 2015-07-04 18:40:41 -0400, Noah Misch wrote: (1) IBM XL C/C++ for AIX, V12.1 (5765-J02, 5725-C72). Getting it working required the attached patch. None of my xlc configurations have an atomic.h header, and a web search turned up no evidence of one in connection with xlc platforms. Did you learn of a configuration needing atomic.h under xlc? The rest of the changes are hopefully self-explanatory in light of the documentation cited in generic-xlc.h. (Building on AIX has regressed in other ways unrelated to atomics; I will write more about that in due course.) I wrote this entirely blindly, as evidenced here by the changes you needed. At the time somebody had promised to soon put up an aix animal, but that apparently didn't work out. Will you apply? Having the ability to test change seems to put you in a much better spot then me. (2) IBM XL C/C++ for Linux, V13.1.2 (5725-C73, 5765-J08) for ppc64le, http://www-01.ibm.com/support/docview.wss?uid=swg27044056aid=1. This compiler has a Clang-derived C frontend. It defines __GNUC__ and offers GCC-style __sync_* atomics. Phew. I don't see much reason to try to support this. Why would that be interesting? Therefore, PostgreSQL selects generic-gcc.h. test_atomic_ops() fails because __sync_lock_test_and_set() of one-byte types segfaults at runtime. I have reported this to the vendor. Adding pgac_cv_gcc_sync_char_tas=no to the configure invocation is a good workaround. I could add a comment about that to src/test/regress/sql/lock.sql for affected folks to see in regression.diffs. To do better, we could make PGAC_HAVE_GCC__SYNC_CHAR_TAS perform a runtime test where possible. Yet another option is to force use of generic-xlc.h on this compiler. It seems fair enough to simply add another test and include generic-xlc.h in that case. If it's indeed xlc, why not? (3) IBM XL C/C++ for Linux, V13.1.2 (5725-C73, 5765-J08) for ppc64le, modifying atomics.h to force use of generic-xlc.h. While not a supported PostgreSQL configuration, I felt this would make an interesting data point. It worked fine after applying the patch developed for the AIX configuration. Cool. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xlc atomics
On Sun, Jul 05, 2015 at 12:54:43AM +0200, Andres Freund wrote: On 2015-07-04 18:40:41 -0400, Noah Misch wrote: (1) IBM XL C/C++ for AIX, V12.1 (5765-J02, 5725-C72). Getting it working required the attached patch. Will you apply? Having the ability to test change seems to put you in a much better spot then me. I will. (2) IBM XL C/C++ for Linux, V13.1.2 (5725-C73, 5765-J08) for ppc64le, http://www-01.ibm.com/support/docview.wss?uid=swg27044056aid=1. This compiler has a Clang-derived C frontend. It defines __GNUC__ and offers GCC-style __sync_* atomics. Phew. I don't see much reason to try to support this. Why would that be interesting? Therefore, PostgreSQL selects generic-gcc.h. test_atomic_ops() fails because __sync_lock_test_and_set() of one-byte types segfaults at runtime. I have reported this to the vendor. Adding pgac_cv_gcc_sync_char_tas=no to the configure invocation is a good workaround. I could add a comment about that to src/test/regress/sql/lock.sql for affected folks to see in regression.diffs. To do better, we could make PGAC_HAVE_GCC__SYNC_CHAR_TAS perform a runtime test where possible. Yet another option is to force use of generic-xlc.h on this compiler. It seems fair enough to simply add another test and include generic-xlc.h in that case. If it's indeed xlc, why not? Works for me. I'll do that as attached. diff --git a/src/include/port/atomics.h b/src/include/port/atomics.h index 1a4c748..97a0064 100644 --- a/src/include/port/atomics.h +++ b/src/include/port/atomics.h @@ -81,8 +81,15 @@ * * pg_atomic_compare_exchange_u32(), pg_atomic_fetch_add_u32() * using compiler intrinsics are a good idea. */ +/* + * Given a gcc-compatible xlc compiler, prefer the xlc implementation. The + * ppc64le IBM XL C/C++ for Linux, V13.1.2 implements both interfaces, but + * __sync_lock_test_and_set() of one-byte types elicits SIGSEGV. + */ +#if defined(__IBMC__) || defined(__IBMCPP__) +#include port/atomics/generic-xlc.h /* gcc or compatible, including clang and icc */ -#if defined(__GNUC__) || defined(__INTEL_COMPILER) +#elif defined(__GNUC__) || defined(__INTEL_COMPILER) #include port/atomics/generic-gcc.h #elif defined(WIN32_ONLY_COMPILER) #include port/atomics/generic-msvc.h @@ -90,8 +97,6 @@ #include port/atomics/generic-acc.h #elif defined(__SUNPRO_C) !defined(__GNUC__) #include port/atomics/generic-sunpro.h -#elif (defined(__IBMC__) || defined(__IBMCPP__)) !defined(__GNUC__) -#include port/atomics/generic-xlc.h #else /* * Unsupported compiler, we'll likely use slower fallbacks... At least -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?
Hello Robert, I've looked at the maths. I think that the load is distributed as the derivative of this function, that is (1.5 * x ** 0.5): It starts at 0 but very quicky reaches 0.5, it pass the 1.0 (average load) around 40% progress, and ends up at 1.5, that is the finishing load is 1.5 the average load, just before fsyncing files. This looks like a recipee for a bad time: I would say this is too large an overload. I would suggest a much lower value, say around 1.1... The other issue with this function is that it should only degrade performance by disrupting the write distribution if someone has WAL on a different disk. As I understand it this thing does only make sense if the WAL the data are on the samee disk. This really suggest a guc. I am a bit skeptical about this. We need test scenarios that clearly show the benefit of having and of not having this behavior. It might be that doing this always is fine for everyone. Do you mean I have to proove that there is an actual problem induced from this patch? The logic fails me: I thought the patch submitter would have to show that his/her patch did not harm performance in various reasonable cases. At least this is what I'm told in another thread:-) Currently this patch changes heavily the checkpoint write load distribution in many cases with a proof which consist in showing that it may improve tps *briefly* on *one* example, as far as I understood the issue and the tests. If this is enough proof to apply the patch, then the minimum is that it should be possible to desactivate it, hence a guc. Having a guc would also help to test the feature with different values than 1.5, which really seems harmful from a math point of view. I'm not sure at all that a power formula is the right approach. The potential impact I see would be to aggravate significantly the write stall issues I'm working on, but the measures provided in these tests do not even look at that or measure that. -- Fabien. -- Sent 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:do not set Win32 server-side socket buffer size on windows 2012
2015-07-03 16:49:44,David Rowley david.row...@2ndquadrant.com wrote： I'm wondering what the original test setup was. I'm assuming psql and postgres both running on separate windows machines? I've tested the patch just connecting to a database running on localhost and I'm not getting much of a speedup. Perhaps 1%, if that's not noise. I don't have enough hardware here to have client and server on separate machines, at least not with a stable network that goes through copper. My original test environments is as the following Environment1: Server:Windows 2012(x64) The host is a VM in a private cloud Client:RHEL6(x64) The host is another VM in the same private cloud Network:1Gbit LAN Environment2: Server:Windows 2012(x64) The host is a VM in a private cloud Client:Windows 7(x64) The host is a physical machine(in fact it is My PC). Network:1Gbit LAN This Patch should only can speedup the environment which satisfy the following conditions. 1. The OS of the server is Windows 2012 or Win8(but i only tested it in Windows 2012). 2. The client and the server is separate machines. 3. The performance bottleneck is network throughput. 4. The utilization rate of network bandwidth is not full(such as only 50% or lower). Best Regards Chen Huajun
Re: [HACKERS] postgresql-9.5alpha1 packaging
On Sat, Jul 4, 2015 at 4:40 PM, Marco Atzeri marco.atz...@gmail.com wrote: As there are additional or moved binaries in comparison to 9.4 -usr/bin/pg_archivecleanup.exe -usr/bin/pg_rewind.exe -usr/bin/pg_test_fsync.exe -usr/bin/pg_test_timing.exe -usr/bin/pg_upgrade.exe -usr/bin/pg_xlogdump.exe -usr/bin/pgbench.exe pgbench should be put on the client-side as it is a benchmark tool, and the rest on the server-side as they serve directly the backend. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #13126: table constraint loses its comment
On Fri, Jul 3, 2015 at 11:59 PM, Petr Jelinek wrote:
Well for indexes you don't really need to add the new AT command, as
IndexStmt has char *idxcomment which it will automatically uses as comment
if not NULL. While I am not huge fan of the idxcomment it doesn't seem to
be easy to remove it in the future and it's what transformTableLikeClause
uses so it might be good to be consistent with that.
Oh, right, I completely missed your point and this field in IndexStmt.
Yes it is better to be consistent in this case and to use it. It makes
as well the code easier to follow.
Btw, regarding the new AT routines, I am getting find of them, it
makes easier to follow which command is added where in the command
queues.
Updated patch attached.
--
Michael
From 6d98dfd7f191dfe99f24c3022f30af8fc6a624dd Mon Sep 17 00:00:00 2001
From: Michael Paquier michael@otacoo.com
Date: Sat, 4 Jul 2015 20:40:42 +0900
Subject: [PATCH] Ensure COMMENT persistency of indexes and constraint with
ALTER TABLE
When rewriting a table, in some cases indexes and constraints present
on it need to be recreated from scratch, making any existing comment
entry, as known as a description in pg_description, disappear after
ALTER TABLE.
This commit fixes this issue by tracking the existing constraint,
indexes, and combinations of both when running ALTER TABLE and recreate
COMMENT entries when appropriate. A set of regression tests is added
to test all the new code paths added.
---
src/backend/commands/tablecmds.c | 268 +-
src/include/nodes/parsenodes.h| 1 +
src/test/regress/expected/alter_table.out | 95 +++
src/test/regress/sql/alter_table.sql | 37 +
4 files changed, 327 insertions(+), 74 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d394713..79de187 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -316,6 +316,13 @@ static void ATRewriteTables(AlterTableStmt *parsetree,
List **wqueue, LOCKMODE lockmode);
static void ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode);
static AlteredTableInfo *ATGetQueueEntry(List **wqueue, Relation rel);
+static void ATAttachQueueCommand(Oid oldId, Oid refRelId, List **wqueue,
+Node *stm, Relation rel, bool rewrite);
+static void ATAttachQueueIndexStmt(Oid oldId, List **wqueue,
+IndexStmt *stmt, Relation rel, bool rewrite);
+static void ATAttachQueueAlterTableStmt(Oid oldId, Oid refRelId,
+ List **wqueue, AlterTableStmt *stmt,
+ Relation rel, bool rewrite);
static void ATSimplePermissions(Relation rel, int allowed_targets);
static void ATWrongRelkindError(Relation rel, int allowed_targets);
static void ATSimpleRecursion(List **wqueue, Relation rel,
@@ -386,6 +393,10 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
char *cmd, List **wqueue, LOCKMODE lockmode,
bool rewrite);
+static void RebuildConstraintComment(AlteredTableInfo *tab,
+ int cmdidx,
+ Oid objid,
+ List *objname);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
static void TryReuseForeignKey(Oid oldId, Constraint *con);
static void change_owner_fix_column_acls(Oid relationOid,
@@ -3498,6 +3509,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
address = ATExecAddIndex(tab, rel, (IndexStmt *) cmd-def, true,
lockmode);
break;
+ case AT_ReAddComment: /* Re-add existing comment */
+ CommentObject((CommentStmt *) cmd-def);
+ break;
case AT_AddConstraint: /* ADD CONSTRAINT */
address =
ATExecAddConstraint(wqueue, tab, rel, (Constraint *) cmd-def,
@@ -4251,6 +4265,150 @@ ATGetQueueEntry(List **wqueue, Relation rel)
return tab;
}
+
+/*
+ * ATAttachQueueCommand
+ *
+ * Attach each generated command to the proper place in the work queue.
+ * Note this could result in creation of entirely new work-queue entries.
+ *
+ * Also note that the command subtypes have to be tweaked, because it
+ * turns out that re-creation of indexes and constraints has to act a bit
+ * differently from initial creation.
+ */
+static void
+ATAttachQueueCommand(Oid oldId, Oid refRelId, List **wqueue,
+ Node *stm, Relation rel, bool rewrite)
+{
+ switch (nodeTag(stm))
+ {
+ case T_IndexStmt:
+ ATAttachQueueIndexStmt(oldId, wqueue,
+ (IndexStmt *) stm, rel, rewrite);
+ break;
+ case T_AlterTableStmt:
+ ATAttachQueueAlterTableStmt(oldId, refRelId, wqueue,
+ (AlterTableStmt *) stm,
+ rel, rewrite);
+ break;
+ default:
+ elog(ERROR, unexpected statement type: %d,
+ (int) nodeTag(stm));
+ }
+}
+
+
+/*
+ * ATAttachQueueIndexStmt
+ *
+ * Attach to the correct queue the given IndexStmt, re-creating at the same
+ * time a comment for it if necessary.
+ */
+static void
+ATAttachQueueIndexStmt(Oid oldId, List **wqueue,
+
