Optimze usage of immutable functions as relation

[Aleksandr Parfenov]

Hi hackers,

There is a strange behavior of the query planner in some cases if
stable/immutable was used a relation. In some cases, it affects costs of
operations and leads to a bad plan of the execution. Oleg Bartunov 
noticed
such behavior in queries with a to_tsvector as a relation:

=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple') q where body_tsvector @@ 
q;
 QUERY PLAN
--
  Nested Loop  (cost=383.37..58547.70 rows=4937 width=36)
->  Function Scan on to_tsquery q  (cost=0.25..0.26 rows=1 width=32)
->  Bitmap Heap Scan on messages  (cost=383.12..58461.04 rows=4937 
width=275)
  Recheck Cond: (body_tsvector @@ q.q)
  ->  Bitmap Index Scan on message_body_idx  (cost=0.00..381.89 
rows=4937 width=0)
Index Cond: (body_tsvector @@ q.q)
(6 rows)

=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple') q where body_tsvector @@
q limit 10;
QUERY PLAN

  Limit  (cost=0.25..425.62 rows=10 width=36)
->  Nested Loop  (cost=0.25..210005.80 rows=4937 width=36)
  Join Filter: (messages.body_tsvector @@ q.q)
  ->  Function Scan on to_tsquery q  (cost=0.25..0.26 rows=1 
width=32)
  ->  Seq Scan on messages  (cost=0.00..197625.45 rows=987445 
width=275)

The idea of the fix for this situation is to check is a result of the
function constant or not during the planning of the query. Attached 
patch does
this by processing Var entries at planner stage and replace them with
constant value if it is possible. Plans after applying a patch (SeqScan
query for comparison):

=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple') q where body_tsvector @@
q limit 10;
   QUERY PLAN
--
  Limit  (cost=224.66..268.11 rows=3 width=36)
->  Nested Loop  (cost=224.66..268.11 rows=3 width=36)
  ->  Function Scan on to_tsquery q  (cost=0.25..0.26 rows=1 
width=0)
  ->  Bitmap Heap Scan on messages  (cost=224.41..267.04 rows=3 
width=275)
Recheck Cond: (body_tsvector @@ 
to_tsquery('tuple'::text))
->  Bitmap Index Scan on message_body_idx  
(cost=0.00..224.41 rows=3 width=0)
  Index Cond: (body_tsvector @@ 
to_tsquery('tuple'::text))
(7 rows)

=# set enable_bitmapscan=off;
SET
=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple') q where body_tsvector @@
q limit 10;
 QUERY PLAN
--
  Limit  (cost=1000.25..296754.14 rows=3 width=36)
->  Gather  (cost=1000.25..296754.14 rows=3 width=36)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.25..295753.32 rows=1 width=36)
->  Parallel Seq Scan on messages
(cost=0.00..295752.80 rows=1 width=275)
  Filter: (body_tsvector @@ 
to_tsquery('tuple'::text))
->  Function Scan on to_tsquery q  (cost=0.25..0.26 
rows=1 width=0)
(7 rows)

-- 
Aleksandr Parfenov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 505ae0af85..410a14ed95 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3655,6 +3655,40 @@ eval_const_expressions_mutator(Node *node,
 	  context);
 			}
 			break;
+		case T_Var:
+			if (context->root && context->root->parse->rtable)
+			{
+Var		   *var;
+Query	   *query;
+RangeTblEntry *pointedNode;
+
+var = (Var *)node;
+query = context->root->parse;
+
+if (var->varlevelsup != 0)
+	break;
+
+pointedNode = list_nth(query->rtable, var->varno - 1);
+Assert(IsA(pointedNode, RangeTblEntry));
+
+if (pointedNode->rtekind == RTE_FUNCTION && pointedNode->functions->length == 1)
+{
+	Node	   *result;
+	RangeTblFunction *tblFunction = pointedNode->functions->head->data.ptr_value;
+
+	Assert(IsA(tblFunction, RangeTblFunction));
+	result = eval_const_expressions(context->root, tblFunction->funcexpr);
+
+	if (result->type == T_FuncExpr)
+	{
+		pfree(result);
+		return node;
+	}
+
+	return result;
+}
+			}
+			break;
 		default:
 			break;
 	}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index cbc882d47b..eb92f556d8 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3021,23 +3021,23 @@ 

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Andrew Gierth]

> "Peter" == Peter Geoghegan  writes:

 Peter> There are a couple of other odd things that look related, such
 Peter> as this extract from the triggers.out section of my
 Peter> regression.diffs:

That one is pretty obvious (from RelationBuildTriggers):

 * Note: since we scan the triggers using TriggerRelidNameIndexId, we will
 * be reading the triggers in name order, except possibly during
 * emergency-recovery operations (ie, IgnoreSystemIndexes). This in turn
 * ensures that triggers will be fired in name order.

Nothing in the code attempts to preserve the order of trigger firing in
the ignore_system_indexes=on case.

-- 
Andrew (irc:RhodiumToad)

Re: Proper way to reload config files in backend SIGHUP handler

[Mike Palmiotto]

On 05/03/2018 08:43 PM, Euler Taveira wrote:
> 2018-05-03 19:25 GMT-03:00 Mike Palmiotto :
>> I am writing a PostgreSQL extension and need to reload certain
>> extension-managed files whenever the postmaster is reloaded/receives SIGHUP,
>> but cannot find anything that looks like a standard way to do that. Is there 
>> a
>> hook or recommended method or something else I am missing?
>>
> Signals are initially blocked in bgworker. You have to unblock them
> (see BackgroundWorkerUnblockSignals) to allow your extension to
> customize its signal handlers. See an example in worker_spi test
> module.

I don't seem to have any problem catching the SIGHUP in my extension without
BackgroundWorkerUnblockSignals a la:



pqsignal_sighup(SIGHUP, sighup_handler);
.
.
.
static void
sighup_handler(SIGNAL_ARGS)
{
  
}



Perhaps the signal is already unblocked at this point. Unblocking signals
first doesn't appear to have any affect. Am I missing something here?

I noticed that 6e1dd27
(https://github.com/postgres/postgres/commit/6e1dd2773eb60a6ab87b27b8d9391b756e904ac3)
introduced a ConfigReloadPending flag that can be set by calling
PostgresSigHupHandler inside the extension's handler, which seemingly allows
things to work as they usually would, and then we can go on to do other config
processing.

Is this approach kosher, or is there another preferred route?

Thanks,

-- 
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Peter Geoghegan]

On Thu, May 3, 2018 at 7:26 PM, Alvaro Herrera  wrote:
> I bet this is related to how are these objects reached while walking the
> dependency graph -- i.e. they are reached first as columns and reported
> explicitly in the second case, but in the first case the tables are
> reached first so the columns are not considered individually.  So it'd
> just be because of pg_depend scan order.

There are a couple of other odd things that look related, such as this
extract from the triggers.out section of my regression.diffs:

***
*** 1047,1056 
  NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
  NOTICE:  OLD: (20,30), NEW: (20,31)
  NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE,
