Re: [HACKERS] [CORE] Restore-reliability mode

[Michael Paquier]

On Sat, Jun 6, 2015 at 12:05 AM, Alvaro Herrera wrote:
> Michael Paquier wrote:
> What happened with the extension tests patches you submitted?  They
> seemed valuable to me, but I lost track.

Those ones are registered in the queue of 9.6:
https://commitfest.postgresql.org/5/187/
And this is the latest patch:
http://www.postgresql.org/message-id/CAB7nPqSQr1UjZ1h8=be1wBq3mMdmM38nrjBKvBJuM--tTTY=e...@mail.gmail.com
This patch extends prove_check by giving the possibility for a given
utility using t/ to add extra modules in t/extra that will be
installed and usable for its regression tests. This becomes more
interesting considering as well that pg_upgrade could be switched to
use the TAP infrastructure, where we could have modules dedicated to
only the tests of pg_upgrade (supporting TAP tests on Windows is a
necessary condition though before switching pg_upgrade).
-- 
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] could not truncate directory "pg_subtrans": apparent wraparound

[Thomas Munro]

On Sat, Jun 6, 2015 at 1:25 PM, Alvaro Herrera  wrote:
> Thomas Munro wrote:
>
>> My idea was that if I could get oldestXact == next XID in
>> TruncateSUBSTRANS, then TransactionIdToPage(oldestXact) for a value of
>> oldestXact that happens to be immediately after a page boundary (so
>> that xid % 2048 == 0) might give page number that is >=
>> latest_page_number, causing SimpleLruTruncate to print that message.
>> But I can't figure out how to get next XID == oldest XID, because
>> vacuumdb --freeze --all consumes xids itself, so in my first attempt
>> at this, next XID is always 3 ahead of the oldest XID when a
>> checkpoint is run.
>
> vacuumdb starts by querying pg_database, which eats one XID.
>
> Vacuum itself only uses one XID when vac_truncate_clog() is called.
> This is called from vac_update_datfrozenxid(), which always happen at
> the end of each user-invoked VACUUM (so three times for vacuumdb if you
> have three databases); autovacuum does it also at the end of each run.
> Maybe you can get autovacuum to quit before doing it.
>
> OTOH, if the values in the pg_database entry do not change,
> vac_truncate_clog is not called, and thus vacuum would finish without
> consuming an XID.

I have manage to reproduce it a few times but haven't quite found the
right synchronisation hacks to make it reliable so I'm not posting a
repro script yet.

I think it's a scary sounding message but very rare and entirely
harmless (unless you really have wrapped around...).  The fix is
probably something like: if oldest XID == next XID, then just don't
call SimpleLruTruncate (truncation is deferred until the next
checkpoint), or perhaps (if we can confirm this doesn't cause problems
for dirty pages or that there can't be any dirty pages before cutoff
page because of the preceding flush (as I suspect)) we could use
cutoffPage = TransactionIdToPage(oldextXact - 1) if oldest == next, or
maybe even always.

-- 
Thomas Munro
http://www.enterprisedb.com


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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file

[Amit Kapila]

On Fri, Jun 5, 2015 at 10:51 AM, Amit Kapila 
wrote:

> On Fri, Jun 5, 2015 at 9:57 AM, Andrew Dunstan 
> wrote:
>
>>
>> On 06/04/2015 11:35 PM, Amit Kapila wrote:
>>
>>>
>>> Theoretically, I don't see much problem by changing the checks
>>> way you have done in patch, but it becomes different than what
>>> we have in destroy_tablespace_directories() and it is slightly
>>> changing the way check was originally done in
>>> create_tablespace_directories(), basically original check will try
>>> unlink if lstat returns non-zero return code. If you want to proceed
>>> with the changed checks as in v3, then may be we can modify
>>> comments on top of function remove_tablespace_symlink() which
>>> indicates that it works like destroy_tablespace_directories().
>>>
>>>
>>
>> The difference is that here we're getting the list from a base backup and
>> it seems to me the risk of having a file we don't really want to unlink is
>> significantly greater.
>>
>
> Okay, I think I can understand why you want to be cautious for
> having a different check for this path, but in that case there is a
> chance that recovery might fail when it will try to create a symlink
> for that file.  Shouldn't we then try to call this new function only
> when we are trying to restore from tablespace_map file and also
> is there a need of ifdef S_ISLINK in remove_tablespace_link?
>

Shall I send an updated patch on these lines or do you want to
proceed with v3 version?


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] [CORE] Restore-reliability mode

[Bruce Momjian]

On Fri, Jun  5, 2015 at 04:54:56PM +0100, Simon Riggs wrote:
> On 5 June 2015 at 16:05, Bruce Momjian  wrote:
> 
> 
> Please address some of the specific issues I mentioned. 
> 
> 
> I can discuss them but not because I am involved directly. I take
> responsibility as a committer and have an interest from that perspective.
> 
> In my role at 2ndQuadrant, I approved all of the time Alvaro and Andres spent
> on submitting, reviewing and fixing bugs - at this point that has cost
> something close to fifty thousand dollars just on this feature and subsequent
> actions. (I believe the feature was originally funded, but we never saw a 
> penny
> of that, though others did.)

Yes, the burden has fallen heavily on Alvaro.  I personally am concerned
that many people were focusing on 9.5 rather than helping him.  I think
that was a mistake on our part and we need to take reliability problems
more seriously.

What has also concerned me is that there are so many 9.3/9.4 bugs in
this area that few of us can even understand what was fixed when, and we
are then having problems figuring out what bugs were present when
analyzing bug reports.  pg_upgrade has made this worse by allowing
multi-xact bugs to propagate across major versions, and pg_upgrade had
some multi-xact bugs of its own in early 9.3 releases. :-(

> The problem
> with the multi-xact case is that we just kept fixing bugs as people
> found them, and did not do a holistic review of the code. 
> 
> 
> I observed much discussion and review. The bugs we've had have all been fairly
> straightforwardly fixed. There haven't been any design-level oversights or
> head-palm moments. It's complex software that had complex behaviour that 
> caused
> problems. The problem has been that anything on-disk causes more problems when
> errors occur. We should review carefully anything that alters the way on-disk
> structures work, like the WAL changes, UPSERTs new mechanism etc..

Agreed.  However, I think a thorough review early on could have caught
many of these bugs before they were reported by users.  As proof, even
in the past few weeks, review is finding bugs before they are found by
users.

> From my side, it is only recently I got some clear answers to my questions
> about how it worked. I think it is very important that major features have
> extensive README type documentation with them so the underlying principles 
> used
> in the development are clear. I would define the measure of a good feature as
> whether another committer can read the code comments and get a good feel. A 
> bad
> feature is one where committers walk away from it, saying I don't really get 
> it
> and I can't read an explanation of why it does that. Tom's most significant
> contribution is his long descriptive comments on what the problem is that need
> to be solved, the options and the method chosen. Clarity of thought is what
> solves bugs.

Yes, I think we should have done that early-on for multi-xact, and I am
hopeful we will learn to do that more often when complex features are
implemented, or when we identify areas that are more complex than we
thought.

> Overall, I don't see the need to stop the normal release process and do a
> holistic review. But I do think we should check each feature to see whether it
> is fully documented or whether we are simply trusting one of us to be around 
> to
> fix it.

Agreed.  We just need to be honest that we are doing what we need for
reliability and not allow schedule and feature pressure to cause us to
skimp in this area.

> I am just saying we need to ask the
> reliability question _first_.
> 
> 
> Agreed
>  
> 
> Let me restate something that has appeared in many replies to my ideas
> --- I am not asking for infinite or unbounded review, but I am asking
> that we make sure reliability gets the proper focus in relation to our
> time pressures.  Our balance was so off a month ago that I feel only a
> full stop on time pressure would allow us to refocus because people are
> not good at focusing on multiple things. It is sometimes necessary to
> stop everything to get people's attention, and to help them remember
> that without reliability, a database is useless.
> 
> 
> Here, I think we are talking about different types of reliability. PostgreSQL
> software is well ahead of most industry measures of quality; these recent bugs
> have done nothing to damage that, other than a few people woke up and said
> "Wow! Postgres had a bug??!?!?". The presence of bugs is common and if we have
> grown unused to them, we should be wary of that, though not tolerant.

In going over the 9.5 commits, I was struck by a high volume of cleanups
and fixes, which is good.

> PostgreSQL is now reliable in the sense that we have many features that ensure
> availability even in the face of software problems and bug induced corruption.
> Those have helped us get out of the current situations, giving users a
> workaround whil

Re: [HACKERS] Warn about using single user + standby_mode

[Amit Kapila]

On Fri, Jun 5, 2015 at 9:59 PM, Andres Freund  wrote:
>
> Hi,
>
> When primary_conninfo is configured and standby_mode is enabled single
> user mode starts up, but at some point will just wait in a latch for WAL
> to show up. Which will obviously never happen as walreceiver isn't up.
>

So this essentially means as it stands single user mode can't work
for standby.

> Should we just error out in that case, or at least display a log message
> about that fact?
>

I think error is better in this case as there seems no chance for
user to proceed in this case.  It is even better if we can detect
this early and error out before even reaching the stage where it
starts waiting out for WAL to show up.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] could not truncate directory "pg_subtrans": apparent wraparound

[Alvaro Herrera]

Thomas Munro wrote:

> My idea was that if I could get oldestXact == next XID in
> TruncateSUBSTRANS, then TransactionIdToPage(oldestXact) for a value of
> oldestXact that happens to be immediately after a page boundary (so
> that xid % 2048 == 0) might give page number that is >=
> latest_page_number, causing SimpleLruTruncate to print that message.
> But I can't figure out how to get next XID == oldest XID, because
> vacuumdb --freeze --all consumes xids itself, so in my first attempt
> at this, next XID is always 3 ahead of the oldest XID when a
> checkpoint is run.

vacuumdb starts by querying pg_database, which eats one XID.

Vacuum itself only uses one XID when vac_truncate_clog() is called.
This is called from vac_update_datfrozenxid(), which always happen at
the end of each user-invoked VACUUM (so three times for vacuumdb if you
have three databases); autovacuum does it also at the end of each run.
Maybe you can get autovacuum to quit before doing it.

OTOH, if the values in the pg_database entry do not change,
vac_truncate_clog is not called, and thus vacuum would finish without
consuming an XID.

-- 
Á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] Is it possible to have a "fast-write" Index?

[deavid]

Thanks to everybody for answering. I wasn't expecting this attention; this
is a great community :-)

Jim asked me about something real. Well, the problem is this showed up more
than five years ago, and keeps popping from time to time since in different
circumstances. I solved them in different ways each time, depending the
exact use-case. I wanted to generalize, because seems a good feature for
several situations; and I don't expect a solution for me as each time I hit
with this I found some way to sort it out.
As Jim said, we need here are figures for real examples, and i don't have
yet. I'll do my "homework" and email back with exact problems with exact
timing. Give me a week or two.

Also, some of you are talking about IO. Well, it's hard to say without the
figures here, but I'm pretty sure I'm hitting CPU time only. We use SSD on
those big databases, and also in my tests i tried setting fsync=off.

So the problem is: i see a low iowait, and CPU time for one core is at
80-90% most of the time. I can buy more ram, better disks, or cpu's with
more cores. But one cpu core would have more-or-less the same speed no
matter how much money you invest.

When someone wants a delayed-write index is similar to setting
 "synchronous_commit = off". We want to give an OK to the backend as soon
as is possible and do this work in background. But we also want some
reliability against crashes.

Also, if the task is done in background it may be done from other backend,
so probably several indexes could be packed at once using different backend
processes. We could use the entire cpu if our index writes aren't tied to
the session who wrote the row.

PD: I'm very interested on existent approaches like GIN or BRIN (this one
is new to me). Thanks a lot; i'll try them in my tests.

Re: [HACKERS] Is it possible to have a "fast-write" Index?

[Gavin Flower]

On 06/06/15 04:07, deavid wrote:
There are several use cases where I see useful an index, but adding it 
will slow too much inserts and updates.
For example, when we have 10 million rows on a table, and it's a table 
which has frequent updates, we need several index to speed up selects, 
but then we'll slow down updates a lot, specially when we have 10 or 
more indexes.
Other cases involve indexes for text search, which are used only for 
user search and aren't that important, so we want to have them, but we 
don't want the overload they put whenever we write on the table.
I know different approaches that already solve some of those problems 
in some ways (table partitioning, partial indexes, etc), but i don't 
feel they are the solution to every problem of this kind.


Some people already asked for "delayed write" indexes, but the idea 
gets discarded because the index could get out of sync, so it can omit 
results and this is unacceptable. But i think maybe that could be 
fixed in several ways and we can have a fast and reliable index (but 
maybe not so fast on selects).


Since I do not know every internal of postgres, i feel simpler to 
share here and ask which things can or cannot be done.


Let's imagine there is a new type of index called "weird_btree", where 
we trade-off simplicity for speed. In almost every mode, we will rely 
on VACUUM to put our index in optimal state.


Mode 1: on "aminsert" mark the index as INVALID. So, if you modified 
the table you need to run REINDEX/CREATE INDEX CONCURRENTLY before 
doing SELECT. This is almost the same as create index concurrently, 
the main difference is you don't have to remember to drop the index 
before writing. (I don't see much benefit here)


Mode 2: on "aminsert", put the new entry in a plain, unordered list 
instead of the btree. Inserting at the end of a list should be faster 
than big btrees and you'll know later which entries you missed indexing.


Mode 2.a: on index scan (amrescan, amgettuple), pretend that after the 
btree there is the list and output every row, out-of order. You will 
have to tell postgres that our index isn't sorted and it will have to 
recheck every row.


Mode 2.b: mark the index invalid instead. When doing the next vacuum, 
sort the list and insert it to the btree in a bulk operation. If it's 
ok, mark the index valid.


Mode 3: on "aminsert", put the new entry on a second btree; leaving 
the first one untouched. Because the second btree is new, will be 
small, and writes should be faster. When doing a index scan, read 
tuples from both at same time (like merge sort). On vacuum, merge the 
second btree onto the first. On this mode, the index is sorted and 
there's no need of recheck.


Anyone thinks this would be a interesting feature for postgresql?
Did I miss something?

PD: Maybe it's also possible to take advantage of clustering, and have 
indexes which entries are range of TIDs; but i'm not sure if this is 
too exotic, or if it will make a difference.


Sincerely,
David.

How about a hybrid indexing system, with 2 parts:

(1) existing index system which is checked first and has been mostly 
optimised for speed of reading.  If there are only a few inserts/updates 
and the system is not heavily loaded, then it gets modified 
immediately.  The threshold for being too busy, and few enough changes, 
could be configurable.


(2) overflow index optimised for writing.  Possible in memory and not 
backed to permanent storage.  A crash would require a complete index 
rebuild - but only when there were entries in it (or at least more than 
some configurable threshold, to allow for cases were some missing index 
entries are acceptable).


So where the index is needed for a query, part 1 is checked first, and 
the part 2 if necessary


Have a background process that will move entries from part 2 to part 1, 
when the systems is less busy.



Cheers,
Gavin





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

Re: [HACKERS] could not truncate directory "pg_subtrans": apparent wraparound

[Thomas Munro]

On Sat, Jun 6, 2015 at 10:12 AM, Thomas Munro
 wrote:
> On Sat, Jun 6, 2015 at 9:45 AM, Dan Langille  wrote:
>> I noticed this today on my 9.4.2 server running on FreeBSD 10.1:
>>
>> Jun  5 18:59:40 slocum postgres[986]: [3957-1] LOG:  could not truncate 
>> directory "pg_subtrans": apparent wraparound
>>
>> Looking at a post from 2010, Tom Lane suggest this information was useful:
>>
>> [root@slocum:/usr/local/pgsql/data/pg_subtrans] # ls -l
>> total 1
>> -rw---  1 pgsql  pgsql  8192 Jun  5 19:04 0032
>>
>> This not not a high throughput server.
>
> It looks like subtrantransactions may have a fencepost error similar
> to multixacts, described here:
>
> http://www.postgresql.org/message-id/CAEepm=0DqAtnM=23oq44bbnwvn3g6+dxx+s5g4jrbp-vy8g...@mail.gmail.com
>
> I will try to repro this.

My idea was that if I could get oldestXact == next XID in
TruncateSUBSTRANS, then TransactionIdToPage(oldestXact) for a value of
oldestXact that happens to be immediately after a page boundary (so
that xid % 2048 == 0) might give page number that is >=
latest_page_number, causing SimpleLruTruncate to print that message.
But I can't figure out how to get next XID == oldest XID, because
vacuumdb --freeze --all consumes xids itself, so in my first attempt
at this, next XID is always 3 ahead of the oldest XID when a
checkpoint is run.

-- 
Thomas Munro
http://www.enterprisedb.com


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

Re: [HACKERS] Further issues with jsonb semantics, documentation

[Peter Geoghegan]

On Fri, Jun 5, 2015 at 1:05 PM, Andrew Dunstan  wrote:
> So probably the least invasive change would be to rename the text[] variant
> operator to something like "#-" and rename the corresponding function to
> jsonb_delete_path.
>
> We could also decide not to keep an operator at all, on the ground that we
> think we'll implement a type that encapsulates json pointer in 9.6, and just
> keep the renamed function.

Obviously I prefer the latter option, but the former is still an
improvement. To repeat myself, ambiguities around operators are not
the only problem: It seems no good to me that there is no way to
accomplish an equivalent outcome to that shown below with the
similarly-spelled operator you talk about (that is, the operator
currently spelled "operator jsonb - text[]"):

postgres=# select '["a", "c", "a"]'::jsonb - 'a';
 ?column?
--
 ["c"]
(1 row)

With the operator currently spelled "operator jsonb - text[]", at the
very least you have to do this instead:

postgres=# select '["a", "c", "a"]'::jsonb - '{0}'::text[] - '{1}'::text[];
 ?column?
--
 ["c"]
(1 row)

If nothing else, these operators are too dissimilar for overloading to
be helpful.
-- 
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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Robert Haas]

