Re: [HACKERS] memory layouts for binary search in nbtree

2016-05-19 Thread Peter Geoghegan 
On Wed, May 18, 2016 at 6:25 AM, Andres Freund  wrote:
> currently we IIRC use linearly sorted datums for the search in
> individual btree nodes.  Not surprisingly that's often one of the
> dominant entries in profiles. We could probably improve upon that by
> using an order more optimized for efficient binary search.

Did you ever try running a pgbench SELECT benchmark, having modified
things such that all PKs are on columns that are not of type
int4/int8, but rather are of type numeric? It's an interesting
experiment, that I've been meaning to re-run on a big box.

Obviously this will be slower than an equivalent plain pgbench SELECT,
but the difference may be smaller than you expect.

-- 
Peter Geoghegan


-- 
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] memory layouts for binary search in nbtree

2016-05-19 Thread Peter Geoghegan 
On Wed, May 18, 2016 at 6:55 AM, Simon Riggs  wrote:
> I think its a good area of work.

I strongly agree.

Abbreviated keys in indexes are supposed to help with this. Basically,
the ItemId array is made to be interlaced with small abbreviated keys
(say one or two bytes), only in the typically less than 1% of pages
that are internal (leaf page binary searches don't change). Those
internal pages naturally have a wide range of values represented, so 1
byte turns out to be a lot more than you'd think. And, you only have
to generate a new one when there is a pagesplit, which is relatively
infrequent. You could squeeze out the lp_len bits to fit the
abbreviated keys, and store that in the IndexTuple proper. I've
discussed this idea with Mashahiko extensively in private. I have lots
of related ideas, and think it's a very promising area.

I think that this project will be very difficult without better testing.

This idea also enables complementary techniques, like interpolation
search that can degrade to binary search.

-- 
Peter Geoghegan


-- 
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] foreign table batch inserts

2016-05-19 Thread Tsunakawa, Takayuki 
From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
Well, there's FE/BE level batching/pipelining already. Just no access to it 
from libpq.

Oh, really.  The Bind ('B') appears to take one set of parameter values, not 
multiple sets (array).  Anyway, I had to say "I want batch update API in libpq" 
to use it in ODBC and ECPG.

Regards
Takayuki Tsunakawa

[HACKERS] To-Do item: skip table scan for adding column with provable check constraints

2016-05-19 Thread Jeff Janes 
I recently had to run something like:

alter table pgbench_accounts add locked text check (locked != 'unlocked');

And was surprised that it took several minutes to complete as it
scanned the whole table.

The new column is going to start out as NULL in every row, so there is
no need to validate the check constraint by reading the table as it
can be proven from first principles.  Correct?

Is there a reason such an improvement would be unwanted or not
feasible?  If not, I will add as a To-Do item.

Cheers,

Jeff


-- 
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] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David Christensen 

> On May 19, 2016, at 5:24 PM, Евгений Шишкин  wrote:
> 
> 
>> On 20 May 2016, at 01:12, Tom Lane  wrote:
>> 
>> 
>> I'm a bit inclined to think that what this is really about is that we
>> made the wrong call on the BUFFERS option, and that it should default
>> to ON just like COSTS and TIMING do.  Yeah, that would be an incompatible
>> change, but that's what major releases are for no?
> 
> After thinking about it, i think this is a better idea.

Yeah, if that’s the only practical difference, WORKSFORME; I can see the point 
about boxing us into a corner at some future time.

+1.
--
David Christensen
End Point Corporation
da...@endpoint.com
785-727-1171





-- 
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] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Евгений Шишкин 

> On 20 May 2016, at 01:12, Tom Lane  wrote:
> 
> 
> I'm a bit inclined to think that what this is really about is that we
> made the wrong call on the BUFFERS option, and that it should default
> to ON just like COSTS and TIMING do.  Yeah, that would be an incompatible
> change, but that's what major releases are for no?

After thinking about it, i think this is a better idea.


-- 
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] Tracking wait event for latches

2016-05-19 Thread Michael Paquier 
On Thu, May 19, 2016 at 4:14 PM, Michael Paquier
 wrote:
> Comments are welcome, I am adding that in the 9.7 queue.

Take that as 10.0 as things are going.
-- 
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] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Gavin Flower 

On 20/05/16 10:11, David G. Johnston wrote:

[...]


EXPAIN ABCTV (might need permission to document this variation though)
What has an Australian Broadcast Corporation Television got to do with 
this???  :-)


[...]


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

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Tom Lane 
=?utf-8?B?0JXQstCz0LXQvdC40Lkg0KjQuNGI0LrQuNC9?=  writes:
>> On 19 May 2016, at 22:59, Tom Lane  wrote:
>> I'm not sure this is well thought out.  It would mean for example that
>> we could never implement EXPLAIN options that are mutually exclusive
>> ... at least, not without having to redefine ALL as all-except-something.
>> Non-boolean options would be problematic as well.

> Maybe EVERYTHING would be ok.

That's not really getting at the substance of my complaint, which is that
I foresee regretting inventing such an option later, when we wish to invent
some option that it's not reasonable for ALL/EVERYTHING/WHATEVER to turn
"on" (if indeed the new option is "off"/"on" in the first place).  We'll
either have to accept an arbitrary/inconsistent definition of what ALL
does to that option, or have to explain that ALL doesn't mean all.

My own experience is that I seldom want ANALYZE and VERBOSE at the same
time, so I'm doubtful that I'd find this proposal helpful even just
considering the current option set.

I'm also not convinced about the semantics of, say, EXPLAIN (ALL, COSTS
OFF).  I see what the patch will do, but I'm not convinced I like that
(and I am convinced that the patch's documentation is misleading about
it).

I'm a bit inclined to think that what this is really about is that we
made the wrong call on the BUFFERS option, and that it should default
to ON just like COSTS and TIMING do.  Yeah, that would be an incompatible
change, but that's what major releases are for no?

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] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David G. Johnston 
On Thursday, May 19, 2016, David Christensen  wrote:

>
> > On May 19, 2016, at 3:17 PM, Евгений Шишкин  > wrote:
> >
> >
> >> On 19 May 2016, at 22:59, Tom Lane >
> wrote:
> >>
> >> David Christensen > writes:
> >>> This simple patch adds “ALL” as an EXPLAIN option as shorthand for
> “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” for usability.
> >>
> >> I'm not sure this is well thought out.  It would mean for example that
> >> we could never implement EXPLAIN options that are mutually exclusive
> >> ... at least, not without having to redefine ALL as
> all-except-something.
> >> Non-boolean options would be problematic as well.
> >>
> >
> > Maybe EVERYTHING would be ok.
> > But it is kinda long word to type.
>
> If it’s just a terminology issue, what about EXPLAIN (*); already a
> precedent with SELECT * to mean “everything”.  (MAX?


>

> LIKE_I’M_5?) Let the bikeshedding begin!


+1


> In any case, I think a shorthand for “give me the most possible detail
> without me having to lookup/type/remember the options” is a good tool.
>

EXPLAIN THIS
EXPLAIN BETTER

EXPAIN ABCTV (might need permission to document this variation though)

The later has some resemblance to option short form in command lines.

The bigger question is do we want to solve this in the server or let it be
a client concern and stick some usability enhancements into psql?  Maybe
even put it in psql first then migrate to the server after some field
experience.

The middle road is probably something like the following:

We could setup a guc for "default_explain_options" that the user could set
or have set for them using alter role.  Maybe we'd want to include a new
option "CLEAN" or "NONE" that tells the system to skip those default and
only use ones that are explicitly specified in the SQL command.  Basically
an envvar like many command line apps use in lieu of an .rc file but with a
simpler way to disable than setting it to nothing.

David J.

[HACKERS] Parallel safety tagging of extension functions

2016-05-19 Thread Andreas Karlsson 

Hi,

I have gone through all our extensions and tried to tag all functions 
correctly according to their parallel safety.


I also did the same for the aggregate functions in a second patch, and 
for min(citext)/max(citext) set a COMBINEFUNC.


The changes for the functions is attached as one huge patch. Feel free 
to suggest a way to split it up or change it in any way if that would 
make it easier to review/apply.