when = BEFORE, level = STATEMENT
- NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE,
when = AFTER, level = ROW
  NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when
= AFTER, level = ROW
! NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE,
when = AFTER, level = STATEMENT
  NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when
= AFTER, level = STATEMENT
  NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
  UPDATE 1
  UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
--- 1051,1060 
  NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
  NOTICE:  OLD: (20,30), NEW: (20,31)
  NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE,
when = BEFORE, level = STATEMENT
  NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when
= AFTER, level = ROW
! NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE,
when = AFTER, level = ROW
  NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when
= AFTER, level = STATEMENT
+ NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE,
when = AFTER, level = STATEMENT
  NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
  UPDATE 1
  UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;

This is a trigger on a view. I'm too tired to figure out whether or
not this is truly cause for concern right now, though

-- 
Peter Geoghegan

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Peter Geoghegan]

On Thu, May 3, 2018 at 7:31 PM, Tom Lane  wrote:
> Ah, I think it's just an order-of-visitation issue then.  There are
> dependencies at both the column and whole-table level, specifically
>
> schema collate_tests -> table collate_test4
> schema collate_tests -> domain testdomain_p -> column collate_test4.b
>
> I think if we already know that table collate_test4 is scheduled to be
> deleted, we just ignore column collate_test4.b when the recursion reaches
> that ... but if we visit those two things in the other order, then both
> will be reported as deletion targets.  And it's not surprising that
> disabling indexscans on pg_depend changes the visitation order.

I also noticed that there are cases where we see less helpful (though
still technically correct) HINT messages about which other object the
user may prefer to drop.

-- 
Peter Geoghegan

Re: unused_oids script is broken with bsd sed

[Tom Lane]

Peter Eisentraut  writes:
> On 5/3/18 15:37, Tom Lane wrote:
>> I took a quick look into this.  It's very easy to do so far as the Perl
>> code is concerned:

> I think in order to introduce warts like that, we have to have really
> great savings.  I haven't seen any actual analysis what the current
> problem is, other than one person expression a suspicion.

Well, the work's already done, and personally I think the code is
cleaner after 9bf28f96c and bad51a49a regardless of whether there's any
performance benefit.  You could call 1f1cd9b5d a wart if you wanted.
But we've done largely the same thing to pgindent, for one, in the past
year or so, and I find that to be a usability improvement, independently
of whether there's any build performance win.  My editor gets cranky
when files change under it for no reason.

regards, tom lane

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Tom Lane]

Peter Geoghegan  writes:
> In the case of the collate tests, these are the 17 objects I can see
> with ignore_system_indexes=off, once I remove the "\set VERBOSITY
> terse" line from the end of collate.sql:
> ...
> drop cascades to table collate_test23
> drop cascades to table collate_test4
> drop cascades to table collate_test5
> ...

> In the case of ignore_system_indexes=on, I see the same 17 entries, in
> addition to these 3 (20 total):
> drop cascades to table collate_test23 column f1
> drop cascades to table collate_test4 column b
> drop cascades to table collate_test5 column b

Ah, I think it's just an order-of-visitation issue then.  There are
dependencies at both the column and whole-table level, specifically

schema collate_tests -> table collate_test4
schema collate_tests -> domain testdomain_p -> column collate_test4.b

I think if we already know that table collate_test4 is scheduled to be
deleted, we just ignore column collate_test4.b when the recursion reaches
that ... but if we visit those two things in the other order, then both
will be reported as deletion targets.  And it's not surprising that
disabling indexscans on pg_depend changes the visitation order.

This makes me realize that the "\set VERBOSITY terse" hack is not
really as bulletproof a way of preventing regression test diffs
during DROP CASCADE as I'd imagined :-(

regards, tom lane

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Alvaro Herrera]

Peter Geoghegan wrote:

> In the case of ignore_system_indexes=on, I see the same 17 entries, in
> addition to these 3 (20 total):
> 
> drop cascades to table collate_test23 column f1
> drop cascades to table collate_test4 column b
> drop cascades to table collate_test5 column b
> 
> Perhaps this means something to you. I find it suspicious that all 3
> possibly-missing entries are "column" entries.

I bet this is related to how are these objects reached while walking the
dependency graph -- i.e. they are reached first as columns and reported
explicitly in the second case, but in the first case the tables are
reached first so the columns are not considered individually.  So it'd
just be because of pg_depend scan order.

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

Re: unused_oids script is broken with bsd sed

[Peter Eisentraut]

On 5/3/18 15:37, Tom Lane wrote:
> I took a quick look into this.  It's very easy to do so far as the Perl
> code is concerned:

I think in order to introduce warts like that, we have to have really
great savings.  I haven't seen any actual analysis what the current
problem is, other than one person expression a suspicion.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Clock with Adaptive Replacement

[Peter Geoghegan]

On Thu, May 3, 2018 at 12:37 PM, Robert Haas  wrote:
> On Wed, May 2, 2018 at 3:06 PM, Vladimir Sitnikov
>  wrote:
>> Sample output can be seen here:
>> https://github.com/vlsi/pgsqlstat/tree/pgsqlio#pgsqlio
>
> Neat.  Not sure what generated this trace, but note this part:
>
> 32718388813748820500 16631638516604  0
>  3271840973321 436800 16631638516604  1
>  3271842680626 450200 16631638516604  1
>  3271846077927 417300 16631638516604  1
>
> If we want to avoid artificial inflation of usage counts, that kind of
> thing would be a good place to start -- obviously 4 consecutive
> accesses to the same buffer by the same backend doesn't justify a
> separate usage count bump each time.

I don't have time to check this out just now, but it seems like an
excellent idea. It would be nice if it could be enhanced further, so
you get some idea of what the blocks are without having to decode them
yourself using tools like pageinspect.

-- 
Peter Geoghegan

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Peter Geoghegan]

On Thu, May 3, 2018 at 4:14 PM, Peter Geoghegan  wrote:
> On Thu, May 3, 2018 at 3:18 PM, Tom Lane  wrote:
>> Indeed, that seems weird.  Maybe tweak the test scripts so you can see
>> all the objects cascaded to, and then find out what the additional
>> object is?  (I think also you could look into the postmaster log,
>> without changing the test.)

In the case of the collate tests, these are the 17 objects I can see
with ignore_system_indexes=off, once I remove the "\set VERBOSITY
terse" line from the end of collate.sql:

drop cascades to collation mycoll2
drop cascades to function dup(anyelement)
drop cascades to table collate_test1
drop cascades to table collate_test10
drop cascades to table collate_test2
drop cascades to table collate_test20
drop cascades to table collate_test21
drop cascades to table collate_test22
drop cascades to table collate_test23
drop cascades to table collate_test4
drop cascades to table collate_test5
drop cascades to table collate_test_like
drop cascades to type testdomain
drop cascades to type testdomain_p
drop cascades to view collview1
drop cascades to view collview2
drop cascades to view collview3

(I sorted this output in my text editor)