On Fri, Jun 5, 2015 at 4:40 PM, Andres Freund  wrote:
>>I think we would be foolish to rush that part into the tree.  We
>>probably got here in the first place by rushing the last round of
>>fixes too much; let's try not to double down on that mistake.
>
> My problem with that approach is that I think the code has gotten 
> significantly more complex in the least few weeks. I have very little trust 
> that the interactions between vacuum, the deferred truncations in the 
> checkpointer, the state management in shared memory and recovery are correct. 
> There's just too many non-local subtleties here.
>
> I don't know what the right thing to do here is.

That may be true, but we don't need to get to perfect to be better
than 9.4.2 and 9.4.3, where some people can't start the database.

I will grant you that, if the patch I committed today introduces some
regression that is even worse, life will suck.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Further issues with jsonb semantics, documentation

[Peter Geoghegan]

On Fri, Jun 5, 2015 at 10:51 AM, Andrew Dunstan  wrote:
>>> Also, what about negative array subscripting (making the 9.4-era
>>> "operator jsonb -> integer" operator support that for consistency with
>>> the new "operator jsonb - integer" operator)? Should I write the
>>> patch? Will you commit it if I do?

> Send the first one, I'm still thinking about the second one.

The first patch is attached.

Regardless of anything else, I see no reason to delay applying my
documentation patch for "operator jsonb - text" [1].

Thanks

[1] 
http://www.postgresql.org/message-id/cam3swzqfswmi2avi-lun_jbyh-rfhq3-0fm8txpw8olc+v8...@mail.gmail.com
-- 
Peter Geoghegan
From 6513017eabbd4bdd4980056ed73ca8e3fbe58d1b Mon Sep 17 00:00:00 2001
From: Peter Geoghegan 
Date: Fri, 5 Jun 2015 13:55:48 -0700
Subject: [PATCH] Desupport jsonb subscript deletion on objects

Supporting deletion of JSON pairs within jsonb objects using an
array-style integer subscript allowed for surprising outcomes.  This was
mostly due to the implementation-defined ordering of pairs within
objects for jsonb.

It also seems desirable to make jsonb integer subscript deletion
consistent with the 9.4 era general purpose integer subscripting
operator for jsonb (although that operator returns NULL when an object
is encountered, while we prefer to throw an error).
---
 doc/src/sgml/func.sgml|  5 ++--
 src/backend/utils/adt/jsonfuncs.c |  5 
 src/test/regress/expected/jsonb.out   | 56 ++-
 src/test/regress/expected/jsonb_1.out | 56 ++-
 src/test/regress/sql/jsonb.sql| 11 +--
 5 files changed, 13 insertions(+), 120 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c6e3540..be0c3d6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10309,8 +10309,9 @@ table2-mapping

 -
 integer
-Delete the field or element with specified index (Negative
-integers count from the end)
+Delete the array element with specified index (Negative
+integers count from the end).  Throws an error if top level
+container is not an array.
 '["a", "b"]'::jsonb - 1 


diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index f87ba77..c14d3f7 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -3400,6 +3400,11 @@ jsonb_delete_idx(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  errmsg("cannot delete from scalar")));
 
+	if (JB_ROOT_IS_OBJECT(in))
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot delete from object using integer subscript")));
+
 	if (JB_ROOT_COUNT(in) == 0)
 		PG_RETURN_JSONB(in);
 
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 412bf97..e6654d4 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3031,54 +3031,6 @@ select '["a","b","c"]'::jsonb - -4;
  ["a", "b", "c"]
 (1 row)
 
-select '{"a":1, "b":2, "c":3}'::jsonb - 3;
- ?column? 
---
- {"a": 1, "b": 2, "c": 3}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - 2;
- ?column? 
---
- {"a": 1, "b": 2}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - 1;
- ?column? 
---
- {"a": 1, "c": 3}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - 0;
- ?column? 
---
- {"b": 2, "c": 3}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - -1;
- ?column? 
---
- {"a": 1, "b": 2}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - -2;
- ?column? 
---
- {"a": 1, "c": 3}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - -3;
- ?column? 
---
- {"b": 2, "c": 3}
-(1 row)
-
-select '{"a":1, "b":2, "c":3}'::jsonb - -4;
- ?column? 
---
- {"a": 1, "b": 2, "c": 3}
-(1 row)
-
 select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
 jsonb_set 
 --
@@ -3192,12 +3144,8 @@ select '[]'::jsonb - 'a';
 
 select '"a"'::jsonb - 1; -- error
 ERROR:  cannot delete from scalar
-select '{}'::jsonb -  1 ;
- ?column? 
---
- {}
-(1 row)
-
+select '{}'::jsonb -  1; -- error
+ERROR:  cannot delete from object using integer subscript
 select '[]'::jsonb - 1;
  ?column? 
 --
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index 4ead74b..0a1ec93 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -3031,54 +3031,6 @@ select '["a","b","c"]'::jsonb - -4;
  ["a", "b", "c"]
 (1 row)
 
-select '{"a":1, "b":2, "c":3}'::jsonb -

Re: [HACKERS] could not truncate directory "pg_subtrans": apparent wraparound

[Thomas Munro]

On Sat, Jun 6, 2015 at 9:45 AM, Dan Langille  wrote:
> I noticed this today on my 9.4.2 server running on FreeBSD 10.1:
>
> Jun  5 18:59:40 slocum postgres[986]: [3957-1] LOG:  could not truncate 
> directory "pg_subtrans": apparent wraparound
>
> Looking at a post from 2010, Tom Lane suggest this information was useful:
>
> [root@slocum:/usr/local/pgsql/data/pg_subtrans] # ls -l
> total 1
> -rw---  1 pgsql  pgsql  8192 Jun  5 19:04 0032
>
> This not not a high throughput server.

It looks like subtrantransactions may have a fencepost error similar
to multixacts, described here:

http://www.postgresql.org/message-id/CAEepm=0DqAtnM=23oq44bbnwvn3g6+dxx+s5g4jrbp-vy8g...@mail.gmail.com

I will try to repro this.

-- 
Thomas Munro
http://www.enterprisedb.com


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

[HACKERS] could not truncate directory "pg_subtrans": apparent wraparound

[Dan Langille]

I noticed this today on my 9.4.2 server running on FreeBSD 10.1:

Jun  5 18:59:40 slocum postgres[986]: [3957-1] LOG:  could not truncate 
directory "pg_subtrans": apparent wraparound

Looking at a post from 2010, Tom Lane suggest this information was useful:

[root@slocum:/usr/local/pgsql/data/pg_subtrans] # ls -l
total 1
-rw---  1 pgsql  pgsql  8192 Jun  5 19:04 0032

This not not a high throughput server.

—
Dan Langille
http://langille.org/







signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Alvaro Herrera]

Joshua D. Drake wrote:

> I believe there are likely quite a few parties willing to help test, if we
> knew how?

The code involved is related to checkpoints, pg_basebackups that take a
long time to run, and multixact freezing and truncation.  If you can set
up test servers that eat lots of multixacts(*), then have many multixact
freezes and truncations occur, that would probably hit the right spots.
(You can set very frequent freezing by lowering
vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age
settings.  Perhaps changing multixact_freeze_max_age would lead to other
interesting results too.  Truncation occurs during checkpoint, some time
after freezing, so it's probably good that those are frequent too.)

Also, pg_upgrade prior to 9.3.4 is able to produce database with
invalid oldestMulti=1, if you start from a 9.2-or-earlier database that
has already consumed some number of multis.  It would be good to test
starting from those, too, just to make sure the mechanism that deals
with that is good.  There are at least two variations: those that have
nextMulti larger than 65k but less than 2 billion, and those that have
nextMulti closer to 4 billion.  (I think a 9.2 database with nextMulti
less than 65k is uninteresting, because the resulting oldestMulti=1 is
the correct value there.)

(*) Thomas Munro posted a sample program that does that; I believe with
minimal changes you could turn it into infinite looping instead of a
pre-set number of iteration.  Also, perhaps it's possible to come up
with programs that consume multixacts even faster than that, and that
create larger multixacts too.  All variations are useful.

-- 
Á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] [CORE] Restore-reliability mode

[Peter Geoghegan]

On Fri, Jun 5, 2015 at 7:00 AM, Robert Haas  wrote:
> I do agree that an indefinite development freeze with unclear
> parameters for resuming development and unclear goals is a bad plan.
> But I think giving ourselves a little more time to, say, turn the
> buildfarm consistently green, and, say, fix the known but
> currently-unfixed multixact bugs, and, say, fix the known bugs in 9.5
> features is a good plan, and I hope you and others will support it.

FWIW, I have 3 pending bug fixes for UPSERT. While those are pretty
benign issues, I'd be annoyed if they didn't get into the first 9.5
beta (or alpha, even).

-- 
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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Joshua D. Drake]

On 06/05/2015 01:56 PM, Tom Lane wrote:


If we have confidence that we can ship something on Monday that is
materially more trustworthy than the current releases, then let's aim to
do that; but let's ship only patches we are confident in.  We can do
another set of releases later that incorporate additional fixes.  (As some
wise man once said, there's always another bug.)

If what you're saying is that you don't trust the already-committed patch
very much, then maybe we'd better hold off another couple weeks for more
review and testing.

regards, tom lane



I believe there are likely quite a few parties willing to help test, if 
we knew how?


Sincerely,

jD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Tom Lane]

Andres Freund  writes:
> On June 5, 2015 10:02:37 PM GMT+02:00, Robert Haas  
> wrote:
>> I think we would be foolish to rush that part into the tree.  We
>> probably got here in the first place by rushing the last round of
>> fixes too much; let's try not to double down on that mistake.

> My problem with that approach is that I think the code has gotten 
> significantly more complex in the least few weeks. I have very little trust 
> that the interactions between vacuum, the deferred truncations in the 
> checkpointer, the state management in shared memory and recovery are correct. 
> There's just too many non-local subtleties here. 

> I don't know what the right thing to do here is.

My gut feeling is that rushing to make a release date is the wrong thing.

If we have confidence that we can ship something on Monday that is
materially more trustworthy than the current releases, then let's aim to
do that; but let's ship only patches we are confident in.  We can do
another set of releases later that incorporate additional fixes.  (As some
wise man once said, there's always another bug.)

If what you're saying is that you don't trust the already-committed patch
very much, then maybe we'd better hold off another couple weeks for more
review and testing.

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] Further issues with jsonb semantics, documentation

[Tom Lane]

Andrew Dunstan  writes:
> Yeah, Good point. Actually, if my memory serves me correctly (always a 
> dubious bet), the avoidance of that kind of ambiguity is why we 
> introduced the #> and #>> operators in the first place, after going 
> round and round for a while on what the API would look like. I should 
> have remembered that when this came around. Mea culpa.

> So probably the least invasive change would be to rename the text[] 
> variant operator to something like "#-" and rename the corresponding 
> function to jsonb_delete_path.

Not sure that's a great choice of operator name; consider for example
select 4#-1;
It's not immediately obvious whether the "-" is meant as a separate
unary minus.  There are heuristics in the lexer that try to deal with
cases like this, but it doesn't seem like a good plan to double down
on such heuristics always doing the right thing.

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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Andres Freund]

On June 5, 2015 10:02:37 PM GMT+02:00, Robert Haas  
wrote:
>On Fri, Jun 5, 2015 at 2:47 PM, Andres Freund 
>wrote:
>> On 2015-06-05 14:33:12 -0400, Tom Lane wrote:
>>> Robert Haas  writes:
>>> > 1. The problem that we might truncate an SLRU members page away
>when
>>> > it's in the buffers, but not drop it from the buffers, leading to
>a
>>> > failure when we try to write it later.
>>
>> I've got a fix for this, and about three other issues I found during
>> development of the new truncation codepath.
>>
>> I'll commit the fix tomorrow.
>
>OK.  Then I think we should release next week, so we get the fixes we
>have out before PGCon.  The current situation is not good.
>
>>> > I think we might want to try to fix one or both of those before
>>> > cutting a new release.  I'm less sold on the idea of installing
>>> > WAL-logging in this minor release.  That probably needs to be
>done,
>>> > but right now we've got stuff that worked in early 9.3.X release
>and
>>> > is now broken, and I'm in favor of fixing that first.
>>
>> I've implemented this, and so far it removes more code than it
>> adds. It's imo also a pretty clear win in how understandable the code
>> is.  The remaining work, besides testing, is primarily going over
>lots
>> of comment and updating them. Some of them are outdated by the patch,
>> and some already were.
>>
>> Will post tonight, together with the other fixes, after I get back
>from
>> climbing.
>>
>> My gut feeling right now is that it's a significant improvement, and
>> that it'll be reasonable to include it. But I'd definitely like some
>> independent testing for it, and I'm not sure if that's doable in time
>> for the wrap.
>
>I think we would be foolish to rush that part into the tree.  We
>probably got here in the first place by rushing the last round of
>fixes too much; let's try not to double down on that mistake.

My problem with that approach is that I think the code has gotten significantly 
more complex in the least few weeks. I have very little trust that the 
interactions between vacuum, the deferred truncations in the checkpointer, the 
state management in shared memory and recovery are correct. There's just too 
many non-local subtleties here. 

I don't know what the right thing to do here is.



--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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] Further issues with jsonb semantics, documentation

[Andrew Dunstan]

On 06/05/2015 02:32 PM, Alvaro Herrera wrote:

'some jsonb value' - '{foo,bar}' is already ambiguous  - the RH operand
could be a single text datum or a text array.

Hmm, but that's not in 9.4, so we can still tweak it if necessary.

Consider this jsonb datum.  Nobody in their right mind would have a key
that looks like a path, I hear you say; yet I'm sure this is going to
happen.

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}, "{c,a}": "uh"}' ;
 jsonb
--
  {"a": "1", "b": "2", "c": {"a": "2"}, "{c,a}": "uh"}
(1 fila)

This seems pretty surprising to me:

-- here, the -(jsonb,text) operator is silently chosen, even though the
-- right operand looks like an array.  And we do the wrong thing.
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}';
?column?
---
  {"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)

-- here, the -(jsonb,text[]) operator is chosen
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  _text '{c,a}';
?column?
---
  {"a": "1", "b": "2", "c": {}}
(1 fila)

But this seems worse to me, because we silently do nothing:

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}';
?column?
---
  {"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)


I think the first operator can be qualified as dangerous.  If you delete
that one, then it's fine because you can't do that query anymore because
of the conflict with -(jsonb, int).

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}';
ERROR:  operator is not unique: jsonb - unknown
LÍNEA 1: ...elect jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}'...
   ^
SUGERENCIA:  Could not choose a best candidate operator. You might need to add 
explicit type casts.




Yeah, Good point. Actually, if my memory serves me correctly (always a 
dubious bet), the avoidance of that kind of ambiguity is why we 
introduced the #> and #>> operators in the first place, after going 
round and round for a while on what the API would look like. I should 
have remembered that when this came around. Mea culpa.


So probably the least invasive change would be to rename the text[] 
variant operator to something like "#-" and rename the corresponding 
function to jsonb_delete_path.


