Re: [HACKERS] memory layouts for binary search in nbtree
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
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
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
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
> 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
> 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
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
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
=?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
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
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 FUNCTION
Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand
> 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
Евгений Шишкин 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
> 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