In the case of ignore_system_indexes=on, I see the same 17 entries, in
addition to these 3 (20 total):

drop cascades to table collate_test23 column f1
drop cascades to table collate_test4 column b
drop cascades to table collate_test5 column b

Perhaps this means something to you. I find it suspicious that all 3
possibly-missing entries are "column" entries.

-- 
Peter Geoghegan

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Peter Geoghegan]

On Thu, May 3, 2018 at 3:18 PM, Tom Lane  wrote:
> Peter Geoghegan  writes:
>> Why should the drop cascade to 63 objects rather than 62 because I've
>> set ignore_system_indexes=on?
>
> Indeed, that seems weird.  Maybe tweak the test scripts so you can see
> all the objects cascaded to, and then find out what the additional
> object is?  (I think also you could look into the postmaster log,
> without changing the test.)

I wrote a patch that makes ignore_system_indexes=off agree with
ignore_system_indexes=on in the two specific places I see problems
(the create_view and collate tests). Actually, I started with the
patch, and only ended up finding this issue later, while debugging my
own work.

The goal of this patch of mine is to have nbtree use heap TID as a
unique-ifier -- an implicit "final column" that is a first class part
of the key space (we sometimes have to store an "extra" heap TID
attribute in internal page tuples to make this work across the entire
tree structure). Vadim wanted to do something like this about 20 years
ago, and I think he had the right idea -- it's more or less what
Lehman and Yao intended.

I don't actually know what the problem is just yet, but I suspect some
subtle nbtree issue. I haven't actually debugged the underlying issue
at all just yet, but I'll start on that now. I don't want to post my
patch, since it's very much still a work in progress, and I'm not yet
100% sure that it actually truly fixes the issue.

-- 
Peter Geoghegan

Re: A few warnings on Windows

[Thomas Munro]

On Fri, May 4, 2018 at 2:46 AM, Peter Eisentraut
 wrote:
> On 5/3/18 10:18, Tom Lane wrote:
>> Christian Ullrich  writes:
 Thomas Munro  writes:
> Does anyone know what line 174 of pyconfig.h happens to say?
>>
>>> typedef _W64 int ssize_t;
>>> , in a "not for 64-bit" block.
>>> , 3.6.3 is
>>> the installed version on whelk.
>>
>> Thanks.  Not a lot we're going to be able to do about silencing that
>> one, I'm afraid.  Too bad they haven't wrapped that stanza in
>> "#ifndef HAVE_SSIZE_T".
>
> There is still time to send a patch for Python 3.7.

Maybe we could poke this?  https://bugs.python.org/issue11717

Apparently ssize_t is not defined on Windows (it's from POSIX, not C)
and various projects step on each other's toes defining it.  On 64 bit
systems we both use __int64.  On 32 bit systems, we chose long and
they chose int.  If we changed our definition to use int too, I assume
that would fix the warnings here but might risk creating the opposite
problem somewhere else...

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

Proper way to reload config files in backend SIGHUP handler

[Mike Palmiotto]

All,

I am writing a PostgreSQL extension and need to reload certain
extension-managed files whenever the postmaster is reloaded/receives SIGHUP,
but cannot find anything that looks like a standard way to do that. Is there a
hook or recommended method or something else I am missing?

Thanks,

-- 
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com

Re: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Tom Lane]

Peter Geoghegan  writes:
> Why should the drop cascade to 63 objects rather than 62 because I've
> set ignore_system_indexes=on?

Indeed, that seems weird.  Maybe tweak the test scripts so you can see
all the objects cascaded to, and then find out what the additional
object is?  (I think also you could look into the postmaster log,
without changing the test.)

regards, tom lane

Re: Python 3.7 support

[Tom Lane]

Peter Eisentraut  writes:
> I have committed this now, since the release of Python 3.7 is soon.
> I'll let the build farm have a pass at it, then backport it for the
> upcoming minor releases.

gaur/pademelon (Python 2.5) not very happy :-(.  Let me know if you'd
like me to try anything particular there.

regards, tom lane

ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

[Peter Geoghegan]

Setting ignore_system_indexes=off in postgresql.conf has the effect of
making almost all regression tests fail during a "make installcheck".
This is unsurprising, since warnings are emitted all over the place.
However, some of the specific ways in which it fails *are* surprising.

I see the following regressions.diff, for the create_view tests:

***
*** 1711,1714 
  DROP SCHEMA temp_view_test CASCADE;
  NOTICE:  drop cascades to 27 other objects
  DROP SCHEMA testviewschm2 CASCADE;
! NOTICE:  drop cascades to 62 other objects
--- 1725,1732 
  DROP SCHEMA temp_view_test CASCADE;
  NOTICE:  drop cascades to 27 other objects
  DROP SCHEMA testviewschm2 CASCADE;
! NOTICE:  drop cascades to 63 other objects
! WARNING:  using index "pg_toast_2618_index" despite IgnoreSystemIndexes
! WARNING:  using index "pg_toast_2618_index" despite IgnoreSystemIndexes
! WARNING:  using index "pg_toast_2618_index" despite IgnoreSystemIndexes
! WARNING:  using index "pg_toast_2618_index" despite IgnoreSystemIndexes

Why should the drop cascade to 63 objects rather than 62 because I've
set ignore_system_indexes=on? I know that the order of objects is
unspecified/unstable for the verbose DETAIL output of CASCADE, but
that's rather a different thing to the total number of objects
affected.

The same thing happens to the collate tests:

***
*** 668,671 
  --
  \set VERBOSITY terse
  DROP SCHEMA collate_tests CASCADE;
! NOTICE:  drop cascades to 17 other objects
--- 676,679 
  --
  \set VERBOSITY terse
  DROP SCHEMA collate_tests CASCADE;
! NOTICE:  drop cascades to 20 other objects

-- 
Peter Geoghegan

Re: unused_oids script is broken with bsd sed

[Andrew Dunstan]

On Thu, May 3, 2018 at 3:37 PM, Tom Lane  wrote:
>
> Anyway, I'm happy to go make this happen; it looks like only another hour
> or so's worth of work to fix the makefiles.  But I wonder if anyone will
> say this is too much churn for post-feature-freeze and we should shelve
> it till v12.
>

I think we can be a bit more liberal about build system changes than
we would be with core code. So I'd say go for it.

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: unused_oids script is broken with bsd sed

[Alvaro Herrera]

Tom Lane wrote:

> However, RenameTempFile is also used by Gen_fmgrtab.pl, and having the
> same sort of no-touch semantics for fmgroids.h and friends would have some
> additional fallout.  The makefiles would think they have to keep
> re-running Gen_fmgrtab.pl if fmgroids.h is older than the mod time on any
> input file, and that's certainly no good.  We can fix that by inventing a
> stamp file for the Gen_fmgrtab.pl run, analogous to bki-stamp for the
> genbki.pl run, but that means changes in the makefiles that go a bit
> beyond the realm of triviality.

Sounds OK to me -- a stamp file is already established technique, so it
shouldn't go *too much* beyond triviality.  I do note that
msvc/Solution.pm runs Gen_fmgrtab.pl, but it shouldn't require any
changes anyhow.

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

Re: unused_oids script is broken with bsd sed

[Tom Lane]

Robert Haas  writes:
> On Thu, Apr 26, 2018 at 11:29 AM, Tom Lane  wrote:
>> Personally, I use ccache which doesn't seem to care too much, but I agree
>> than in some usages, extra touches of headers would be costly.  Perhaps
>> it's worth adding logic to avoid overwriting an existing output file
>> unless it changed?  I'm not sure; that would cost something too.

> It seems like a good idea to me.

I took a quick look into this.  It's very easy to do so far as the Perl
code is concerned: just teach Catalog::RenameTempFile that if the target
file already exists, run File::Compare::compare to see if its contents are
identical to the temp file, and if so unlink the temp file rather than
renaming.  I've tried this, it works, and I can't measure any difference
in the runtime of genbki.pl (at least on my usual dev machine).  So it
seems like there's little downside.

However, RenameTempFile is also used by Gen_fmgrtab.pl, and having the
same sort of no-touch semantics for fmgroids.h and friends would have some
additional fallout.  The makefiles would think they have to keep
re-running Gen_fmgrtab.pl if fmgroids.h is older than the mod time on any
input file, and that's certainly no good.  We can fix that by inventing a
stamp file for the Gen_fmgrtab.pl run, analogous to bki-stamp for the
genbki.pl run, but that means changes in the makefiles that go a bit
beyond the realm of triviality.

A compromise answer is to arrange things so that genbki.pl has no-touch
semantics but Gen_fmgrtab.pl doesn't, requiring either two support
functions in Catalog.pm or an extra argument to RenameTempFile.  But
that's really ugly.  Besides, if we're trying to avoid excess recompiles
due to useless touches of common header files, then failing to have
no-touch behavior for fmgroids.h is leaving a lot of money on the table.
So I don't like that answer at all.

Anyway, I'm happy to go make this happen; it looks like only another hour
or so's worth of work to fix the makefiles.  But I wonder if anyone will
say this is too much churn for post-feature-freeze and we should shelve
it till v12.

regards, tom lane

diff --git a/src/backend/catalog/Catalog.pm b/src/backend/catalog/Catalog.pm
index eee7cb3..1b31cdd 100644
*** a/src/backend/catalog/Catalog.pm
--- b/src/backend/catalog/Catalog.pm
*** package Catalog;
*** 16,21 
--- 16,24 
  use strict;
  use warnings;
  
+ use File::Compare;
+ 
+ 
  # Parses a catalog header file into a data structure describing the schema
  # of the catalog.
  sub ParseHeader
*** sub AddDefaultValues
*** 336,350 
  }
  
  # Rename temporary files to final names.