We could also decide not to keep an operator at all, on the ground that 
we think we'll implement a type that encapsulates json pointer in 9.6, 
and just keep the renamed function.


cheers

andrew




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

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Robert Haas]

On Fri, Jun 5, 2015 at 2:36 PM, Alvaro Herrera  wrote:
> Tom Lane wrote:
>> Robert Haas  writes:
>
>> > There are at least two other known issues that seem like they should
>> > be fixed before we release:
>>
>> > 1. The problem that we might truncate an SLRU members page away when
>> > it's in the buffers, but not drop it from the buffers, leading to a
>> > failure when we try to write it later.
>>
>> > 2. Thomas's bug fix for another longstanding but that occurs when you
>> > run his checkpoint-segment-boundary.sh script.
>>
>> > I think we might want to try to fix one or both of those before
>> > cutting a new release.  I'm less sold on the idea of installing
>> > WAL-logging in this minor release.  That probably needs to be done,
>> > but right now we've got stuff that worked in early 9.3.X release and
>> > is now broken, and I'm in favor of fixing that first.
>>
>> Okay, but if we're not committing today to a release wrap on Monday,
>> I don't see it happening till after PGCon.
>
> In that case, I think we should get a release out next week.  The
> current situation is rather badly broken and dangerous, and the above
> two bugs are nowhere as problematic.  If we can get fixes for these over
> the weekend, that would be additional bonus.

Yeah, I think I agree.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Robert Haas]

On Fri, Jun 5, 2015 at 2:47 PM, Andres Freund  wrote:
> On 2015-06-05 14:33:12 -0400, Tom Lane wrote:
>> Robert Haas  writes:
>> > 1. The problem that we might truncate an SLRU members page away when
>> > it's in the buffers, but not drop it from the buffers, leading to a
>> > failure when we try to write it later.
>
> I've got a fix for this, and about three other issues I found during
> development of the new truncation codepath.
>
> I'll commit the fix tomorrow.

OK.  Then I think we should release next week, so we get the fixes we
have out before PGCon.  The current situation is not good.

>> > I think we might want to try to fix one or both of those before
>> > cutting a new release.  I'm less sold on the idea of installing
>> > WAL-logging in this minor release.  That probably needs to be done,
>> > but right now we've got stuff that worked in early 9.3.X release and
>> > is now broken, and I'm in favor of fixing that first.
>
> I've implemented this, and so far it removes more code than it
> adds. It's imo also a pretty clear win in how understandable the code
> is.  The remaining work, besides testing, is primarily going over lots
> of comment and updating them. Some of them are outdated by the patch,
> and some already were.
>
> Will post tonight, together with the other fixes, after I get back from
> climbing.
>
> My gut feeling right now is that it's a significant improvement, and
> that it'll be reasonable to include it. But I'd definitely like some
> independent testing for it, and I'm not sure if that's doable in time
> for the wrap.

I think we would be foolish to rush that part into the tree.  We
probably got here in the first place by rushing the last round of
fixes too much; let's try not to double down on that mistake.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Further issues with jsonb semantics, documentation

[Alvaro Herrera]

Jim Nasby wrote:
> On 6/5/15 2:08 PM, Petr Jelinek wrote:
> >That's a good point, and it won't get any better if/when we add the json
> >point support in 9.6 since the syntax would be something like select
> >jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' - '/c/a'; and we will again
> >silently do nothing. That's going to cause bugs in applications using this.
> 
> Yeah, this is a miniature version of the pain I've felt with variant: trying
> to get sane casting for a data type that encompasses other types in current
> Postgres is essentially impossible.

I'm not sure this is the same problem.  But anyway I think requiring
explicit casts in this stuff is a good thing -- relying on implicit
cast to text, when most useful behavior uses other types, seems bad.

-- 
Á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] Further issues with jsonb semantics, documentation

[Jim Nasby]

On 6/5/15 2:08 PM, Petr Jelinek wrote:

That's a good point, and it won't get any better if/when we add the json
point support in 9.6 since the syntax would be something like select
jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' - '/c/a'; and we will again
silently do nothing. That's going to cause bugs in applications using this.


Yeah, this is a miniature version of the pain I've felt with variant: 
trying to get sane casting for a data type that encompasses other types 
in current Postgres is essentially impossible. Your only option is to 
put implicit or assignment casts in and cross your fingers, or to do 
only explicit casts and force the user to cast everything (which is a 
PITA). Even a json_pointer type may not help this much unless we have 
some way to reliable transform an unknown into a json_pointer.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] RFC: Remove contrib entirely

[Jeff Janes]

On Fri, Jun 5, 2015 at 7:42 AM, Joshua D. Drake 
wrote:

>
> On 06/05/2015 04:56 AM, Robert Haas wrote:
> somewhere else.  At least not that I can see.
>
>>
>>  4. Eliminate the EGO of saying "I have a contrib module in core"
>>>
>>
>> I've got multiple major features in core.  Any ego I may have about my
>> PostgreSQL contributions is not based on pg_prewarm.
>>
>
> This was worded badly by me. This isn't about your ego, it is about the
> mysticism surrounding the idea that "they have a feature in core". It is
> really last on the list and not really important to this discussion.
>
>
>>  1. 15 years of the same argument (current source: pg_audit)
>>>
>>
>> The argument about pg_audit has little to do with contrib.  It is
>> primarily about code quality, and secondarily about whether one
>> committer can go do something unliterally when a long list of other
>> committers and contributors have expressed doubts about it.
>>
>>
> The argument was about whether it should be in contrib, code quality or
> not. If contrib didn't exist and we accepted that extensions are an outside
> core thing, the argument never would have happened.


That is only the case if we also accept that all extensions are just an
amorphous mass.  If some extensions are "blessed" or "vetted" or "by
default tested against HEAD by the buildfarm" or whatever, then the debate
would still happen, just with different words.  And I don't think that
forestalling debate is such a worthwhile goal in itself.  Some debates are
worth having.

Cheers,

Jeff

Re: [HACKERS] gcc -ansi versus SSE4.2 detection

[Heikki Linnakangas]

On 06/05/2015 10:07 PM, Tom Lane wrote:

It looks like the actual reason that we aren't using the runtime-check
CRC implementation is that we can't find a way to do "cpuid" on this
old version of OS X.  Not sure if it's worth the time to look for one;
modern versions of OS X do have __get_cpuid().


Doesn't seem worth it to me.

- Heikki



--
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] Further issues with jsonb semantics, documentation

[Petr Jelinek]

On Fri, Jun 5, 2015 at 8:32 , Alvaro Herrera  
wrote:

Andrew Dunstan wrote:

 'some jsonb value' - '{foo,bar}' is already ambiguous  - the RH 
operand

 could be a single text datum or a text array.


Hmm, but that's not in 9.4, so we can still tweak it if necessary.

Consider this jsonb datum.  Nobody in their right mind would have a 
key

that looks like a path, I hear you say; yet I'm sure this is going to
happen.

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}, "{c,a}": 
"uh"}' ;

jsonb
--
 {"a": "1", "b": "2", "c": {"a": "2"}, "{c,a}": "uh"}
(1 fila)

This seems pretty surprising to me:

-- here, the -(jsonb,text) operator is silently chosen, even though 
the

-- right operand looks like an array.  And we do the wrong thing.
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  
'{c,a}';

   ?column?
---
 {"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)

-- here, the -(jsonb,text[]) operator is chosen
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  
_text '{c,a}';

   ?column?
---
 {"a": "1", "b": "2", "c": {}}
(1 fila)

But this seems worse to me, because we silently do nothing:

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  
'{c,a}';

   ?column?
---
 {"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)


I think the first operator can be qualified as dangerous.  If you 
delete
that one, then it's fine because you can't do that query anymore 
because

of the conflict with -(jsonb, int).

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  
'{c,a}';

ERROR:  operator is not unique: jsonb - unknown
LÍNEA 1: ...elect jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  
'{c,a}'...

  ^
SUGERENCIA:  Could not choose a best candidate operator. You might 
need to add explicit type casts.



That's a good point, and it won't get any better if/when we add the 
json point support in 9.6 since the syntax would be something like 
select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' - '/c/a'; and we 
will again silently do nothing. That's going to cause bugs in 
applications using this.


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



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

Re: [HACKERS] gcc -ansi versus SSE4.2 detection

[Tom Lane]

Heikki Linnakangas  writes:
> On 06/05/2015 09:27 PM, Tom Lane wrote:
>> ... However I found out that adding -ansi
>> also caused configure to stop selecting "-msse4.2", which seemed odd,
>> since that switch has no bearing on C language conformance.  And it fell
>> back to the slicing-by-8 CRC implementation too.

> Hmm, that's odd. -ansi has no effect on the CRC implementation on my system.

Ummm ... I was reading the diff backwards.  Actually it seems that on
dromedary's platform, CFLAGS_SSE42 is set to empty by default, but forcing
"-ansi" makes it get set to "-msse4.2".  Evidently, (this) gcc will accept
the _mm_crc32_foo intrinsics by default normally, but if you say -ansi
then it won't accept them unless you also say "-msse4.2".

It looks like the actual reason that we aren't using the runtime-check
CRC implementation is that we can't find a way to do "cpuid" on this
old version of OS X.  Not sure if it's worth the time to look for one;
modern versions of OS X do have __get_cpuid().

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] gcc -ansi versus SSE4.2 detection

[Heikki Linnakangas]

On 06/05/2015 09:27 PM, Tom Lane wrote:

[ this is a bit roundabout, bear with me ]

I noticed that, contrary to project policy, a //-style comment snuck into
pg_regress.c a month or two back.  I had had the idea that buildfarm
member pademelon would complain about such comments, given its stone-age
C compiler, but evidently not.  After some experimentation it seems that
"gcc -ansi" can be used to throw errors for // comments, so I'm planning
to enable that flag on dromedary.  However I found out that adding -ansi
also caused configure to stop selecting "-msse4.2", which seemed odd,
since that switch has no bearing on C language conformance.  And it fell
back to the slicing-by-8 CRC implementation too.


Hmm, that's odd. -ansi has no effect on the CRC implementation on my system.


Investigation showed that that's because -ansi causes the compiler to
stop defining __SSE4_2__, which configure supposes must get defined if
we are targeting an SSE 4.2 processor.  This seems a bit dumb to me:
if we have determined that "-msse4.2" works, isn't that sufficient
evidence that we can use the intrinsics?  Or if not, isn't there a less
fragile way to find out the target?

Also, it seems like the logic in configure.in is broken in any case:
if we are able to compile the intrinsics, should it not pick the
runtime-determination option for CRC?  It isn't doing that.


It's quite subtle. The point of the __SSE4.2__ test is to determine if 
we are targeting a system that has SSE4.2 instructions. If it's defined, 
then we can assume that SSE4.2 instructions are always available. For 
example, if you pass CFLAGS=-msse4.2, gcc can freely use SSE4.2 
instructions when optimizing, and the produced binary will not work on a 
system without SSE4.2 support. In that case, __SSE4.2__ is defined, and 
we don't need the run-time check or the fallback implementation, because 
we can also freely assume that SSE 4.2 support is available.


If __SSE4.2__ is not defined, but the compiler accepts -msse4.2, that 
means that the compiler will normally not use SSE4.2 instructions, and 
the produced binary must work without them. We will use the -msse4.2 
flag when compiling pg_crc32c_sse42.c, and at runtime, we check that SSE 
4.2 instructions are available before using it.


- Heikki



--
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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Steve Kehlet]

On Fri, Jun 5, 2015 at 11:47 AM Andres Freund  wrote:

> But I'd definitely like some
> independent testing for it, and I'm not sure if that's doable in time
> for the wrap.
>

I'd be happy to test on my database that was broken, for however much that
helps. It's a VM so I can easily revert back as needed. I'm just losing
track of all the patches, and what's committed and what I need to manually
apply :-). I was about to test what's on REL9_4_STABLE. Let me know if I
should do this.

Thanks so much everyone.

[HACKERS] Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Alvaro Herrera]

Robert Haas wrote:
> On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch  wrote:
> > On Thu, Jun 04, 2015 at 05:29:51PM -0400, Robert Haas wrote:
> >> Here's a new version with some more fixes and improvements:
> >
> > I read through this version and found nothing to change.  I encourage other
> > hackers to study the patch, though.  The surrounding code is challenging.
> 
> Andres tested this and discovered that my changes to
> find_multixact_start() were far more creative than intended.
> Committed and back-patched with a trivial fix for that stupidity and a
> novel-length explanation of the changes.

I think novel-length is fine.  The bug itself is pretty complicated, and
so is the solution.  Many thanks for working through this.

FWIW I tested with the (attached) reproducer script(*) for my customer's
problem, and it works fine now where it failed before.  One thing which
surprised me a bit, but in hindsight should have been pretty obvious, is
that the "multixact member protections are fully armed" message is only
printed once the standby gets out of recovery, instead of when it
reaches consistent state or some such earlier point.

(*) Actually the script cheats to get past an issue, which I couldn't
actually figure out, that a file can't be "seeked"; I just do a "touch"
to create an empty file there, which causes the same error situation as
on my customer's log.

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


repro-chkpt-replay-failure.sh
Description: Bourne shell script

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

Re: [HACKERS] [CORE] Restore-reliability mode

[Peter Geoghegan]

On Fri, Jun 5, 2015 at 8:51 AM, Andres Freund  wrote:
>> 4. Arguable RLS security bug, EvalPlanQual() paranoia - This seems
>> like another question of what the expectations around RLS actually
>> are.
>
> In the end that's minor from the end user's perspective.

I think that depends on what we ultimately decide to do about it,
which is something that I have yet to form an opinion on (although I
know we need to document the issue, at the very least). For example,
one idea that Stephen and I discussed privately was making security
barrier quals referencing other relations lock the referenced rows.
This was an informal throwing around of ideas, but it's possible that
something like that could end up happening.

-- 
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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Andres Freund]

On 2015-06-05 14:33:12 -0400, Tom Lane wrote:
> Robert Haas  writes:
> > 1. The problem that we might truncate an SLRU members page away when
> > it's in the buffers, but not drop it from the buffers, leading to a
> > failure when we try to write it later.

I've got a fix for this, and about three other issues I found during
development of the new truncation codepath.

I'll commit the fix tomorrow.

> > I think we might want to try to fix one or both of those before
> > cutting a new release.  I'm less sold on the idea of installing
> > WAL-logging in this minor release.  That probably needs to be done,
> > but right now we've got stuff that worked in early 9.3.X release and
> > is now broken, and I'm in favor of fixing that first.

I've implemented this, and so far it removes more code than it
adds. It's imo also a pretty clear win in how understandable the code
is.  The remaining work, besides testing, is primarily going over lots
of comment and updating them. Some of them are outdated by the patch,
and some already were.

Will post tonight, together with the other fixes, after I get back from
climbing.

My gut feeling right now is that it's a significant improvement, and
that it'll be reasonable to include it. But I'd definitely like some
independent testing for it, and I'm not sure if that's doable in time
for the wrap.

> Okay, but if we're not committing today to a release wrap on Monday,
> I don't see it happening till after PGCon.

I wonder if, with all the recent, err, training, we could wrap it on
Tuesday instead. Independent of the truncation rework going in or not,
an additional work day to go over all the changes and do some more
testing would be good from my POV.

Greetings,

Andres Freund


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Josh Berkus]

On 06/05/2015 07:23 AM, Tom Lane wrote:
> So let's call it an alpha, or some other way of setting expectations
> appropriately.  But I think it's silly to maintain that the code is not in
> a state where end-user testing is useful.  They just have to understand
> that they can't trust it with production data.

Yes ... that seems like a good compromise.

Frankly, I'm testing 9.5 already; having alpha packages would make that
testing easier for me, and maybe possible for others.

We'd need to take into account that our packagers are a bit overworked
this month due to update releases ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Alvaro Herrera]

Tom Lane wrote:
> Robert Haas  writes:

> > There are at least two other known issues that seem like they should
> > be fixed before we release:
> 
> > 1. The problem that we might truncate an SLRU members page away when
> > it's in the buffers, but not drop it from the buffers, leading to a
> > failure when we try to write it later.
> 
> > 2. Thomas's bug fix for another longstanding but that occurs when you
> > run his checkpoint-segment-boundary.sh script.
> 
> > I think we might want to try to fix one or both of those before
> > cutting a new release.  I'm less sold on the idea of installing
> > WAL-logging in this minor release.  That probably needs to be done,
> > but right now we've got stuff that worked in early 9.3.X release and
> > is now broken, and I'm in favor of fixing that first.
> 
> Okay, but if we're not committing today to a release wrap on Monday,
> I don't see it happening till after PGCon.