Some open questions:

- How should we modify the aggregate functions when upgrading 
extensions? ALTER AGGREGATE cannot change COMBINEFUNC or PARALLEL. My 
current patch updates the system catalogs directly, which should be safe 
in this case, but is this an acceptable solution?


- Do you think we should add PARALLEL UNSAFE to the functions which we 
know are unsafe to make it obvious that it is intentional?


- I have not added the parallel tags to the functions used by our 
procedural languages. Should we do so?


- I have marked uuid-ossp, chkpass_in() and pgcrypto functions which 
generate random data as safe, despite that they depend on state in the 
backend. My reasoning is that, especially for the pgcrypto functions, 
that nobody should not rely on the PRNG state. For uuid-ossp I am on the 
fence.


- I have touched a lot of legacy libraries, like tsearch2 and the spi/* 
stuff. Is that a good idea?


- I decided to ignore that isn_weak() exists (and would make all input 
functions PARALLEL RESTRICTED) since it is only there is ISN_WEAK_MODE 
is defined. Is that ok?


Andreas


parallel-contrib-1-funcs-v1.patch.gz
Description: application/gzip
diff --git a/contrib/citext/citext--1.1--1.2.sql b/contrib/citext/citext--1.1--1.2.sql
index 5d5e48d..074eec1 100644
--- a/contrib/citext/citext--1.1--1.2.sql
+++ b/contrib/citext/citext--1.1--1.2.sql
@@ -40,3 +40,29 @@ ALTER FUNCTION strpos(citext, citext) PARALLEL SAFE;
 ALTER FUNCTION replace(citext, citext, citext) PARALLEL SAFE;
 ALTER FUNCTION split_part(citext, citext, int) PARALLEL SAFE;
 ALTER FUNCTION translate(citext, citext, text) PARALLEL SAFE;
+
+UPDATE pg_proc SET proparallel = 's'
+WHERE proname = 'min'
+AND proargtypes = 'citext'::regtype::oid::text::oidvector
+AND pronamespace = current_schema()::regnamespace;
+
+UPDATE pg_proc SET proparallel = 's'
+WHERE proname = 'max'
+AND proargtypes = 'citext'::regtype::oid::text::oidvector
+AND pronamespace = current_schema()::regnamespace;
+
+UPDATE pg_aggregate SET aggcombinefn = 'citext_smaller'
+WHERE aggfnoid = (
+	SELECT oid FROM pg_proc
+	WHERE proname = 'min'
+	AND proargtypes = 'citext'::regtype::oid::text::oidvector
+	AND pronamespace = current_schema()::regnamespace
+);
+
+UPDATE pg_aggregate SET aggcombinefn = 'citext_larger'
+WHERE aggfnoid = (
+	SELECT oid FROM pg_proc
+	WHERE proname = 'max'
+	AND proargtypes = 'citext'::regtype::oid::text::oidvector
+	AND pronamespace = current_schema()::regnamespace
+);
diff --git a/contrib/citext/citext--1.2.sql b/contrib/citext/citext--1.2.sql
index 01fbb93..c2d0c0c 100644
--- a/contrib/citext/citext--1.2.sql
+++ b/contrib/citext/citext--1.2.sql
@@ -242,13 +242,17 @@ LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
 CREATE AGGREGATE min(citext)  (
 SFUNC = citext_smaller,
 STYPE = citext,
-SORTOP = <
+SORTOP = <,
+PARALLEL = SAFE,
+COMBINEFUNC = citext_smaller
 );
 
 CREATE AGGREGATE max(citext)  (
 SFUNC = citext_larger,
 STYPE = citext,
-SORTOP = >
+SORTOP = >,
+PARALLEL = SAFE,
+COMBINEFUNC = citext_larger
 );
 
 --
diff --git a/contrib/intagg/intagg--1.0--1.1.sql b/contrib/intagg/intagg--1.0--1.1.sql
index 9bdf355..ad69146 100644
--- a/contrib/intagg/intagg--1.0--1.1.sql
+++ b/contrib/intagg/intagg--1.0--1.1.sql
@@ -6,3 +6,8 @@
 ALTER FUNCTION int_agg_state(internal, int4) PARALLEL SAFE;
 ALTER FUNCTION int_agg_final_array(internal) PARALLEL SAFE;
 ALTER FUNCTION int_array_enum(int4[]) PARALLEL SAFE;
+
+UPDATE pg_proc SET proparallel = 's'
+WHERE proname = 'int_array_aggregate'
+AND proargtypes = 'int4'::regtype::oid::text::oidvector
+AND pronamespace = current_schema()::regnamespace;
diff --git a/contrib/intagg/intagg--1.1.sql b/contrib/intagg/intagg--1.1.sql
index 32b84e4..3796a2a 100644
--- a/contrib/intagg/intagg--1.1.sql
+++ b/contrib/intagg/intagg--1.1.sql
@@ -21,11 +21,11 @@ LANGUAGE INTERNAL;
 
 -- The aggregate function itself
 -- uses the above functions to create an array of integers from an aggregation.
-CREATE AGGREGATE int_array_aggregate (
-	BASETYPE = int4,
+CREATE AGGREGATE int_array_aggregate(int4) (
 	SFUNC = int_agg_state,
 	STYPE = internal,
-	FINALFUNC = int_agg_final_array
+	FINALFUNC = int_agg_final_array,
+	PARALLEL = SAFE
 );
 
 -- The enumeration function
diff --git a/contrib/tsearch2/tsearch2--1.0--1.1.sql b/contrib/tsearch2/tsearch2--1.0--1.1.sql
index e8d3518..b3687e8 100644
--- a/contrib/tsearch2/tsearch2--1.0--1.1.sql
+++ b/contrib/tsearch2/tsearch2--1.0--1.1.sql
@@ -82,3 +82,8 @@ ALTER FUNCTION rewrite_accum(tsquery, tsquery[]) PARALLEL SAFE;
 ALTER

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David Christensen 

> On May 19, 2016, at 3:17 PM, Евгений Шишкин  wrote:
> 
> 
>> On 19 May 2016, at 22:59, Tom Lane  wrote:
>> 
>> David Christensen  writes:
>>> This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN 
>>> (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” for usability.
>> 
>> I'm not sure this is well thought out.  It would mean for example that
>> we could never implement EXPLAIN options that are mutually exclusive
>> ... at least, not without having to redefine ALL as all-except-something.
>> Non-boolean options would be problematic as well.
>> 
> 
> Maybe EVERYTHING would be ok.
> But it is kinda long word to type.

If it’s just a terminology issue, what about EXPLAIN (*); already a precedent 
with SELECT * to mean “everything”.  (MAX? LIKE_I’M_5?) Let the bikeshedding 
begin!

In any case, I think a shorthand for “give me the most possible detail without 
me having to lookup/type/remember the options” is a good tool.

David
--
David Christensen
End Point Corporation
da...@endpoint.com
785-727-1171





-- 
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] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Alvaro Herrera 
Евгений Шишкин wrote:

> Maybe EVERYTHING would be ok.
> But it is kinda long word to type.

There's never need to run the EXPLAIN (EVERTHING) command; you already
know that the answer is 42.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Евгений Шишкин 

> On 19 May 2016, at 22:59, Tom Lane  wrote:
> 
> David Christensen  writes:
>> This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN 
>> (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” for usability.
> 
> I'm not sure this is well thought out.  It would mean for example that
> we could never implement EXPLAIN options that are mutually exclusive
> ... at least, not without having to redefine ALL as all-except-something.
> Non-boolean options would be problematic as well.
> 

Maybe EVERYTHING would be ok.
But it is kinda long word to type.


>   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



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

[HACKERS] Tracking wait event for latches

2016-05-19 Thread Michael Paquier 
Hi all,

As I mentioned $subject a couple of months back after looking at the
wait event facility here:
http://www.postgresql.org/message-id/CAB7nPqTJpgAvOK4qSC96Fpm5W+aCtJ9D=3Vn9AfiEYsur=-j...@mail.gmail.com
I have actually taken some time to implement this idea.

The particular case that I had in mind was to be able to track in
pg_stat_activity processes that are waiting on a latch for synchronous
replication, and here is what this patch gives in this case:
=# alter system set synchronous_standby_names = 'foo';
ALTER SYSTEM
=# select pg_reload_conf();
 pg_reload_conf

 t
(1 row)
=# -- Do something
[...]

And from another session:
=# select wait_event_type, wait_event from pg_stat_activity where pid = 83316;
 wait_event_type | wait_event
-+
 Latch   | SyncRep
(1 row)

This is a boring patch, and it relies on the wait event facility that
has been added recently in 9.6. Note a couple of things though:
1) There is something like 30 code paths calling WaitLatch in the
backend code, all those events are classified and documented similarly
to LWLock events.
2) After discussing this stuff while at PGCon, it does not seem worth
to have any kind of APIs to be able to add in shared memory custom
latch names that extensions could load through _PG_init(). In
replacement to that, there is a latch type flag called "Extension"
that can be used for this purpose.
Comments are welcome, I am adding that in the 9.7 queue.

Regards,
-- 
Michael


wait-event-latch.patch
Description: invalid/octet-stream

-- 
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] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Peter Geoghegan 
On Thu, May 19, 2016 at 12:59 PM, Tom Lane  wrote:
>
> I'm not sure this is well thought out.  It would mean for example that
> we could never implement EXPLAIN options that are mutually exclusive
> ... at least, not without having to redefine ALL as all-except-something.
> Non-boolean options would be problematic as well.

While the spelling needs work, I like the general idea.

-- 
Peter Geoghegan


-- 
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] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Tom Lane 
David Christensen  writes:
> This simple patch adds “ALL” as an EXPLAIN option as shorthand for 
> “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” for usability.

I'm not sure this is well thought out.  It would mean for example that
we could never implement EXPLAIN options that are mutually exclusive
... at least, not without having to redefine ALL as all-except-something.
Non-boolean options would be problematic as 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

[HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David Christensen 
This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN 
(ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” for usability.


0001-Add-EXPLAIN-ALL-shorthand.patch
Description: Binary data


--
David Christensen
End Point Corporation
da...@endpoint.com
785-727-1171




-- 
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] Somebody forgot to pin the built-in access methods

2016-05-19 Thread Alvaro Herrera 
Tom Lane wrote:
> Commit 473b93287 forgot a rather critical detail:
> 
> regression=# drop access method btree;
> DROP ACCESS METHOD
> regression=# select * from tenk1;
> ERROR:  cache lookup failed for access method 403

Hah, nice one.  Thanks for fixing.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[HACKERS] Somebody forgot to pin the built-in access methods

2016-05-19 Thread Tom Lane 
Commit 473b93287 forgot a rather critical detail:

regression=# drop access method btree;
DROP ACCESS METHOD
regression=# select * from tenk1;
ERROR:  cache lookup failed for access method 403

The fact that the command is restricted to superusers doesn't make
this a good idea.

regards, tom lane


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

[HACKERS] Typo in 001_initdb.pl

2016-05-19 Thread Michael Paquier 
Hi all,

I just bumped into the following typo for $subject:
--- a/src/bin/initdb/t/001_initdb.pl
+++ b/src/bin/initdb/t/001_initdb.pl
@@ -31,7 +31,7 @@ command_fails(

 command_fails(
[ 'initdb', '-U', 'pg_test', $datadir ],
-   'role names cannot being with "pg_"');
+   'role names cannot begin with "pg_"');

Regards,
-- 
Michael


initdb-typo.patch
Description: invalid/octet-stream

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

[HACKERS] ExecProject() in advance_aggregates() is rather expensive

2016-05-19 Thread Andres Freund 
Hi,

Since 34d26872ed816b2 ("Support ORDER BY within aggregate function
calls") we use ExecProject() and a slot within advance_aggregates().
Previous to that patch we simply directly filled fcinfo.args with
ExecEvalExpr().

According to my profiles the new way generally is considerably slower,
but especially so if there are a number of aggregates (which each have a
separate projection).

E.g. the profile of
SELECT SUM(abalance), AVG(abalance), count(*) FROM pgbench_accounts;
starts with
+   18.85%  postgres  postgres   [.] ExecProject
+   10.14%  postgres  postgres   [.] advance_aggregates
+9.50%  postgres  postgres   [.] advance_transition_function
+8.92%  postgres  postgres   [.] slot_getsomeattrs


I wonder if we should add a ExecEvalExpr() and/or try to build a single
projection that can compute the input for all transition functions at
once.

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

[HACKERS] Parallel query

2016-05-19 Thread Tatsuo Ishii 
Robert,
(and others who are involved in parallel query of PostgreSQL)

PostgreSQL Enterprise Consortium (one of the PostgreSQL communities in
Japan, in short "PGECons") is planning to test the parallel query
performance of PostgreSQL 9.6. Besides TPC-H (I know you have already
tested on an IBM box), what kind of tests would you like be performed?

We are planning to use a big intel box (like more than 60 cores).
Any suggestions are welcome.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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

Re: [HACKERS] Autovacuum to prevent wraparound tries to consume xid

2016-05-19 Thread Alexander Korotkov 
On Mon, Mar 28, 2016 at 2:05 PM, Alexander Korotkov <
a.korot...@postgrespro.ru> wrote:

> After some debugging I found that vac_truncate_clog consumes xid just to
> produce warning.  I wrote simple patch which replaces
> GetCurrentTransactionId() with ShmemVariableCache->nextXid.  That
> completely fixes this situation for me: ShmemVariableCache was successfully
> updated.
>

I found that direct reading of ShmemVariableCache->nextXid is not corrent,
it's better to use ReadNewTransactionId() then.  Fixed version of patch is
attached.

--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


fix_vac_truncate_clog_xid_consume_2.patch
Description: Binary data

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

Re: [HACKERS] foreign table batch inserts

2016-05-19 Thread Craig Ringer 
On 19 May 2016 at 14:08, Tsunakawa, Takayuki  wrote:


>
>
> Yes, I want FE-BE protocol-level batch inserts/updates/deletes, too.  I
> was just about to start thinking of how to implement it because of recent
> user question in pgsql-odbc.  The OP uses Microsoft SQL Server Integration
> Service (SSIS) to migrate data to PostgreSQL.  He asked for a method to
> speed up multi-row inserts, because the ODBC's multi-row insert API takes
> as long a time as when performing single-row inserts separately.  This may
> prevent the migration to PostgreSQL.
>

Well, there's FE/BE level batching/pipelining already. Just no access to it
from libpq.



-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Declarative partitioning

2016-05-19 Thread Amit Langote 
On 2016/05/19 2:48, Tom Lane wrote:
> Amit Langote  writes:
>> On 2016/05/18 2:22, Tom Lane wrote:
>>> The two ways that we've dealt with this type of hazard are to copy data
>>> out of the relcache before using it; or to give the relcache the
>>> responsibility of not moving a particular portion of data if it did not
>>> change.  From memory, the latter applies to the tuple descriptor and
>>> trigger data, but we've done most other things the first way.
> 
> After actually looking at the code, we do things that way for the
> tupledesc, the relation's rules if any, and RLS policies --- see
> RelationClearRelation().

I think I confused refcounting method of keeping things around with the
RelationClearRelation()'s method.  I now understand that you meant the
latter in your original message.

>> It seems that tuple descriptor is reference-counted; however trigger data
>> is copied.  The former seems to have been done on performance grounds (I
>> found 06e10abc).
> 
> We do refcount tuple descriptors, but we've been afraid to try to rely
> completely on that; there are too many places that assume a relcache
> entry's tupdesc is safe to reference.  It's not that easy to go over to
> a fully refcounted approach, because that creates a new problem of being
> sure that refcounts are decremented when necessary --- that's a pain,
> particularly when a query is abandoned due to an error.

I see.

>> So for a performance-sensitive relcache data structure, refcounting is the
>> way to go (although done quite rarely)?
> 
> I'd be suspicious of this because of the cleanup problem.  The
> don't-replace-unless-changed approach is the one that's actually battle
> tested.

OK, I will try the RelationClearRelation()'s method of keeping partition
descriptor data around so that no repeated copying is necessary.

Thanks,
Amit




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

[HACKERS] PostgreSQL and inherits

2016-05-19 Thread Jan Johansson 
Hi,

I want to discuss about inherits in PostgreSQL.
Everything I write here is my own opinion, but I hope for a good dialog.

I think that inherits is a feature to good to be plain legacy, but it is
unfortunately riddled with some inconsistencies. The inheritance keyword
link tables together in a "is-a" chain, from child up to parent. The
children has the same fields as the parent, plus their own (if they
declare).

In one way there is an illusion of object orientation, where the children
share data with the parent (comes from when you select from a parent in the
inheritance and you see the data), but in another way if you use the
condition "only" (in select) or discover that field behavior (primary key,
foreign key, etc) is not inherited, then inheritance is of the behavior of
interface.

It's like the implementation sits on the fence, and does not know if
inherits is object oriented, or if it is interface oriented.

This inconsistency makes the feature not as smooth as it could be.

A suggestion would be to walk down the path for the object oriented
approach, meaning that fields shared with the parent, also share behavior
(primary key, foreign key, etc). This suggestion might break compatibility
with some implementations (using inheritance and where coding has been done
to account for the object-interface-oriented approach).

Another suggestion would be to walk down the path for the interface
oriented approach, meaning that nothing is shared between the child and
parent, except for the field name and types. This may also break
compatibility.

The third suggestion would be to let inheritance be as it is, but to
introduce "extends" keyword to make a more clean object-oriented approach
to the "is-a" chain. Then nothing is broken, but it adds complexity to the
schema.

I'd be happy to know your reflections about the subject.

Kind regards

[HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-05-19 Thread Amit Langote 
Currently in HEAD and 9.6, one can issue a non-exclusive backup on
standby, so this is OK:

select pg_is_in_recovery();
 pg_is_in_recovery
---
 t
(1 row)

select pg_start_backup('sby-bkp-test', 'f', 'f');
 pg_start_backup
-
 0/5000220
(1 row)

However the following happens:

select pg_xlogfile_name_offset(pg_start_backup('sby-bkp-test', 'f', 'f'));
ERROR:  recovery is in progress
HINT:  pg_xlogfile_name_offset() cannot be executed during recovery.

Should this restriction be relaxed or am I missing something?

Thanks,
Amit




-- 
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] foreign table batch inserts

2016-05-19 Thread Tsunakawa, Takayuki 
From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
On 19 May 2016 at 01:39, Michael Paquier  wrote:
On Wed, May 18, 2016 at 12:27 PM, Craig Ringer  wrote:
> On 18 May 2016 at 06:08, Michael Paquier  wrote:
>> > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ?
>>
>> Using a single query string with multiple values, perhaps, but after
>> that comes into consideration query string limit particularly for
>> large text values... The query used for the insertion is a prepared
>> statement since writable queries are supported in 9.3, which makes the
>> code quite simple actually.
>
> This should be done how PgJDBC does batches. It'd require a libpq
> enhancement, but it's one we IMO need anyway: allow pipelined query
> execution from libpq.

That's also something that would be useful for the ODBC driver. Since
it is using libpq as a hard dependency and does not speak the protocol
directly, it is doing additional round trips to the server for this
exact reason when preparing a statement.


Yes, I want FE-BE protocol-level batch inserts/updates/deletes, too.  I was 
just about to start thinking of how to implement it because of recent user 
question in pgsql-odbc.  The OP uses Microsoft SQL Server Integration Service 
(SSIS) to migrate data to PostgreSQL.  He asked for a method to speed up 
multi-row inserts, because the ODBC's multi-row insert API takes as long a time 
as when performing single-row inserts separately.  This may prevent the 
migration to PostgreSQL.

And it's also useful for ECPG.  Our customer wanted ECPG to support multi-row 
insert to migrate to PostgreSQL, because their embedded-SQL apps use the 
feature with a commercial database.

If you challenge this feature, I can help you by reviewing and testing, 
implementing the ODBC and ECPG sides, etc.

Regards
Takayuki Tsunakawa