! # Call this function with the final file name and the .tmp extension
  # Note: recommended extension is ".tmp$$", so that parallel make steps
! # can't use the same temp files
  sub RenameTempFile
  {
  	my $final_name = shift;
  	my $extension  = shift;
  	my $temp_name  = $final_name . $extension;
! 	rename($temp_name, $final_name) || die "rename: $temp_name: $!";
  }
  
  # Find a symbol defined in a particular header file and extract the value.
--- 339,367 
  }
  
  # Rename temporary files to final names.
! # Call this function with the final file name and the .tmp extension.
! #
! # If the final file already exists and has identical contents, don't
! # overwrite it; this behavior avoids unnecessary recompiles due to updating
! # the mod date on header files.
! #
  # Note: recommended extension is ".tmp$$", so that parallel make steps
! # can't use the same temp files.
  sub RenameTempFile
  {
  	my $final_name = shift;
  	my $extension  = shift;
  	my $temp_name  = $final_name . $extension;
! 
! 	if (-f $final_name &&
! 		compare($temp_name, $final_name) == 0)
! 	{
! 		unlink $temp_name || die "unlink: $temp_name: $!";
! 	}
! 	else
! 	{
! 		rename($temp_name, $final_name) || die "rename: $temp_name: $!";
! 	}
  }
  
  # Find a symbol defined in a particular header file and extract the value.

Re: [HACKERS] Clock with Adaptive Replacement

[Robert Haas]

On Wed, May 2, 2018 at 3:06 PM, Vladimir Sitnikov
 wrote:
> Sample output can be seen here:
> https://github.com/vlsi/pgsqlstat/tree/pgsqlio#pgsqlio

Neat.  Not sure what generated this trace, but note this part:

32718388813748820500 16631638516604  0
 3271840973321 436800 16631638516604  1
 3271842680626 450200 16631638516604  1
 3271846077927 417300 16631638516604  1

If we want to avoid artificial inflation of usage counts, that kind of
thing would be a good place to start -- obviously 4 consecutive
accesses to the same buffer by the same backend doesn't justify a
separate usage count bump each time.

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

Moving libpg{common,port,feutils}.a to pkglibdir?

[Christoph Berg]

Debian's PostgreSQL packages allow installation of several server
versions in parallel, but only one libpq and libpq-dev package is
provided, i.e. libpq is always from the latest stable branch. There
are separate postgresql-server-dev-NN packages for the
version-specific header files. So far, postgresql-server-dev-NN does
not contain any libraries.

In the context of Michael Banck's pg_checksums program [*], we ran
into the problem that libpqport.a is not compatible across server
versions. Currently it is installed into LIBDIR (=
/usr/lib/). It would need to be in PKGLIBDIR (=
/usr/lib/postgresql/NN/lib) to allow co-installation of several
versions. (This is actually the first time we try to package client
programs that are server-version-dependant; pg-filedump works on all
server versions no matter what version it is linked against.)

I'm contemplating moving these static libs to PKGLIBDIR. Or should
libfeutils.a better stay in LIBDIR, with only one version?

Maybe they should be moved in "stock" PostgreSQL as well?

Christoph

[*] I haven't checked, but this likely applies to Michael Paquier's
version of it as well

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

[Robert Haas]

On Wed, May 2, 2018 at 9:17 AM, Ashutosh Bapat
 wrote:
> On Wed, May 2, 2018 at 11:56 AM, Amit Langote
>  wrote:
>> But one could very well argue that BEFORE ROW triggers on the
>> parent should run before performing the tuple routing and not be cloned to
>> individual partitions, in which case the result would not have been the
>> same.  Perhaps that's the motivation behind leaving this to be considered
>> later.
>
> +1 for that. We should associate before row triggers with the
> partitioned table and not with the partitions. Those should be
> executed before tuple routing (for that partition level) kicks in. But
> then that would be asymetric with AFTER row trigger behaviour. From
> this POV, pushing AFTER row triggers to the individual partitions
> doesn't look good.

The asymmetry doesn't seem horrible to me on its own merits, but it
would lead to some odd behavior: suppose you defined a BEFORE ROW
trigger and an AFTER ROW trigger on the parent, and then inserted one
row into the parent table and a second row directly into a partition.
It seems like the BEFORE ROW trigger would fire only for the parent
insert, but the AFTER ROW trigger would fire in both cases.