In that case, I think we should get a release out next week.  The
current situation is rather badly broken and dangerous, and the above
two bugs are nowhere as problematic.  If we can get fixes for these over
the weekend, that would be additional bonus.

-- 
Á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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Tom Lane]

Robert Haas  writes:
> On Fri, Jun 5, 2015 at 12:00 PM, Andres Freund  wrote:
>> On 2015-06-05 11:43:45 -0400, Tom Lane wrote:
>>> So where are we on this?  Are we ready to schedule a new set of
>>> back-branch releases?  If not, what issues remain to be looked at?

>> We're currently still doing bad things while the database is in an
>> inconsistent state (i.e. read from SLRUs and truncate based on the
>> results of that). It's quite easy to reproduce base backup startup
>> failures.
>> 
>> On the other hand, that's not new. And the fix requires, afaics, a new
>> type of WAL record (issued very infrequently). I'll post a first version
>> of the patch, rebased ontop of Robert's commit, tonight or tomorrow. I
>> guess we can then decide what we'd like to do.

> There are at least two other known issues that seem like they should
> be fixed before we release:

> 1. The problem that we might truncate an SLRU members page away when
> it's in the buffers, but not drop it from the buffers, leading to a
> failure when we try to write it later.

> 2. Thomas's bug fix for another longstanding but that occurs when you
> run his checkpoint-segment-boundary.sh script.

> I think we might want to try to fix one or both of those before
> cutting a new release.  I'm less sold on the idea of installing
> WAL-logging in this minor release.  That probably needs to be done,
> but right now we've got stuff that worked in early 9.3.X release and
> is now broken, and I'm in favor of fixing that first.

Okay, but if we're not committing today to a release wrap on Monday,
I don't see it happening till after PGCon.

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] Further issues with jsonb semantics, documentation

[Alvaro Herrera]

Andrew Dunstan wrote:
> 
> On 06/04/2015 03:16 PM, Alvaro Herrera wrote:
> >I'm just skimming here, but if a jsonb_path type is being proposed,
> >perhaps it would be better not to have operators that take text or
> >text[] as second argument.  We can provide that functionality with just
> >functions.  For example, it will be confusing to have
> >
> >jsonb 'some json value' - '{foo,bar}'
> >
> >operate too differently from
> >
> >jsonb 'some json value' - json_path '{foo,bar}'
> >
> >And it will be a nasty regression to have 9.5 allow
> >jsonb 'some json value' - '{foo,bar}'
> >and then have 9.6 error out with "ambiguous operator" when the json_path
> >thing is added.
> 
> The boat has sailed on this. We have had the #> and #>> operators since 9.3,
> i.e. even before we got the operators that Peter wants us to adopt the usage
> from, and their right hand operands are text arrays with the same path
> semantics.

Well, some boats sailed, but maybe those were different boats.  I don't
think we should shut discussion off only because we made some choice or
other in the past.  Since we haven't released yet, we can base decisions
on what's the most useful API for users, rather on what got committed in
the initial patch.

> 'some jsonb value' - '{foo,bar}' is already ambiguous  - the RH operand
> could be a single text datum or a text array.

Hmm, but that's not in 9.4, so we can still tweak it if necessary.

Consider this jsonb datum.  Nobody in their right mind would have a key
that looks like a path, I hear you say; yet I'm sure this is going to
happen.

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}, "{c,a}": "uh"}' ;
jsonb 
--
 {"a": "1", "b": "2", "c": {"a": "2"}, "{c,a}": "uh"}
(1 fila)

This seems pretty surprising to me:

-- here, the -(jsonb,text) operator is silently chosen, even though the
-- right operand looks like an array.  And we do the wrong thing.
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}';
   ?column?
---
 {"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)

-- here, the -(jsonb,text[]) operator is chosen
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  _text '{c,a}';
   ?column?
---
 {"a": "1", "b": "2", "c": {}}
(1 fila)

But this seems worse to me, because we silently do nothing:

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}';
   ?column?
---
 {"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)


I think the first operator can be qualified as dangerous.  If you delete
that one, then it's fine because you can't do that query anymore because
of the conflict with -(jsonb, int).

alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}';
ERROR:  operator is not unique: jsonb - unknown
LÍNEA 1: ...elect jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -  '{c,a}'...
  ^
SUGERENCIA:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

-- 
Á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] gcc -ansi versus SSE4.2 detection

[Tom Lane]

[ this is a bit roundabout, bear with me ]

I noticed that, contrary to project policy, a //-style comment snuck into
pg_regress.c a month or two back.  I had had the idea that buildfarm
member pademelon would complain about such comments, given its stone-age
C compiler, but evidently not.  After some experimentation it seems that
"gcc -ansi" can be used to throw errors for // comments, so I'm planning
to enable that flag on dromedary.  However I found out that adding -ansi
also caused configure to stop selecting "-msse4.2", which seemed odd,
since that switch has no bearing on C language conformance.  And it fell
back to the slicing-by-8 CRC implementation too.

Investigation showed that that's because -ansi causes the compiler to
stop defining __SSE4_2__, which configure supposes must get defined if
we are targeting an SSE 4.2 processor.  This seems a bit dumb to me:
if we have determined that "-msse4.2" works, isn't that sufficient
evidence that we can use the intrinsics?  Or if not, isn't there a less
fragile way to find out the target?

Also, it seems like the logic in configure.in is broken in any case:
if we are able to compile the intrinsics, should it not pick the
runtime-determination option for CRC?  It isn't doing that.

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] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Robert Haas]

On Fri, Jun 5, 2015 at 12:00 PM, Andres Freund  wrote:
> On 2015-06-05 11:43:45 -0400, Tom Lane wrote:
>> Robert Haas  writes:
>> > On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch  wrote:
>> >> I read through this version and found nothing to change.  I encourage 
>> >> other
>> >> hackers to study the patch, though.  The surrounding code is challenging.
>>
>> > Andres tested this and discovered that my changes to
>> > find_multixact_start() were far more creative than intended.
>> > Committed and back-patched with a trivial fix for that stupidity and a
>> > novel-length explanation of the changes.
>>
>> So where are we on this?  Are we ready to schedule a new set of
>> back-branch releases?  If not, what issues remain to be looked at?
>
> We're currently still doing bad things while the database is in an
> inconsistent state (i.e. read from SLRUs and truncate based on the
> results of that). It's quite easy to reproduce base backup startup
> failures.
>
> On the other hand, that's not new. And the fix requires, afaics, a new
> type of WAL record (issued very infrequently). I'll post a first version
> of the patch, rebased ontop of Robert's commit, tonight or tomorrow. I
> guess we can then decide what we'd like to do.

There are at least two other known issues that seem like they should
be fixed before we release:

1. The problem that we might truncate an SLRU members page away when
it's in the buffers, but not drop it from the buffers, leading to a
failure when we try to write it later.

2. Thomas's bug fix for another longstanding but that occurs when you
run his checkpoint-segment-boundary.sh script.

I think we might want to try to fix one or both of those before
cutting a new release.  I'm less sold on the idea of installing
WAL-logging in this minor release.  That probably needs to be done,
but right now we've got stuff that worked in early 9.3.X release and
is now broken, and I'm in favor of fixing that first.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Is it possible to have a "fast-write" Index?

[Alvaro Herrera]

deavid wrote:
> There are several use cases where I see useful an index, but adding it will
> slow too much inserts and updates.

Maybe try a BRIN index.  You can't use them for text search currently,
or many other cases for that matter, but there are enough interesting
cases in which they are useful that perhaps you don't need anything
extra.

-- 
Á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] Is it possible to have a "fast-write" Index?

[Claudio Freire]

On Fri, Jun 5, 2015 at 2:59 PM, Tom Lane  wrote:
> deavid  writes:
>> Some people already asked for "delayed write" indexes, but the idea gets
>> discarded because the index could get out of sync, so it can omit results
>> and this is unacceptable. But i think maybe that could be fixed in several
>> ways and we can have a fast and reliable index (but maybe not so fast on
>> selects).
>
> FWIW, GIN indexes already implement something that's like your mode 2 but
> a bit better: there's an unordered "pending insertions" list that has to
> be scanned by every search in addition to checking the main index body.
> Every so often the pending insertions list is automatically flushed into
> the main index body.
>
> The reason we do this for GIN is that that index type puts a huge premium
> on doing inserts "in bulk"; it's a lot more efficient if you push many
> rows into the index at once, because frequently they'll be inserting into
> the same per-key posting lists.  I do not see much opportunity for a
> corresponding gain for btree.


A forest of btrees (say mode 2.c) may not be a bad idea. When tables
grow consistently, the cost of I/O is usually high in FPW and random
I/O due to the large spread of index updates. I don't have numbers,
but on the databases I've handled it certainly was so.

If you have a btree_forest am that will consist of several btrees that
follow the GIN pattern only instead of an unordered list you have an
ordered btree (which also simplifies merging), you should gain a lot.

The big btrees will be read-only, so they will be compact (100% fill
rate), you will generate less WAL (updates are all local on the small
"staging" btree) and even the disk may perform better with that
pattern.

It is in fact a pattern used by inverted indexes already, so it
wouldn't be too far-fetched.

It is however hard to figure out when compaction has to happen.
Concurrency shouldn't be an issue though, since all but the smallest
btree would be read-only, so you only need a lock while modifying the
forest structure (adding a new btree, swapping components with merged
versions, etc).

It would indeed, though, require a lot of extra storage to perform
compaction. An alternative would be to implement compaction as a
massive insert/delete instead. Certainly, how exactly compaction gets
implemented would be key in deciding whether the approach breaks even.


-- 
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] Is it possible to have a "fast-write" Index?

[Tom Lane]

deavid  writes:
> Some people already asked for "delayed write" indexes, but the idea gets
> discarded because the index could get out of sync, so it can omit results
> and this is unacceptable. But i think maybe that could be fixed in several
> ways and we can have a fast and reliable index (but maybe not so fast on
> selects).

FWIW, GIN indexes already implement something that's like your mode 2 but
a bit better: there's an unordered "pending insertions" list that has to
be scanned by every search in addition to checking the main index body.
Every so often the pending insertions list is automatically flushed into
the main index body.

The reason we do this for GIN is that that index type puts a huge premium
on doing inserts "in bulk"; it's a lot more efficient if you push many
rows into the index at once, because frequently they'll be inserting into
the same per-key posting lists.  I do not see much opportunity for a
corresponding gain for btree.

So I really doubt that anyone would have any enthusiasm for saddling btree
with a similar mechanism.  It's complicated (and has been the cause of
multiple bugs); it's hard to figure out when is the optimal time to flush
the pending insertions; and it slows down searches in favor of making
inserts cheap, which is generally not the way to bet --- if that's the
tradeoff you want, why not drop the index altogether?

But anyway, since you can use contrib/btree_gin to get more or less btree
semantics for GIN indexes (except for uniqueness enforcement), you might
try whether just replacing your btree indexes with GIN indexes provides
any win for your insertions.

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] RFC: Remove contrib entirely

[David E. Wheeler]

On Jun 5, 2015, at 10:47 AM, Jim Nasby  wrote:

>> Right. Just stick it in your README.
>> 
>>   http://blog.pgxn.org/post/116087351668/badges
> 
> I meant something more integrated with PGXN itself, such as what you're 
> proposing for pgxn-tester. That would allow for things like PGXN search 
> results to show Travis results.

Well, it’s not really possible to tell if there’s a Travis job for a project. I 
could add something to the META.json, though.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Further issues with jsonb semantics, documentation

[Andrew Dunstan]

On 06/05/2015 01:39 PM, Peter Geoghegan wrote:

On Thu, Jun 4, 2015 at 12:10 PM, Peter Geoghegan  wrote:

But I agree that it's not a great contribution to science, especially since
the index will be applied to the list of elements in the somewhat
counter-intuitive storage order we use, and we could just raise an error if
we try to apply integer delete to an object instead of an array.

Cool. Do you want to write a patch, or should I?

Also, what about negative array subscripting (making the 9.4-era
"operator jsonb -> integer" operator support that for consistency with
the new "operator jsonb - integer" operator)? Should I write the
patch? Will you commit it if I do?

Please let me know if you want me to write these two patches.




Send the first one, I'm still thinking about the second one.

cheers

andrew


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

Re: [HACKERS] RFC: Remove contrib entirely

[Jim Nasby]

On 6/5/15 10:49 AM, David E. Wheeler wrote:

On Jun 5, 2015, at 12:34 AM, Jim Nasby  wrote:


A number of modules also run Travis-CI. Might be worth having a way for a 
module to provide it's status .png.


Right. Just stick it in your README.

   http://blog.pgxn.org/post/116087351668/badges


I meant something more integrated with PGXN itself, such as what you're 
proposing for pgxn-tester. That would allow for things like PGXN search 
results to show Travis results.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] Is it possible to have a "fast-write" Index?

[Jim Nasby]

On 6/5/15 11:07 AM, deavid wrote:

Did I miss something?


These are interesting ideas but the problem here is the problem is far 
to hypothetical. You're trying to defer index maintenance cost in a case 
where if there's any real problem the index pages are already in memory. 
So if it's too slow it's not because of IO... but then why is it too slow?


If you have significantly more than 10M rows then IO would be much more 
likely to be a problem, but at that point you should probably just be 
partitioning anyway.


If you want to attract attention here I think you'll need to come up 
with some concrete scenarios and provide data on where all the 
performance hit actually is.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] Further issues with jsonb semantics, documentation

[Peter Geoghegan]

On Thu, Jun 4, 2015 at 12:10 PM, Peter Geoghegan  wrote:
>> But I agree that it's not a great contribution to science, especially since
>> the index will be applied to the list of elements in the somewhat
>> counter-intuitive storage order we use, and we could just raise an error if
>> we try to apply integer delete to an object instead of an array.
>
> Cool. Do you want to write a patch, or should I?
>
> Also, what about negative array subscripting (making the 9.4-era
> "operator jsonb -> integer" operator support that for consistency with
> the new "operator jsonb - integer" operator)? Should I write the
> patch? Will you commit it if I do?

Please let me know if you want me to write these two patches.

-- 
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] pg_stat_*_columns?

[Jim Nasby]

On 6/5/15 6:51 AM, Joel Jacobson wrote:


1. I think it would be helpful for DBAs to better understand their own
system.
Finding unused *tables* is today easy thanks to pg_stat_*_tables, but
knowing if something is accessing a *column* or not is not easy.
In my case all our database access is via sprocs, so I can just grep the
source code for the column name to see if something is using it, but
most DBAs probably don't have that luxury.


I have wanted this exact thing when considering vertical partitioning. 
It's easy to tell from a size standpoint what columns are good 
candidates for putting in a 'side table', but it's very hard to know how 
often columns are actually used.


BTW, I think the right way to measure this would be how many rows were 
returned for queries referencing a column. Simply knowing how many 
queries reference a column doesn't tell you much; you want to know how 
much column data was actually pulled out.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] Further issues with jsonb semantics, documentation

[Andrew Dunstan]

On 06/04/2015 03:16 PM, Alvaro Herrera wrote:

I'm just skimming here, but if a jsonb_path type is being proposed,
perhaps it would be better not to have operators that take text or
text[] as second argument.  We can provide that functionality with just
functions.  For example, it will be confusing to have

jsonb 'some json value' - '{foo,bar}'

operate too differently from

jsonb 'some json value' - json_path '{foo,bar}'

And it will be a nasty regression to have 9.5 allow
jsonb 'some json value' - '{foo,bar}'
and then have 9.6 error out with "ambiguous operator" when the json_path
thing is added.





The boat has sailed on this. We have had the #> and #>> operators since 
9.3, i.e. even before we got the operators that Peter wants us to adopt 
the usage from, and their right hand operands are text arrays with the 
same path semantics.


'some jsonb value' - '{foo,bar}' is already ambiguous  - the RH operand 
could be a single text datum or a text array.


cheers

andrew


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Robert Haas]

On Fri, Jun 5, 2015 at 11:18 AM, Simon Riggs  wrote:
> We don't have a clear definition of what Beta means. For me, Beta has always
> meant "trial software, please test".
>
> I don't think anybody will say anything bad about us if we release a beta
> and then later pull some of the features because we are not confident with
> them when AFTER testing the feature is shown to be below our normal
> standard; that will bring us credit, I feel. It is extremely common in
> software development to defer some of the features if their goals aren't
> met, or to change APIs and interfaces based upon user feedback.

