why partition pruning doesn't work?
Hi
CREATE TABLE data(a text, vlozeno date) PARTITION BY RANGE(vlozeno);
CREATE TABLE data_2016 PARTITION OF data FOR VALUES FROM
('2016-01-01') TO ('2016-12-31');
CREATE TABLE data_2017 PARTITION OF data FOR VALUES FROM
('2017-01-01') TO ('2017-12-31');
CREATE TABLE data_other PARTITION OF DATA DEFAULT;
insert into data select 'ahoj', '2016-01-01'::date + (random() *
900)::int from generate_series(1,100);
analyze data;
postgres=# explain analyze select * from data where vlozeno > '2018-06-01';
┌──┐
│ QUERY PLAN
│
╞══╡
│ Append (cost=0.00..3519.83 rows=20001 width=9) (actual
time=0.042..27.750 rows=19428 loops=1) │
│ -> Seq Scan on data_other (cost=0.00..3419.83 rows=20001
width=9) (actual time=0.040..25.895 rows=19428 loops=1) │
│ Filter: (vlozeno > '2018-06-01'::date)
│
│ Rows Removed by Filter: 171518
│
│ Planning Time: 0.766 ms
│
│ Execution Time: 28.718 ms
│
└──┘
(6 rows)
postgres=# explain analyze select * from data where vlozeno > current_date;
┌─┐
│ QUERY
PLAN │
╞═╡
│ Gather (cost=1000.00..17281.36 rows=20080 width=9) (actual
time=0.749..95.389 rows=19428 loops=1)
│
│ Workers Planned: 2
│
│ Workers Launched: 2
│
│ -> Parallel Append (cost=0.00..14273.36 rows=8367 width=9)
(actual time=59.141..89.458 rows=6476 loops=3) │
│ -> Parallel Seq Scan on data_2016 (cost=0.00..5768.69
rows=24 width=9) (actual time=34.847..34.847 rows=0 loops=3)│
│ Filter: (vlozeno > CURRENT_DATE)
│
│ Rows Removed by Filter: 135119
│
│ -> Parallel Seq Scan on data_2017 (cost=0.00..5745.02
rows=23 width=9) (actual time=53.269..53.269 rows=0 loops=2)│
│ Filter: (vlozeno > CURRENT_DATE)
│
│ Rows Removed by Filter: 201848
│
│ -> Parallel Seq Scan on data_other (cost=0.00..2717.82
rows=11765 width=9) (actual time=0.044..55.502 rows=19428 loops=1) │
│ Filter: (vlozeno > CURRENT_DATE)
│
│ Rows Removed by Filter: 171518
│
│ Planning Time: 0.677 ms
│
│ Execution Time: 98.349 ms
│
└─┘
(15 rows)
but
postgres=# explain analyze select * from data where vlozeno > (select
current_date);
┌──┐
│ QUERY
PLAN │
╞══╡
│ Append (cost=0.01..19574.68 rows=33 width=9) (actual
time=0.095..31.945 rows=19428 loops=1) │
│ InitPlan 1 (returns
$0)
│
│ -> Result (cost=0.00..0.01 rows=1 width=4) (actual
time=0.010..0.010 rows=1 loops=1) │
│ -> Seq Scan on data_2016 (cost=0.00..7258.98 rows=135119 width=9)
(never executed) │
│ Filter: (vlozeno >
$0)
│
│ -> Seq Scan on data_2017 (cost=0.00..7229.20 rows=134565 width=9)
(never executed) │
│ Filter: (vlozeno >
$0)
│
│ -> Seq Scan on data_other (cost=0.00..3419.83 rows=63649
ToDo: show size of partitioned table
Hi postgres=# SELECT count(*) from data; ┌─┐ │ count │ ╞═╡ │ 100 │ └─┘ (1 row) \dt+ can display actual size of partitioned table data - now zero is displayed postgres=# \dt+ data List of relations ┌┬──┬───┬───┬─┬─┐ │ Schema │ Name │ Type │ Owner │ Size │ Description │ ╞╪══╪═══╪═══╪═╪═╡ │ public │ data │ table │ pavel │ 0 bytes │ │ └┴──┴───┴───┴─┴─┘ (1 row) postgres=# \dt+ data* List of relations ┌┬┬───┬───┬─┬─┐ │ Schema │Name│ Type │ Owner │ Size │ Description │ ╞╪╪═══╪═══╪═╪═╡ │ public │ data │ table │ pavel │ 0 bytes │ │ │ public │ data_2016 │ table │ pavel │ 17 MB │ │ │ public │ data_2017 │ table │ pavel │ 17 MB │ │ │ public │ data_other │ table │ pavel │ 8224 kB │ │ └┴┴───┴───┴─┴─┘ (4 rows) or we can introduce some option for display only partitioned tables without partitions. Regards Pavel
Re: [PATCH] We install pg_regress and isolationtester but not pg_isolation_regress
On 28 May 2018 at 15:06, Craig Ringer wrote: > Hi > > Per topic, the Pg makefiles install pg_regress (for use by extensions) and > htey install the isolationtester, but they don't install > pg_isolation_regress. > > We should install it too. > Now with a patch that isn't brain-dead. I'm wondering if I should add ISOLATION support to PGXS too, like we have REGRESS . -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services From 819eda0c40617b57a8ddd1b5d4d30de453bc11ec Mon Sep 17 00:00:00 2001 From: Craig Ringer Date: Fri, 1 Jun 2018 11:26:09 +0800 Subject: [PATCH v2] Install pg_isolation_regress not just isolationtester --- src/test/isolation/Makefile | 6 ++ 1 file changed, 6 insertions(+) diff --git a/src/test/isolation/Makefile b/src/test/isolation/Makefile index c3c8280ea2..911bb5f43a 100644 --- a/src/test/isolation/Makefile +++ b/src/test/isolation/Makefile @@ -64,3 +64,9 @@ installcheck-prepared-txns: all temp-install check-prepared-txns: all temp-install $(pg_isolation_regress_check) --schedule=$(srcdir)/isolation_schedule prepared-transactions + +installdirs: + $(MKDIR_P) '$(DESTDIR)$(pgxsdir)/$(subdir)' + +install: all installdirs + $(INSTALL_PROGRAM) pg_isolation_regress$(X) '$(DESTDIR)$(pgxsdir)/$(subdir)/pg_isolation_regress$(X)' -- 2.14.3
Re: I'd like to discuss scaleout at PGCon
2018-05-31 22:44 GMT+09:00, Robert Haas : > On Thu, May 31, 2018 at 8:12 AM, MauMau wrote: >> Oh, I didn't know you support FDW approach mainly for analytics. I >> guessed the first target was OLTP read-write scalability. > > That seems like a harder target to me, because you will have an extra > hop involved -- SQL from the client to the first server, then via SQL > to a second server. The work of parsing and planning also has to be > done twice, once for the foreign table and again for the table. For > longer-running queries this overhead doesn't matter as much, but for > short-running queries it is significant. Yes, that extra hop and double parsing/planning were the killer for our performance goal when we tried to meet our customer's scaleout needs with XL. The application executes 82 DML statements in one transaction. Those DMLs consist of INSERT, UPDATE and SELECT that only accesses one row with a primary key. The target tables are only a few, so the application PREPAREs a few statements and EXECUTEs them repeatedly. We placed the coordinator node of XL on the same host as the application, and data nodes and GTM on other individual nodes. The response time of XL compared to PostgreSQL was 2.4 times, and the throughput (tps) was 43%. Interestingly, perf showed that base_yyparse() was the top CPU consumer on both coordinator and data node, while base_yyparse() appeared near the bottom of the ranking. The SQL processor should be one layer, not two layers. In the above benchmark, each transaction only accessed data on one data node. That's what sharding principles recommend. The FDW approach would be no problem as long as the application follows the sharding recommendation. But not all applications will/can follow the sharding recommendation. The above application, which is migrated from a mainframe, uses INSERTs to load data, inserting rows onto various nodes. Considering your concern of double parsing/planning for a local foreign table and a remote real table, wouldn't the FDW approach hit the wall? > I don't know what "node management" and "failure dectection/failover" > mean specifically. I'd like to hear proposals, though. That's nothing special or new. Things like: * Define a set of nodes that can join the cluster. * Initialize or configure a node according to its role in the cluster. * Decommission a node from the cluster. * Define a node group in which all member nodes have the same data set for redundancy. * One command to start and shutdown the entire cluster. * System tables to display the member nodes and node groups. * Each node's in-memory view of the current cluster state. * How each node monitors which other nodes. * Elect a new primary node within a node group when the current primary node fails. * Whether each node group should be configured with a master-slaves replication topology, or a multi-master topology like MySQL Group Replication Some of the above may end up with XL's things like pgxc_node/pgxc_group system tables, pgxc_ctl command, CREATE/DROP NODE/NODE GROUP commands, etc. Regards MauMau
Re: behave of --create-slot option
On Wed, May 30, 2018 at 2:00 PM, Michael Paquier wrote: > Hm. There could be an argument for improving the user experience here > so as some cleanup is at least attempted except if --no-clean is defined > by the caller when --create-slot is used. Do we want an open item for > this issue? Sounds like new development to me. This isn't a bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: json results parsing
> "Chapman" == Chapman Flack writes: >> To clarify, I think my question is functions like json_each or >> json_object_keys() are >> set returning functions ( >> https://www.postgresql.org/docs/9.5/static/functions-srf.html), >> which means it returns a set of results into a Datum. Chapman> Well, it returns one result row as a Datum (either a single Chapman> value or a tuple) each time it is called, until the whole set Chapman> has been returned. The process is described here Unfortunately that describes only one of the two calling protocols for SRFs - the value-per-call mode, which as it happens is NOT the one that json_each uses; that requires materialize mode, which is not actually covered in the docs (but you can find examples in contrib/tablefunc). -- Andrew (irc:RhodiumToad)
Re: json results parsing
On 05/31/18 20:20, Charles Cui wrote: > To clarify, I think my question is functions like json_each or > json_object_keys() are > set returning functions ( > https://www.postgresql.org/docs/9.5/static/functions-srf.html), > which means it returns a set of results into a Datum. Well, it returns one result row as a Datum (either a single value or a tuple) each time it is called, until the whole set has been returned. The process is described here https://www.postgresql.org/docs/9.5/static/xfunc-c.html#XFUNC-C-RETURN-SET (though that description is from the point of view of the set-returning function being called, not the point of view of its caller). -Chap
Re: json results parsing
> "Charles" == Charles Cui writes: Charles> Thanks you guys for answering my questions, and sorry for confusion. Charles> To clarify, I think my question is functions like json_each or Charles> json_object_keys() are Charles> set returning functions ( Charles> https://www.postgresql.org/docs/9.5/static/functions-srf.html), Charles> which means it returns a set of results into a Datum. No. Set-returning functions follow a somewhat complex call protocol that may involve the function being called multiple times (returning one row each time as a composite Datum) or may involve the function being called once and returning a tuplestore (not as the return value but via the ReturnSetInfo struct). I would not try calling a SRF directly from C code without going through SPI (i.e. setting up and running an actual query from inside your function). I definitely think you should be looking at pg_parse_json instead. -- Andrew (irc:RhodiumToad)
Re: json results parsing
Thanks you guys for answering my questions, and sorry for confusion. To clarify, I think my question is functions like json_each or json_object_keys() are set returning functions ( https://www.postgresql.org/docs/9.5/static/functions-srf.html), which means it returns a set of results into a Datum. I am wondering how to parse the Datum? I know there are functions like DatumGetCString() which parse the Datum into a cstring. Are there any helpers for set returning functions? 2018-05-30 23:00 GMT-07:00 Andrew Gierth : > > "Charles" == Charles Cui writes: > > Charles> Hi mentors and hackers, > Charles>Found the json API page here > Charles> https://www.postgresql.org/docs/9.3/static/functions-json.html > > Charles> For some APIs, it returns set of results, for example, > Charles> json_each(json) and json_object_keys(). > > Charles> Basically, I want to call these json API inside a plugin, > > If you want to process the whole of a json (not jsonb) value inside a > plugin, then maybe pg_parse_json is what you should be looking at rather > than the SQL-callable functions. > > For jsonb, there's an iterator interface (JsonbIteratorInit/ > JsonbIteratorNext) > > -- > Andrew (irc:RhodiumToad) >
Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
On Thu, May 31, 2018 at 07:23:57PM -0400, Tom Lane wrote:
> Noah Misch writes:
> > On Thu, May 31, 2018 at 10:28:12AM -0400, Tom Lane wrote:
> >> I wondered why the existing 32-bit AIX buildfarm machines aren't showing
> >> problems, but looking closer at them, they are manually forcing
> >> _LARGE_FILES, which probably is masking things:
> >>'config_env' => {
> >>'CC' => 'wrap-gcc -D_THREAD_SAFE=1 -D_LARGE_FILES=1 -maix32',
> >> Noah, why'd you do that, and would you be willing to remove it? IMO
> >> Postgres should work without that.
>
> > I did that to work around a problem like the one articulated upthread.
> > Specifically, a 64-bit build w/ plpython failed:
> > ...
> > Today's "configure" test concludes that we don't need _LARGE_FILES, because
> > off_t is 64-bit ("long", specifically) in this configuration. The trouble
> > arises when Python.h does cause _LARGE_FILES to be defined.
>
> Ugh. That's a pretty crummy decision on their part, although maybe there
> was no better alternative.
>
> This does not seem like it explains Tony's problem with AIX 32-bit,
> though, as you'd think all concerned would agree _LARGE_FILES needs
> to be 1 in that case.
Yep. I suspect _LARGE_FILES is orthogonal to $SUBJECT, which looks like a
problem with floating point ABI or possibly structure layout. Since, as you
say, all code concerned wants 64-bit off_t at all times, I doubt _LARGE_FILES
would be the cause of a structure layout mismatch.
> > At the time I added the workaround, I scratched down these candidates for a
> > proper fix:
>
> > 1. Add "configure" test to determine whether Python defines _LARGE_FILES.
> >When it does, define it ourselves at the top of each Python-associated
> >source file.
>
> That would make aspects of our extension ABI dependent on whether one had
> configured with --enable-python, which would be surprising to say the
> least.
I don't think it would. We use _LARGE_FILES anyway on 32-bit. On 64-bit,
_LARGE_FILES boils down to s/long/long long/, which is a C API change but not
an ABI change.
> > 2. Define _LARGE_FILES unconditionally on AIX. That is, adopt the
> > workaround
> >as permanent.
>
> Perhaps. I wonder though whether this is really an AIX-only problem.
> (In particular, I wonder whether Python.h is clobbering _FILE_OFFSET_BITS
> on other platforms.) There's a comment in Autoconf's AC_SYS_LARGEFILE
> that suggests it is, but ...
>
> > 3. Define _LARGE_FILES unconditionally. This should be harmless, but I
> >wouldn't tend to back-patch it.
>
> It seems like variants of this issue should exist in all branches,
> so I'm not really happy with taking a fix we're scared to back-patch.
> If we were willing to do so, though, this might be OK. Seems like there
> are three possibilities:
> * Defining _LARGE_FILES does something good, in which case we want it.
> * Defining _LARGE_FILES does nothing.
> * Defining _LARGE_FILES does something bad ... but it's hard to see
> how that could be.
Fair. I'd be content about back-patching for AIX, because 100% of AIX
buildfarm animals already do this. While I don't anticipate specific breakage
on other platforms, testing the _LARGE_FILES interpretation of every 64-bit
libc on the planet feels like a recipe for surprises.
Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
Noah Misch writes:
> On Thu, May 31, 2018 at 10:28:12AM -0400, Tom Lane wrote:
>> I wondered why the existing 32-bit AIX buildfarm machines aren't showing
>> problems, but looking closer at them, they are manually forcing
>> _LARGE_FILES, which probably is masking things:
>> 'config_env' => {
>> 'CC' => 'wrap-gcc -D_THREAD_SAFE=1 -D_LARGE_FILES=1 -maix32',
>> Noah, why'd you do that, and would you be willing to remove it? IMO
>> Postgres should work without that.
> I did that to work around a problem like the one articulated upthread.
> Specifically, a 64-bit build w/ plpython failed:
> ...
> Today's "configure" test concludes that we don't need _LARGE_FILES, because
> off_t is 64-bit ("long", specifically) in this configuration. The trouble
> arises when Python.h does cause _LARGE_FILES to be defined.
Ugh. That's a pretty crummy decision on their part, although maybe there
was no better alternative.
This does not seem like it explains Tony's problem with AIX 32-bit,
though, as you'd think all concerned would agree _LARGE_FILES needs
to be 1 in that case.
> At the time I added the workaround, I scratched down these candidates for a
> proper fix:
> 1. Add "configure" test to determine whether Python defines _LARGE_FILES.
>When it does, define it ourselves at the top of each Python-associated
>source file.
That would make aspects of our extension ABI dependent on whether one had
configured with --enable-python, which would be surprising to say the
least.
> 2. Define _LARGE_FILES unconditionally on AIX. That is, adopt the workaround
>as permanent.
Perhaps. I wonder though whether this is really an AIX-only problem.
(In particular, I wonder whether Python.h is clobbering _FILE_OFFSET_BITS
on other platforms.) There's a comment in Autoconf's AC_SYS_LARGEFILE
that suggests it is, but ...
> 3. Define _LARGE_FILES unconditionally. This should be harmless, but I
>wouldn't tend to back-patch it.
It seems like variants of this issue should exist in all branches,
so I'm not really happy with taking a fix we're scared to back-patch.
If we were willing to do so, though, this might be OK. Seems like there
are three possibilities:
* Defining _LARGE_FILES does something good, in which case we want it.
* Defining _LARGE_FILES does nothing.
* Defining _LARGE_FILES does something bad ... but it's hard to see
how that could be.
> 4. Include system headers that react to _LARGE_FILES before including
>Python.h. This is fragile; the list of affected headers may change.
Yeah, that seems fairly unworkable, though I wonder whether the
include-ordering advice in plpython.h isn't basically meant to achieve
this result.
We're still left with the question of why Tony is having a problem.
I wonder whether his build of Python.h is doing something strange
with _LARGE_FILES.
regards, tom lane
Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
On Thu, May 31, 2018 at 10:28:12AM -0400, Tom Lane wrote:
> "REIX, Tony" writes:
> > It looks like configure does figure out that LARGE_FILES is required, only
> > in 32bit.
> > No need in 64bit.
>
> Check ...
>
> > However, in 32bit, though there is:
> >#define _LARGE_FILES 1
> > in file :
> >src/include/pg_config.h
> > I had to add it at the beg of file by means of a patch to several files:
>
> This is surely not what's intended. It seems like plpython is messing
> things up somehow, probably through an #include-ordering error, but
> I don't see the exact problem offhand.
>
> I wondered why the existing 32-bit AIX buildfarm machines aren't showing
> problems, but looking closer at them, they are manually forcing
> _LARGE_FILES, which probably is masking things:
>
>'config_env' => {
> 'CC' => 'wrap-gcc -D_THREAD_SAFE=1 -D_LARGE_FILES=1
> -maix32',
>
> Noah, why'd you do that, and would you be willing to remove it? IMO
> Postgres should work without that.
I did that to work around a problem like the one articulated upthread.
Specifically, a 64-bit build w/ plpython failed:
xlc_r -qnoansialias -g -O2 -qmaxmem=16384 -qsrcmsg -qlonglong -I. -I.
-I/home/nm/sw/python3-64/include/python3.4m -I../../../src/include -c -o
plpy_elog.o plpy_elog.c
"/usr/include/unistd.h", line 201.17: 1506-343 (S) Redeclaration of lseek64
differs from previous declaration on line 199 of "/usr/include/unistd.h".
"/usr/include/unistd.h", line 201.17: 1506-050 (I) Return type "long long" in
redeclaration is not compatible with the previous return type "long".
"/usr/include/unistd.h", line 201.17: 1506-377 (I) The type "long long" of
parameter 2 differs from the previous type "long".
"/usr/include/sys/lockf.h", line 64.20: 1506-343 (S) Redeclaration of lockf64
differs from previous declaration on line 62 of "/usr/include/sys/lockf.h".
"/usr/include/sys/lockf.h", line 64.20: 1506-377 (I) The type "long long" of
parameter 3 differs from the previous type "long".
... ...
Today's "configure" test concludes that we don't need _LARGE_FILES, because
off_t is 64-bit ("long", specifically) in this configuration. The trouble
arises when Python.h does cause _LARGE_FILES to be defined. Some system
headers don't tolerate a mix of _LARGE_FILES values in one compilation. At
the time I added the workaround, I scratched down these candidates for a
proper fix:
1. Add "configure" test to determine whether Python defines _LARGE_FILES.
When it does, define it ourselves at the top of each Python-associated
source file.
2. Define _LARGE_FILES unconditionally on AIX. That is, adopt the workaround
as permanent.
3. Define _LARGE_FILES unconditionally. This should be harmless, but I
wouldn't tend to back-patch it.
4. Include system headers that react to _LARGE_FILES before including
Python.h. This is fragile; the list of affected headers may change.
I lean toward (2), because it will defend against other libraries choosing to
define _LARGE_FILES like Python does. It would cause problems if some library
does an explicit "#undef _LARGE_FILES", but that's a less-likely choice.
Other preferences?
Re: Re: [HACKERS] [PATCH] Incremental sort
I've attached an updated copy of the patch that applies cleanly to
current master.
commit 6428245702a40b3e3fa11bb64b7611cdd33a0778
Author: Alexander Korotkov
Date: Sat Apr 7 18:51:20 2018 +0300
Implement incremental sort
Incremental sort is an optimized variant of multikey sort for cases when the
input is already sorted by a prefix of the sort keys. For example when a
sort
by (key1, key2 ... keyN) is requested, and the input is already sorted by
(key1, key2 ... keyM), M < N, we can divide the input into groups where keys
(key1, ... keyM) are equal, and only sort on the remaining columns.
Incremental sort can give a huge benefit when LIMIT clause is specified,
then it wouldn't even have to read the whole input. Another huge benefit
of incremental sort is that sorting data in small groups may help to evade
using disk during sort. However, on small datasets which fit into memory
incremental sort may be slightly slower than full sort. That was reflected
in costing.
This patch implements very basic usage of incremental sort: it gets used
only in create_ordered_paths(), while it sort can help in much more use
cases,
for instance in merge join. But latter would require much more changes in
optimizer and postponed for further releases.
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index e4d9469fdd..61775e6726 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1999,28 +1999,62 @@ SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1
FROM ft2 t2 WHERE t1.c1 = t2
119
(10 rows)
--- CROSS JOIN, not pushed down
+-- CROSS JOIN, not pushed down. For this query, essential optimization is
top-N
+-- sort. But it can't be processed at remote side, because we never do LIMIT
+-- push down. Assuming that sorting is not worth it to push down, CROSS JOIN
+-- is also not pushed down in order to transfer less tuples over network.
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET
100 LIMIT 10;
- QUERY PLAN
--
+SELECT t1.c3, t2.c3 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c3, t2.c3 OFFSET
100 LIMIT 10;
+QUERY PLAN
+--
Limit
- Output: t1.c1, t2.c1
+ Output: t1.c3, t2.c3
-> Sort
- Output: t1.c1, t2.c1
- Sort Key: t1.c1, t2.c1
+ Output: t1.c3, t2.c3
+ Sort Key: t1.c3, t2.c3
-> Nested Loop
- Output: t1.c1, t2.c1
+ Output: t1.c3, t2.c3
-> Foreign Scan on public.ft1 t1
- Output: t1.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+ Output: t1.c3
+ Remote SQL: SELECT c3 FROM "S 1"."T 1"
-> Materialize
- Output: t2.c1
+ Output: t2.c3
-> Foreign Scan on public.ft2 t2
- Output: t2.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+ Output: t2.c3
+ Remote SQL: SELECT c3 FROM "S 1"."T 1"
(15 rows)
+SELECT t1.c3, t2.c3 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c3, t2.c3 OFFSET
100 LIMIT 10;
+ c3 | c3
+---+---
+ 1 | 00101
+ 1 | 00102
+ 1 | 00103
+ 1 | 00104
+ 1 | 00105
+ 1 | 00106
+ 1 | 00107
+ 1 | 00108
+ 1 | 00109
+ 1 | 00110
+(10 rows)
+
+-- CROSS JOIN, pushed down. Unlike previous query, remote side is able to
+-- return tuples in given order without full sort, but using index scan and
+-- incremental sort. This is much cheaper than full sort on local side, even
+-- despite we don't know LIMIT on remote side.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET
100 LIMIT 10;
+
QUERY PLAN
+---
+ Limit
+ Output: t1.c1, t2.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (TRUE)) ORDER BY r1."C 1" ASC NULLS LAST, r2."C 1" ASC NULLS
LAST
+(6 rows)
+
SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET
100 LIMIT 10;
c1 | c1
+-
diff --g
Re: JIT documentation fixes
On 2018-05-31 14:50:04 -0400, Daniel Gustafsson wrote: > When reading the JIT developer documentation, a few small wordsmithing issues > stood out (although this may be due to me not being a native english > speaker). I think it's more likely because *I* am not a native speaker ;) > The attached patch fixes these to what I think the sentences inteded to say. Thanks. I'm not carrying my commit bit right here, so I'll try to commit it later. - Andres
Re: pgAgent: ERROR: Couldn't register event handle.
On Tue, May 29, 2018 at 8:15 AM, Arturas Mazeika wrote: > I am overseeing a few hundred/thousand postgres/pgagent installations and > on one installation the pgAgent does not start with a message as a service > on a Windows Server: > > ERROR: Couldn't register event handle. I think that this isn't the right mailing list for pgAgent questions. Perhaps the pgAdmin mailing list is the right place, since pgagent is, IIRC, distributed from the pgAdmin site? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
JIT documentation fixes
When reading the JIT developer documentation, a few small wordsmithing issues stood out (although this may be due to me not being a native english speaker). The attached patch fixes these to what I think the sentences inteded to say. cheers ./daniel jit_wordsmithing.patch Description: Binary data
Re: SHOW ALL does not honor pg_read_all_settings membership
On Thu, May 31, 2018 at 07:05:58PM +0100, Simon Riggs wrote: > Any objections to backpatch to v10? A backpatch is acceptable in my opinion. -- Michael signature.asc Description: PGP signature
Re: SHOW ALL does not honor pg_read_all_settings membership
On 17 April 2018 at 04:28, Michael Paquier wrote: > On Mon, Apr 16, 2018 at 02:32:10PM +0200, Laurenz Albe wrote: >> Now that the dust from the last commitfest is settling, I'll make a second >> attempt to attract attention for this small bug fix. >> >> The original commit was Simon's. > > Thanks for the ping. > > This was new as of v10, so this cannot be listed as an open item still I > have added that under the section for older bugs, because you are right > as far as I can see. OK, agreed, its a bug. Any objections to backpatch to v10? > GetConfigOption is wrong by the way, as restrict_superuser means that > all members of the group pg_read_all_settings can read > GUC_SUPERUSER_ONLY params, and not only superusers, so the comment at > least needs a fix, the variable ought to be renamed as well. OK -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: pg_replication_slot_advance to return NULL instead of 0/0 if slot not advanced
On 28 May 2018 at 09:57, Michael Paquier wrote: > On Fri, May 25, 2018 at 02:12:32PM +0200, Magnus Hagander wrote: >> I agree that returning 0/0 on this is wrong. >> >> However, can this actually occour for any case other than exactly the case >> of "moving the position to where the position already is"? If I look at the >> physical slot path at least that seems to be the only case, and in that >> case I think the correct thing to return would be the new position, and not >> NULL. If we actually *fail* to move the position, we give an error. > > Yes, this only returns InvalidXLogRecPtr if the location could not be > moved forward. Still, there is more going on here. For a physical > slot, confirmed_lsn is always 0/0, hence the backward check is never > applied for it. What I think should be used for value assigned to > startlsn is restart_lsn instead. Then what do we do if the position > cannot be moved: should we raise an error, as what my patch attached > does, or just report the existing position the slot is at? I don't see why an ERROR would be appropriate. > A second error that I can see is in pg_logical_replication_slot_advance, > which does not take the mutex lock of MyReplicationSlot, so concurrent > callers like those of ReplicationSlotsComputeRequiredLSN (applies to > physical slot actually) and pg_get_replication_slots() may read false > data. > > On top of that, it seems to me that StartLogicalReplication is reading a > couple of fields from a slot without taking a lock, so at least > pg_get_replication_slots() may read incorrect data. > ReplicationSlotReserveWal also is missing a lock.. Those are older than > v11 though. > >> Actually, isn't there also a race there? That is, if we try to move it, we >> check that we're not trying to move it backwards, and throw an error, but >> that's checked outside the lock. Then later we actually move it, and check >> *again* if we try to move it backwards, but if that one fails we return >> InvalidXLogRecPtr (which can happen in the case of concurrent activity on >> the slot, I think)? In this case, maybe we should just re-check that and >> raise an error appropriately? > > Er, isn't the take here that ReplicationSlotAcquire is used to lock any > replication slot update to happen from other backends? It seems to me > that what counts at the end if if a backend PID is associated to a slot > in memory. If you look at the code paths updating a logical or physical > slot then those imply that the slot is owned, still a spin lock needs to > be taken for concurrent readers. > >> (I haven't looked at the logical slot path, but I assume it would have >> something similar in it) > > Found one. All the things I have spotted are in the patch attached. I think the problem here is there are no comments explaining how to access the various fields in the structure, so there was no way to check whether the code was good or not. If we add corrective code we should clarify that in comments the .h file also, as is done in XlogCtl Your points look correct to me, well spotted. I'd like to separate the correction of these issues from the change of behavior patch. Those earlier issues can be backpatched, but the change of behavior only affects PG11. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
"REIX, Tony" writes: > v11beta1 brings new json files. Either these files reveal some issue on AIX > 32bit or they contain code that is not compatible with AIX environment and > some change should be applied... One thing I notice is that jsonb_plperl.c starts with #include "postgres.h" #include "plpython.h" #include "plpy_elog.h" #include "plpy_typeio.h" #include "utils/jsonb.h" #include "utils/fmgrprotos.h" #include "utils/numeric.h" which does not follow the comment in plpython.h: * Include order should be: postgres.h, other postgres headers, plpython.h, * other plpython headers So it seems this should be #include "postgres.h" #include "utils/jsonb.h" #include "utils/fmgrprotos.h" #include "utils/numeric.h" #include "plpython.h" #include "plpy_elog.h" #include "plpy_typeio.h" However, I don't see how that relates to _LARGE_FILES. As long as postgres.h is first, which it is, seems like that should work. regards, tom lane
RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
Hi Tom,
Yes, there is something strange with _LARGE_FILES in 32bit.
However, that exists too with version 10.4 and 9.6.9 , and tests are 100% OK.
So, it seems that something new appears within v11
v11beta1 brings new json files. Either these files reveal some issue on AIX
32bit or they contain code that is not compatible with AIX environment and some
change should be applied...
Cordialement,
Tony Reix
ATOS / Bull SAS
ATOS Expert
IBM Coop Architect & Technical Leader
Office : +33 (0) 4 76 29 72 67
1 rue de Provence - 38432 Échirolles - France
www.atos.net
De : Tom Lane [t...@sss.pgh.pa.us]
Envoyé : jeudi 31 mai 2018 16:28
À : REIX, Tony
Cc : Alvaro Herrera; PostgreSQL-development; APEKE, SENA (ext); Peter
Eisentraut; Anthony Bykov; Noah Misch
Objet : Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
"REIX, Tony" writes:
> It looks like configure does figure out that LARGE_FILES is required, only in
> 32bit.
> No need in 64bit.
Check ...
> However, in 32bit, though there is:
>#define _LARGE_FILES 1
> in file :
>src/include/pg_config.h
> I had to add it at the beg of file by means of a patch to several files:
This is surely not what's intended. It seems like plpython is messing
things up somehow, probably through an #include-ordering error, but
I don't see the exact problem offhand.
I wondered why the existing 32-bit AIX buildfarm machines aren't showing
problems, but looking closer at them, they are manually forcing
_LARGE_FILES, which probably is masking things:
'config_env' => {
'CC' => 'wrap-gcc -D_THREAD_SAFE=1 -D_LARGE_FILES=1
-maix32',
Noah, why'd you do that, and would you be willing to remove it? IMO
Postgres should work without that.
regards, tom lane
Re: Problem while updating a foreign table pointing to a partitioned table on foreign server
On Thu, May 31, 2018 at 7:36 PM, Tom Lane wrote: > Kyotaro HORIGUCHI writes: >> If my understanding about non-system junk columns in a base relation >> and identifiers of a foreign tuples are correct, what is needed here >> is giving base relations the ability to have such junk column. > > The core of the problem, I think, is the question of exactly what > postgresAddForeignUpdateTargets should put into the resjunk expressions > it adds to an update/delete query's targetlist. Per discussion yesterday, > up to now it's always emitted Vars referencing the foreign relation, > which is problematic because with that approach the desired info has > to be exposed as either a regular or system column of that relation. > But there's nothing saying that the expression has to be a Var. > > My thought about what we might do instead is that > postgresAddForeignUpdateTargets could reserve a PARAM_EXEC slot > and emit a Param node referencing that. Then at runtime, while > reading a potential target row from the remote, we fill that > param slot along with the regular scan tuple slot. > > What you want for the first part of that is basically like > generate_new_param() in subselect.c. We don't expose that publicly > at the moment, but we could, or maybe better to invent another wrapper > around it like SS_make_initplan_output_param. This looks like a lot of change which might take some time and may not be back-portable. In the mean time, can we see if 0001 and 0002 patches are good and apply them. Those patches intend to stop the multiple rows on the foreign server being updated by throwing error (and aborting the transaction on the foreign server) when that happens. That will at least avoid silent corruption that happens today and should be back-portable. [1] https://www.postgresql.org/message-id/CAFjFpRfK69ptCTNChBBk+LYMXFzJ92SW6NmG4HLn_1y7xFk=k...@mail.gmail.com -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
"REIX, Tony" writes:
> It looks like configure does figure out that LARGE_FILES is required, only in
> 32bit.
> No need in 64bit.
Check ...
> However, in 32bit, though there is:
>#define _LARGE_FILES 1
> in file :
>src/include/pg_config.h
> I had to add it at the beg of file by means of a patch to several files:
This is surely not what's intended. It seems like plpython is messing
things up somehow, probably through an #include-ordering error, but
I don't see the exact problem offhand.
I wondered why the existing 32-bit AIX buildfarm machines aren't showing
problems, but looking closer at them, they are manually forcing
_LARGE_FILES, which probably is masking things:
'config_env' => {
'CC' => 'wrap-gcc -D_THREAD_SAFE=1 -D_LARGE_FILES=1
-maix32',
Noah, why'd you do that, and would you be willing to remove it? IMO
Postgres should work without that.
regards, tom lane
RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
Hum We build in 64bit and 32bit. It looks like configure does figure out that LARGE_FILES is required, only in 32bit. No need in 64bit. # grep LARGE_FILES postgresql-11beta1-1.spec.res_20180530_101845 checking for CFLAGS recommended by Perl... -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -qlanglvl=extc99 -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -maix32 -D_LARGE_FILES checking for _LARGE_FILES value needed for large files... 1 This is for 32bit only. 32bit and 64bit: # find . -name "Makefile*" | xargs grep LARGE_FILE # 64bit: # grep LARGE_FILES config.log # 32bit: # grep LARGE_FILES */config.log 32bit/config.log:configure:9421: result: -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -qlanglvl=extc99 -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -maix32 -D_LARGE_FILES 32bit/config.log:configure:14471: checking for _LARGE_FILES value needed for large files ... 32bit/config.log:#define _LARGE_FILES 1 # find . -name "*.h" | xargs grep LARGE_FILE ./32bit/src/bin/pg_upgrade/tmp_check/install/opt/freeware/include/pg_config.h:#define _LARGE_FILES 1 ./32bit/src/bin/pg_upgrade/tmp_check/install/opt/freeware/include/postgresql/server/pg_config.h:#define _LARGE_FILES 1 ./32bit/src/include/pg_config.h:#define _LARGE_FILES 1 ./32bit/tmp_install/opt/freeware/include/pg_config.h:#define _LARGE_FILES 1 ./32bit/tmp_install/opt/freeware/include/postgresql/server/pg_config.h:#define _LARGE_FILES 1 ./64bit/src/bin/pg_upgrade/tmp_check/install/opt/freeware/include/pg_config.h:/* #undef _LARGE_FILES */ ./64bit/src/bin/pg_upgrade/tmp_check/install/opt/freeware/include/postgresql/server/pg_config.h:/* #undef _LARGE_FILES */ ./64bit/src/include/pg_config.h:/* #undef _LARGE_FILES */ ./64bit/tmp_install/opt/freeware/include/pg_config.h:/* #undef _LARGE_FILES */ ./64bit/tmp_install/opt/freeware/include/postgresql/server/pg_config.h:/* #undef _LARGE_FILES */ However, in 32bit, though there is: #define _LARGE_FILES 1 in file : src/include/pg_config.h I had to add it at the beg of file by means of a patch to several files: src/pl/plpython/plpy_cursorobject.c src/pl/plpython/plpy_elog.c src/pl/plpython/plpy_exec.c src/pl/plpython/plpy_main.c src/pl/plpython/plpy_planobject.c src/pl/plpython/plpy_plpymodule.c src/pl/plpython/plpy_procedure.c src/pl/plpython/plpy_resultobject.c src/pl/plpython/plpy_spi.c src/pl/plpython/plpy_subxactobject.c src/pl/plpython/plpy_typeio.c src/pl/plpython/plpy_util.c contrib/hstore_plpython/hstore_plpython.c contrib/ltree_plpython/ltree_plpython.c contrib/jsonb_plpython/jsonb_plpython.c src/common/file_perm.c All involve plpython. Maybe we have something wrong there and my patch is a work-around. However, my work-around works perfectly for v10.4 and v9.6.9, built in the exact same environment, but not for v11beta1 . Regards, Cordialement, Tony Reix ATOS / Bull SAS ATOS Expert IBM Coop Architect & Technical Leader Office : +33 (0) 4 76 29 72 67 1 rue de Provence - 38432 Échirolles - France www.atos.net De : Tom Lane [t...@sss.pgh.pa.us] Envoyé : jeudi 31 mai 2018 15:35 À : REIX, Tony Cc : Alvaro Herrera; PostgreSQL-development; APEKE, SENA (ext); Peter Eisentraut; Anthony Bykov Objet : Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode "REIX, Tony" writes: > For files: contrib/jsonb_plpython/jsonb_plpython.c and src/common/file_perm.c > , I had to use #define _LARGE_FILES 1 like I did for 14 older files. That > deals with lseek() and lseek64() definitions. I'm not following this. Doesn't configure manage to figure out that _LARGE_FILES=1 is needed? It certainly tries to. regards, tom lane
Re: Problem while updating a foreign table pointing to a partitioned table on foreign server
Kyotaro HORIGUCHI writes: > If my understanding about non-system junk columns in a base relation > and identifiers of a foreign tuples are correct, what is needed here > is giving base relations the ability to have such junk column. The core of the problem, I think, is the question of exactly what postgresAddForeignUpdateTargets should put into the resjunk expressions it adds to an update/delete query's targetlist. Per discussion yesterday, up to now it's always emitted Vars referencing the foreign relation, which is problematic because with that approach the desired info has to be exposed as either a regular or system column of that relation. But there's nothing saying that the expression has to be a Var. My thought about what we might do instead is that postgresAddForeignUpdateTargets could reserve a PARAM_EXEC slot and emit a Param node referencing that. Then at runtime, while reading a potential target row from the remote, we fill that param slot along with the regular scan tuple slot. What you want for the first part of that is basically like generate_new_param() in subselect.c. We don't expose that publicly at the moment, but we could, or maybe better to invent another wrapper around it like SS_make_initplan_output_param. regards, tom lane
Re: I'd like to discuss scaleout at PGCon
On Thu, May 31, 2018 at 8:12 AM, MauMau wrote: > I anticipated a decision process at the unconference like this: > "Do we want to build on shared everything architecture?" > "No, because it limits scalability, requires expensive shared storage, > and it won't run on many clouds." > "Then do we want to employ a new architecture like AWS Aurora?" > "That may be interesting. But AWS could do it because they have an > infinitely scalable storage layer which is built and used for a long > time. This architecture may not be our option. But let's keep our > eye on leveraging services of major cloud vendors just like Vertica > does recently. Cloud services are now like traditional > vendor-specific hardware. Maybe PostgreSQL should utilize them just > like we use CPU-specific instructions now and GPU/persistent memory in > the near future." > "Then, it seems that we should go on the shared nothing architecture. > Is it OK?" > "Yes." I think all of that except for the last two sentences accords with my view, at least. As for the last two sentences, I'm happy to see someone develop some kind of cloud-native storage if they want, but I have no immediate plans in that area myself. >> The FDW approach, of which I have been a supporter for some years now, >> is really aiming at a different target, which is to allow efficient >> analytics queries across a multi-node cluster. > > Oh, I didn't know you support FDW approach mainly for analytics. I > guessed the first target was OLTP read-write scalability. That seems like a harder target to me, because you will have an extra hop involved -- SQL from the client to the first server, then via SQL to a second server. The work of parsing and planning also has to be done twice, once for the foreign table and again for the table. For longer-running queries this overhead doesn't matter as much, but for short-running queries it is significant. > Agreed. I think we should not write much code from scratch, too. On > the other hand, if we have to support sharding natively without FDW, I > wonder if we can reuse the FDW artifact. I mean, extracting necessary > logics from FDW into common functions, and native sharding code also > calls them. I wouldn't do it that way. I want to use postgres_fdw as a whole, not break it up into pieces and reuse the individual bits of code. We should think also about whether other FDWs could be part of it, although maybe that's not realistic. >> In fact, even if we said that we want a fully distributed database, >> we'd probably still need some kind of distributed table concept. >> Unless every node has a full copy of everything in the database, you >> still need to reason about which data is present on which nodes and >> optimize queries accordingly. > > Then, how about building the cluster membership management first, > including node management and failure detection/failover? I think > that node management is necessary anyway, and other developers can > experiment other things on that cluster infrastructure. Do you think > it would be helpful or wasteful? I'm finding what we can do for early > scaleout release. I don't know what "node management" and "failure dectection/failover" mean specifically. I'd like to hear proposals, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
"REIX, Tony" writes: > For files: contrib/jsonb_plpython/jsonb_plpython.c and src/common/file_perm.c > , I had to use #define _LARGE_FILES 1 like I did for 14 older files. That > deals with lseek() and lseek64() definitions. I'm not following this. Doesn't configure manage to figure out that _LARGE_FILES=1 is needed? It certainly tries to. regards, tom lane
Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)
Hello Moon, I promised to email links to the articles I mentioned during your talk on the PGCon Unconference to this thread. Here they are: * http://cryptowiki.net/index.php?title=Order-preserving_encryption * https://en.wikipedia.org/wiki/Homomorphic_encryption Also I realized that I was wrong regarding encryption of the indexes since they will be encrypted on the block level the same way the heap will be. -- Best regards, Aleksander Alekseev signature.asc Description: PGP signature
Re: New GUC to sample log queries
On Thu, May 31, 2018 at 02:37:07PM +0200, Adrien Nayrat wrote: > On 05/31/2018 03:34 AM, David Rowley wrote: >> On 31 May 2018 at 06:44, Adrien Nayrat wrote: >>> Here is a naive SELECT only bench with a dataset which fit in ram (scale >>> factor >>> = 100) and PGDATA and log on a ramdisk: >>> shared_buffers = 4GB >>> seq_page_cost = random_page_cost = 1.0 >>> logging_collector = on (no rotation) >> >> It would be better to just: SELECT 1; to try to get the true overhead >> of the additional logging code. > > Right, I wonder why I didn't have the idea :) Using prepared statements help also in reducing the load with unnecessary planning. -- Michael signature.asc Description: PGP signature
RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
Hi Tom, Hummm About Perl, version 5.24.0 were installed 6 months ago and is still there. And, still on the same AIX 7.2 machine, I've been able to build and test 100%OK version 10.4 before buiding/testing v11beta1, and I've been able to build and test 100%OK version 9.6.9 after v11beta1. v11beta1 seems to add new 39 new files dealing with json. For files: contrib/jsonb_plpython/jsonb_plpython.c and src/common/file_perm.c , I had to use #define _LARGE_FILES 1 like I did for 14 older files. That deals with lseek() and lseek64() definitions. Regards, Cordialement, Tony Reix ATOS / Bull SAS ATOS Expert IBM Coop Architect & Technical Leader Office : +33 (0) 4 76 29 72 67 1 rue de Provence - 38432 Échirolles - France www.atos.net De : Tom Lane [t...@sss.pgh.pa.us] Envoyé : mercredi 30 mai 2018 21:36 À : Alvaro Herrera Cc : REIX, Tony; PostgreSQL-development; APEKE, SENA (ext); Peter Eisentraut; Anthony Bykov Objet : Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode Alvaro Herrera writes: > It's pretty obvious that the transform is broken on your platform. Seems so, but why? The code involved doesn't look very machine-dependent. I'm wondering about some sort of version skew or misinstallation on the Perl side --- say, header files that we're using to compile that don't match the libperl.so used at runtime. regards, tom lane
RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
Hi Álvaro Here is the regression.diffs file. Regards, Cordialement, Tony Reix ATOS / Bull SAS ATOS Expert IBM Coop Architect & Technical Leader Office : +33 (0) 4 76 29 72 67 1 rue de Provence - 38432 Échirolles - France www.atos.net De : Alvaro Herrera [alvhe...@2ndquadrant.com] Envoyé : mercredi 30 mai 2018 20:46 À : REIX, Tony Cc : PostgreSQL-development; APEKE, SENA (ext); Peter Eisentraut; Anthony Bykov Objet : Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode On 2018-May-30, REIX, Tony wrote: > 32bit: 2 failures: >== running regression test queries == >test jsonb_plperl ... FAILED >test jsonb_plperlu ... FAILED > > Expected/Result logs attached to this email. This is not particularly helpful. Please send the "regression.diff" file instead. > What about tests on Linux on i686 ? Seems to work fine there, see lapwing: https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=lapwing&dt=2018-05-30%2017%3A20%3A01&stg=contrib-install-check-C also dromedary: https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=dromedary&dt=2018-05-30%2016%3A47%3A23&stg=contrib-install-check-C It's pretty obvious that the transform is broken on your platform. Probably related to breakage fixed in 331b2369c0ad. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services regression.out Description: regression.out regression.diffs Description: regression.diffs
Re: Problem while updating a foreign table pointing to a partitioned table on foreign server
Thanks. > I don't think this thread has reached a consensus on a design for a fix Right. If my understanding about non-system junk columns in a base relation and identifiers of a foreign tuples are correct, what is needed here is giving base relations the ability to have such junk column. I'm willing to work on that if I'm not on a wrong way here. -- Kyotaro Horiguchi
Re: New GUC to sample log queries
On 05/31/2018 03:34 AM, David Rowley wrote:
> On 31 May 2018 at 06:44, Adrien Nayrat wrote:
>> Here is a naive SELECT only bench with a dataset which fit in ram (scale
>> factor
>> = 100) and PGDATA and log on a ramdisk:
>> shared_buffers = 4GB
>> seq_page_cost = random_page_cost = 1.0
>> logging_collector = on (no rotation)
>
> It would be better to just: SELECT 1; to try to get the true overhead
> of the additional logging code.
Right, I wonder why I didn't have the idea :)
>
>> I don't know the cost of random() call?
>
> It's probably best to test in Postgres to see if there's an overhead
> to the new code. It may be worth special casing the 0 and 1 case so
> random() is not called.
Thanks, I based on auto_explain's code. I will send a patch for auto_explain
with same changes.
>
> +(random() < log_sample_rate * MAX_RANDOM_VALUE);
>
> this should be <=, or you'll randomly miss logging a query when
> log_sample_rate is 1.0 every 4 billion or so queries.
Right, it is the same in auto_explain.
>
> Of course, it would be better if we had a proper profiler, but I can
> see your need for this. Enabling logging of all queries in production
> is currently reserved for people with low traffic servers and the
> insane.
Yes, here are results with "SELECT 1" and changes to avoid random() call with 0
and 1.
master :
log_min_duration_statement = 0
TPS: 41294
log size: 2477589 lines (199MB)
log_min_duration_statement = -1
TPS: 49478
log size: 0 lines
patched:
log_min_duration_statement = 0
log_sample_rate = 1
TPS: 41635
log size: 2497994 lines (201MB)
log_min_duration_statement = 0
log_sample_rate = 0.1
TPS: 46915
log size: 282303 lines (23MB)
log_min_duration_statement = 0
log_sample_rate = 0.01
TPS: 48867
log size: 29193 lines (2.4MB)
log_min_duration_statement = 0
log_sample_rate = 0
TPS: 48912
log size: 0 lines
log_min_duration_statement = -1
log_sample_rate = 1
TPS: 49061
log size: 0 lines
Difference between l_m_d_s = 0 and -1 is about 16% and logger process eat around
30% CPU on my laptop.
Revised patch attached.
Thanks,
--
Adrien NAYRAT
commit d7d82d9d07a9285a45bb28b8dbe3cff6c8b91408
Author: anayrat
Date: Wed May 30 20:27:54 2018 +0200
Add a new GUC to log a fraction of queries.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index f4133953be..10d4e44209 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2115,7 +2115,8 @@ check_log_statement(List *stmt_list)
/*
* check_log_duration
- * Determine whether current command's duration should be logged
+ * Determine whether current command's duration should be logged.
+ * If log_sample_rate < 1.0, log only a fraction of the command.
*
* Returns:
* 0 if no logging is needed
@@ -2151,7 +2152,9 @@ check_log_duration(char *msec_str, bool was_logged)
exceeded = (log_min_duration_statement == 0 ||
(log_min_duration_statement > 0 &&
(secs > log_min_duration_statement / 1000 ||
- secs * 1000 + msecs >= log_min_duration_statement)));
+ secs * 1000 + msecs >= log_min_duration_statement))) &&
+ log_sample_rate != 0 && (log_sample_rate == 1 ||
+ random() <= log_sample_rate * MAX_RANDOM_VALUE);
if (exceeded || log_duration)
{
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index ee1444c427..b219682b83 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -455,6 +455,7 @@ int log_min_messages = WARNING;
int client_min_messages = NOTICE;
int log_min_duration_statement = -1;
int log_temp_files = -1;
+double log_sample_rate = 1.0;
int trace_recovery_messages = LOG;
int temp_file_limit = -1;
@@ -3257,6 +3258,17 @@ static struct config_real ConfigureNamesReal[] =
NULL, NULL, NULL
},
+ {
+ {"log_sample_rate", PGC_SUSET, LOGGING_WHEN,
+ gettext_noop("Fraction of statements to log."),
+ gettext_noop("1.0 log all statements."),
+ NULL
+ },
+ &log_sample_rate,
+ 1.0, 0.0, 1.0,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 3d88e80a20..c990f6c20b 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -447,6 +447,8 @@
# statements running at least this number
# of milliseconds
+#log_sample_rate = 1 # Fraction of logged statements. 1 means log all
+ # statements.
# - What to Log -
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 3d13a33b94..4480756954 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -252,6 +252,7 @@ extern PGDLLIMPORT int log_min_messages;
extern PGDLLIMPORT int client_min_messages;
extern int log_min_duration_statement;
extern int log_temp_files;
+extern double log_sample_rate;
extern int temp_file_limit;
signatur
RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode
Hi Michael, We have used GCC, version 8.1 . We also can build it with XLC, though I did not tried yet with v11beta1. I know about the 2 threads you have provided. Today, with our own patches, we are able to compile and run 100% OK PostgreSQL v10 or v9 on AIX 6 or AIX7, with GCC and XLC. (I'll submit these patches for AIX once we have run the stress/perf campaign we plan to do on AIX & Linux on P9) I'm using nearly exactly the same .spec and .patch files for v10.4 and v11beta1. However, I see that there are new files dealing with json coming with v11beta1: # find $BUILD/postgresql-11beta1/64bit/ -name "json*" | wc 72 727575 # find $BUILD/postgresql-10.4/64bit/ -name "json*" | wc 33 333180 Build/Tests in 64bit is 100% OK with v11beta1. Regards Cordialement, Tony Reix ATOS / Bull SAS ATOS Expert IBM Coop Architect & Technical Leader Office : +33 (0) 4 76 29 72 67 1 rue de Provence - 38432 Échirolles - France www.atos.net De : Michael Paquier [mich...@paquier.xyz] Envoyé : mercredi 30 mai 2018 22:25 À : Tom Lane Cc : Alvaro Herrera; REIX, Tony; PostgreSQL-development; APEKE, SENA (ext); Peter Eisentraut; Anthony Bykov Objet : Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode On Wed, May 30, 2018 at 03:36:27PM -0400, Tom Lane wrote: > I'm wondering about some sort of version skew or misinstallation on > the Perl side --- say, header files that we're using to compile > that don't match the libperl.so used at runtime. Tony, was the compiler used gcc or xlc? Support for xlc 13 still needs some work. Here are two other, similar threads: https://www.postgresql.org/message-id/b37989f2852398498001550c29155be51790b...@frcrpvv9ex3msx.ww931.my-it-solutions.net https://www.postgresql.org/message-id/2fc764c7--83e3-45c2-33c17853e...@atos.net -- Michael
Re: I'd like to discuss scaleout at PGCon
2018-05-31 11:26 GMT+09:00, Robert Haas : > It was nice to meet you in person. Me too. And it was very kind of you to help me to display the wiki page well and guide the session. When I first heard your voice at the Developer Meeting, I thought Bruce Momjian was speaking, because your voice sounded similar to him... > We didn't have time in the unconference session to discuss these > topics in detail, for you have raised many issues here each of which > deserves discussion individually and in detail. I wrote a blog post > somewhat related to this topic recently which you can find at > http://rhaas.blogspot.com/2018/05/built-in-sharding-for-postgresql.html Yes, I read this article before PGCon. Your articles are always helpful to catch the current overall situation of the community. > In > terms of high-level architecture, I think you are right to wonder > about the possibility of a cloud-native mode based on separating > storage and compute. Amazon Aurora offers that feature, but we might > want to have it in PostgreSQL. > Another, somewhat different thing that we might want is a fully > distributed database, with a distributed buffer cache, distributed > lock manager, distributed invalidation queue, etc. That would be like > what Oracle RAC does, but it's a tremendous amount of work, and a lot > of that work has no value by itself. You don't get the payoff until > it's all working. There are a few pieces that might be independently > useful, though, like a distributed deadlock detector. The goal of > this kind of effort is, I think, to support zillions of connections -- > scale further than you can with just one node. This would be a lot > better if combined with the cloud-native storage, but of course that's > even more work. Yes, I can understand the difficulty. So, I simply wanted to ask opinions at the unconference on which (hard) direction the community wants to go and what database we want PostgreSQL to be like ultimately. Without that fundamental consensus, development work might be wasted, facing objections after submitting patches. As you mentioned in your blog post and in a past email, I don't think anyone yet has a clear image of what the scaleout of PostgreSQL should be. How should we proceed? Which approach should we take to minimize rework? a) Define functional specification with the basic overall architecture (this doesn't mean to write a heavy detailed design document or manual; I think a general README or wiki would be sufficient.) At this time, I expect we can evaluate how to use scaleout feature and whether it's reasonably easy to use. Then we can proceed to design and code each part with relief -- 2PC, global consistency, failure detection and failover, distributed lock management and deadlock handling, etc. b) Various developers design and code each part, bring together those patches, and then all try to figure out how to combine them. I'm in favor of a) at least at the basic architecture level. Otherwise, such an unhappiness could happen: "Hey, I made a patch to implement a distributed cache management like Oracle Cache Fusion." "No, we don't want features based on shared everything architecture." I anticipated a decision process at the unconference like this: "Do we want to build on shared everything architecture?" "No, because it limits scalability, requires expensive shared storage, and it won't run on many clouds." "Then do we want to employ a new architecture like AWS Aurora?" "That may be interesting. But AWS could do it because they have an infinitely scalable storage layer which is built and used for a long time. This architecture may not be our option. But let's keep our eye on leveraging services of major cloud vendors just like Vertica does recently. Cloud services are now like traditional vendor-specific hardware. Maybe PostgreSQL should utilize them just like we use CPU-specific instructions now and GPU/persistent memory in the near future." "Then, it seems that we should go on the shared nothing architecture. Is it OK?" "Yes." > The FDW approach, of which I have been a supporter for some years now, > is really aiming at a different target, which is to allow efficient > analytics queries across a multi-node cluster. Oh, I didn't know you support FDW approach mainly for analytics. I guessed the first target was OLTP read-write scalability. > We might not want to confine ourselves strictly to the FDW interface > -- for example, I've thought about the idea of building introducing a > new relkind for a "distributed table". A distributed table may be > present on the local node, in which case it can be scanned like a > local table, or it may be not present, in which case it can be scanned > like a foreign table by connecting to a node on which it is present. > The set of nodes on which a table is present is metadata that is > shared throughout the cluster. Multi-master logical replication > propagates changes between all nodes on whic
Re: We still claim "cannot begin/end transactions in PL/pgSQL"
On 5/25/18 12:16, Tom Lane wrote:
> I notice there are still several places in pl_exec.c like this:
>
> case SPI_ERROR_TRANSACTION:
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("cannot begin/end transactions in PL/pgSQL"),
> errhint("Use a BEGIN block with an EXCEPTION clause
> instead.")));
> break;
>
> At best, the wording of these error messages is now obsolete. I'm not
> sure if we expect them to be reachable at all. If they should be
> can't-happen cases, I'd suggest just deleting them and letting control
> fall to the generic default: cases in each switch. If they are reachable,
> the messages need work.
There are three occurrences:
The occurrence in exec_prepare_plan() could never be reached AFAICT,
because SPI_prepare_params() does not produce those SPI_prepare_params()
error values in the first place. So remove them altogether.
The occurrence in exec_stmt_execsql() can be reached by running for
example SAVEPOINT, or any other TransactionStmt other than COMMIT and
ROLLBACK, which are intercepted by PL/pgSQL. So we still need an error
message there. Unfortunately, we don't know which TransactionStmt
caused the error, so the error has to be pretty generic.
The occurrence in exec_stmt_dynexecute() can be reached using something
like EXECUTE 'COMMIT', which is not supported/not implemented. Hence a
tweaked error message there as well.
Possible patch attached.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 94a5dc7ad0b3a9d1a500a3220f7cbbc9af7c9465 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Thu, 31 May 2018 07:51:02 -0400
Subject: [PATCH] Reword SPI_ERROR_TRANSACTION errors in PL/pgSQL
The previous message for SPI_ERROR_TRANSACTION claimed "cannot begin/end
transactions in PL/pgSQL", but that is no longer true. Nevertheless,
the error can still happen, so reword the messages. The error cases in
exec_prepare_plan() could never happen, so remove them.
---
src/pl/plpgsql/src/pl_exec.c | 29 -
1 file changed, 4 insertions(+), 25 deletions(-)
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index ef013bcdc7..66ecf5eb55 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -3965,27 +3965,8 @@ exec_prepare_plan(PLpgSQL_execstate *estate,
(void *) expr,
cursorOptions);
if (plan == NULL)
- {
- /* Some SPI errors deserve specific error messages */
- switch (SPI_result)
- {
- case SPI_ERROR_COPY:
- ereport(ERROR,
-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-errmsg("cannot COPY to/from
client in PL/pgSQL")));
- break;
- case SPI_ERROR_TRANSACTION:
- ereport(ERROR,
-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-errmsg("cannot begin/end
transactions in PL/pgSQL"),
-errhint("Use a BEGIN block
with an EXCEPTION clause instead.")));
- break;
- default:
- elog(ERROR, "SPI_prepare_params failed for
\"%s\": %s",
-expr->query,
SPI_result_code_string(SPI_result));
- break;
- }
- }
+ elog(ERROR, "SPI_prepare_params failed for \"%s\": %s",
+expr->query, SPI_result_code_string(SPI_result));
if (keepplan)
SPI_keepplan(plan);
expr->plan = plan;
@@ -4129,8 +4110,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
case SPI_ERROR_TRANSACTION:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-errmsg("cannot begin/end transactions
in PL/pgSQL"),
-errhint("Use a BEGIN block with an
EXCEPTION clause instead.")));
+errmsg("unsupported transaction
command in PL/pgSQL")));
break;
default:
@@ -4317,8 +4297,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
case SPI_ERROR_TRANSACTION:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-errmsg("cannot begin/end transactions
in PL/pgSQL"),