What seems like a better idea is to have the BEFORE ROW trigger get
cloned to each partition just as we do with AFTER ROW triggers, but
arrange things so that if it already got fired for the parent table,
it doesn't get fired again after tuple routing.  This would be a bit
tricky to get correct in cases where there are multiple levels of
partitioning involved, but it seems doable.

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

Re: unused_oids script is broken with bsd sed

[Robert Haas]

On Thu, Apr 26, 2018 at 11:29 AM, Tom Lane  wrote:
> Personally, I use ccache which doesn't seem to care too much, but I agree
> than in some usages, extra touches of headers would be costly.  Perhaps
> it's worth adding logic to avoid overwriting an existing output file
> unless it changed?  I'm not sure; that would cost something too.

It seems like a good idea to me.

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

Re: Python 3.7 support

[Peter Eisentraut]

On 2/14/18 21:25, Peter Eisentraut wrote:
> On 2/13/18 21:45, Michael Paquier wrote:
>> On Tue, Feb 13, 2018 at 04:17:13PM -0500, Peter Eisentraut wrote:
>>> A small patch to tweak the tests to support output differences with
>>> Python 3.7 (currently in beta).
>>
>> Wouldn't it be better to wait for the version to be released before
>> pushing anything in the tree?  If there are again changes when this
>> comes out as GA then an extra patch would be needed.
> 
> Sure, we can just leave it here for the record until the release comes
> closer.

I have committed this now, since the release of Python 3.7 is soon.
I'll let the build farm have a pass at it, then backport it for the
upcoming minor releases.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Global snapshots

[Stas Kelvich]

> On 3 May 2018, at 18:28, Masahiko Sawada  wrote:
> 
> On Wed, May 2, 2018 at 1:27 AM, Stas Kelvich  wrote:
>> 1) To achieve commit atomicity of different nodes intermediate step is
>>   introduced: at first running transaction is marked as InDoubt on all nodes,
>>   and only after that each node commit it and stamps with a given GlobalCSN.
>>   All readers who ran into tuples of an InDoubt transaction should wait until
>>   it ends and recheck visibility.
> 
> I'm concerned that long-running transaction could keep other
> transactions waiting and then the system gets stuck. Can this happen?
> and is there any workaround?

InDoubt state is set just before commit, so it is short-lived state.
During transaction execution global tx looks like an ordinary running
transaction. Failure during 2PC with coordinator not being able to
commit/abort this prepared transaction can result in situation where
InDoubt tuples will be locked for reading, but in such situation
coordinator should be blamed. Same problems will arise if prepared
transaction holds locks, for example.

--
Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: FinishPreparedTransaction missing HOLD_INTERRUPTS section

[Teodor Sigaev]

Thank you, pushed!

Stas Kelvich wrote:

Hello.

It seems that during COMMIT PREPARED FinishPreparedTransaction() doesn't
hold interrupts around writing to wal and cleaning up ProcArray and GXact
entries. At least RemoveTwoPhaseFile (which is called in between) can print
a warning with ereport(), which, in turn will check for interrupts and
therefore can cancel backend or throw an error before GXact clean-up.

Other similar places like CommitTransaction and PrepareTransaction have
such hold interrupts sections.

--
Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

Re: lazy detoasting

[Andrew Gierth]

> "Peter" == Peter Eisentraut  writes:

 Peter> The attached test fixes this issue by flattening the toast
 Peter> values before storing them into PL/pgSQL variables. It could use
 Peter> another check to see if there are other code paths that need
 Peter> similar adjustments, but I think it's the right idea in general.

Uhh.

What about:

  somevar := (select blah);

or

  somevar := function_returning_toasted_val(blah);

or

  call someproc(function_returning_toasted_val(blah));

or or or ...

-- 
Andrew (irc:RhodiumToad)

Re: Built-in connection pooling

[Robert Haas]

On Fri, Apr 27, 2018 at 4:43 PM, Merlin Moncure  wrote:
> What _I_ (maybe not others) want is a
> faster pgbouncer that is integrated into the database; IMO it does
> everything exactly right.

I have to admit that I find that an amazing statement.  Not that
pgbouncer is bad technology, but saying that it does everything
exactly right seems like a vast overstatement.  That's like saying
that you don't want running water in your house, just a faster motor
for the bucket you use to draw water from the well.

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

Re: FPW stats?

[Robert Haas]

On Wed, May 2, 2018 at 7:10 AM, Michael Paquier  wrote:
> Your patch adds a new field to PgStat_StatDBEntry?  Wouldn't you
> increase the bottleneck of deployments with many databases?  What's
> actually your use case?

I'm a little doubtful about whether this particular thing is generally
useful but I think the bar for adding a field to PgStat_StatDBEntry is
probably a lot lower than for a per-table counter.  I think adding
table-level counters is basically not happening without some kind of
rework of the infrastructure; whereas adding db-level counters seems
like it might be OK if we were convinced that they had real value.

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

Re: lazy detoasting

[Peter Eisentraut]

On 5/1/18 19:56, Andrew Gierth wrote:
>  Peter> insert into test1 values (1, repeat('foo', 2000));
> 
> That value is no good because it's too compressible; it'll be left
> inline in the main table rather than being externalized, so the value of
> 'x' in the DO-block is still self-contained (though it's still toasted
> in the sense of being VARATT_IS_EXTENDED).

Right.  I added

alter table test1 alter column b set storage external;

then I can see the error.

The attached test fixes this issue by flattening the toast values before
storing them into PL/pgSQL variables.  It could use another check to see
if there are other code paths that need similar adjustments, but I think
it's the right idea in general.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 294b68508cade1dd0df5b78c0f0ec12d7ce761b8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Thu, 3 May 2018 10:54:13 -0400
Subject: [PATCH v1] PL/pgSQL: Flatten TOAST data in nonatomic context

When in a nonatomic execution context, when storing a potentially
toasted datum into a PL/pgSQL variable, we need to flatten the
datum (i.e., remove any references to TOAST data).  Otherwise, a
transaction end combined with, say, a concurrent VACUUM, between storing
the datum and reading it, could remove the TOAST data, and then the data
in the variable would no longer be readable.
---
 src/pl/plpgsql/src/pl_exec.c | 11 +++
 1 file changed, 11 insertions(+)

diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 228d1c0d00..28a6957286 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -20,6 +20,7 @@
 #include "access/htup_details.h"
 #include "access/transam.h"
 #include "access/tupconvert.h"