Yeah, but we usually haven't.  Tom, for example, has previously not
wanted to even bump catversion after beta1, which rules out a huge
variety of possible fixes and interface changes.  If we want to make a
policy decision to change our approach, we should be up-front about
that.

> None of this means I disagree with assessments of the current state of the
> software, I'm saying that we should simply follow the normal process and
> stick to the schedule we have previously agreed, for all of the reasons
> cited when we agreed it.

Well, to my way of looking at it, our feature freeze was later this
year than it has been in the past, so our beta will be later, too.  If
we want to stick with the schedule, we have to do that throughout.
Our typical schedule has been a two-month final CommitFest starting on
January 15th.  This year we had a three month final CommitFest
starting on February 15th.  So we finished the last CommitFest two
months later than has been typical.

Typically our beta has been in early May, 1-2 months after the end of
the last CommitFest.  If you add the same two months to that, you get
early July, which sounds reasonable, rather than early June, which
sounds rushed, especially since we have an urgent need to get minor
releases out the door to fix critical stability bugs right now, and
then we have PGCon, during which nobody's going to be looking at
anything.

It sounds to me like the original plan was to put out a beta in early
June, which would have been fine if we'd stuck to the traditional
2-month final CommitFest.  But we didn't.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] Warn about using single user + standby_mode

[Andres Freund]

Hi,

When primary_conninfo is configured and standby_mode is enabled single
user mode starts up, but at some point will just wait in a latch for WAL
to show up. Which will obviously never happen as walreceiver isn't up.

Should we just error out in that case, or at least display a log message
about that fact?

Greetings,

Andres Freund


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

Re: [HACKERS] RFC: Remove contrib entirely

[Alvaro Herrera]

Andrew Dunstan wrote:

> It's also quite possible to test extensions in the buildfarm using an addon
> module, which is mostly boilerplate. Of course, that does require that
> you're running a buildfarm member. Here's the code for the module that tests
> the FileTextArray extension. 
> 

I think important modules should all be tested and reported in the
buildfarm in this way.  For instance, I think PostGIS should report
there as well, so that we know when we break it the moment we do.

> This and the RedisFDW are tested daily or more often, on machines I control.

Yeah, that's pretty neat.

-- 
Á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] [CORE] Restore-reliability mode

[Alvaro Herrera]

Simon Riggs wrote:
> On 5 June 2015 at 15:00, Robert Haas  wrote:

> > Stamping it a beta implies that we think it's something fairly
> > stable that we'd be pretty happy to release if things go well, which
> > is a higher bar to clear.
> 
> We don't have a clear definition of what Beta means. For me, Beta has
> always meant "trial software, please test".

I think that definition *is* the problem, actually.  To me, "beta" means
"trial software, please test, but final product will be very similar to
what you see here".  What we need to convey at this point is what you
said, but I think a better word for that is "alpha".  There may be more
mobility in there than in a beta, in users's perception, which is the
right impression we want to convey.

Another point is that historically, once we've released a beta, we're
pretty reluctant to bump catversion.  We're not ready for that at this
stage, which is one criteria that suggests to me that we're not ready
for beta.

So I think the right thing to do at this point is to get an alpha out,
shortly after releasing upcoming minors.

-- 
Á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] cannot set view triggers to replica

[Kevin Grittner]

Michael Paquier  wrote:> On Sat, May 30, 2015 at 
11:47 AM, Peter Eisentraut  wrote:



>> the ALTER TABLE ... ENABLE RULE commands only allow acting on>> tables, even 
>> though rules can also exist on views and materialized views.
>
> I think that ALTER VIEW and ALTER MATERIALIZED VIEW should be able to
> accept the command as well.


What would be a use case for disabling the _RETURN rule for a [ MATERIALIZED ] 
VIEW?


--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] Is it possible to have a "fast-write" Index?

[deavid]

There are several use cases where I see useful an index, but adding it will
slow too much inserts and updates.
For example, when we have 10 million rows on a table, and it's a table
which has frequent updates, we need several index to speed up selects, but
then we'll slow down updates a lot, specially when we have 10 or more
indexes.
Other cases involve indexes for text search, which are used only for user
search and aren't that important, so we want to have them, but we don't
want the overload they put whenever we write on the table.
I know different approaches that already solve some of those problems in
some ways (table partitioning, partial indexes, etc), but i don't feel they
are the solution to every problem of this kind.

Some people already asked for "delayed write" indexes, but the idea gets
discarded because the index could get out of sync, so it can omit results
and this is unacceptable. But i think maybe that could be fixed in several
ways and we can have a fast and reliable index (but maybe not so fast on
selects).

Since I do not know every internal of postgres, i feel simpler to share
here and ask which things can or cannot be done.

Let's imagine there is a new type of index called "weird_btree", where we
trade-off simplicity for speed. In almost every mode, we will rely on
VACUUM to put our index in optimal state.

Mode 1: on "aminsert" mark the index as INVALID. So, if you modified the
table you need to run REINDEX/CREATE INDEX CONCURRENTLY before doing
SELECT. This is almost the same as create index concurrently, the main
difference is you don't have to remember to drop the index before writing.
(I don't see much benefit here)

Mode 2: on "aminsert", put the new entry in a plain, unordered list instead
of the btree. Inserting at the end of a list should be faster than big
btrees and you'll know later which entries you missed indexing.

Mode 2.a: on index scan (amrescan, amgettuple), pretend that after the
btree there is the list and output every row, out-of order. You will have
to tell postgres that our index isn't sorted and it will have to recheck
every row.

Mode 2.b: mark the index invalid instead. When doing the next vacuum, sort
the list and insert it to the btree in a bulk operation. If it's ok, mark
the index valid.

Mode 3: on "aminsert", put the new entry on a second btree; leaving the
first one untouched. Because the second btree is new, will be small, and
writes should be faster. When doing a index scan, read tuples from both at
same time (like merge sort). On vacuum, merge the second btree onto the
first. On this mode, the index is sorted and there's no need of recheck.

Anyone thinks this would be a interesting feature for postgresql?
Did I miss something?

PD: Maybe it's also possible to take advantage of clustering, and have
indexes which entries are range of TIDs; but i'm not sure if this is too
exotic, or if it will make a difference.

Sincerely,
David.

Re: [HACKERS] [CORE] Restore-reliability mode

[Jim Nasby]

On 6/5/15 10:39 AM, Tom Lane wrote:

The other side of that coin is that we might get useful comments from
testers on how the feature ought to work.  I don't agree with the notion
that all feature details must be graven on stone tablets before we start
trying to get feedback from people outside the core development community.


+1


The same point applies to the FDW C API questions, or to RLS, or to the
"expanded objects" work that I did.  (I'd really love it if the PostGIS
folk would try to use that sometime before it's too late to adjust the
definition...)  Now, you could argue that people likely to have useful
input on those issues are fully capable of working with git tip, and you'd
probably be right, but would they do so?  As Simon says nearby, publishing
an alpha/beta/whatever is our signal to the wider community that it's time
for them to start paying attention.  I do not think they will look at 9.5
until we do that; and I think it'll be our loss if they don't start
looking at these things soon.


+1, but I also think we should have a better mechanism for soliciting 
user input on these things while design discussions are happening. ISTM 
that there's a lot of hand-waving that happens around use cases that 
could probably be clarified with end user input.


FWIW, I don't think the blocker here is git or building from source. If 
someone has that amount of time to invest it's not much different than 
grabbing a tarball.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Andres Freund]

On 2015-06-05 11:43:45 -0400, Tom Lane wrote:
> Robert Haas  writes:
> > On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch  wrote:
> >> I read through this version and found nothing to change.  I encourage other
> >> hackers to study the patch, though.  The surrounding code is challenging.
> 
> > Andres tested this and discovered that my changes to
> > find_multixact_start() were far more creative than intended.
> > Committed and back-patched with a trivial fix for that stupidity and a
> > novel-length explanation of the changes.
> 
> So where are we on this?  Are we ready to schedule a new set of
> back-branch releases?  If not, what issues remain to be looked at?

We're currently still doing bad things while the database is in an
inconsistent state (i.e. read from SLRUs and truncate based on the
results of that). It's quite easy to reproduce base backup startup
failures.

On the other hand, that's not new. And the fix requires, afaics, a new
type of WAL record (issued very infrequently). I'll post a first version
of the patch, rebased ontop of Robert's commit, tonight or tomorrow. I
guess we can then decide what we'd like to do.


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Jim Nasby]

On 6/4/15 11:28 PM, Michael Paquier wrote:

* More configuration variations; ./configure, initdb options, and *.conf
* More edge-case testing. (ie: what happens to each varlena as it 
approaches 1GB? 1B tables test. Etc.)
* More race-condition testing, like the tool Peter used heavily during 
ON CONFLICT development (written by Jeff Janes?)
* More non-SQL testing. For example, the logic in HeapTupleSatisfies* is 
quite complicated yet there's no tests dedicated to ensuring the logic 
is correct because it'd be extremely difficult (if not impossible) to 
construct those tests at a SQL level. Testing them with direct test 
calls to HeapTupleSatisfies* wouldn't be difficult, but we have no 
machinery to do C level testing.



Is pg_tap a reasonable starting point for this sort of testing?

IMO, using the TAP machinery would be a good base for that. What lacks
is a basic set of perl routines that one can easily use to set of test
scenarios.


I think Stephen was referring specifically to pgTap (http://pgtap.org/).

Isn't our TAP framework just different output from pg_regress? Is there 
documentation on our TAP stuff?



>How would a test that would've caught the multixact issues look?

I have not followed closely those discussions, not sure about that.


I've thought about this and unfortunately I think this may be a scenario 
that's just too complex to completely protect against with a test. What 
might help though is having better testing of edge cases (such as MXID 
wrap) and then combining that with other forms of testing, such as 
pg_upgrade and streaming rep. testing. Test things like "What happens if 
we pg_upgrade a cluster that's in danger of wraparound?"

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Simon Riggs]

On 5 June 2015 at 16:05, Bruce Momjian  wrote:

>
> Please address some of the specific issues I mentioned.


I can discuss them but not because I am involved directly. I take
responsibility as a committer and have an interest from that perspective.

In my role at 2ndQuadrant, I approved all of the time Alvaro and Andres
spent on submitting, reviewing and fixing bugs - at this point that has
cost something close to fifty thousand dollars just on this feature and
subsequent actions. (I believe the feature was originally funded, but we
never saw a penny of that, though others did.)


> The problem
> with the multi-xact case is that we just kept fixing bugs as people
> found them, and did not do a holistic review of the code.


I observed much discussion and review. The bugs we've had have all been
fairly straightforwardly fixed. There haven't been any design-level
oversights or head-palm moments. It's complex software that had complex
behaviour that caused problems. The problem has been that anything on-disk
causes more problems when errors occur. We should review carefully anything
that alters the way on-disk structures work, like the WAL changes, UPSERTs
new mechanism etc..

>From my side, it is only recently I got some clear answers to my questions
about how it worked. I think it is very important that major features have
extensive README type documentation with them so the underlying principles
used in the development are clear. I would define the measure of a good
feature as whether another committer can read the code comments and get a
good feel. A bad feature is one where committers walk away from it, saying
I don't really get it and I can't read an explanation of why it does that.
Tom's most significant contribution is his long descriptive comments on
what the problem is that need to be solved, the options and the method
chosen. Clarity of thought is what solves bugs.

Overall, I don't see the need to stop the normal release process and do a
holistic review. But I do think we should check each feature to see whether
it is fully documented or whether we are simply trusting one of us to be
around to fix it.

I am just saying we need to ask the
> reliability question _first_.
>

Agreed


> Let me restate something that has appeared in many replies to my ideas
> --- I am not asking for infinite or unbounded review, but I am asking
> that we make sure reliability gets the proper focus in relation to our
> time pressures.  Our balance was so off a month ago that I feel only a
> full stop on time pressure would allow us to refocus because people are
> not good at focusing on multiple things. It is sometimes necessary to
> stop everything to get people's attention, and to help them remember
> that without reliability, a database is useless.
>

Here, I think we are talking about different types of reliability.
PostgreSQL software is well ahead of most industry measures of quality;
these recent bugs have done nothing to damage that, other than a few people
woke up and said "Wow! Postgres had a bug??!?!?". The presence of bugs is
common and if we have grown unused to them, we should be wary of that,
though not tolerant.

PostgreSQL is now reliable in the sense that we have many features that
ensure availability even in the face of software problems and bug induced
corruption. Those have helped us get out of the current situations, giving
users a workaround while bugs are fixed. So the impact of database software
bugs is not what it once was.

Reliable delivery of new versions of software is important too. New
versions often contain new features that fix real world problems, just as
much as bug fixes do, hence why I don't wish to divert from the normal
process and schedule.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] [CORE] Restore-reliability mode

[Andres Freund]

On 2015-06-05 11:20:52 -0400, Robert Haas wrote:
> I don't maintain that end-user testing is unuseful at this point.

Unless I misunderstand you, and you're not saying that user level
testing wouldn't be helpful right now, I'm utterly baffled. There's
loads of user-exposed features that desperately need exposure.

Looking at https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL I
don't see a single item that correlates with the ones on the open items
list list. Sure, it's incomplete. But that's a lot of stuff to test
already. And the authors of those features can work on fixing the issues
coming up.  Lots of those features have barely got any testing at this
point.

> do maintain that it would be better to (1) finish fixing the known
> multixact bugs and (2) clean up some of the open items before we make
> a big push in that direction.

There's maybe 3-4 people that can actually do something about the
existing issues on that list. The community is far bigger than
that. Right now everyone is sitting on the sidelines and twiddling their
thumbs or developing new stuff. At least that's my impression.

> 2. custom-join has no way to construct Plan nodes of child Path nodes
> - The entire feature is a C API, and the API needs to be changed.  We
> should finalize the API before asking people to test whether they can
> use it for interesting things.

I think any real world exposure of that API will result in much larger
changes than that.

> 3. recovery_target_action = pause & hot_standby = off - Rumor has it
> we replaced one surprising behavior with a different but
> equally-surprising behavior.  We should decide what the right thing is
> and make sure the code is doing that before calling it a release.

Fujii pushed the bugfix, restoring the old behaviour afaics. It's imo
still crazy, but at this point it doesn't look like a 9.5 discussion.

> 4. Arguable RLS security bug, EvalPlanQual() paranoia - This seems
> like another question of what the expectations around RLS actually
> are.

In the end that's minor from the end user's perspective.

> I would also argue that we really ought to make a decision about
> "basebackups during ALTER DATABASE ... SET TABLESPACE ... not safe"
> before we get too close to final release.  Maybe it's not a
> beta-blocker, exactly, but it doesn't seem like the sort of change
> that should be rushed in too close to the end, because it looks sorta
> complicated and scary.  (Those are the technical terms.)

Yea, I'd really like to get that in at some point. I'll work on it as
soon I've finished the multixact truncation thingy.


Greetings,

Andres Freund


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Bruce Momjian]

On Fri, Jun  5, 2015 at 05:36:41PM +0200, Andres Freund wrote:
> I don't think anything as localized as 'do nothing but bugfixes for a
> while and then carry on' actually will solve the problem. We need to
> find and reallocate resources to put more emphasis on review, robustness
> and refactoring in the long term, not do panick-y stuff short term. This
> isn't a problem that can be solved by focusing on bugfixing for a week
> or four.

Fine.  We just need that refocus, and people usually can't refocus while
they are worried about other pressures, e.g. time --- its like trying to
adjust the GPS while driving --- not easy.

> That means we have to convince employers to actually *pay* us (people
> experienced with the codebase) to do work on these kind of things
> instead of much-easier-to-market new features. A lot of
> review/robustness work has been essentially done in our spare time,
> after long days. Which means the employers need to get more people.

Agreed --- that is a serious long-term need.

> > Sure.  I think everyone agrees the multi-xact work is all good, so I am
> > asking what else needs this kind of research.  If there is nothing else,
> > we can move forward again --- I am just saying we need to ask the
> > reliability question _first_.
> 
> I'm starting to get grumpy here. You've called for review in lots of
> emails now. Let's get going then?

I really don't know.  If people say we don't have anything like
multi-xact that we have avoided, then I have no further concerns.  I am
asking that such decisions be made independent of external time
pressures.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] RFC: Remove contrib entirely

[David E. Wheeler]

On Jun 5, 2015, at 12:34 AM, Jim Nasby  wrote:

> A number of modules also run Travis-CI. Might be worth having a way for a 
> module to provide it's status .png.

Right. Just stick it in your README.

  http://blog.pgxn.org/post/116087351668/badges

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Tom Lane]