+#include "access/tuptoaster.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -6621,6 +6622,16 @@ exec_move_row(PLpgSQL_execstate *estate,
 {
ExpandedRecordHeader *newerh = NULL;
 
+   /*
+* If not in atomic context, flatten TOAST references.  Otherwise the
+* TOAST data might disappear if a transaction is committed.
+*/
+   if (!estate->atomic)
+   {
+   if (tup)
+   tup = toast_flatten_tuple(tup, tupdesc);
+   }
+
/*
 * If target is RECORD, we may be able to avoid field-by-field 
processing.
 */

base-commit: 30c66e77be1d890c3cca766259c0bec80bcac1b5
-- 
2.17.0

Re: GSoC 2018: thrift encoding format

[Vladimir Sitnikov]

>Personally I think raw data bytes are OK if functions for getting all
keys and values from this data are provided

What is the purpose of using Thrift "encoding" if it turns out to be a
simple wrapper for existing binary data?

Do you mean the goal is to have "get/set" functions to fetch data out of
bytea field?

Frankly speaking, I can hardly imagine why one would want to store MAP
Datum inside Thrift inside bytea.

Vladimir

Re: Optimize Arm64 crc32c implementation in Postgresql

[Tom Lane]

Andrew Gierth  writes:
> "Tom" == Tom Lane  writes:
>  Tom> I also noticed that we'd been sloppy about making the file safe to
>  Tom> compile for both frontend and backend, so I cleaned that up.

> In a frontend, wouldn't it be more kosher to restore the previous SIGILL
> handler rather than unconditionally reset it to SIG_DFL?

If we had any other code that was setting the SIGILL trap, I might
worry about that, but we don't.

The whole thing is really a bit questionable to run in arbitrary
environments -- for instance, it'd be pretty unsafe inside a threaded
application.  So if we had code in libpq or ecpg that computed CRCs,
I'd be worrying about this approach quite a bit more.  But it seems all
right for current and foreseen uses.

regards, tom lane

Re: Optimize Arm64 crc32c implementation in Postgresql

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 Tom> I also noticed that we'd been sloppy about making the file safe to
 Tom> compile for both frontend and backend, so I cleaned that up.

In a frontend, wouldn't it be more kosher to restore the previous SIGILL
handler rather than unconditionally reset it to SIG_DFL?

-- 
Andrew (irc:RhodiumToad)

Re: Optimize Arm64 crc32c implementation in Postgresql

[Tom Lane]

Thomas Munro  writes:
> Let me try that again with that stupid typo (crc2) fixed...

I didn't like that too much as-is, because it was capable of calling
elog(ERROR) without having reset the SIGILL trap first.  That's just
trouble waiting to happen, so I rearranged to avoid it.

I also noticed that we'd been sloppy about making the file safe to
compile for both frontend and backend, so I cleaned that up.

Also, I had thought that maybe the postmaster should do something to
ensure that it sets up the function pointer, so that child processes
inherit the correct pointer via fork() and don't need to repeat the
test (and then possibly spam the postmaster log).  On closer inspection,
no new code is needed because ReadControlFile runs a CRC check, but
I felt it was worth documenting that.

regards, tom lane

Re: Global snapshots

[Masahiko Sawada]

On Wed, May 2, 2018 at 1:27 AM, Stas Kelvich  wrote:
> 1) To achieve commit atomicity of different nodes intermediate step is
>introduced: at first running transaction is marked as InDoubt on all nodes,
>and only after that each node commit it and stamps with a given GlobalCSN.
>All readers who ran into tuples of an InDoubt transaction should wait until
>it ends and recheck visibility.

I'm concerned that long-running transaction could keep other
transactions waiting and then the system gets stuck. Can this happen?
and is there any workaround?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: documentation is now XML

[Liudmila Mantrova]

Hi Peter,

Thank you for taking your time to comment on this long-discussed topic.
I was not around when all the great work was done to implement XSLT 
transforms in branch 10 and convert branch 11 to XML, but judging by 
this thread 
(https://www.postgresql.org/message-id/flat/4ffd72d6-8ab6-37c6-d7d5-dfed8967c0fc%40gmail.com), 
much of the testing was automated and could be re-applied to older 
branches.


Taking into account that both XSLT transforms and testing scripts can be 
reused for the most part, do you think that the benefits of having 
consistent source (easy back-porting of doc patches and one-time 
translation to all languages) in all the supported branches could 
potentially outweigh the inevitable conversion overhead? Are there any 
specific features that make branch 10 considerably different from its 
predecessors that we are missing?



On 04/30/2018 10:20 PM, Peter Eisentraut wrote:

On 4/27/18 11:03, Bruce Momjian wrote:

On Fri, Apr 27, 2018 at 11:00:36AM -0400, Peter Eisentraut wrote:

On 4/23/18 05:54, Liudmila Mantrova wrote:

Reading this thread, I got an impression that everyone would benefit
from converting back branches to XML, but the main concern is lack of
resources to complete this task. Are there any other issues that affect
this decision? Looks like Aleksander Lakhin's offer to prepare patches
was missed somehow as the discussion sidetracked to other issues

That proposal seemed to indicate not only converting the source code to
XML but also the build system to XSL.  The latter is out of the
question, I think.

Why is that?

Because there would be a thousand lines of tooling changes to be
backported and thousands of pages of documentation to be checked
manually that it doesn't create a mess (times branches times platforms).



--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: A few warnings on Windows

[Peter Eisentraut]

On 5/3/18 10:18, Tom Lane wrote:
> Christian Ullrich  writes:
>>> Thomas Munro  writes:
 Does anyone know what line 174 of pyconfig.h happens to say?
> 
>> typedef _W64 int ssize_t;
>> , in a "not for 64-bit" block.
>> , 3.6.3 is 
>> the installed version on whelk.
> 
> Thanks.  Not a lot we're going to be able to do about silencing that
> one, I'm afraid.  Too bad they haven't wrapped that stanza in
> "#ifndef HAVE_SSIZE_T".

There is still time to send a patch for Python 3.7.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: A few warnings on Windows

[Tom Lane]

Christian Ullrich  writes:
>> Thomas Munro  writes:
>>> Does anyone know what line 174 of pyconfig.h happens to say?

> typedef _W64 int ssize_t;
> , in a "not for 64-bit" block.
> , 3.6.3 is 
> the installed version on whelk.

Thanks.  Not a lot we're going to be able to do about silencing that
one, I'm afraid.  Too bad they haven't wrapped that stanza in
"#ifndef HAVE_SSIZE_T".

regards, tom lane

Re: Issues while building PG in MS Windows, using MSYS2 and MinGW-w64

[Pavlo Golub]

Hello, insaf.k.

You wrote:



ik> Hello,



ik> I am trying to build PG from source, in MS Windows using MSYS2
ik> and MinGW-w64. I've tried to build PG 10.0 as wells as 10.3.

Just checked. All compiled in a sane way. I suppose you have some
environmental problems.

This is my step-by-step guide:

1. Download MSYS2 installer, 64-bit.
2. Run, choose simple install folder, e.g. C:\msys64
3. Run MSYS2 after installation and execute:
  pacman -Syu
4. Maybe you'll need this several times. Close console
5. Open appropriate console with correct environment:
  a) C:\msys64\msys2_shell.cmd -mingw64
 or
  b) just click on the Start menu “MSYS2 MinGW 64-bit” shortcut
6. In it update packages:
   pacman -Syu
7. Install needed packages:
   pacman --needed -S git mingw-w64-x86_64-gcc base-devel