Robert Haas  writes:
> On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch  wrote:
>> I read through this version and found nothing to change.  I encourage other
>> hackers to study the patch, though.  The surrounding code is challenging.

> Andres tested this and discovered that my changes to
> find_multixact_start() were far more creative than intended.
> Committed and back-patched with a trivial fix for that stupidity and a
> novel-length explanation of the changes.

So where are we on this?  Are we ready to schedule a new set of
back-branch releases?  If not, what issues remain to be looked at?

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] [CORE] Restore-reliability mode

[Alvaro Herrera]

Michael Paquier wrote:
> On Fri, Jun 5, 2015 at 8:53 AM, Craig Ringer  wrote:

> > In terms of specific testing improvements, things I think we need to have
> > covered and runnable on the buildfarm are:
> >
> > * pg_dump and pg_restore testing (because it's scary we don't do this)
> 
> We do test it in some way with pg_upgrade using set of objects that
> are not removed by the regression test suite. Extension dumps are
> uncovered yet though.

We could put more emphasis on having objects of all kinds remain in the
regression database, so that the pg_upgrade test covers more of this.

What happened with the extension tests patches you submitted?  They
seemed valuable to me, but I lost track.

> > * DDL deparse test coverage for all operations
> 
> What do you have in mind except what is already in objectaddress.sql
> and src/test/modules/test_dll_deparse/?

The current SQL scripts in that test do not cover all possible object
types, so there's a lot of the decoding capabilities that are currently
not exercised.  So one way to attack this would be to add more object
types to those files.  However, a completely different way is to have
the test process serial_schedule from src/test/regress and run
everything in there under deparse.  That would be even more useful,
because whenever some future DDL is added, we will automatically get
coverage.

> > How would a test that would've caught the multixact issues look?
> 
> I have not followed closely those discussions, not sure about that.

One issue with these bugs is that unless you use things such as
pg_burn_multixact, producing large enough numbers of multixacts takes a
long time.  I've wondered if we could somehow make those easier to
reproduce by lowering the range, and thus doing thousands of
wraparounds, freezing and truncations in reasonable time.  (For example,
change the typedefs to uint16 rather than uint32).  But then the issue
becomes that the test code is not exactly equivalent to the production
code, which could cause its own bugs.

-- 
Á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] RFC: Remove contrib entirely

[Neil Tiffin]

> On Jun 4, 2015, at 3:11 PM, David E. Wheeler  wrote:
> 
> On Jun 4, 2015, at 11:53 AM, Neil Tiffin  wrote:
> 
>> I have looked at PGXN and would never install anything from it.  Why?  
>> Because it is impossible to tell, without inside knowledge or a lot of work, 
>> what is actively maintained and tested, and what is an abandoned 
>> proof-of-concept or idea.
> 
> Well, you can see the last release dates for a basic idea of that sort of 
> thing. Also the release status (stable, unstable, testing).
> 
>> There is no indication of what versions of pg any of PGXN modules are tested 
>> on, or even if there are tests that can be run to prove the module works 
>> correctly with a particular version of pg.
> 
> Yeah, I’ve been meaning to integrate http://pgxn-tester.org/ results for all 
> modules, which would help with that. In the meantime you can hit that site 
> itself. Awesome work by Tomas Vondra.
> 
>> There are many modules that have not been updated for several years.  What 
>> is their status?  If they break is there still someone around to fix them or 
>> even cares about them?  If not, then why waste my time.
> 
> These are challenges to open-source software in general, and not specific to 
> PGXN.

Of course, but the solution is having tools to easily determine the risk.  The 
fact that the modules pass or fail the tests on pgxn-tester is a significant 
step.  Knowing how long the module has been failing would be even better.

> 
>> So adding to Jim’s comment above, anything that vets or approves PGXN 
>> modules is, in my opinion, essentially required to make PGXN useful for 
>> anything other than a scratchpad.
> 
> Most of the distributions on PGXN feature links to their source code 
> repositories.
> 
>> A big help would be to pull in the date of the last git commit in the module 
>> overview and ask the authors to edit the readme to add what major version of 
>> pg the author last tested or ran on.
> 
> That’s difficult to maintain; I used to do it for pgTAP, was too much work. 
> pgxn-tester.org is a much better idea.

Yes it is.

Wow, that is awesome work (pgxn-tester.org).  Thanks Tomas Vondra, and David 
for pointing it out.  This improved my opinion of PGXN significantly.  It might 
be helpful to at least put a link on the PGXN home page, beta or not, its 
awesome and even in beta it shows the future direction.

It would be nice to see the development branch in the tests.  One project I am 
working on now targets 9.5.

It is important to know how long a stable module has been failing for a 
specific version of pg.  This is IMO a critical measure of the level of support 
a module is receiving. 

Neil




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

Re: [HACKERS] [CORE] Restore-reliability mode

[Tom Lane]

Robert Haas  writes:
> I don't maintain that end-user testing is unuseful at this point.  I
> do maintain that it would be better to (1) finish fixing the known
> multixact bugs and (2) clean up some of the open items before we make
> a big push in that direction.  For example, consider this item from
> the open items list:

> http://www.postgresql.org/message-id/CAHGQGwEqWD=ynqe+zojbpoxywt3xlk52-v_q9s+xofckjd5...@mail.gmail.com

> Now this is a fundamental definitional issue about how RLS is supposed
> to work.  I'm not going to deny that we COULD ship a release without
> deciding what the behavior should be there, but I don't think it's a
> good idea.  I am fine with the possibility that one of our new
> features may, say, dump core someplace due to a NULL pointer deference
> we haven't found yet.  Such bugs can always exist, but they are easily
> fixed once found.  But if we're not clear on how a feature is supposed
> to behave, which seems to be the case here, I favor trying to resolve
> that issue before shipping anything.  Otherwise, we're saying "test
> this, even though the final version will likely work differently".
> That's not really helpful for us and will discourage testers from
> doing anything at all.

The other side of that coin is that we might get useful comments from
testers on how the feature ought to work.  I don't agree with the notion
that all feature details must be graven on stone tablets before we start
trying to get feedback from people outside the core development community.

The same point applies to the FDW C API questions, or to RLS, or to the
"expanded objects" work that I did.  (I'd really love it if the PostGIS
folk would try to use that sometime before it's too late to adjust the
definition...)  Now, you could argue that people likely to have useful
input on those issues are fully capable of working with git tip, and you'd
probably be right, but would they do so?  As Simon says nearby, publishing
an alpha/beta/whatever is our signal to the wider community that it's time
for them to start paying attention.  I do not think they will look at 9.5
until we do that; and I think it'll be our loss if they don't start
looking at these things soon.

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] [CORE] Restore-reliability mode

[Andres Freund]

On 2015-06-05 11:05:14 -0400, Bruce Momjian wrote:
> To release 9.5 beta would be to get back into that cycle, and I am not
> sure we are ready for that.  I think the fact we have multiple people
> all reviewing the multi-xact code now (and not dealing with 9.5) is a
> good sign.  If we were focused on 9.5 beta, I doubt this would have
> happened.

At least form me that I'm working on multixacts right now has nothing to
do with to beta or not to beta.

And I don't understand why releasing an alpha or beta would detract from
that right now. We need more people doing crazy shit with our codebase,
not fewer.

None of the master-only issues is a blocker for an alpha, so besides
some release work within the next two weeks I don't see what'd detract
us that much?

> I am saying let's make sure we are not deficient in other areas, then
> let's move forward again.

I don't think we actually can do that. The problem of the multixact
stuff is precisely that it looked so innocent that a bunch of
experienced people just didn't see the problem. Omniscience is easy in
hindsight.

> I would love to think we can do multiple things at once, but for
> multi-xact, serious review didn't happen for 18 months, so if slowing
> release development is what is required, I support it.

FWIW, I can stomach a week or four of doing bugfix only stuff. After
that I'm simply not going to be efficient at that anymore. And I
seriously doubt that I'm the only one like that. Doing the same thing
for weeks makes you miss obvious stuff.


I don't think anything as localized as 'do nothing but bugfixes for a
while and then carry on' actually will solve the problem. We need to
find and reallocate resources to put more emphasis on review, robustness
and refactoring in the long term, not do panick-y stuff short term. This
isn't a problem that can be solved by focusing on bugfixing for a week
or four.

That means we have to convince employers to actually *pay* us (people
experienced with the codebase) to do work on these kind of things
instead of much-easier-to-market new features. A lot of
review/robustness work has been essentially done in our spare time,
after long days. Which means the employers need to get more people.

> Sure.  I think everyone agrees the multi-xact work is all good, so I am
> asking what else needs this kind of research.  If there is nothing else,
> we can move forward again --- I am just saying we need to ask the
> reliability question _first_.

I'm starting to get grumpy here. You've called for review in lots of
emails now. Let's get going then?

Greetings,

Andres Freund


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

Re: [HACKERS] RFC: Remove contrib entirely

[David Fetter]

On Thu, Jun 04, 2015 at 01:11:21PM -0700, David E. Wheeler wrote:
> On Jun 4, 2015, at 11:53 AM, Neil Tiffin  wrote:
> 
> > I have looked at PGXN and would never install anything from it.
> > Why?  Because it is impossible to tell, without inside knowledge
> > or a lot of work, what is actively maintained and tested, and what
> > is an abandoned proof-of-concept or idea.
> 
> Well, you can see the last release dates for a basic idea of that
> sort of thing. Also the release status (stable, unstable, testing).
> 
> > There is no indication of what versions of pg any of PGXN modules
> > are tested on, or even if there are tests that can be run to prove
> > the module works correctly with a particular version of pg.
> 
> Yeah, I’ve been meaning to integrate http://pgxn-tester.org/ results
> for all modules, which would help with that. In the meantime you can
> hit that site itself. Awesome work by Tomas Vondra.

Giant +1 for this.  I had no idea it existed until this morning.  As a
PGXN contributor, this kind of feedback is invaluable to me.

> > There are many modules that have not been updated for several
> > years.  What is their status?  If they break is there still
> > someone around to fix them or even cares about them?  If not, then
> > why waste my time.
> 
> These are challenges to open-source software in general, and not
> specific to PGXN.

The pgxn-tester system answers more of that question than a lot of
other projects do.  At some point, each organization using software,
free or proprietary, open source or closed, has to do some of their
own vetting.

> > So adding to Jim’s comment above, anything that vets or approves
> > PGXN modules is, in my opinion, essentially required to make PGXN
> > useful for anything other than a scratchpad.
> 
> Most of the distributions on PGXN feature links to their source code
> repositories.

Should this just be made a hard requirement for PGXN?  Lack of a
source code repo is a pretty good sign of abandonment.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Simon Riggs]

On 5 June 2015 at 15:00, Robert Haas  wrote:


> I do agree that an indefinite development freeze with unclear
> parameters for resuming development and unclear goals is a bad plan.
> But I think giving ourselves a little more time to, say, turn the
> buildfarm consistently green, and, say, fix the known but
> currently-unfixed multixact bugs, and, say, fix the known bugs in 9.5
> features is a good plan, and I hope you and others will support it.
>

Yes, its a good plan and I support that. That's just normal process.

If you mean we should allow that to stall the release of Beta then I
disagree. The presence of bugs clearly has nothing to do with the discovery
of new ones and we should be looking to discover as many as possible as
quickly as possible.

I can understand the argument to avoid releasing Beta because of Dev
Meeting, so we should aim for June 25th Beta 1.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] [CORE] Restore-reliability mode

[Robert Haas]

On Fri, Jun 5, 2015 at 10:23 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Fri, Jun 5, 2015 at 2:50 AM, Simon Riggs  wrote:
>>> Agreed. Cleanup can occur while we release code for public testing.
>
>> The code is available for public testing right now.
>
> Only to people who have the time and ability to pull the code from git
> and build from source.  I don't know exactly what fraction of interested
> testers that excludes, but I bet it's significant.  The point of producing
> packages would be to remove that barrier to testing.

Sure, I agree with that.

>> Stamping it a
>> beta implies that we think it's something fairly stable that we'd be
>> pretty happy to release if things go well, which is a higher bar to
>> clear.
>
> So let's call it an alpha, or some other way of setting expectations
> appropriately.  But I think it's silly to maintain that the code is not in
> a state where end-user testing is useful.  They just have to understand
> that they can't trust it with production data.

I don't maintain that end-user testing is unuseful at this point.  I
do maintain that it would be better to (1) finish fixing the known
multixact bugs and (2) clean up some of the open items before we make
a big push in that direction.  For example, consider this item from
the open items list:

http://www.postgresql.org/message-id/CAHGQGwEqWD=ynqe+zojbpoxywt3xlk52-v_q9s+xofckjd5...@mail.gmail.com

Now this is a fundamental definitional issue about how RLS is supposed
to work.  I'm not going to deny that we COULD ship a release without
deciding what the behavior should be there, but I don't think it's a
good idea.  I am fine with the possibility that one of our new
features may, say, dump core someplace due to a NULL pointer deference
we haven't found yet.  Such bugs can always exist, but they are easily
fixed once found.  But if we're not clear on how a feature is supposed
to behave, which seems to be the case here, I favor trying to resolve
that issue before shipping anything.  Otherwise, we're saying "test
this, even though the final version will likely work differently".
That's not really helpful for us and will discourage testers from
doing anything at all.

Going through the open items, the other ones that seem to involve
definitional changes are:

1. FPW compression leaks information - The usefulness of the GUC may
depend on its PGC_*-ness.  We should decide what we want to do before
asking people to test it.

2. custom-join has no way to construct Plan nodes of child Path nodes
- The entire feature is a C API, and the API needs to be changed.  We
should finalize the API before asking people to test whether they can
use it for interesting things.

3. recovery_target_action = pause & hot_standby = off - Rumor has it
we replaced one surprising behavior with a different but
equally-surprising behavior.  We should decide what the right thing is
and make sure the code is doing that before calling it a release.

4. Arguable RLS security bug, EvalPlanQual() paranoia - This seems
like another question of what the expectations around RLS actually
are.

I would also argue that we really ought to make a decision about
"basebackups during ALTER DATABASE ... SET TABLESPACE ... not safe"
before we get too close to final release.  Maybe it's not a
beta-blocker, exactly, but it doesn't seem like the sort of change
that should be rushed in too close to the end, because it looks sorta
complicated and scary.  (Those are the technical terms.)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Simon Riggs]

On 5 June 2015 at 15:00, Robert Haas  wrote:

> On Fri, Jun 5, 2015 at 2:50 AM, Simon Riggs  wrote:
> > Agreed. Cleanup can occur while we release code for public testing.
>
> The code is available for public testing right now.


People test when they get the signal from us, not before. While what you
say is literally correct, that is not the point.


> Stamping it a
> beta implies that we think it's something fairly stable that we'd be
> pretty happy to release if things go well, which is a higher bar to
> clear.
>

We don't have a clear definition of what Beta means. For me, Beta has
always meant "trial software, please test".

I don't think anybody will say anything bad about us if we release a beta
and then later pull some of the features because we are not confident with
them when AFTER testing the feature is shown to be below our normal
standard; that will bring us credit, I feel. It is extremely common in
software development to defer some of the features if their goals aren't
met, or to change APIs and interfaces based upon user feedback.

Making decisions on what will definitely be in a release BEFORE testing and
feedback seems foolhardy and certainly not scientific.

None of this means I disagree with assessments of the current state of the
software, I'm saying that we should simply follow the normal process and
stick to the schedule we have previously agreed, for all of the reasons
cited when we agreed it.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] RFC: Remove contrib entirely

[Bruce Momjian]

On Fri, Jun  5, 2015 at 02:42:45PM +0100, Simon Riggs wrote:
> On 29 May 2015 at 02:50, Peter Eisentraut  wrote:
> 
> On 5/28/15 3:35 PM, Stephen Frost wrote:
> > What we would need for this is an 'extensions' directory, or similar,
> > and a clear definition of what the requirements are around getting into
> > it are.  With that, we could decide for each module currently in contrib
> > if it should go into the 'extensions' directory.  I'm not sure that we
> > would necessairly have to remove the contrib module or any modules which
> > are deemed to not be appropriate for the 'extensions' directory.
> 
> This seems reasonable to me.  It's in line with the recent move from
> contrib to bin.  It'll just be quite a bit bigger of an undertaking.
> (50 threads to discuss the merits of each module separately?)  Maybe
> start by picking the top 5 and sort those out.
> 
> 
> +1 for Extensions directory for 9.6
> 
> This doesn't seem worth delaying the release for.

I didn't think any of this was for 9.5 consideration.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Bruce Momjian]