8. Execute:

   git clone git://git.postgresql.org/git/postgresql.git
   cd postgresql
   ./configure --host=x86_64-w64-mingw32 --prefix=/c/pgsql/ && make && make 
install

Here we tell our target machine will be 64-bit and we want our
binaries to be copied to C:\pgsql\. 

ik> I've done configuring like this 

ik>   ./configure --prefix="/d/pg10/"

ik> And when I do "make" or "make world", I'm getting compilation
ik> error. I've attached complete error report at the end of the mail.



ik> Basically, one error is pre-processor #error must have a working 64-bit 
integer datatype.

ik> Another error is "redifinition of fseeko".

ik> Another error is "FLEXIBLE_ARRAY_MEMBER" is undefined.





ik> I don't know why the error is coming, I tried reinstalling MSYS2
ik> and but still no luck. Could you please help?


ik> My $PATH var is

ik> $ echo $PATH
ik> 
/mingw64/bin:/usr/local/bin:/usr/bin:/bin:/c/Windows/System32:/c/Windows:/c/Windows/System32/Wbem:/c/Windows/System32/WindowsPowerShell/v1.0/:/usr/bin/site_perl:/usr/bin/vendor_perl:/usr/bin/core_perl







ik> $ make
ik> make -C src all
ik> make[1]: Entering directory '/d/Insaf/pgSource/postgresql-10.0/src'
ik> make -C common all
ik> make[2]: Entering directory
ik> '/d/Insaf/pgSource/postgresql-10.0/src/common'
ik> make -C ../backend submake-errcodes
ik> make[3]: Entering directory
ik> '/d/Insaf/pgSource/postgresql-10.0/src/backend'
ik> make[3]: Nothing to be done for 'submake-errcodes'.
ik> make[3]: Leaving directory
ik> '/d/Insaf/pgSource/postgresql-10.0/src/backend'
ik> gcc -Wall -Wmissing-prototypes -Wpointer-arith
ik> -Wdeclaration-after-statement -Wendif-labels
ik> -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
ik> -fwrapv -fexce ss-precision=standard -O2
ik> -DFRONTEND -I../../src/include  -I./src/include/port/win32
ik> -DEXEC_BACKEND  "-I../../src/include/port/win32" -DBUILDING_DLL
ik> -DVAL_CONFIGURE="\"'--p refix=/d/Insaf/pgGcc'
ik> 'PKG_CONFIG_PATH=/mingw64/lib/pkgconfig:/mingw64/share/pkgconfig'\""
ik> -DVAL_CC="\"gcc\"" -DVAL_CPPFLAGS="\"-DFRONTEND
ik> -I./src/include/port/win32 -DEXEC _BACKEND
ik> "-I../../src/include/port/win32" -DBUILDING_DLL\""
ik> -DVAL_CFLAGS="\"-Wall -Wmissing-prototypes -Wpointer-arith
ik> -Wdeclaration-after-statement -Wendif-labels -Wmissin 
ik> g-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv
ik> -fexcess-precision=standard -O2\"" -DVAL_CFLAGS_SL="\"\""
ik> -DVAL_LDFLAGS="\"-L../../src/common -Wl,--allow-
ik> multiple-definition -Wl,--disable-auto-import -Wl,--as-needed\""
ik> -DVAL_LDFLAGS_EX="\"\"" -DVAL_LDFLAGS_SL="\"\""
ik> -DVAL_LIBS="\"-lpgcommon -lpgport -lz -lws2_32 -lm  -lws2_32 \""  
-c -o base64.o base64.c
ik> In file included from ../../src/include/c.h:48:0,
ik>  from ../../src/include/postgres_fe.h:25,
ik>  from base64.c:18:
ik> ../../src/include/postgres_ext.h:47:9: error: unknown type name 
'PG_INT64_TYPE'
ik>  typedef PG_INT64_TYPE pg_int64;
ik>  ^
ik> In file included from ../../src/include/postgres_fe.h:25:0,
ik>  from base64.c:18:
ik> ../../src/include/c.h:306:2: error: #error must have a working 64-bit 
integer datatype
ik>  #error must have a working 64-bit integer datatype
ik>   ^
ik> ../../src/include/c.h:446:15: error: 'FLEXIBLE_ARRAY_MEMBER'
ik> undeclared here (not in a function)
ik>   char  vl_dat[FLEXIBLE_ARRAY_MEMBER]; /* Data content is here */
ik>^
ik> In file included from ../../src/include/postgres_fe.h:25:0,
ik>  from base64.c:18:
ik> ../../src/include/c.h:1054:1: warning: 'PG_PRINTF_ATTRIBUTE' is
ik> an unrecognized format function type [-Wformat=]
ik>  extern int snprintf(char *str, size_t count, const char
ik> *fmt,...) pg_attribute_printf(3, 4);
ik>  ^~
ik> In file included from ../../src/include/c.h:1129:0,
ik>  from ../../src/include/postgres_fe.h:25,
ik>  from base64.c:18:
ik> ../../src/include/port.h:375:0: warning: "fseeko" redefined
ik>  #define fseeko(a, b, c) fseek(a, b, c)

ik> In file included from 

Re: pgsql: Clean up warnings from -Wimplicit-fallthrough.

[Peter Eisentraut]

On 5/1/18 23:33, Tom Lane wrote:
> Andres Freund  writes:
>> On 2018-05-01 23:35:18 +, Tom Lane wrote:
>>> Clean up warnings from -Wimplicit-fallthrough.
> 
>> I found one more oddity with the current committed state: ...
>> It seems that gcc gets confused by the #ifdef ECONNRESET.
> 
> Yeah, there's a gcc bug or three about that:
> 
> https://gcc.gnu.org/bugzilla/show_bug.cgi?id=77817
> 
>> ... But the nicer fix seems to be to move the
>> FALL THRU into the ifdef, which even seems more correct when nitpicking
>> in god mode.
> 
> I thought about that, and didn't like it much.  I hoped maybe the gcc guys
> would fix it soon, but after reading their bugzilla entry more closely,
> it sounds like we shouldn't hold our breath.  So if you want to do it
> like that, OK by me.

It sounds like we have a handle on all the potential issues for now, so
I think it would be OK to add this warning to the standard set on master.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Anyone keep mirrors of old packages from apt.postgresql.org?

[Craig Ringer]

On 3 May 2018 at 16:54, Craig Ringer  wrote:
> On 3 May 2018 at 16:48, Sergei Kornilov  wrote:
>> Hello
>> Do you know http://atalia.postgresql.org/morgue/ repository?
>
> Ugh. It's in the README. I'm blind. So sorry for the noise.

I don't see anything similar for yum.postgresql.org though.

Devrim?

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

Re: Is there a memory leak in commit 8561e48?

[Peter Eisentraut]