On Fri, Jun  5, 2015 at 07:50:31AM +0100, Simon Riggs wrote:
> On 3 June 2015 at 18:21, Josh Berkus  wrote:
>  
> 
> I would argue that if we delay 9.5 in order to do a 100% manual review
> of code, without adding any new automated tests or other non-manual
> tools for improving stability, then it's a waste of time; we might as
> well just release the beta, and our users will find more issues than we
> will.  I am concerned that if we declare a cleanup period, especially in
> the middle of the summer, all that will happen is that the project will
> go to sleep for an extra three months.
> 
> 
> Agreed. Cleanup can occur while we release code for public testing.
> 
> Many eyeballs of Beta beats anything we can throw at it thru manual 
> inspection.
> The whole problem of bugs is that they are mostly found by people trying to 
> use
> the software. 

Please address some of the specific issues I mentioned.  The problem
with the multi-xact case is that we just kept fixing bugs as people
found them, and did not do a holistic review of the code.  I am saying
let's not _keep_ doing that and let's make sure we don't have any
systematic problems in our code where we just keep fixing things without
doing a thorough analysis.

To release 9.5 beta would be to get back into that cycle, and I am not
sure we are ready for that.  I think the fact we have multiple people
all reviewing the multi-xact code now (and not dealing with 9.5) is a
good sign.  If we were focused on 9.5 beta, I doubt this would have
happened.

I am saying let's make sure we are not deficient in other areas, then
let's move forward again.  I would love to think we can do multiple
things at once, but for multi-xact, serious review didn't happen for 18
months, so if slowing release development is what is required, I support
it.

> We've decided previously that having a fixed annual schedule was a good thing
> for the project. Getting the features that work into the hands of the people
> that want them is very important.

Yes, but let's not be a slave to the schedule if our reliability is
suffering, which it clearly has in the past 18 months.

> Discussing halting the development schedule publicly is very damaging. 

Agreed.

> If there are features in doubt, lets do more work on them or just pull them 
> now
> and return to the schedule. I don't really care which ones get canned as long
> as we return to the schedule.

Again, please address my concerns above.  This is not about 9.5
features, but rather our overall focus on schedule vs. reliability, and
your arguments are reinforcing my idea that we do not have the proper
balance here.

> Whatever we do must be exact and measurable. If its not, it means we haven't
> assembled enough evidence for action that is sufficiently directed to achieve
> the desired goal.

Sure.  I think everyone agrees the multi-xact work is all good, so I am
asking what else needs this kind of research.  If there is nothing else,
we can move forward again --- I am just saying we need to ask the
reliability question _first_.

Let me restate something that has appeared in many replies to my ideas
--- I am not asking for infinite or unbounded review, but I am asking
that we make sure reliability gets the proper focus in relation to our
time pressures.  Our balance was so off a month ago that I feel only a
full stop on time pressure would allow us to refocus because people are
not good at focusing on multiple things. It is sometimes necessary to
stop everything to get people's attention, and to help them remember
that without reliability, a database is useless.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] RFC: Remove contrib entirely

[Andrew Dunstan]

On 06/05/2015 03:34 AM, Jim Nasby wrote:

On 6/4/15 3:11 PM, David E. Wheeler wrote:
There is no indication of what versions of pg any of PGXN modules 
are tested on, or even if there are tests that can be run to prove 
the module works correctly with a particular version of pg.
Yeah, I’ve been meaning to integratehttp://pgxn-tester.org/ results 
for all modules, which would help with that. In the meantime you can 
hit that site itself. Awesome work by Tomas Vondra.


A number of modules also run Travis-CI. Might be worth having a way 
for a module to provide it's status .png.


It's also quite possible to test extensions in the buildfarm using an 
addon module, which is mostly boilerplate. Of course, that does require 
that you're running a buildfarm member. Here's the code for the module 
that tests the FileTextArray extension. 



This and the RedisFDW are tested daily or more often, on machines I control.

cheers

andrew


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

Re: [HACKERS] Multixid hindsight design

[Robert Haas]

On Fri, Jun 5, 2015 at 10:27 AM, Tom Lane  wrote:
>> That way if we need to make Offsets SLRU persistent it won't bloat.
>> We then leave the Members SLRU as non-persistent, just as it was <9.3
>
> I don't think you can do that, because it supposes that locking XIDs need
> not be remembered across a crash.  Don't prepared transactions break that
> assumption?

Well, that issue existed before 9.3, too.  It's possible our old
releases weren't entirely correct either, but the big change in 9.3 is
that we have to keep MultiXacts around until they are frozen, rather
than just until their member transactions are no longer running.  If I
understand correctly, Simon's proposal would mean that
pg_multixact/offsets would still need to survive until freezing, but
pg_multixact/members would only need to survive until the member
transactions were no longer running.  That might span a crash or
restart, in the case of prepared transactions, but we could clean up
the member offsets when the prepared transactions were committed,
rather than having to scan every table in the cluster first.  That
only eliminates half the need for multixact vacuuming, but it's
something.

It would be a great deal nicer if we didn't have to keep ANY of the
transactional data for a tuple around once it's all-visible.  Heikki
defined ephemeral as "only needed when xmin or xmax is in-progress",
but if we extended that definition slightly to "only needed when xmin
or xmax is in-progress or commited but not all-visible" then the
amount non-ephemeral data in the tuple header is 5 bytes (infomasks +
t_hoff).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Joshua D. Drake]

On 06/05/2015 04:56 AM, Robert Haas wrote:
somewhere else.  At least not that I can see.



4. Eliminate the EGO of saying "I have a contrib module in core"


I've got multiple major features in core.  Any ego I may have about my
PostgreSQL contributions is not based on pg_prewarm.


This was worded badly by me. This isn't about your ego, it is about the 
mysticism surrounding the idea that "they have a feature in core". It is 
really last on the list and not really important to this discussion.





1. 15 years of the same argument (current source: pg_audit)


The argument about pg_audit has little to do with contrib.  It is
primarily about code quality, and secondarily about whether one
committer can go do something unliterally when a long list of other
committers and contributors have expressed doubts about it.



The argument was about whether it should be in contrib, code quality or 
not. If contrib didn't exist and we accepted that extensions are an 
outside core thing, the argument never would have happened.


JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Tom Lane]

Robert Haas  writes:
> On Fri, Jun 5, 2015 at 2:50 AM, Simon Riggs  wrote:
>> Agreed. Cleanup can occur while we release code for public testing.

> The code is available for public testing right now.

Only to people who have the time and ability to pull the code from git
and build from source.  I don't know exactly what fraction of interested
testers that excludes, but I bet it's significant.  The point of producing
packages would be to remove that barrier to testing.

> Stamping it a
> beta implies that we think it's something fairly stable that we'd be
> pretty happy to release if things go well, which is a higher bar to
> clear.

So let's call it an alpha, or some other way of setting expectations
appropriately.  But I think it's silly to maintain that the code is not in
a state where end-user testing is useful.  They just have to understand
that they can't trust it with production data.

> I can't help noticing for all the drumbeat of "let's release 9.5 beta
> now", activity to clean up the items on this list seems quite
> sluggish:
> https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items

While we need to work on those items, I do not agree that getting that
list to empty has to happen before we release a test version.  I think
serializing effort in that way is simply bad project management.  And
it's not how we've operated in the past either: getting the open items
list to empty has always been understood as a prerequisite to RC versions,
not to betas.

To get to specifics instead of generalities: exactly which of the current
open items do you think is so bad that it precludes user testing?  I do
not see a beta-blocker in the lot.

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] Multixid hindsight design

[Tom Lane]

Simon Riggs  writes:
> On 11 May 2015 at 22:20, Heikki Linnakangas  wrote:
>> So the lesson here is that having a permanent pg_multixact is not nice,
>> and we should get rid of it. Here's how to do that:

> An alternate proposal:

> 1. Store only the Locking xids in the Members SLRU
> 2. In the Offsets SLRU store: 1) the Updating Xid and 2) the offset to the
> Locking xids in the Members SLRU.

> This means the Offsets SLRU will be around twice the size it was before BUT
> since we reduce the size of each Members array by one, there is a balanced
> saving there, so this change is disk-space-neutral.

> That way if we need to make Offsets SLRU persistent it won't bloat.
> We then leave the Members SLRU as non-persistent, just as it was <9.3

I don't think you can do that, because it supposes that locking XIDs need
not be remembered across a crash.  Don't prepared transactions break that
assumption?

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] Multixid hindsight design

[Robert Haas]

On Fri, Jun 5, 2015 at 6:17 AM, Simon Riggs  wrote:
> I think we should think back to exactly what we are trying to store, why and
> for how long. A clear definition of what we are trying to achieve is
> essential to solving the problem.
>
> In my understanding we need to store
> * at most one xid - the Updating Xid
> * potentially many Locking Xids
>
> The current design has two SLRUs and puts all of those xids in the Members
> SLRU, causing it to need to be persistent.
>
> The problems come from having significant numbers of locking xids. My
> understanding is that any change in the number of lockers requires the full
> array to be rewritten. So with N lockers we end up with 2N-1 arrays, each
> array has an average of N/2 members, or N^2  entries, i.e. an O(N^2)
> algorithm, which makes it a bad thing to persist. Assuming that design
> continues mostly unchanged in its core points...
>
> An alternate proposal:
>
> 1. Store only the Locking xids in the Members SLRU
> 2. In the Offsets SLRU store: 1) the Updating Xid and 2) the offset to the
> Locking xids in the Members SLRU.
>
> This means the Offsets SLRU will be around twice the size it was before BUT
> since we reduce the size of each Members array by one, there is a balanced
> saving there, so this change is disk-space-neutral.
>
> That way if we need to make Offsets SLRU persistent it won't bloat.
> We then leave the Members SLRU as non-persistent, just as it was <9.3

Hmm, this is a neat idea.  It would have been easier to implement if
we'd thought of it before we released 9.3, though.  At this point, I
guess we'd have to either have a pg_upgrade compatibility break, or
teach pg_upgrade to rejigger the old files into the new file format,
or some other fix that's not immediately apparent to me.  And it also
sounds like a fair amount of work.  But it might be worth it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] [CORE] Restore-reliability mode

[Robert Haas]

On Fri, Jun 5, 2015 at 2:50 AM, Simon Riggs  wrote:
> Agreed. Cleanup can occur while we release code for public testing.

The code is available for public testing right now.  Stamping it a
beta implies that we think it's something fairly stable that we'd be
pretty happy to release if things go well, which is a higher bar to
clear.

I can't help noticing for all the drumbeat of "let's release 9.5 beta
now", activity to clean up the items on this list seems quite
sluggish:

https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items

I've seen Tom and a few other people doing some work that I would
describe as useful pre-beta stabilization, but I think there is a good
bit more that could be done, and that list is a good starting point.
I hope to have time to do some myself, but right now I am busy trying
to stabilize 9.3, along with Alvaro, Noah, Andres, and Thomas Munro,
and PGCon is coming up in just over a week.  I think we could afford
to give ourselves at least until a few weeks following PGCon to tidy
up.

I do agree that an indefinite development freeze with unclear
parameters for resuming development and unclear goals is a bad plan.
But I think giving ourselves a little more time to, say, turn the
buildfarm consistently green, and, say, fix the known but
currently-unfixed multixact bugs, and, say, fix the known bugs in 9.5
features is a good plan, and I hope you and others will support it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Robert Haas]

On Fri, Jun 5, 2015 at 8:32 AM, Stephen Frost  wrote:
> Painting it as the unilateral actions of one committer is uncharitable,
> at best.

As I see it, it is just stating the facts.  There were several emails
from other committers on the pg_audit thread not that long before it
was committed, and zero of them were supportive.  None of them were
dead-set against it, but it is not our typical way of doing business
to press forward with committing something when that many doubts have
been expressed.  I believe you will struggle to find comparable
examples involving other patches.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Memory leak with XLogFileCopy since de768844 (WAL file with .partial)

[Fujii Masao]

On Fri, Jun 5, 2015 at 12:39 PM, Michael Paquier
 wrote:
>
>
> On Thu, Jun 4, 2015 at 10:40 PM, Fujii Masao  wrote:
>>
>> On Mon, Jun 1, 2015 at 4:24 PM, Michael Paquier
>>  wrote:
>> > On Thu, May 28, 2015 at 9:09 PM, Michael Paquier
>> >  wrote:
>> >> Since commit de768844, XLogFileCopy of xlog.c returns to caller a
>> >> pstrdup'd string that can be used afterwards for other things.
>> >> XLogFileCopy is used in only one place, and it happens that the result
>> >> string is never freed at all, leaking memory.
>>
>> That seems to be almost harmless because the startup process will exit
>> just after calling XLogFileCopy(). No?
>
>
> Yes that's harmless. My point here is correctness, prevention does not hurt
> particularly if this code path is used more in the future.

Why don't we call InstallXLogFileSegment() at the end of XLogFileCopy()?
If we do that, the risk of memory leak you're worried will disappear at all.

Regards,

-- 
Fujii Masao


-- 
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] RFC: Remove contrib entirely

[Simon Riggs]

On 29 May 2015 at 02:50, Peter Eisentraut  wrote:

> On 5/28/15 3:35 PM, Stephen Frost wrote:
> > What we would need for this is an 'extensions' directory, or similar,
> > and a clear definition of what the requirements are around getting into
> > it are.  With that, we could decide for each module currently in contrib
> > if it should go into the 'extensions' directory.  I'm not sure that we
> > would necessairly have to remove the contrib module or any modules which
> > are deemed to not be appropriate for the 'extensions' directory.
>
> This seems reasonable to me.  It's in line with the recent move from
> contrib to bin.  It'll just be quite a bit bigger of an undertaking.
> (50 threads to discuss the merits of each module separately?)  Maybe
> start by picking the top 5 and sort those out.


+1 for Extensions directory for 9.6

This doesn't seem worth delaying the release for.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

[HACKERS] Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Robert Haas]

On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch  wrote:
> On Thu, Jun 04, 2015 at 05:29:51PM -0400, Robert Haas wrote:
>> Here's a new version with some more fixes and improvements:
>
> I read through this version and found nothing to change.  I encourage other
> hackers to study the patch, though.  The surrounding code is challenging.

Andres tested this and discovered that my changes to
find_multixact_start() were far more creative than intended.
Committed and back-patched with a trivial fix for that stupidity and a
novel-length explanation of the changes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Stephen Frost]

Robert,

* Robert Haas (robertmh...@gmail.com) wrote:
> On Thu, Jun 4, 2015 at 2:33 PM, Joshua D. Drake  
> wrote:
> > 1. 15 years of the same argument (current source: pg_audit)
> 
> The argument about pg_audit has little to do with contrib.  It is
> primarily about code quality, and secondarily about whether one
> committer can go do something unliterally when a long list of other
> committers and contributors have expressed doubts about it.

I would certainly welcome any further review or comments regarding the
code quality of pg_audit from anyone interested in the capability.  I do
not agree that the code quality is significantly below that of other
modules or core.  There were design restrictions due to it being an
extension which quite a few people had questions and concerns about,
which I addressed through the discussions on the list.

Further, pg_audit was originally presented by 2ndQ, worked on by
multiple major contributors and committers, and had multiple committers
expressing interest in committing it during the push to close out the
final CF for 9.5.  I understand that I've been the one who has primairly
been spending time discussing it on the lists, but I was also one of the
biggest nay-sayers of it over the summer last year.  What changed my
opinion of it?  The point made by other committers that the upgrade
concerns could be addressed and the strong interest from users in the
capability.

Painting it as the unilateral actions of one committer is uncharitable,
at best.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] nested loop semijoin estimates

[Robert Haas]

On Tue, Jun 2, 2015 at 10:10 PM, Tom Lane  wrote:
> What it seems like we should do, if we want to back-patch this, is apply
> it without the add_path_precheck changes.  Then as an independent
> HEAD-only patch, change add_path_precheck so that it's behaving as
> designed.  It looks to me like that will save some planning time in any
> case --- changing add_path_precheck to disregard startup cost when
> appropriate seems to let it reject a lot more paths than it used to.

I'd just like to mention that I really appreciate the time and thought
that went into keeping the back-patched portion of this fix narrow.

Thanks!

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] RFC: Remove contrib entirely

[Robert Haas]

On Thu, Jun 4, 2015 at 2:33 PM, Joshua D. Drake  wrote:
> My argument was (after some preliminary discussion):
>
> 1. Review contrib
> 2. All modules that are "core worthy" install by default
> 3. Push all other contrib out into the wild

So above, I said that we keep adding to contrib because "there are
some things we want to include in the core distribution without baking
them irrevocably into the server" and you said that you weren't
arguing with that, but here you're saying you don't want any such
things to exist.  That doesn't really make any sense.