On 5/2/18 20:11, Michael Paquier wrote:
> On Wed, May 02, 2018 at 07:03:21PM -0400, Tom Lane wrote:
>> It's only ~100 bytes per stack level.  I think under normal loads
>> nobody would notice.  If you're worried about cross-transaction
>> memory consumption, our various caches tend to be a lot worse.
> 
> Perhaps, that's one reason why people drop connections from time to time
> to the server even with a custom pooler.  I am wondering if we are going
> to have complains about "performance regressions" found after upgrading
> to Postgres 11 for deployments which rely on complicated PL call stacks,
> or complains about the OOM killer though.  Getting to review large
> procedures stacks can be a pain for application developers.

I went with the patch I had posted, since I needed to move ahead with
something.  If it turns out to be a problem, we can easily switch it around.

As Tom mentioned, in order to grow the SPI stack to where it has a
significant size, you might also overrun the OS stack and other things.
On the other hand, the current/new arrangement is a win for normal SPI
use, since you don't need to rebuild the stack on every call.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

ParseDateTime in src/backend/utils/adt/datetime.c

[Vladimir Svedov]

Hi guys,
https://stackoverflow.com/questions/50153122/how-come-this-postgres-query-is-working
Above guys are surprised by "ignore other punctuation but use as delimiter"
part.
Do you think this should be mentioned in docs?
E.g.
https://www.postgresql.org/docs/current/static/functions-formatting.html
docs have very thorough explanation on what is skipped and what parsed.

I believe at least interval '1^minute' equality to interval '1 minute' or
other short comment would make it clear.

Sorry if Iwrote to a wrong list. Maybe I should write to
pgsql-d...@lists.postgresql.org instead? please point me If I should.

Regards

Re: GSOC 2018

[Andrey Borodin]

Hi, Joshua!

> 2 мая 2018 г., в 22:05, Joshua D. Drake  написал(а):
> 
> Who is coordinating GSOC this year?
Stephen Frost is org admin.

Best regards, Andrey Borodin.

Re: GSoC 2018: thrift encoding format

[Aleksander Alekseev]

Hello Charles,

> Thanks for your confirm Aleksander!
> Also I am thinking of how to deal with complex
> data structure like map, list, or set. I guess one possible
> solution is to get raw data bytes for these data structure?
> Otherwise it could be hard to wrap into a Datum.

Personally I think raw data bytes are OK if functions for getting all
keys and values from this data are provided. Another possibility is just
converting Thrift to JSONB and vise versa. In this case only two
procedures are required and all the rest is available out-of-the-box.

-- 
Best regards,
Aleksander Alekseev


signature.asc
Description: PGP signature

Re: Anyone keep mirrors of old packages from apt.postgresql.org?

[Craig Ringer]

On 3 May 2018 at 16:48, Sergei Kornilov  wrote:
> Hello
> Do you know http://atalia.postgresql.org/morgue/ repository?

Ugh. It's in the README. I'm blind. So sorry for the noise.

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

Re: Anyone keep mirrors of old packages from apt.postgresql.org?

[Sergei Kornilov]

Hello
Do you know http://atalia.postgresql.org/morgue/ repository?

> copy of postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb anywhere
Here is 
http://atalia.postgresql.org/morgue/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1.pgdg80%2b1_amd64.deb

regards, Sergei

Anyone keep mirrors of old packages from apt.postgresql.org?

[Craig Ringer]

Hi all

I'm trying to assemble a mirror of old package versions from
apt.postgresql.org and yum.postgresql.org, as I'm encountering more
and more cases where I really need debuginfo for a package but the
user hasn't installed it. The repos only keep the last couple of
builds, so it quickly becomes impossible to install debuginfo (or
contribs, etc) matching a user's version.

For !debuginfo that's not too bad. I just upgrade the server. But for
debuginfo it's a real problem because I often want to debug a core
that comes from a very specific build. I can't do that with another
version's debuginfo. Or I want to debug an in-progress process and
I'll lose the very state I want to look at if I restart the server
into a new build where I can get matching debuginfos.

Not coincidentally, if anyone happens to have a copy of
postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb anywhere, or has it
installed, *please* get in touch. I have a system with a
semi-reproducible buffer table corruption issue that only happens
after multiple months, and it turns out the user doesn't have
debuginfo...

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

Re: Is a modern build system acceptable for older platforms

[Pavel Golub]

Hello, Yuriy.

You wrote:



YZ>  (2) it might make things easier on Windows,
YZ>  which could be a sufficiently good reason but I don't think I've seen
YZ>  anyone explain exactly how much easier it will make things and in what
YZ>  ways.



YZ> 1. You can remove tools/msvc folder because all your build rules
YZ> will be universal. (cmake build now have much fewer lines of code)
YZ> 2. You can forget about terminal in Windows (for windows guys it's 
important)
YZ> 3. You can normally check environment on Windows, right now we
YZ> have hardcoded headers and many options. Configure process will be same on 
all platforms.
YZ> 4. You can generate not only GNU Make or MSVC project, you also
YZ> can make Xcode projects, Ninja or NMake for build under MSVC Make.
YZ> For Windows, you also can easily change MSVC to Clang it's not hardcoded at 
all. 
YZ> 5. With CMake you have an easy way to build extra modules
YZ> (plugins), I have already working prototype for windows PGXS.  A
YZ> plugin should just include .cmake file generated with Postgres build.
YZ> Example:
YZ> 
https://github.com/stalkerg/postgres_cmake/blob/cmake/contrib/adminpack/CMakeLists.txt
YZ> If PGXS is True it's mean we build module outside postgres.


Cool! Thanks for pointing this out. I just had problems building PG
extensions for Windows. So I switched to MSYS2 and only then I managed
that. No chance for MSVC :(


YZ> But in my opinion, you should just try CMake to figure out all benefits.


>> we can't judge whether they do without a clear explanation of what the gains 
>>will be


YZ> I think it's not that thing what easy to explain. Main benefits
YZ> not in unix console area and C language...



-- 
With best wishes,
 Pavel  mailto:pa...@gf.microolap.com

Re: A few warnings on Windows

[Christian Ullrich]

* Tom Lane wrote:


Thomas Munro  writes:

One more problem.  whelk builds against Python 3.6 and says:



c:\users\pgbf\appdata\local\programs\python\python36-32\include\pyconfig.h(174):
warning C4142: benign redefinition of type
(src/pl/plpython/plpy_elog.c)
[C:\buildfarm\buildenv\HEAD\pgsql.build\plpython3.vcxproj]



Does anyone know what line 174 of pyconfig.h happens to say?


typedef _W64 int ssize_t;

, in a "not for 64-bit" block.

, 3.6.3 is 
the installed version on whelk.


--
Christian

Re: Optimize Arm64 crc32c implementation in Postgresql

[Thomas Munro]

On Thu, May 3, 2018 at 5:18 PM, Thomas Munro
 wrote:
> 2018-05-03 05:07:25.904 UTC [19677] DEBUG:  using armv8 crc2 hardware = 1

Let me try that again with that stupid typo (crc2) fixed...

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


0001-Fix-endianness-bug-in-ARMv8-CRC32-detection-v2.patch
Description: Binary data