postgres_fdw is a good example.  It's "core-worthy" in the sense that
it is useful enough to be installed in core, but not everybody may
want their database server to have the ability to make network
connections at the behest of unprivileged users.  Today, they can
achieve that by not installing the extension if they don't want users
to have access to it.  Putting it into core would require us to come
up with some new way to control whether that functionality is
available or not.  That seems like making a lot of work for ourselves
for no real benefit.

This same argument applies to (at least) dblink and adminpack.

> 1. Decrease in code maintenance for core

contrib requires very little maintenance, and is often very helpful
for judging whether other core changes - e.g. changes to hooks - are
working properly.  I see no maintenance benefit to removing it; it
would probably just make it harder to figure out whether other stuff
is broken.  And the removal itself would be a ton of work.

> 2. Removal of the idea that contrib is a holding queue for not quite up to
> snuff code

I don't think it's really being used that away any more.

> 3. Most extensions don't need to follow the same development pattern that
> core does

That's not a reason to move things that are already in contrib to
somewhere else.  At least not that I can see.

> 4. Eliminate the EGO of saying "I have a contrib module in core"

I've got multiple major features in core.  Any ego I may have about my
PostgreSQL contributions is not based on pg_prewarm.

> 1. 15 years of the same argument (current source: pg_audit)

The argument about pg_audit has little to do with contrib.  It is
primarily about code quality, and secondarily about whether one
committer can go do something unliterally when a long list of other
committers and contributors have expressed doubts about it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] pg_stat_*_columns?

[Joel Jacobson]

Would others find it useful to see per column statistics about accesses to
specific columns?

Two possible use-cases: (maybe there are more?)

1. I think it would be helpful for DBAs to better understand their own
system.
Finding unused *tables* is today easy thanks to pg_stat_*_tables, but
knowing if something is accessing a *column* or not is not easy.
In my case all our database access is via sprocs, so I can just grep the
source code for the column name to see if something is using it, but most
DBAs probably don't have that luxury.

2. It could also be useful for audit trailing, if you want to know what a
query did, i.e. what tables/columns were accessed in the txn.

Here is an idea of a very simple audit trailing system that would probably
fulfill my own needs:

Imagine if we had pg_stat_xact_user_columns and for each committed txn, do
an insert to an unlogged table with the same structure as
pg_stat_xact_user_columns
with the addition of session_user and timestamp for the txn.

I would much rather have audit trailing in a nice table than in a text
file. Maybe a foreign data wrapper could be used to ship the audit trail
data to some other external append-only pg-database, if the purpose of the
audit trailing is to prevent an evil DBA from doing evil things. But for
others it might be sufficient to do audit trailing to the same database,
for convenience purposes.

In summary:

Some users might only be interested in the statistics and mostly use
pg_stat_user_columns.
Other others might also be interested in what happened in a specific txn
and use pg_stat_xact_user_columns.
Yet some other users might be interested in audit trailing and want to log
pg_stat_xact_user_columns for each txn. Probably very expensive performance
wise, but might make sense if you have extremely sensitive data and audit
trailing is more important than performance.

Thoughts?

[HACKERS] Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

[Thomas Munro]

On Fri, Jun 5, 2015 at 1:47 PM, Thomas Munro
 wrote:
> On Fri, Jun 5, 2015 at 11:47 AM, Thomas Munro
>  wrote:
>> On Fri, Jun 5, 2015 at 9:29 AM, Robert Haas  wrote:
>>> Here's a new version with some more fixes and improvements:
>>> [...]
>>
>> With this patch, when I run the script
>> "checkpoint-segment-boundary.sh" from
>> http://www.postgresql.org/message-id/CAEepm=1_KbHGbmPVmkUGE5qTP+B4efoCJYS0unGo-Mc5NV=u...@mail.gmail.com
>> I see the following during shutdown checkpoint:
>>
>> LOG:  could not truncate directory "pg_multixact/offsets": apparent 
>> wraparound
>>
>> That message comes from SimpleLruTruncate.
>
> Suggested patch attached.

Is it a problem that we don't drop/forget page buffers from the
members SLRU (unlike SimpleLruTruncate, which is used for the offsets
SLRU)?

I may be missing something but it seems to me that it isn't, because
(1) CheckPointMultiXact is called to flush any dirty pages to disk
before TruncateMultiXact is called and (2) no pages older than the one
holding the oldest offset should be dirtied after CheckPointMultiXact
runs (member space is 'append only', at least until it is recycled),
so any pages in the SLRU whose underlying file has been truncated
should just naturally fall out of the LRU slots.  So they can't create
problems by being written to disk after the unlink.

-- 
Thomas Munro
http://www.enterprisedb.com


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

Re: [HACKERS] Multixid hindsight design

[Simon Riggs]

On 5 June 2015 at 11:02, Andres Freund  wrote:

> On 2015-06-05 10:45:09 +0100, Simon Riggs wrote:
> > On 1 June 2015 at 20:53, Thomas Munro 
> wrote:
> >
> > > On Tue, May 12, 2015 at 9:20 AM, Heikki Linnakangas 
> > > wrote:
> > > > The beauty of this would be that the TED entries can be zapped at
> > > restart,
> > > > just like pg_subtrans, and pg_multixact before 9.3. It doesn't need
> to be
> > > > WAL-logged, and we are free to change its on-disk layout even in a
> minor
> > > > release.
> > >
> > > What about prepared transactions?  They can lock rows FOR SHARE that
> > > survive server restarts.
> > >
> >
> > Interesting comment. I'm not aware that we do.
> >
> > If we do support row locking that survives server restart, how did it
> work
> > before 9.3?
>
> Multixacts were persistent before 9.3 as well. A good number of the bugs
> existed then as well, but their effect was much more limited. The
> difference is that now multixacts don't just have to survive till the
> last locker isn't running anymore (which was determined by a horizon),
> but that they have to live till they're vacuumed away, since xmax might
> be stored in the multixact.
>

Phew! Had me worried for a minute.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Multixid hindsight design

[Simon Riggs]

On 11 May 2015 at 22:20, Heikki Linnakangas  wrote:

> I'd like to discuss how we should've implemented the infamous 9.3
> multixid/row-locking stuff, and perhaps still should in 9.6. Hindsight is
> always 20/20 - I'll readily admit that I didn't understand the problems
> until well after the release - so this isn't meant to bash what's been
> done. Rather, let's think of the future.
>
> The main problem with the infamous multixid changes was that it made
> pg_multixact a permanent, critical, piece of data. Without it, you cannot
> decipher whether some rows have been deleted or not. The 9.3 changes
> uncovered pre-existing issues with vacuuming and wraparound, but the fact
> that multixids are now critical turned those the otherwise relatively
> harmless bugs into data loss.
>
> We have pg_clog, which is a similar critical data structure. That's a pain
> too - you need VACUUM and you can't easily move tables from one cluster to
> another for example - but we've learned to live with it. But we certainly
> don't need any more such data structures.
>
> So the lesson here is that having a permanent pg_multixact is not nice,
> and we should get rid of it. Here's how to do that:
>

I think we should think back to exactly what we are trying to store, why
and for how long. A clear definition of what we are trying to achieve is
essential to solving the problem.

In my understanding we need to store
* at most one xid - the Updating Xid
* potentially many Locking Xids

The current design has two SLRUs and puts all of those xids in the Members
SLRU, causing it to need to be persistent.

The problems come from having significant numbers of locking xids. My
understanding is that any change in the number of lockers requires the full
array to be rewritten. So with N lockers we end up with 2N-1 arrays, each
array has an average of N/2 members, or N^2  entries, i.e. an O(N^2)
algorithm, which makes it a bad thing to persist. Assuming that design
continues mostly unchanged in its core points...

An alternate proposal:

1. Store only the Locking xids in the Members SLRU
2. In the Offsets SLRU store: 1) the Updating Xid and 2) the offset to the
Locking xids in the Members SLRU.

This means the Offsets SLRU will be around twice the size it was before BUT
since we reduce the size of each Members array by one, there is a balanced
saving there, so this change is disk-space-neutral.

That way if we need to make Offsets SLRU persistent it won't bloat.
We then leave the Members SLRU as non-persistent, just as it was <9.3

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Multixid hindsight design

[Andres Freund]

On 2015-06-05 10:45:09 +0100, Simon Riggs wrote:
> On 1 June 2015 at 20:53, Thomas Munro  wrote:
> 
> > On Tue, May 12, 2015 at 9:20 AM, Heikki Linnakangas 
> > wrote:
> > > The beauty of this would be that the TED entries can be zapped at
> > restart,
> > > just like pg_subtrans, and pg_multixact before 9.3. It doesn't need to be
> > > WAL-logged, and we are free to change its on-disk layout even in a minor
> > > release.
> >
> > What about prepared transactions?  They can lock rows FOR SHARE that
> > survive server restarts.
> >
> 
> Interesting comment. I'm not aware that we do.
> 
> If we do support row locking that survives server restart, how did it work
> before 9.3?

Multixacts were persistent before 9.3 as well. A good number of the bugs
existed then as well, but their effect was much more limited. The
difference is that now multixacts don't just have to survive till the
last locker isn't running anymore (which was determined by a horizon),
but that they have to live till they're vacuumed away, since xmax might
be stored in the multixact.


-- 
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] [idea] more aggressive join pushdown on postgres_fdw

[Shigeru HANADA]

2015/06/05 6:43、Robert Haas  のメール：
> On Sat, May 30, 2015 at 9:03 PM, Kouhei Kaigai  wrote:
> Neat idea.  This ties into something I've thought about and mentioned
> before: what if the innerrel is local, but there's a replicated copy
> on the remote server?  Perhaps both cases are worth thinking about at
> some point.

Interesting, but I’m not sure that I understood the situation.

Here which kind of replication method do you mean?  I guess you assume some 
kind of per-table replication such as Slony-I or materialized views with 
postgres_fdw or dblink, in postgres_fdw case.  If this assumption is correct, 
we need a mapping between a local ordinary table and a foreign table which 
points remote replicated table.

--
Shigeru HANADA
shigeru.han...@gmail.com






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

Re: [HACKERS] Multixid hindsight design

[Simon Riggs]

On 1 June 2015 at 20:53, Thomas Munro  wrote:

> On Tue, May 12, 2015 at 9:20 AM, Heikki Linnakangas 
> wrote:
> > The beauty of this would be that the TED entries can be zapped at
> restart,
> > just like pg_subtrans, and pg_multixact before 9.3. It doesn't need to be
> > WAL-logged, and we are free to change its on-disk layout even in a minor
> > release.
>
> What about prepared transactions?  They can lock rows FOR SHARE that
> survive server restarts.
>

Interesting comment. I'm not aware that we do.

If we do support row locking that survives server restart, how did it work
before 9.3?

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Handle PGRES_COPY_BOTH in psql for logical replication?

[Shulgin, Oleksandr]

On Fri, Jun 5, 2015 at 10:22 AM, Andres Freund  wrote:

> >
> > Maybe I'm missing something, which functions do you have in mind exactly?
>
> pg_logical_slot_get_changes() etc?
>

Oh, totally forgot about these.  However there are two significant
differences between using the functions and using START_REPLICATION command:

1. With get/peek_changes one cannot specify start_lsn. A parameter upto_lsn
is supported instead.
2. The functions return when either of the upto_* limits is reached or
there are no more data to decode, while with internal command it should
wait for more data until interrupted by user.

Anyway, using pg_recvlogical is perfectly fine by me, it's just psql can
pass the command, but is not ready to handle the request.  Maybe just
having are more sensible error message for PGRES_COPY_BOTH is the way to go.

--
Alex

Re: [HACKERS] Handle PGRES_COPY_BOTH in psql for logical replication?

[Andres Freund]

On 2015-06-05 09:43:45 +0200, Shulgin, Oleksandr wrote:
> > How would that look like? It's just binary data you'd get back? And what
> > would be the feedback messages and such look like, and what would be the
> > content?

> It would be whatever the logical decoding plugin sends.  Makes perfect
> sense for OUTPUT_PLUGIN_TEXTUAL_OUTPUT to see it on the terminal.  And you
> could even use binary with psql's \o, if you want (though usefulness is
> less apparent to me in this case).

Hm. I severely doubt that that's a useful thing to do.

> > For example, one could try replication from different wal positions,
> > > different output plugins and/or plugin options, w/o re-establishing the
> > > connection, interactively by editing the command directly in psql prompt,
> > > as opposed to using pg_recvlogical and editing the command line switches.
> >
> > You can mostly use the sql functions for that.
> >
> 
> Maybe I'm missing something, which functions do you have in mind exactly?

pg_logical_slot_get_changes() etc?


-- 
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] Handle PGRES_COPY_BOTH in psql for logical replication?

[Shulgin, Oleksandr]

On Fri, Jun 5, 2015 at 9:26 AM, Andres Freund  wrote:

> On 2015-06-05 09:20:41 +0200, Shulgin, Oleksandr wrote:
> > Hello Hackers,
> >
> > At the moment one can open up a replication connection using psql and use
> > all of the commands available on this special type of connection, except
> > for START_REPLICATION:
> >
> > $ psql -d "dbname=test replication=database"
> > psql (9.4.1)
> > Type "help" for help.
> >
> > test=# IDENTIFY_SYSTEM;
> >   systemid   | timeline |  xlogpos  | dbname
> > -+--+---+-
> >  6146106447402521313 |1 | 0/76422B0 | bw_test
> > (1 row)
> >
> > test=# START_REPLICATION SLOT test LOGICAL 0/0;
> > unexpected PQresultStatus: 8
> > test=#
> >
> > This is due to PGRES_COPY_BOTH not being handled in psql.  With
> > introduction of logical replication I think it makes sense to support
> this
> > mode, for the sake of (semi-)interactive testing from psql.
>
> How would that look like? It's just binary data you'd get back? And what
> would be the feedback messages and such look like, and what would be the
> content?
>

It would be whatever the logical decoding plugin sends.  Makes perfect
sense for OUTPUT_PLUGIN_TEXTUAL_OUTPUT to see it on the terminal.  And you
could even use binary with psql's \o, if you want (though usefulness is
less apparent to me in this case).

Since my intent is to just use it for testing the look of text logical
output, I'm not planning any support for feedback messages in this case.

> For example, one could try replication from different wal positions,
> > different output plugins and/or plugin options, w/o re-establishing the
> > connection, interactively by editing the command directly in psql prompt,
> > as opposed to using pg_recvlogical and editing the command line switches.
>
> You can mostly use the sql functions for that.
>

Maybe I'm missing something, which functions do you have in mind exactly?

--
Alex

Re: [HACKERS] RFC: Remove contrib entirely

[Jim Nasby]

On 6/4/15 3:11 PM, David E. Wheeler wrote:

There is no indication of what versions of pg any of PGXN modules are tested 
on, or even if there are tests that can be run to prove the module works 
correctly with a particular version of pg.

Yeah, I’ve been meaning to integratehttp://pgxn-tester.org/  results for all 
modules, which would help with that. In the meantime you can hit that site 
itself. Awesome work by Tomas Vondra.


A number of modules also run Travis-CI. Might be worth having a way for 
a module to provide it's status .png.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] Handle PGRES_COPY_BOTH in psql for logical replication?

[Andres Freund]

On 2015-06-05 09:20:41 +0200, Shulgin, Oleksandr wrote:
> Hello Hackers,
> 
> At the moment one can open up a replication connection using psql and use
> all of the commands available on this special type of connection, except
> for START_REPLICATION:
> 
> $ psql -d "dbname=test replication=database"
> psql (9.4.1)
> Type "help" for help.
> 
> test=# IDENTIFY_SYSTEM;
>   systemid   | timeline |  xlogpos  | dbname
> -+--+---+-
>  6146106447402521313 |1 | 0/76422B0 | bw_test
> (1 row)
> 
> test=# START_REPLICATION SLOT test LOGICAL 0/0;
> unexpected PQresultStatus: 8
> test=#
> 
> This is due to PGRES_COPY_BOTH not being handled in psql.  With
> introduction of logical replication I think it makes sense to support this
> mode, for the sake of (semi-)interactive testing from psql.

How would that look like? It's just binary data you'd get back? And what
would be the feedback messages and such look like, and what would be the
content?

> For example, one could try replication from different wal positions,
> different output plugins and/or plugin options, w/o re-establishing the
> connection, interactively by editing the command directly in psql prompt,
> as opposed to using pg_recvlogical and editing the command line switches.

You can mostly use the sql functions for that.

Regards,

Andres


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