Re: [RFC] Add an until-0 loop in psql

[Corey Huinker]

>> What you don't see here is that you're using your psql process's
>> available open file handles as a stack, and when you hit that limit psql
>> will fail. If you remove that limit, then you get a bit further before psql
>> segfaults on you. I think I got ~2700 files deep before that happened. Your
>> stackage may vary.
>>
>> I'm not saying this is a good solution, quite the contrary. I think the
>> sane solution is right around the corner in Version 11.
>>
>> Now if we just had a way of passing parameters into DO blocks...
>>
>
> I hope so there will be schema (temporal) variables:
>
> create temp variable foo int default 10;
>
> do $$
> begin
>   for i in 1..foo loop
> raise notice '%', i;
>   end loop;
> end;
> $$;
>

That would be nice too.

A while back, somebody explained why implementing parameters in a DO block
was so hard, but I don't recall why, and the search terms "do" and
"parameter" don't really narrow things down.

Re: GSoC 2018: Sorting Algorithm and Benchmarking

[Atri Sharma]

+1 For Slack

On Sat, 28 Apr 2018, 09:52 Andrey Borodin,  wrote:

>
>
> > 27 апр. 2018 г., в 23:23, Kefan Yang  написал(а):
> >
> > I think Slack would be great for our communication
>
> Here's Slack invite app http://postgres-slack.herokuapp.com/
> Should we create a channel for this project?
>
> Best regards, Andrey Borodin.

Re: [RFC] Add an until-0 loop in psql

[Pavel Stehule]

Hi

2018-04-27 21:40 GMT+02:00 Corey Huinker :

> As of v11, DO blocks can do transactions. I think this will meet your
> needs.
>
> A loop that starts at point X in the code and terminates at point Y has to
> know how to jump back in the file (if there even is a file!) to point X and
> re-interpret commands as it makes it's way back through the "file" toward
> point Y again... a place it might not reach, or it might reach some other
> loop termination first. \commands can be hidden inside psql variables,
> files can be conditionally included based on \if statements, and those
> files might have loop starters/terminators in them. And those commands
> *are* processed.
>
> That, or you'd have to capture the code by somehow parsing ahead to the
> next \until-0 (processing all inner loops as you go, and the files they
> include, etc), but that means that variables that were expanded the first
> time are *not* expanded on subsequent iterations, and that makes it hard
> to set an exit-condition variable. It would also seriously alter what psql
> is when inside that loop.
>
> I once did a presentation on ways to (ab)use psql, and one thing I did was
> recursion via include files. Adapting your loop as literally as possible,
> it would look like this:
>
> loop_file.sql:
>
> BEGIN;
> WITH deleted_rows AS (DELETE FROM big_table
>   WHERE id in (SELECT id FROM big_table WHERE bad =
> true LIMIT 1000)
>   RETURNING 1)
> SELECT (COUNT(*) > 0) as deleted_some_rows FROM deleted_rows
> \gset
> VACUUM big_table;
> COMMIT;
> \if :deleted_some_rows
>\include loop_file.sql
> \endif
>
>
> What you don't see here is that you're using your psql process's available
> open file handles as a stack, and when you hit that limit psql will fail.
> If you remove that limit, then you get a bit further before psql segfaults
> on you. I think I got ~2700 files deep before that happened. Your stackage
> may vary.
>
> I'm not saying this is a good solution, quite the contrary. I think the
> sane solution is right around the corner in Version 11.
>
> Now if we just had a way of passing parameters into DO blocks...
>

I hope so there will be schema (temporal) variables:

create temp variable foo int default 10;

do $$
begin
  for i in 1..foo loop
raise notice '%', i;
  end loop;
end;
$$;



>
>
> On Tue, Apr 24, 2018 at 3:59 AM Pierre Ducroquet <
> pierre.ducroq...@people-doc.com> wrote:
>
>> Hi
>>
>> When running database migrations with .sql files on a live database, it's
>> not
>> uncommon to have to run a migration in a loop to prevent a big lock on a
>> table.
>> For instance if one want to delete some old datas from a big table one
>> would
>> write :
>>
>> DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad =
>> true
>> LIMIT 1000);
>> VACUUM big_table;
>>
>> Right now, doing this is quite inefficient. We either have to write a
>> script
>> in another language, or run psql in a shell loop and wait for the
>> migration to
>> stop altering rows.
>>
>> The attached **proof of concept** patch (I insist, it's a 15 minutes hack
>> sprint with no previous knowledge of psql code) implements an 'until-0'
>> loop
>> in psql.
>> The previous migration could be simply written as :
>>
>> \until-0
>> BEGIN;
>> DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad =
>> true
>> LIMIT 1000);
>> VACUUM big_table;
>> COMMIT;
>> \end-until
>>
>> And psql will execute it until there is no row affected in the inner
>> queries.
>>
>> I am willing to write a proper patch for this (I hope the tell/seek is an
>> acceptable implementation…), but I prefer having some feedback first.
>>
>> Thanks
>>
>>  Pierre
>
>

Fix some trivial issues of the document/comment

[ZHUO QL (KDr2)]

I'm learning PGSQL and reading the source, and find few trivial issues in the 
docs:
- doc of catalogs: table 'pg_replication_origin' is a shared relation  but is 
not mentioned in the doc.
- README of mmgr: make variable names consistent.
- trivial error fix in the comment of block.h: InvalidBlockNumber is defined in 
bufmgr.h not buf.h now.

I have my fixes attached, please consider giving it a review.
 
Greetings.

ZHUO QL (KDr2, http://kdr2.com)

From 4cb0ca16b2b36588287173bc5dae810fa9ece805 Mon Sep 17 00:00:00 2001
From: KDr2 
Date: Sat, 28 Apr 2018 11:58:25 +0800
Subject: [PATCH] doc: update doc of catalogs, README of mmgr, etc.

- doc of catalogs: table 'pg_replication_origin' is a shared relation
  but is not mentioned in the doc.

- README of mmgr: make variable names consistent

- trivial error fix in the comment of block.h
---
 doc/src/sgml/catalogs.sgml| 7 +++
 src/backend/utils/mmgr/README | 2 +-
 src/include/storage/block.h   | 2 +-
 3 files changed, 9 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 14aeed3076..6dc9249e19 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5690,6 +5690,13 @@ SCRAM-SHA-256$iteration count:
see .
   
 
+  
+   Unlike most system catalogs, pg_replication_origin
+   is shared across all databases of a cluster: There is only one copy
+   of pg_replication_origin per cluster, not one per
+   database.
+  
+
   
 
pg_replication_origin Columns
diff --git a/src/backend/utils/mmgr/README b/src/backend/utils/mmgr/README
index a42e568d5c..7e6541d0de 100644
--- a/src/backend/utils/mmgr/README
+++ b/src/backend/utils/mmgr/README
@@ -401,7 +401,7 @@ GetMemoryChunkContext())
 
 and then invoke the corresponding method for the context
 
-context->methods->free_p(p);
+context->methods->free_p(pointer);
 
 
 More Control Over aset.c Behavior
diff --git a/src/include/storage/block.h b/src/include/storage/block.h
index e2bfa11e37..182aa8d453 100644
--- a/src/include/storage/block.h
+++ b/src/include/storage/block.h
@@ -22,7 +22,7 @@
  * contains exactly one disk block).  the blocks are numbered
  * sequentially, 0 to 0xFFFE.
  *
- * InvalidBlockNumber is the same thing as P_NEW in buf.h.
+ * InvalidBlockNumber is the same thing as P_NEW in bufmgr.h.
  *
  * the access methods, the buffer manager and the storage manager are
  * more or less the only pieces of code that should be accessing disk
-- 
2.17.0

Re: GSoC 2018: Sorting Algorithm and Benchmarking

[Andrey Borodin]

> 27 апр. 2018 г., в 23:23, Kefan Yang  написал(а):
> 
> I think Slack would be great for our communication

Here's Slack invite app http://postgres-slack.herokuapp.com/
Should we create a channel for this project?

Best regards, Andrey Borodin.

Re: Is a modern build system acceptable for older platforms

[Yuriy Zhuravlev]

>
> Makefiles generated by automake are more feature rich in general,
> which is understandable as its the only backend it has to support.


The main problem here - Postrges do not use automake at all!
Postgres it's autoconf + handmade GNU Make files + perl script for
generating old MSVC project from this Makefiles.

"make distcheck"
>

CMake have no this bad concept, in my opinion, if you want to make the
project you should have a full build environment. (but I don't want to
argue about it here)

Also, as I wrote before, CMake it's not equivalent of GNU Make or Autoconf,
many your reasons based on that fact what CMake, is not a build system it's
more like project generation system.
And anyway, you have no option if you want to support Windows without pain
and much more hacks ways.

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

[David Rowley]

On 26 April 2018 at 21:03, Amit Langote  wrote:
> I noticed also that the item regarding row triggers might be obsolete as
> of 86f575948c7, thanks again to Alvaro!  So, I updated your patch to take
> care of that.

Thanks. I walked right past that one.

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

Re: "could not reattach to shared memory" on buildfarm member dory

[Tom Lane]

Noah Misch  writes:
> On Tue, Apr 24, 2018 at 11:37:33AM +1200, Thomas Munro wrote:
>> Maybe try asking what's mapped there with VirtualQueryEx() on failure?

> +1.  An implementation of that:
> https://www.postgresql.org/message-id/20170403065106.GA2624300%40tornado.leadboat.com

Not seeing any other work happening here, I pushed a little bit of
quick-hack investigation code.  This is based on noting that
VirtualAllocEx is documented as rounding the allocation up to a page
boundary (4K), but there's nothing specific about whether or how much
CreateFileMapping or MapViewOfFileEx might round up.  The observed
failures could be explained if those guys might eat more virtual
address space for the same request size as VirtualAllocEx does.
This is a stretch, for sure, but given the lack of any other theories
we might as well check it.

regards, tom lane

Re: Postgres, fsync, and OSs (specifically linux)

[Andres Freund]

On 2018-04-27 19:38:30 -0400, Bruce Momjian wrote:
> On Fri, Apr 27, 2018 at 04:10:43PM -0700, Andres Freund wrote:
> > Hi,
> > 
> > On 2018-04-27 19:04:47 -0400, Bruce Momjian wrote:
> > > On Fri, Apr 27, 2018 at 03:28:42PM -0700, Andres Freund wrote:
> > > > - We need more aggressive error checking on close(), for ENOSPC and
> > > >   EIO. In both cases afaics we'll have to trigger a crash recovery
> > > >   cycle. It's entirely possible to end up in a loop on NFS etc, but I
> > > >   don't think there's a way around that.
> > > 
> > > If the no-space or write failures are persistent, as you mentioned
> > > above, what is the point of going into crash recovery --- why not just
> > > shut down?
> > 
> > Well, I mentioned that as an alternative in my email. But for one we
> > don't really have cases where we do that right now, for another we can't
> > really differentiate between a transient and non-transient state. It's
> > entirely possible that the admin on the system that ran out of space
> > fixes things, clearing up the problem.
> 
> True, but if we get a no-space error, odds are it will not be fixed at
> the time we are failing.  Wouldn't the administrator check that the
> server is still running after they free the space?

I'd assume it's pretty common that those are separate teams. Given that
we currently don't behave that way for other cases where we *already*
can enter crash-recovery loops I don't think we need to introduce that
here. It's far more common to enter this kind of problem with pg_xlog
filling up the ordinary way. And that can lead to such loops.


> > > Also, since we can't guarantee that we can write any persistent state
> > > to storage, we have no way of preventing infinite crash recovery
> > > loops, which, based on inconsistent writes, might make things worse.
> > 
> > How would it make things worse?
> 
> Uh, I can imagine some writes working and some not, and getting things
> more inconsistent.  I would say at least that we don't know.

Recovery needs to fix that or we're lost anyway. And we'll retry exactly
the same writes each round.


> > > An additional features we have talked about is running some kind of
> > > notification shell script to inform administrators, similar to
> > > archive_command.  We need this too when sync replication fails.
> > 
> > To me that seems like a feature independent of this thread.
> 
> Well, if we are introducing new panic-and-not-restart behavior, we might
> need this new feature.

I don't see how this follows. It's easier to externally script
notification for the server having died, than doing it for crash
restarts. That's why we have restart_after_crash=false...  There might
be some arguments for this type of notification, but I don't think it
should be conflated with the problem here. Nor is it guaranteed that
such a script could do much, given that disks might be failing and such.

Greetings,

Andres Freund

Re: Postgres, fsync, and OSs (specifically linux)

[Bruce Momjian]

On Fri, Apr 27, 2018 at 04:10:43PM -0700, Andres Freund wrote:
> Hi,
> 
> On 2018-04-27 19:04:47 -0400, Bruce Momjian wrote:
> > On Fri, Apr 27, 2018 at 03:28:42PM -0700, Andres Freund wrote:
> > > - We need more aggressive error checking on close(), for ENOSPC and
> > >   EIO. In both cases afaics we'll have to trigger a crash recovery
> > >   cycle. It's entirely possible to end up in a loop on NFS etc, but I
> > >   don't think there's a way around that.
> > 
> > If the no-space or write failures are persistent, as you mentioned
> > above, what is the point of going into crash recovery --- why not just
> > shut down?
> 
> Well, I mentioned that as an alternative in my email. But for one we
> don't really have cases where we do that right now, for another we can't
> really differentiate between a transient and non-transient state. It's
> entirely possible that the admin on the system that ran out of space
> fixes things, clearing up the problem.

True, but if we get a no-space error, odds are it will not be fixed at
the time we are failing.  Wouldn't the administrator check that the
server is still running after they free the space?

> > Also, since we can't guarantee that we can write any persistent state
> > to storage, we have no way of preventing infinite crash recovery
> > loops, which, based on inconsistent writes, might make things worse.
> 
> How would it make things worse?

Uh, I can imagine some writes working and some not, and getting things
more inconsistent.  I would say at least that we don't know.

> > An additional features we have talked about is running some kind of
> > notification shell script to inform administrators, similar to
> > archive_command.  We need this too when sync replication fails.
> 
> To me that seems like a feature independent of this thread.

Well, if we are introducing new panic-and-not-restart behavior, we might
need this new feature.

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

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: FinishPreparedTransaction missing HOLD_INTERRUPTS section

[Michael Paquier]

On Sat, Apr 28, 2018 at 12:36:16AM +0300, Stas Kelvich wrote:
> 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.

Good catch!  The places you are suggesting look good to me as well.
That's something which should be back-patched as well.
--
Michael


signature.asc
Description: PGP signature

Re: Postgres, fsync, and OSs (specifically linux)

[Andres Freund]

Hi,

On 2018-04-27 19:04:47 -0400, Bruce Momjian wrote:
> On Fri, Apr 27, 2018 at 03:28:42PM -0700, Andres Freund wrote:
> > - We need more aggressive error checking on close(), for ENOSPC and
> >   EIO. In both cases afaics we'll have to trigger a crash recovery
> >   cycle. It's entirely possible to end up in a loop on NFS etc, but I
> >   don't think there's a way around that.
> 
> If the no-space or write failures are persistent, as you mentioned
> above, what is the point of going into crash recovery --- why not just
> shut down?

Well, I mentioned that as an alternative in my email. But for one we
don't really have cases where we do that right now, for another we can't
really differentiate between a transient and non-transient state. It's
entirely possible that the admin on the system that ran out of space
fixes things, clearing up the problem.


> Also, since we can't guarantee that we can write any persistent state
> to storage, we have no way of preventing infinite crash recovery
> loops, which, based on inconsistent writes, might make things worse.

How would it make things worse?


> An additional features we have talked about is running some kind of
> notification shell script to inform administrators, similar to
> archive_command.  We need this too when sync replication fails.

To me that seems like a feature independent of this thread.

Greetings,

Andres Freund

Re: Postgres, fsync, and OSs (specifically linux)

[Bruce Momjian]

On Fri, Apr 27, 2018 at 03:28:42PM -0700, Andres Freund wrote:
> - We need more aggressive error checking on close(), for ENOSPC and
>   EIO. In both cases afaics we'll have to trigger a crash recovery
>   cycle. It's entirely possible to end up in a loop on NFS etc, but I
>   don't think there's a way around that.

If the no-space or write failures are persistent, as you mentioned
above, what is the point of going into crash recovery --- why not just
shut down?  Also, since we can't guarantee that we can write any
persistent state to storage, we have no way of preventing infinite crash
recovery loops, which, based on inconsistent writes, might make things
worse.  I think a single panic with no restart is the right solution.

An additional features we have talked about is running some kind of
notification shell script to inform administrators, similar to
archive_command.  We need this too when sync replication fails.

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

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Postgres, fsync, and OSs (specifically linux)

[Andres Freund]

Hi,

I thought I'd send this separately from [0] as the issue has become more
general than what was mentioned in that thread, and it went off into
various weeds.

I went to LSF/MM 2018 to discuss [0] and related issues. Overall I'd say
it was a very productive discussion.  I'll first try to recap the
current situation, updated with knowledge I gained. Secondly I'll try to
discuss the kernel changes that seem to have been agreed upon. Thirdly
I'll try to sum up what postgres needs to change.

== Current Situation ==

The fundamental problem is that postgres assumed that any IO error would
be reported at fsync time, and that the error would be reported until
resolved. That's not true in several operating systems, linux included.

There's various judgement calls leading to the current OS (specifically
linux, but the concerns are similar in other OSs) behaviour:

- By the time IO errors are treated as fatal, it's unlikely that plain
  retries attempting to write exactly the same data are going to
  succeed. There are retries on several layers. Some cases would be
  resolved by overwriting a larger amount (so device level remapping
  functionality can mask dead areas), but plain retries aren't going to
  get there if they didn't the first time round.
- Retaining all the data necessary for retries would make it quite
  possible to turn IO errors on some device into out of memory
  errors. This is true to a far lesser degree if only enough information
  were to be retained to (re-)report an error, rather than actually
  retry the write.
- Continuing to re-report an error after one fsync() failed would make
  it hard to recover from that fact. There'd need to be a way to "clear"
  a persistent error bit, and that'd obviously be outside of posix.
- Some other databases use direct-IO and thus these paths haven't been
  exercised under fire that much.
- Actually marking files as persistently failed would require filesystem
  changes, and filesystem metadata IO, far from guaranteed in failure
  scenarios.

Before linux v4.13 errors in kernel writeback would be reported at most
once, without a guarantee that that'd happen (IIUC memory pressure could
lead to the relevant information being evicted) - but it was pretty
likely.  After v4.13 (see https://lwn.net/Articles/724307/) errors are
reported exactly once to all open file descriptors for a file with an
error - but never for files that have been opened after the error
occurred.

It's worth to note that on linux it's not well defined what contents one
would read after a writeback error. IIUC xfs will mark the pagecache
contents that triggered an error as invalid, triggering a re-read from
the underlying storage (thus either failing or returning old but
persistent contents). Whereas some other filesystems (among them ext4 I
believe) retain the modified contents of the page cache, but marking it
as clean (thereby returning new contents until the page cache contents
are evicted).

Some filesystems (prominently NFS in many configurations) perform an
implicit fsync when closing the file. While postgres checks for an error
of close() and reports it, we don't treat it as fatal. It's worth to
note that by my reading this means that an fsync error at close() will
*not* be re-reported by the time an explicit fsync() is issued. It also
means that we'll not react properly to the possible ENOSPC errors that
may be reported at close() for NFS.  At least the latter isn't just the
case in linux.

Proposals for how postgres could deal with this included using syncfs(2)
- but that turns out not to work at all currently, because syncfs()
basically wouldn't return any file-level errors. It'd also imply
superflously flushing temporary files etc.

The second major type of proposal was using direct-IO. That'd generally
be a desirable feature, but a) would require some significant changes to
postgres to be performant, b) isn't really applicable for the large
percentage of installations that aren't tuned reasonably well, because
at the moment the OS page cache functions as a memory-pressure aware
extension of postgres' page cache.


Another topic brought up in this thread was the handling of ENOSPC
errors that aren't triggered on a filesystem level, but rather are
triggered by thin provisioning. On linux that currently apprently lead
to page cache contents being lost (and errors "eaten") in a lot of
places, including just when doing a write(). In a lot of cases it's
pretty much expected that the file system will just hang or react
unpredictably upon space exhaustion.  My reading is that the block-layer
thin provisioning code is still pretty fresh, and should only be used
with great care. The only way to halfway reliably use it appears to
change the configuration so space exhaustion blocks until admin
intervention (at least dm-thinp provides allows that).


There's some clear need to automate some more testing in this area so
that future behaviour changes don't surprise us.


== Proposed Linux 

FinishPreparedTransaction missing HOLD_INTERRUPTS section

[Stas Kelvich]

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



0001-Add-HOLD_INTERRUPTS-section-into-FinishPreparedTrans.patch
Description: Binary data

Re: Goodbye pg_pltemplate, hello trusted extensions

[Tom Lane]

Andres Freund  writes:
> On 2018-04-27 16:34:04 -0400, Tom Lane wrote:
>> So I thought for awhile about how to deal with that, and eventually
>> decided that really what we need to do is solve this as part of the
>> extension mechanism, not CREATE LANGUAGE per se.  What I'm imagining
>> is that we add new option(s) to extension control files that allow
>> specifying that the extension's script is run as a different user
>> than the user invoking CREATE EXTENSION.

> I don't think the extension control file is really the best place for
> such policy decisions. An extension script can be safe, but not a great
> idea for some installations nevertheless. I don't think it'd be a good
> idea to require site operators to modify extension control files to tune
> trustedness.  I think it might be reasonable to have control file state
> whether they *may* be set as trustable, but then the decision which
> extensions a specific cluster determines as safe needs to be made in a
> catalog or configuration file.

Well, the same could be said about the existing contents of pg_pltemplate,
but how many sites do you know that editorialize on that?  I think for
the vast majority of sites, the DBA just decides whether to install an
extension (into the extension/library directories) and that's plenty of
control.  Second-guessing the extension author about whether unprivileged
users should be allowed to install an extension seems like a very niche
requirement.  On the other hand, *lots* of people have wished for being
able to install, say, hstore into a particular DB without having to be
superuser, once the DBA has made that extension available.

I will agree that having a global disable switch in postgresql.conf or
someplace might be worth doing.  I don't think very many people would
use per-extension configuration overrides.

regards, tom lane

Re: Goodbye pg_pltemplate, hello trusted extensions

[Andres Freund]

Hi,

On 2018-04-27 16:34:04 -0400, Tom Lane wrote:
> So I thought for awhile about how to deal with that, and eventually
> decided that really what we need to do is solve this as part of the
> extension mechanism, not CREATE LANGUAGE per se.  What I'm imagining
> is that we add new option(s) to extension control files that allow
> specifying that the extension's script is run as a different user
> than the user invoking CREATE EXTENSION.  The extension object itself
> remains owned by the calling user (who can drop it), but the contained
> objects are then owned by the other user, so that the extension owner
> doesn't have privilege to modify or drop them individually.

> The ultimate security assumption behind this is that the contents of the
> extension script directory are superuser-approved, and so we can trust
> the contents of any script extension file to be OK to run as superuser
> if its control file says so.  That'd be replacing the existing assumption
> that the contents of pg_pltemplate are superuser-approved.  Perhaps
> there's a hole in that, but I don't see what; if an attacker can scribble
> on the extension script directory, it seems like it's already game over
> for database security.

> Anyway, this is all pretty sketchy, but I'd be willing to work towards
> making it happen in v12.  Thoughts?

I don't think the extension control file is really the best place for
such policy decisions. An extension script can be safe, but not a great
idea for some installations nevertheless. I don't think it'd be a good
idea to require site operators to modify extension control files to tune
trustedness.  I think it might be reasonable to have control file state
whether they *may* be set as trustable, but then the decision which
extensions a specific cluster determines as safe needs to be made in a
catalog or configuration file.

Greetings,

Andres Freund

Re: Built-in connection pooling

[Merlin Moncure]

On Fri, Apr 27, 2018 at 11:44 AM, Konstantin Knizhnik
 wrote:
>
>
> On 27.04.2018 18:33, Merlin Moncure wrote:
>> On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik
>>  wrote:
>>> On 27.04.2018 16:49, Merlin Moncure wrote:
>> I'm confused here...could be language issues or terminology (I'll look
>> at your latest code).  Here is how I understand things:
>> Backend=instance of postgres binary
>> Session=application state within postgres binary (temp tables,
>> prepared statement etc)
>> Connection=Client side connection
>
> Backend is a process, forked by postmaster.

right, we are saying the same thing  here.

>> AIUI (I could certainly be wrong), withing connection pooling, ratio
>> of backend/session is still 1:1.  The idea is that client connections
>> when they issue SQL to the server reserve a Backend/Session, use it
>> for the duration of a transaction, and release it when the transaction
>> resolves.  So many client connections share backends.  As with
>> pgbouncer, the concept of session in a traditional sense is not really
>> defined; session state management would be handled within the
>> application itself, or within data within tables, but not within
>> backend private memory.  Does that align with your thinking?
>
> No. Number of sessions is equal to number of client connections.
> So client is not reserving "Backend/Session" as it happen in pgbouncer.
> One backend keeps multiple sessions. And for each session it maintains
> session context which included client's connection.
> And it is backend's decision transaction of which client it is going to
> execute now.
> This is why built-in pooler is able to provide session semantic without
> backend/session=1:1 requirement.

I see.   I'm not so sure that is a good idea in the general sense :(.
Connection sharing sessions is normal and well understood, and we have
tooling to manage that already (DISCARD).  Having the session state
abstracted out and pinned to the client connection seems complex and
wasteful, at least sometimes.  What _I_ (maybe not others) want is a
faster pgbouncer that is integrated into the database; IMO it does
everything exactly right.

merlin

Goodbye pg_pltemplate, hello trusted extensions

[Tom Lane]

Over in the thread about python versions there was some mention of
getting rid of the pg_pltemplate system catalog in favor of putting
all the info about a procedural language into its extension script.
This has been discussed before, and it'd be a very good thing to do
because it would allow third-party PLs to be fully on par with
the PLs provided by the core distribution.  Right now, the existence
of entries in the default contents of pg_pltemplate makes the core
PLs much easier to work with than third-party PLs.

I spent a bit of time poking at this today, and soon found that the
key thing that pg_pltemplate is doing for us is providing a privilege
escalation mechanism, in that it allows database owners to create trusted
PLs, which is something that'd otherwise require superuser privileges.
We could imagine hacking the behavior of CREATE LANGUAGE a bit in hopes
of getting around that, but a standalone PL extension script would also
need to contain "CREATE FUNCTION ... LANGUAGE C" command(s) for the
language's support functions, and we surely do not want to allow that
to be done with less than superuser privileges.  Moreover, the end state
has to be that those functions are *owned* by a superuser; allowing a
non-superuser to have ALTER FUNCTION privileges on a C-language function
is also a security hole.

So I thought for awhile about how to deal with that, and eventually
decided that really what we need to do is solve this as part of the
extension mechanism, not CREATE LANGUAGE per se.  What I'm imagining
is that we add new option(s) to extension control files that allow
specifying that the extension's script is run as a different user
than the user invoking CREATE EXTENSION.  The extension object itself
remains owned by the calling user (who can drop it), but the contained
objects are then owned by the other user, so that the extension owner
doesn't have privilege to modify or drop them individually.

In the particular case of trusted PLs, we'd want to say that anyone
with the privileges of the database owner can do CREATE EXTENSION,
but the script is run as the bootstrap superuser, who ends up owning
the C functions as well as the language object proper.  It's not
very hard to imagine people using this facility for other extensions
containing C functions, though, and some of them might want different
rules like "this extension can be installed by anybody".  So I don't
have a concrete proposal for what the extension options should look like
exactly.  It's tempting to suggest that the options should include ways
to list specific role names who can install the extension, or a specific
role name to run the script as.  But I'm not sure we want to go there:
that would make the control files pretty installation-specific, which
seems like a bad idea for distribution purposes.  We definitely need
ways to say "allow the database owner to run this" and "run this as the
bootstrap superuser" without getting involved in specific role names.
Beyond that I'm uncertain what's needed.

The ultimate security assumption behind this is that the contents of the
extension script directory are superuser-approved, and so we can trust
the contents of any script extension file to be OK to run as superuser
if its control file says so.  That'd be replacing the existing assumption
that the contents of pg_pltemplate are superuser-approved.  Perhaps
there's a hole in that, but I don't see what; if an attacker can scribble
on the extension script directory, it seems like it's already game over
for database security.

Another issue is that with the existing code for CREATE LANGUAGE,
if a database owner creates a trusted PL, she ends up as the owner
of the language object, though not the underlying C functions.
This solution would not replicate that situation, which is a problem
because it'd take away the DB owner's ability to grant/revoke usage on
the PL.  I'm imagining that we could fix that by having the PL's
extension script do something like

GRANT ALL ON LANGUAGE plfoo TO @extowner@ WITH GRANT OPTION;

where @extowner@ is a macro expanded by CREATE EXTENSION in the
same way as it already does for @extschema@.  This idea isn't fully
baked though: we'd need the state to be that the language's default
PUBLIC USAGE grant comes from the DB owner not directly from the
superuser, and this isn't enough to arrange that.  Maybe it could
read

REVOKE ALL ON LANGUAGE plfoo FROM PUBLIC;
GRANT ALL ON LANGUAGE plfoo TO @extowner@ WITH GRANT OPTION;
SET ROLE @extowner@;
GRANT USAGE ON LANGUAGE plfoo TO PUBLIC;
RESET ROLE;

That seems pretty hacky; maybe it's time to implement the SQL standard's
GRANTED BY  option so that the last three lines could be
simplified to

GRANT USAGE ON LANGUAGE plfoo TO PUBLIC GRANTED BY @extowner@;


Anyway, this is all pretty sketchy, but I'd be willing to work towards
making it happen in v12.  Thoughts?

regards, tom lane

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

[legrand legrand]

Hello,
as a complement:

I used MSYS2 and MinGW-w64 to build version 11devel 
from https://www.postgresql.org/ftp/snapshot/dev/
on my win 7 64 bit desktop.

What I remember is that I found good advices in

https://www.postgresql.org/docs/current/static/installation-platform-notes.html#INSTALLATION-NOTES-MINGW
and
https://wiki.postgresql.org/wiki/Building_With_MinGW

I don't remenber the details of the installation process, but it was working
with

Unzip and untar tarball then

cd postgresql-11devel
./configure --build=x86_64-w64-mingw32  --without-zlib
make
make install

regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: [RFC] Add an until-0 loop in psql

[Corey Huinker]

As of v11, DO blocks can do transactions. I think this will meet your needs.

A loop that starts at point X in the code and terminates at point Y has to
know how to jump back in the file (if there even is a file!) to point X and
re-interpret commands as it makes it's way back through the "file" toward
point Y again... a place it might not reach, or it might reach some other
loop termination first. \commands can be hidden inside psql variables,
files can be conditionally included based on \if statements, and those
files might have loop starters/terminators in them. And those commands *are*
 processed.

That, or you'd have to capture the code by somehow parsing ahead to the
next \until-0 (processing all inner loops as you go, and the files they
include, etc), but that means that variables that were expanded the first
time are *not* expanded on subsequent iterations, and that makes it hard to
set an exit-condition variable. It would also seriously alter what psql is
when inside that loop.

I once did a presentation on ways to (ab)use psql, and one thing I did was
recursion via include files. Adapting your loop as literally as possible,
it would look like this:

loop_file.sql:

BEGIN;
WITH deleted_rows AS (DELETE FROM big_table
  WHERE id in (SELECT id FROM big_table WHERE bad =
true LIMIT 1000)
  RETURNING 1)
SELECT (COUNT(*) > 0) as deleted_some_rows FROM deleted_rows
\gset
VACUUM big_table;
COMMIT;
\if :deleted_some_rows
   \include loop_file.sql
\endif


What you don't see here is that you're using your psql process's available
open file handles as a stack, and when you hit that limit psql will fail.
If you remove that limit, then you get a bit further before psql segfaults
on you. I think I got ~2700 files deep before that happened. Your stackage
may vary.

I'm not saying this is a good solution, quite the contrary. I think the
sane solution is right around the corner in Version 11.

Now if we just had a way of passing parameters into DO blocks...


On Tue, Apr 24, 2018 at 3:59 AM Pierre Ducroquet <
pierre.ducroq...@people-doc.com> wrote:

> Hi
>
> When running database migrations with .sql files on a live database, it's
> not
> uncommon to have to run a migration in a loop to prevent a big lock on a
> table.
> For instance if one want to delete some old datas from a big table one
> would
> write :
>
> DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad =
> true
> LIMIT 1000);
> VACUUM big_table;
>
> Right now, doing this is quite inefficient. We either have to write a
> script
> in another language, or run psql in a shell loop and wait for the
> migration to
> stop altering rows.
>
> The attached **proof of concept** patch (I insist, it's a 15 minutes hack
> sprint with no previous knowledge of psql code) implements an 'until-0'
> loop
> in psql.
> The previous migration could be simply written as :
>
> \until-0
> BEGIN;
> DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad =
> true
> LIMIT 1000);
> VACUUM big_table;
> COMMIT;
> \end-until
>
> And psql will execute it until there is no row affected in the inner
> queries.
>
> I am willing to write a proper patch for this (I hope the tell/seek is an
> acceptable implementation…), but I prefer having some feedback first.
>
> Thanks
>
>  Pierre

RE: GSoC 2018: Sorting Algorithm and Benchmarking

[Kefan Yang]

Thank you for the feedback! 

I think Slack would be great for our communication

From: Andrey Borodin
Sent: April 26, 2018 11:38 PM
To: Kefan Yang
Cc: PostgreSQL Hackers; Atri Sharma
Subject: Re: GSoC 2018: Sorting Algorithm and Benchmarking

Hi!

> 26 апр. 2018 г., в 0:12, Kefan Yang  написал(а):
>  
> My name is Kefan Yang. I am so excited that my proposal ‘Sorting Algorithm 
> and Benchmarking 2018’ has been accepted.
Welcome! I'm also glad you've chosen this project.

> I see on the Wiki page that you can mentor this proposal.
Yes, we with Atri Sharma will mentor this project.

> This is what I’ve done in these few days:
>   • I’ve carefully read through Submit a Patch and have a basic 
> understanding of the submission process.
Cool!
>   • I’ve set up the environment and ready to code. The sorting routine is 
> kind of separated from other modules so I don’t need much time to get 
> familiar with the code base. If things go smoothly, I can start coding ahead 
> of schedule
Great!
> Now I have some questions:
>   • If I understand it correctly, the sorting benchmark should be an 
> executable  under the src/bin/ folder just like pgbench?
Well, I think it is up to you how to make reproducible, precise and correct 
benchmarking :)
You can make exec (we will not have to merge it into PG, we need just results), 
or you can make Postgres extension (it is much easier) which will be executed 
from psql.
>   • Do you, or other community members, have any suggestions about the 
> final version of proposal?
As far as I know, proposal cannot be modified. Actual work can be slightly 
adjusted though.
>   • Are there any specific thing you expect me to do during this 
> community bonding period?

Let's establish communication. You can also build PostgreSQL from git and try 
pgbench (tool for benchmarking overall performance). Read something about 
TPC-(A,B,C) and YCSB, and may be even read something about sysbench.
I can think of following mediums of communications:
1. Slack channel
2. Telegram chat
3. Skype chat
4. E-mail

Atri, Kefar, how do you think, which of these will do best for us?


Best re

RE: GSoC 2018: Sorting Algorithm and Benchmarking

[Kefan Yang]

Hey,

This executable should read a config file (path to sorting routines, test data 
size etc.), perform tests on different sorting routines, and output the result.

Kefan

From: Robert Haas
Sent: April 26, 2018 11:53 AM
To: Kefan Yang
Cc: Andrey Borodin; PostgreSQL Hackers
Subject: Re: GSoC 2018: Sorting Algorithm and Benchmarking

On Wed, Apr 25, 2018 at 3:12 PM, Kefan Yang  wrote:
> If I understand it correctly, the sorting benchmark should be an executable
> under the src/bin/ folder just like pgbench?

What would this executable do, exactly?

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

Re: Toast issues with OldestXmin going backwards

[Robert Haas]

On Fri, Apr 27, 2018 at 11:35 AM, Andrew Gierth
 wrote:
>> "Robert" == Robert Haas  writes:
>  Robert> One idea that occurred to me is to somehow record -- I guess in
>  Robert> pg_class using non-transactional updates -- the last cutoff XID
>  Robert> used to vacuum any given table. Then we could just make a rule
>  Robert> that you can't vacuum the TOAST table with an XID that's newer
>  Robert> than the last one used for the main table. That would preserve
>  Robert> the property that you can vacuum the tables separately while
>  Robert> avoiding dangling pointers. But that's obviously not
>  Robert> back-patchable,
>
> The suggestion made previously (in a historical thread) was to use an
> entry in the reloptions field for this, at least in back branches. It
> would be necessary for vacuum to add the entry initially in a normal
> transactional update, after which it could be updated inplace.

Yeah, I suppose.  Sounds pretty rickety to me, though.  Maybe I'm just
a pessimist.

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

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Andres Freund]

On 2018-04-27 13:34:58 -0400, Tom Lane wrote:
> Yeah, there's a separate set of questions about what happens during
> pg_upgrade of a database containing the existing plpythonu extension.
> 
> You could imagine hacking the dump/reload case by defining plpythonu
> as an empty extension with a dependency on plpython2u (or, maybe
> someday in future, a dependency on plpython3u).  But that won't do
> the trick for binary upgrades; we might need special-case code in
> pg_dump/pg_upgrade to fix that.

One way to deal with that is have an upgrade script that does the
reassignment. Seems a mite cleaner than doing it in pg_dump. We could
just have a query updating pg_depend to reference the new extension
rather than the old.  That'd mean we'd be in the "old" situation before
somebody an extension update ran, but that seems hard to avoid?

Greetings,

Andres Freund

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Tom Lane]

Andres Freund  writes:
> Another alternative would be to have a 'plpython' extension that depends
> on plpython2. That'd require users to specify CASCADE when creating it,
> but that actually seems like it could be a useful hint...  I think it's
> probably not worth going that route though, because reassigning objects
> from one extension to another is more work than reasonable...

Yeah, there's a separate set of questions about what happens during
pg_upgrade of a database containing the existing plpythonu extension.

You could imagine hacking the dump/reload case by defining plpythonu
as an empty extension with a dependency on plpython2u (or, maybe
someday in future, a dependency on plpython3u).  But that won't do
the trick for binary upgrades; we might need special-case code in
pg_dump/pg_upgrade to fix that.

regards, tom lane

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Andres Freund]

On 2018-04-27 13:19:43 -0400, Tom Lane wrote:
> If we agree that something like this is the way to go, I wonder if we
> shouldn't squeeze it into v11.  If we wait till v12, that's not going
> to ship till late 2019, which is going to be getting uncomfortably
> close to when PEP 394 might change if we believe Fedora's schedule.

I think that'd be reasonable.

Another alternative would be to have a 'plpython' extension that depends
on plpython2. That'd require users to specify CASCADE when creating it,
but that actually seems like it could be a useful hint...  I think it's
probably not worth going that route though, because reassigning objects
from one extension to another is more work than reasonable...

Greetings,

Andres Freund

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Tom Lane]

Pavel Raiskup  writes:
> On Friday, April 27, 2018 5:51:57 PM CEST Tom Lane wrote:
>> We should evidently plan on making this stuff happen in v12, or we're
>> going to be behind the curve.

> Agreed, but I'm still not sure what would be the expected 2->3 migration
> path.  If that change happens in PG v12, people will migrate to that from
> v11 and there's the trouble.  Would it technically possible to upgrade
> such clusters with pg_upgrade when 'plpythonu' silently goes from
> 'plpython2.so' to 'plpython3.so' behind the scenes?
> Or is it expected that people will migrate semi-automatically/manually
> from plpython2 to plpython3 on v11 - before hitting pg_upgrade?

Good questions.  I think that the most useful thing we could do in the
near term is to cause functions declared with LANGUAGE 'plpythonu'
to be declared with LANGUAGE 'plpython2u' as-stored.  It'd then be on
the user's head to migrate at some point, changing that to LANGUAGE
'plpython3u' along with any required changes to the function body.
(This parallels the remarks in the Fedora migration plan about
discouraging packages from referencing "python" with no version number.)
The time frame for doing that would depend on the user's tolerance for
running out-of-support language code and/or their platform's tolerance for
shipping same.  I'd imagine it would be quite a long time before PG proper
would say we no longer support python2, but individual distros would stop
shipping those packages whenever their policy dictates.

If we go down that path, then 'plpythonu' isn't really a language anymore,
nor an extension --- it's just an alias that CREATE FUNCTION recognizes
and replaces.  In the past we've had needs of that sort and generally just
hacked it with some hard-wired code (see, eg, the overdue-for-retirement
code in ResolveOpClass()).  I'm tempted to propose that we handle this
case with something equally grotty, that is basically do this in
CreateFunction:

if (strcmp(language, "plpythonu") == 0)
language = DEFAULT_PYTHON_LANGUAGE;

where DEFAULT_PYTHON_LANGUAGE is defined as either "plpython2u" or
"plpython3u" according to a configure switch.  You could imagine
building some significantly more complicated infrastructure, but
I doubt it would be worth the effort; how many other use-cases are
we likely to have?

If we agree that something like this is the way to go, I wonder if we
shouldn't squeeze it into v11.  If we wait till v12, that's not going
to ship till late 2019, which is going to be getting uncomfortably
close to when PEP 394 might change if we believe Fedora's schedule.

regards, tom lane

Re: GCC 8 warnings

[Peter Eisentraut]

On 4/24/18 05:57, Devrim Gündüz wrote:
> While building stable releases and v11 on Fedora 28, I am seeing some 
> warnings.

Attached is a patch to fix these warnings in master.  These are very
similar to the class of warnings we fixed last time around for GCC 7.

GCC 8 is now frozen, so it would be a good time to move ahead with this.

> What is the project's policy about fixing those warnings in older branches? 

We did backpatch the GCC 7 changes.  We could do the same here, but it's
a pretty sizeable number of changes.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From ce6142824b745d83511c73efe88c0e2d0ad0522b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Mon, 9 Apr 2018 14:54:41 +
Subject: [PATCH] Fix new warnings from GCC 8

---
 contrib/pg_standby/pg_standby.c   |  4 +--
 src/backend/access/transam/xlog.c |  8 ++---
 src/backend/commands/extension.c  | 32 ++-
 src/backend/postmaster/postmaster.c   |  9 --
 src/backend/storage/file/fd.c |  4 +--
 src/backend/storage/file/reinit.c |  8 ++---
 src/backend/storage/file/sharedfileset.c  | 32 +--
 src/backend/tsearch/ts_utils.c|  7 +---
 src/backend/utils/misc/guc.c  |  2 +-
 src/backend/utils/misc/tzparser.c |  2 +-
 src/backend/utils/time/snapmgr.c  |  2 +-
 src/bin/initdb/findtimezone.c |  2 +-
 src/bin/initdb/initdb.c   |  4 +--
 src/bin/pg_basebackup/pg_basebackup.c |  2 +-
 src/bin/pg_basebackup/receivelog.c| 18 +++
 src/bin/pg_dump/pg_backup_directory.c |  4 +--
 .../pg_verify_checksums/pg_verify_checksums.c |  2 +-
 src/bin/pg_waldump/compat.c   |  4 +--
 src/bin/pgbench/pgbench.c |  6 ++--
 src/bin/psql/startup.c|  6 ++--
 src/interfaces/ecpg/preproc/ecpg.c|  4 +--
 src/interfaces/libpq/fe-connect.c |  8 ++---
 src/interfaces/libpq/fe-secure-openssl.c  |  2 +-
 src/test/regress/pg_regress.c | 14 
 src/timezone/pgtz.c   |  2 +-
 25 files changed, 89 insertions(+), 99 deletions(-)

diff --git a/contrib/pg_standby/pg_standby.c b/contrib/pg_standby/pg_standby.c
index cb785971a9..fcdf4e6a3f 100644
--- a/contrib/pg_standby/pg_standby.c
+++ b/contrib/pg_standby/pg_standby.c
@@ -60,7 +60,7 @@ char *nextWALFileName;/* the file we need to get from 
archive */
 char  *restartWALFileName; /* the file from which we can restart restore */
 char  *priorWALFileName;   /* the file we need to get from archive */
 char   WALFilePath[MAXPGPATH * 2]; /* the file path including archive 
*/
-char   restoreCommand[MAXPGPATH];  /* run this to restore */
+char  *restoreCommand; /* run this to restore */
 char   exclusiveCleanupFileName[MAXFNAMELEN];  /* the file we need to 
get

 * from archive */
 
@@ -98,7 +98,7 @@ int   restoreCommandType;
 intnextWALFileType;
 
 #define SET_RESTORE_COMMAND(cmd, arg1, arg2) \
-   snprintf(restoreCommand, MAXPGPATH, cmd " \"%s\" \"%s\"", arg1, arg2)
+   restoreCommand = psprintf(cmd " \"%s\" \"%s\"", arg1, arg2)
 
 struct stat stat_buf;
 
diff --git a/src/backend/access/transam/xlog.c 
b/src/backend/access/transam/xlog.c
index c0923d97f2..d29459e8d7 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -7702,12 +7702,12 @@ StartupXLOG(void)
if (!XLogArchiveIsReadyOrDone(origfname))
{
charorigpath[MAXPGPATH];
-   charpartialfname[MAXFNAMELEN];
-   charpartialpath[MAXPGPATH];
+   charpartialfname[MAXFNAMELEN + 8];
+   charpartialpath[MAXPGPATH + 8];
 
XLogFilePath(origpath, EndOfLogTLI, 
endLogSegNo, wal_segment_size);
-   snprintf(partialfname, MAXFNAMELEN, 
"%s.partial", origfname);
-   snprintf(partialpath, MAXPGPATH, "%s.partial", 
origpath);
+   snprintf(partialfname, sizeof(partialfname), 
"%s.partial", origfname);
+   snprintf(partialpath, sizeof(partialpath), 
"%s.partial", origpath);
 
/*
 * Make sure there's no .done or .ready file 
for the .partial
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 

Re: Built-in connection pooling

[Konstantin Knizhnik]

On 27.04.2018 18:33, Merlin Moncure wrote:

On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik
 wrote:

On 27.04.2018 16:49, Merlin Moncure wrote:

*) How are you pinning client connections to an application managed
transaction? (IMNSHO, this feature is useless without being able to do
that)

Sorry, I do not completely understand the question.
Rescheduling is now done at transaction level - it means that backand can
not be switched to other session until completing current transaction.
The main argument  for transaction level pooling is that it allows not worry
about heavy weight locks, which are associated with procarray entries.

I'm confused here...could be language issues or terminology (I'll look
at your latest code).  Here is how I understand things:
Backend=instance of postgres binary
Session=application state within postgres binary (temp tables,
prepared statement etc)
Connection=Client side connection

Backend is a process, forked by postmaster.


AIUI (I could certainly be wrong), withing connection pooling, ratio
of backend/session is still 1:1.  The idea is that client connections
when they issue SQL to the server reserve a Backend/Session, use it
for the duration of a transaction, and release it when the transaction
resolves.  So many client connections share backends.  As with
pgbouncer, the concept of session in a traditional sense is not really
defined; session state management would be handled within the
application itself, or within data within tables, but not within
backend private memory.  Does that align with your thinking?

No. Number of sessions is equal to number of client connections.
So client is not reserving "Backend/Session" as it happen in pgbouncer.
One backend keeps multiple sessions. And for each session it maintains 
session context which included client's connection.
And it is backend's decision transaction of which client it is going to 
execute now.
This is why built-in pooler is able to provide session semantic without 
backend/session=1:1 requirement.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Pavel Raiskup]

On Friday, April 27, 2018 5:51:57 PM CEST Tom Lane wrote:
> Note that there's a specific timeline there: they expect PEP 394 to get
> changed not more than 2 years from now.  I am not sure if Red Hat has
> enough pull in the Python community to guarantee that will happen, but
> do you want to bet against it?

Well, also it depends what's meant by "get PEP 394 changed".  My guess is
that distros might well avoid providing /bin/python if that PEP is
a concern..  that's actually typical situation in Fedora even now; python2
package isn't installed by default.  And it can disappear entirely if
that's too expensive for maintenance.

To me, PEP 394 is mostly about python distributors - not about python
projects;  so IMO for PostgreSQL that PEP is a bit orthogonal thing.
For quite some time already, doing 'CREATE EXTENSION plpython' implies at
least some (maybe serious) migration headaches in not so distant future.

> > So that's all great.  There are some opportunities for prep work in the
> > meantime that have been mentioned.
> 
> We should evidently plan on making this stuff happen in v12, or we're
> going to be behind the curve.

Agreed, but I'm still not sure what would be the expected 2->3 migration
path.  If that change happens in PG v12, people will migrate to that from
v11 and there's the trouble.  Would it technically possible to upgrade
such clusters with pg_upgrade when 'plpythonu' silently goes from
'plpython2.so' to 'plpython3.so' behind the scenes?

Or is it expected that people will migrate semi-automatically/manually
from plpython2 to plpython3 on v11 - before hitting pg_upgrade?

Pavel

Re: [HACKERS] pgbench - allow to store select results into variables

[Fabien COELHO]

Hello Stephen,

Attached is v18, another basic rebase after some perl automatic 
reindentation.


--
Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index e4b37dd..28a1387 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -942,6 +942,51 @@ pgbench  options 
 d
   
 
   
+   
+
+ \cset [prefix] or
+ \gset [prefix]
+
+
+
+ 
+  These commands may be used to end SQL queries, replacing a semicolon.
+  \cset replaces an embedded semicolon 
(\;) within
+  a compound SQL command, and \gset replaces a final
+  (;) semicolon which ends the SQL command. 
+ 
+
+ 
+  When these commands are used, the preceding SQL query is expected to
+  return one row, the columns of which are stored into variables named 
after
+  column names, and prefixed with prefix if 
provided.
+ 
+
+ 
+  The following example puts the final account balance from the first query
+  into variable abalance, and fills variables
+  one, two and
+  p_three with integers from a compound query.
+
+UPDATE pgbench_accounts
+  SET abalance = abalance + :delta
+  WHERE aid = :aid
+  RETURNING abalance \gset
+-- compound of two queries
+SELECT 1 AS one, 2 AS two \cset
+SELECT 3 AS three \gset p_
+
+ 
+
+ 
+  
+\cset and \gset commands do not 
work when
+empty SQL queries appear within a compound SQL command.
+  
+ 
+
+   
+

 \if expression
 \elif expression
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 78b8f17..7bf4331 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -437,12 +437,15 @@ static const char *QUERYMODE[] = {"simple", "extended", 
"prepared"};
 
 typedef struct
 {
-   char   *line;   /* text of command line */
+   char   *first_line; /* first line for short display */
+   char   *lines;  /* full multi-line text of 
command */
int command_num;/* unique index of this Command 
struct */
int type;   /* command type 
(SQL_COMMAND or META_COMMAND) */
MetaCommand meta;   /* meta command identifier, or 
META_NONE */
int argc;   /* number of command 
words */
char   *argv[MAX_ARGS]; /* command word list */
+   int compound;   /* last compound command 
(number of \;) */
+   char  **gset;   /* per-compound command prefix */
PgBenchExpr *expr;  /* parsed expression, if needed 
*/
SimpleStats stats;  /* time spent in this command */
 } Command;
@@ -1595,6 +1598,107 @@ valueTruth(PgBenchValue *pval)
}
 }
 
+/* read all responses from backend, storing into variable or discarding */
+static bool
+read_response(CState *st, char **gset)
+{
+   PGresult   *res;
+   int compound = 0;
+
+   while ((res = PQgetResult(st->con)) != NULL)
+   {
+   switch (PQresultStatus(res))
+   {
+   case PGRES_COMMAND_OK: /* non-SELECT commands */
+   case PGRES_EMPTY_QUERY: /* may be used for testing 
no-op overhead */
+   if (gset[compound] != NULL)
+   {
+   fprintf(stderr,
+   "client %d file %d 
command %d compound %d: "
+   "\\gset/cset expects a 
row\n",
+   st->id, st->use_file, 
st->command, compound);
+   st->ecnt++;
+   return false;
+   }
+   break; /* OK */
+
+   case PGRES_TUPLES_OK:
+   if (gset[compound] != NULL)
+   {
+   /* store result into variables if 
required */
+   int ntuples = PQntuples(res),
+   nfields = PQnfields(res),
+   f;
+
+   if (ntuples != 1)
+   {
+   fprintf(stderr,
+   "client %d file 
%d command %d compound %d: "
+   "expecting one 
row, got %d\n",
+   st->id, 
st->use_file, st->command, compound, ntuples);
+   st->ecnt++;
+ 

Re: perltidy tweaks

[Peter Eisentraut]

On 4/26/18 14:10, Andrew Dunstan wrote:
>>> - Use option --nooutdent-long-quotes.  This is similar to a change we
>>> made for pgindent in the C code a while ago.  See patch for the results.
>> No strong opinion as to whether to do that or not, but if we do, I wonder
>> if we shouldn't also select --nooutdent-long-comments.  I noticed a few
>> places where it was doing that (ie moving comments left if they went past
>> the right margin) and didn't particularly like it.
> 
> 
> Yeah, not a fan of the outdenting, so +1 to both of these.

committed all three

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

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Tom Lane]

Peter Eisentraut  writes:
> On 4/26/18 16:39, Tom Lane wrote:
>> and I gather from Pavel's inquiry that the day
>> is in sight when Fedora will do that.

> According to the plan I found at
> , they
> will also wait for an update to PEP 394 ("and we will drive that update
> if needed").

Note that there's a specific timeline there: they expect PEP 394 to get
changed not more than 2 years from now.  I am not sure if Red Hat has
enough pull in the Python community to guarantee that will happen, but
do you want to bet against it?

> So that's all great.  There are some opportunities for prep work in the
> meantime that have been mentioned.

We should evidently plan on making this stuff happen in v12, or we're
going to be behind the curve.

regards, tom lane

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Tom Lane]

Robert Haas  writes:
> Incidentally, it recently came to my notice that we're still issuing
> hints that say:
> Use CREATE LANGUAGE to load the language into the database.
> That is now, and has been for some time, the wrong advice.  We should
> really be recommending CREATE EXTENSION.

Right.  It's too late in the v11 cycle to consider any of this other work
right now, but I think it'd be reasonable to adjust those message(s) for
v11 ... any objections?  I see two places in functioncmds.c that ought
to change, as well as a few examples in the SGML docs.

regards, tom lane

Re: Add read-only param to set_config(...) / SET that effects (at least) customized runtime options

[Chapman Flack]

On 04/20/2018 04:18 PM, Chapman Flack wrote:
> On 04/20/2018 04:03 PM, Andrew Gierth wrote:

>> Assign hooks aren't allowed to throw errors under any circumstance
>> because they're called during transaction abort, and throwing an error
>> during abort is obviously not tolerable.
> 
> Ah yes, now you remind me, that was why I had put my extension idea
> on the table at the time.
> 
> It seems as if, for any of these ideas (cookie-protection of GUCs
> or just SET READONLY for GUCs), at least some small patch to core
> will be prerequisite, to allow either a flag or a reset check hook
> able to make RESET skip protected GUCs.

I suppose an extension could protect a GUC of its own from reset
without the cookie, despite the lack of a reset hook in core, by
simply maintaining its own copy of the original reset value, and
stuffing the protected value into the variable's reset_val for
the duration, no?

Wouldn't be usable to protect core GUCs, but could provide an API
for other extensions to make their GUCs protectable, by passing the
config_* struct pointer to a registration function.

-Chap

Re: documentation is now XML

[Tom Lane]

Bruce Momjian  writes:
> On Fri, Apr 27, 2018 at 11:00:36AM -0400, Peter Eisentraut wrote:
>> 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?  I thought people building current Postgres would already
> have the XML toolchain built to build older release documentation.

Yeah, in principle, anyone who's able to build the v10 branch's docs
should be ready for such a change.  We had discussed polling the
pgsql-packagers list to see whether anyone would have a problem with
changing the docs build toolset for the pre-v10 branches, but I don't
think we actually asked them.

regards, tom lane

Re: Toast issues with OldestXmin going backwards

[Andrew Gierth]

> "Robert" == Robert Haas  writes:

 Robert> One idea that occurred to me is to somehow record -- I guess in
 Robert> pg_class using non-transactional updates -- the last cutoff XID
 Robert> used to vacuum any given table. Then we could just make a rule
 Robert> that you can't vacuum the TOAST table with an XID that's newer
 Robert> than the last one used for the main table. That would preserve
 Robert> the property that you can vacuum the tables separately while
 Robert> avoiding dangling pointers. But that's obviously not
 Robert> back-patchable,

The suggestion made previously (in a historical thread) was to use an
entry in the reloptions field for this, at least in back branches. It
would be necessary for vacuum to add the entry initially in a normal
transactional update, after which it could be updated inplace.

-- 
Andrew (irc:RhodiumToad)

Re: Built-in connection pooling

[Merlin Moncure]

On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik
 wrote:
> On 27.04.2018 16:49, Merlin Moncure wrote:
>> *) How are you pinning client connections to an application managed
>> transaction? (IMNSHO, this feature is useless without being able to do
>> that)
>
> Sorry, I do not completely understand the question.
> Rescheduling is now done at transaction level - it means that backand can
> not be switched to other session until completing current transaction.
> The main argument  for transaction level pooling is that it allows not worry
> about heavy weight locks, which are associated with procarray entries.

I'm confused here...could be language issues or terminology (I'll look
at your latest code).  Here is how I understand things:
Backend=instance of postgres binary
Session=application state within postgres binary (temp tables,
prepared statement etc)
Connection=Client side connection

AIUI (I could certainly be wrong), withing connection pooling, ratio
of backend/session is still 1:1.  The idea is that client connections
when they issue SQL to the server reserve a Backend/Session, use it
for the duration of a transaction, and release it when the transaction
resolves.  So many client connections share backends.  As with
pgbouncer, the concept of session in a traditional sense is not really
defined; session state management would be handled within the
application itself, or within data within tables, but not within
backend private memory.  Does that align with your thinking?

merlin

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

[Tom Lane]

"insaf.k"  writes:
> I am trying to build PG from source, in MS Windows using MSYS2 and MinGW-w64. 
> I've tried to build PG 10.0 as wells as 10.3.
> I've done configuring like this 
>   ./configure --prefix="/d/pg10/"
> And when I do "make" or "make world", I'm getting compilation error. I've 
> attached complete error report at the end of the mail.

I don't know anything about mingw, but from the first error message you
showed, I'd venture that configure failed to fill in pg_config_ext.h
correctly.  It should have put in a line like

#define PG_INT64_TYPE long long int

and evidently it hasn't.  This suggests that there's something wrong
with one of the text-processing tools it uses, such as "sed".  You
might look into config.log and see if there are any relevant-looking
error messages (probably down near the end).  Also, try comparing
your configure text output and config.log to those of one of our
buildfarm machines that's using mingw successfully, such as

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jacana=2018-04-27%2012%3A19%3A49

(the "config" and "configure" links on that page are what to compare to).

Hm ... I notice that jacana's been set up so that it explicitly
gives configure a --host setting instead of letting configure work
that out.  No idea if that was really necessary or not.

regards, tom lane

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Peter Eisentraut]

On 4/26/18 16:39, Tom Lane wrote:
>> I don't think we should do that unless there is an update to PEP 394.
> 
> PEP 394 points out that some distros (at least Arch) have already switched
> "python" to mean "python3",

My recollection is that PEP 394 was written because Arch jumped the gun
and the PEP is saying "do this instead".

> and I gather from Pavel's inquiry that the day
> is in sight when Fedora will do that.

According to the plan I found at
, they
will also wait for an update to PEP 394 ("and we will drive that update
if needed").

So that's all great.  There are some opportunities for prep work in the
meantime that have been mentioned.

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

Re: Built-in connection pooling

[Konstantin Knizhnik]

On 27.04.2018 16:49, Merlin Moncure wrote:

On Thu, Apr 26, 2018 at 6:04 AM, Konstantin Knizhnik
 wrote:

On 25.04.2018 20:02, Merlin Moncure wrote:

Yep.  The main workaround today is to disable them.  Having said that,
it's not that difficult to imagine hooking prepared statement creation
to a backend starting up (feature: run X,Y,Z SQL before running user
queries).

Sorry, I do not completely understand your idea.
Yes, it is somehow possible to simulate session semantic by prepending all
session specific commands (mostly setting GUCs) to each SQL statements.
But it doesn't work for prepared statements: the idea of prepared statements
is that compilation of statement should be done only once.

The idea is that you have arbitrary SQL that runs when after the
backend (postgres binary) is forked from postmaster.  This would be an
ideal place to introduce prepared statements in a way that is pooling
compatible; you still couldn't PREPARE from the application but you'd
be free to call already prepared statements (via SQL level EXECUTE or
libpq PQexecPrepared()).  Of course, if somebody throws a DEALLOCATE
or DISCARD ALL, or issues a problematic DROP x CASCADE, you'd be in
trouble but that'a not a big deal IMO because you can control for
those things in the application.
As far as I know in this way prepared statements can be now handled by 
pgbounce in transaction/statement pooling mode.
But from my point of view, in most cases this approach is practically 
unusable.
It is very hard to predict from the very beginning all statements 
applications will want to execute and prepare then at backend start.



Database performance is mostly limited by disk, so optimal number of
backends may be different from number of cores.
But certainly possibility to launch "optimal" number of backends is one of
the advantages of builtin session pooling.

Sure, but some workloads are cpu limited (all- or mostly- read with
data < memory, or very complex queries on smaller datasets).   So we
would measure configure based one expectations exactly as is done
today with pgbouncer.   This is a major feature of pgbouncer: being
able to _reduce_ the number of session states relative to the number
of connections is an important feature; it isolates your database from
various unpleasant failure modes such as runaway memory consumption.

Anyways, I'm looking at your patch.  I see you've separated the client
connection count ('sessions') from the server backend instances
('backends') in the GUC.  Questions:
*) Should non pooled connections be supported simultaneously with
pooled connections?
*) Should there be multiple pools with independent configurations (yes, please)?



Right now my prototype supports two modes:
1. All connections are polled.
2. There are several session pools, each bounded to its own port. 
Connections to the main Postgres port are normal (dedicated).
Connections to one of session pools port's are redirected to one of the 
workers of this page pool.


Please notice, that the last version of connection pooler is in 
https://github.com/postgrespro/postgresql.builtin_pool.git repository.

*) How are you pinning client connections to an application managed
transaction? (IMNSHO, this feature is useless without being able to do
that)

Sorry, I do not completely understand the question.
Rescheduling is now done at transaction level - it means that backand 
can not be switched to other session until completing current transaction.
The main argument  for transaction level pooling is that it allows not 
worry about heavy weight locks, which are associated with procarray entries.




FYI, it's pretty clear you've got a long road building consensus and
hammering out a reasonable patch through the community here.  Don't
get discouraged -- there is value here, but it's going to take some
work.

Thank you.
I am absolutely sure that a lot of additional work has to be done before 
this prototype may become usable.



merlin


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: documentation is now XML

[Bruce Momjian]

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?  I thought people building current Postgres would already
have the XML toolchain built to build older release documentation.

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

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: Toast issues with OldestXmin going backwards

[Robert Haas]

On Thu, Apr 26, 2018 at 9:03 PM, Andrew Gierth
 wrote:
>(Or do we need to track it across restarts? maybe we do, to deal with
>replication slaves without slots, or changes in parameters)

Yeah, I'm worried that it might need to be persistent across restarts.

One idea that occurred to me is to somehow record -- I guess in
pg_class using non-transactional updates -- the last cutoff XID used
to vacuum any given table.  Then we could just make a rule that you
can't vacuum the TOAST table with an XID that's newer than the last
one used for the main table.  That would preserve the property that
you can vacuum the tables separately while avoiding dangling pointers.
But that's obviously not back-patchable, and it sounds finicky to get
right.  It's really too bad that heap tables don't include a metapage
where we could store details like this...

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

Re: documentation is now XML

[Peter Eisentraut]

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.

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

Re: Built-in connection pooling

[Robert Haas]

On Wed, Apr 25, 2018 at 10:09 PM, Michael Paquier  wrote:
> On Wed, Apr 25, 2018 at 03:42:31PM -0400, Robert Haas wrote:
>> The difficulty of finding them all is really the problem.  If we had a
>> reliable way to list everything that needs to be moved into session
>> state, then we could try to come up with a design to do that.
>> Otherwise, we're just swatting issues one by one and I bet we're
>> missing quite a few.
>
> Hm?  We already know about the reset value of a parameter in
> pg_settings, which points out to the value which would be used if reset
> in a session, even after ebeing reloaded.  If you compare it with the
> actual setting value, wouldn't that be enough to know which parameters
> have been changed at session-level by an application once connecting?
> So you can pull out a list using such comparisons.  The context a
> parameter is associated to can also help.

Uh, there's a lot of session backend state other than GUCs.  If the
only thing that we needed to worry about were GUCs, this problem would
have been solved years ago.

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

Re: obsoleting plpython2u and defaulting plpythonu to plpython3u

[Robert Haas]

On Thu, Apr 26, 2018 at 4:39 PM, Tom Lane  wrote:
> I'm not very sure how many moving parts would be involved in making that
> happen.  One thing we should probably do first is retire the pg_pltemplate
> system catalog in favor of making PL extension scripts self-contained,
> as you'd mentioned recently would be a good project to finish off.

Incidentally, it recently came to my notice that we're still issuing
hints that say:

Use CREATE LANGUAGE to load the language into the database.

That is now, and has been for some time, the wrong advice.  We should
really be recommending CREATE EXTENSION.

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

Re: Built-in connection pooling

[Merlin Moncure]

On Thu, Apr 26, 2018 at 6:04 AM, Konstantin Knizhnik
 wrote:
> On 25.04.2018 20:02, Merlin Moncure wrote:
>> Yep.  The main workaround today is to disable them.  Having said that,
>> it's not that difficult to imagine hooking prepared statement creation
>> to a backend starting up (feature: run X,Y,Z SQL before running user
>> queries).
>
> Sorry, I do not completely understand your idea.
> Yes, it is somehow possible to simulate session semantic by prepending all
> session specific commands (mostly setting GUCs) to each SQL statements.
> But it doesn't work for prepared statements: the idea of prepared statements
> is that compilation of statement should be done only once.

The idea is that you have arbitrary SQL that runs when after the
backend (postgres binary) is forked from postmaster.  This would be an
ideal place to introduce prepared statements in a way that is pooling
compatible; you still couldn't PREPARE from the application but you'd
be free to call already prepared statements (via SQL level EXECUTE or
libpq PQexecPrepared()).  Of course, if somebody throws a DEALLOCATE
or DISCARD ALL, or issues a problematic DROP x CASCADE, you'd be in
trouble but that'a not a big deal IMO because you can control for
those things in the application.

> Database performance is mostly limited by disk, so optimal number of
> backends may be different from number of cores.
> But certainly possibility to launch "optimal" number of backends is one of
> the advantages of builtin session pooling.

Sure, but some workloads are cpu limited (all- or mostly- read with
data < memory, or very complex queries on smaller datasets).   So we
would measure configure based one expectations exactly as is done
today with pgbouncer.   This is a major feature of pgbouncer: being
able to _reduce_ the number of session states relative to the number
of connections is an important feature; it isolates your database from
various unpleasant failure modes such as runaway memory consumption.

Anyways, I'm looking at your patch.  I see you've separated the client
connection count ('sessions') from the server backend instances
('backends') in the GUC.  Questions:
*) Should non pooled connections be supported simultaneously with
pooled connections?
*) Should there be multiple pools with independent configurations (yes, please)?
*) How are you pinning client connections to an application managed
transaction? (IMNSHO, this feature is useless without being able to do
that)

FYI, it's pretty clear you've got a long road building consensus and
hammering out a reasonable patch through the community here.  Don't
get discouraged -- there is value here, but it's going to take some
work.

merlin

Re: minor fix for acquire_inherited_sample_rows

[Ashutosh Bapat]

On Thu, Apr 26, 2018 at 7:08 PM, Amit Langote  wrote:
> On Thu, Apr 26, 2018 at 9:54 PM, Ashutosh Bapat
>  wrote:
>> On Thu, Apr 26, 2018 at 1:08 AM, Robert Haas  wrote:
>>> +1.  I think we're really abusing equalTupleDescs() for purposes for
>>> which it was not invented.  Instead of changing it, let's invent a new
>>> function that tests for the thing partitioning cares about (same
>>> ordering of the same columns with the same type information) and call
>>> it logicallyEqualTupleDescs() or something like that.
>>
>> Why don't we just rely on the output of convert_tuples_by_name(),
>> which it seems is always called right now? What's advantage of adding
>> another tuple descriptor comparison?
>
> The patch I mentioned in my email above does more or less that (what
> you're saying we should do).  In fact it even modifies
> convert_tuple_by_name and convert_tuple_by_name_map to remove some
> redundant computation.  See that patch here if you're interested:
>
> https://www.postgresql.org/message-id/825031be-942c-8c24-6163-13c27f217a3d%40lab.ntt.co.jp

I spent some time looking at the patch. The patch clubs all kinds of
refactoring together, making review a bit difficult. I think, it would
be better to split the patch into multiple, each addressing one set of
changes, it might become easier to review.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: Wrong plan with extra parallel workers

[Amit Kapila]

On Fri, Apr 27, 2018 at 2:48 PM, Guilherme Pereira  wrote:
> Hi,
>
> Having a strange situation, where adding extra parallel workers
> (max_parallel_workers_per_gather), the planner chooses a different plan,

I think I can see what is going on here.  The planner chooses a
different plan because the cost of that plan
(cost=1001.10..31949141.56) is cheap as compared to the original plan
(cost=31676816.72..32928717.16)

> with nested loops, which makes the query twice as slow.

It is slow because it needs to transmit many rows (rows=23845842) from
workers to master backend.

> Strangely with the
> COUNT_DISTINCT implementation from Tomas Vondra
> (https://github.com/tvondra/count_distinct) it scales nicely (almost
> linearly) with the workers.
>

The main difference is that count_distinct is a parallel_safe
aggregate function which can be allowed to push down to workers which
in turn lead to very few rows (rows=9) being transferred from workers
to master backend.

I think ideally it should not have picked the plan which leads to
transmitting so many rows, but the cost turns out to be lesser.

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

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

[insaf.k]

Hello,



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



I've done configuring like this 

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

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



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

Another error is "redifinition of fseeko".

Another error is "FLEXIBLE_ARRAY_MEMBER" is undefined.





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


My $PATH var is

$ echo $PATH 
/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
 




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

Re: Wrong plan with extra parallel workers

[Guilherme Pereira]

Forgot to mention that I'm using the development branch of Postgres 11.
Also as requested, sending the plans via the https://explain.depesz.com
app.

set max_parallel_workers_per_gather = 2;

EXPLAIN ANALYZE
SELECT
"f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
COUNT( DISTINCT "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
"c_fb839a9bd6f2015f"
FROM
"f_zendesktags_aakrjpgq72ad93i"
INNER JOIN
"f_zendesktickets_aaeljtllr5at3el"
ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
"f_zendesktickets_aaeljtllr5at3el"."id"
GROUP BY
1 ;

Plan here :
https://explain.depesz.com/s/Vyb5

-

set max_parallel_workers_per_gather = 6;

Same SQL as above

https://explain.depesz.com/s/9tkK

--

Strangely with the count_distinct implementation from Tomas Vondra from
https://github.com/tvondra/count_distinct, it doesn' happen, and it scales
nicely with the extra workers.
set max_parallel_workers_per_gather = 6;

EXPLAIN ANALYZE
SELECT
"f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
COUNT_DISTINCT ( "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
"c_fb839a9bd6f2015f"
FROM
"f_zendesktags_aakrjpgq72ad93i"
INNER JOIN
"f_zendesktickets_aaeljtllr5at3el"
ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
"f_zendesktickets_aaeljtllr5at3el"."id"
GROUP BY
1 ;

https://explain.depesz.com/s/syKw

On Fri, 27 Apr 2018 at 11:18, Guilherme Pereira  wrote:

> Hi,
>
> Having a strange situation, where adding extra parallel workers
> (max_parallel_workers_per_gather), the planner chooses a different plan,
> with nested loops, which makes the query twice as slow. Strangely with the
> COUNT_DISTINCT implementation from Tomas Vondra (
> https://github.com/tvondra/count_distinct) it scales nicely (almost
> linearly) with the workers.
>
> Can provide the DDL's or more info if needed. Any ideas why this happens?
>
> set max_parallel_workers_per_gather = 2;
>
> EXPLAIN ANALYZE
> SELECT
> "f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
> COUNT( DISTINCT "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
> "c_fb839a9bd6f2015f"
> FROM
> "f_zendesktags_aakrjpgq72ad93i"
> INNER JOIN
> "f_zendesktickets_aaeljtllr5at3el"
> ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
> "f_zendesktickets_aaeljtllr5at3el"."id"
> GROUP BY
> 1 ;
>
>   GroupAggregate  (cost=31676816.72..32928717.16 rows=8 width=12) (actual
> time=124072.467..210958.329 rows=9 loops=1)
>Group Key: f_zendesktickets_aaeljtllr5at3el.via_id
>->  Sort  (cost=31676816.72..32094116.84 rows=166920048 width=8)
> (actual time=124072.431..148808.161 rows=166920891 loops=1)
>  Sort Key: f_zendesktickets_aaeljtllr5at3el.via_id
>  Sort Method: external merge  Disk: 2939944kB
>  ->  Hash Join  (cost=1919106.06..6597948.02 rows=166920048
> width=8) (actual time=13063.794..85782.027 rows=166920891 loops=1)
>Hash Cond: (f_zendesktags_aakrjpgq72ad93i.ticket_id_id =
> f_zendesktickets_aaeljtllr5at3el.id)
>->  Seq Scan on f_zendesktags_aakrjpgq72ad93i
> (cost=0.00..2571476.48 rows=166920048 width=4) (actual
> time=0.016..20886.829 rows=166920891 loo
> ps=1)
>->  Hash  (cost=1774033.25..1774033.25 rows=11605825
> width=8) (actual time=12981.920..12981.920 rows=11605822 loops=1)
>  Buckets: 16777216  Batches: 1  Memory Usage: 584425kB
>  ->  Seq Scan on f_zendesktickets_aaeljtllr5at3el
> (cost=0.00..1774033.25 rows=11605825 width=8) (actual time=0.045..9262.223
> rows=116058
> 22 loops=1)
>  Planning Time: 1.426 ms
>  Execution Time: 211441.893 ms
> (13 rows)
>
> -
>
> set max_parallel_workers_per_gather = 6;
>
> EXPLAIN ANALYZE
> SELECT
> "f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
> COUNT( DISTINCT "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
> "c_fb839a9bd6f2015f"
> FROM
> "f_zendesktags_aakrjpgq72ad93i"
> INNER JOIN
> "f_zendesktickets_aaeljtllr5at3el"
> ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
> "f_zendesktickets_aaeljtllr5at3el"."id"
> GROUP BY
> 1 ;
>
>  GroupAggregate  (cost=1001.10..31949141.56 rows=8 width=12) (actual
> time=9.125..399880.451 rows=9 loops=1)
>Group Key: f_zendesktickets_aaeljtllr5at3el.via_id
>->  Gather Merge  (cost=1001.10..31114541.24 rows=166920048 width=8)
> (actual time=9.037..322148.967 rows=166920891 loops=1)
>  Workers Planned: 6
>  Workers Launched: 6
>  ->  Nested Loop  (cost=1.01..10826616.89 rows=27820008 width=8)
> (actual time=0.150..30867.494 rows=23845842 loops=7)
>->  Parallel Index Scan using
> f_zendesktickets_aaeljtllr5at3el_via_id_idx on
> f_zendesktickets_aaeljtllr5at3el  (cost=0.43..5838596.19 rows=193
> 4304 width=8) (actual time=0.066..3750.816 rows=1657975 loops=7)
>->  Index Only Scan using
> 

Fwd: Failed rpm package signature checks with reposync

[Bruno Lavoie]

Did not get response from general, so trying here. Thanks

-- Forwarded message -
From: Bruno Lavoie 
Date: mer. 25 avr. 2018 11:22 AM
Subject: Failed rpm package signature checks with reposync
To: 


Hello,

Don't know if I should post it to hackers list, by I'll try here first.

For many reasons, we're currently deploying a mirror for postgresql rpm
packages. And when we run reposync (yum mirroring tool) with --gpgcheck
switch some packages are removed due to failed signature check.

Command and output:
# reposync --repoid=pgdg10 --gpgcheck
--download_path=/var/www/html/centos7/repos/postgresql/

Package mysql_fdw_10-2.3.0-3.rhel7.x86_64.rpm is not signed 0% [
 ]  0.0 B/s |0 B  --:--:-- ETA
(1/4): mysql_fdw_10-2.3.0-3.rhel7.x86_64.rpm
|  30 kB  00:00:00
(2/4): mysql_fdw_10-debuginfo-2.3.0-3.rhel7.x86_64.rpm
| 117 kB  00:00:00
(3/4): osm2pgrouting_10-2.3.3-1.rhel7.x86_64.rpm
| 134 kB  00:00:00
(4/4): osm2pgrouting_10-debuginfo-2.3.3-1.rhel7.x86_64.rpm
|  17 kB  00:00:00
Removing mysql_fdw_10-2.3.0-3.rhel7.x86_64.rpm due to failed signature
check.
Removing mysql_fdw_10-debuginfo-2.3.0-3.rhel7.x86_64.rpm due to failed
signature check.
Removing osm2pgrouting_10-2.3.3-1.rhel7.x86_64.rpm due to failed signature
check.
Removing osm2pgrouting_10-debuginfo-2.3.3-1.rhel7.x86_64.rpm due to failed
signature check.

Here is the used .repo file:
[pgdg10]
name=PostgreSQL 10 - RHEL 7 - x86_64
baseurl=
https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64
enabled=0
gpgcheck=1
gpgkey=https://download.postgresql.org/pub/repos/yum/RPM-GPG-KEY-PGDG-10


Any reasons for this?

Thanks
Bruno Lavoie

Range phrase operator in tsquery

[Aleksandr Parfenov]

Hello hackers,

Nowadays, phrase operator in Postgres FTS supports only exact match of
the distance between two words. It is sufficient for a search of
simple/exact phrases, but in some cases exact distance is unknown and we
want to words be close enough. E.g. it may help to search phrases with
additional words in the middle of the phrase ("long, narrow, plastic
brush" vs "long brush")

Proposed patch adds ability to use ranges in phrase operator for
mentioned cases. Few examples:

'term1 <4,10> term2'::tsquery -- Distance between term1 and term2 is
-- at least 4 and no greater than 10
'term1 <,10> term2'::tsquery  -- Distance between term1 and term2 is
-- no greater than 10
'term1 <4,> term2'::tsquery   -- Distance between term1 and term2 is
-- at least 4

In addition, negative distance is supported and means reverse order of
the words. For example:
'term1 <4,10> term2'::tsquery = 'term2 <-10,-4> term1'::tsquery
'term1 <,10> term2'::tsquery = 'term2 <-10,> term1'::tsquery
'term1 <4,> term2'::tsquery = 'term2 <,-4> term1'::tsquery

Negative distance support introduced to use it for AROUND operator
mentioned in websearch_to_tsquery[1]. In web search query language
AROUND(N) does a search for words within given distance N in
both forward and backward direction and it can be represented as <-N,N>
range phrase operator.

[1]
https://www.postgresql.org/message-id/flat/fe93ff7e9ad79196486ada79e...@postgrespro.ru

-- 
Aleksandr Parfenov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Companydiff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml
index 19f58511c8..2ccff5d16d 100644
--- a/doc/src/sgml/textsearch.sgml
+++ b/doc/src/sgml/textsearch.sgml
@@ -373,6 +373,38 @@ SELECT phraseto_tsquery('the cats ate the rats');
 can be used to require that two patterns match the same word.

 
+   
+Range version of the FOLLOWED BY operator having the form
+[N],[M],
+where N and M are integers
+representing for the minimum and maximum difference between the positions of the
+matching lexemes. N can be omitted as a short version of
+0,M. Likewise,
+M can be omitted to represent a maximum distance
+between the positions of the matching lexemes limited by maximum distance in
+tsquery. Negative distance is supported to show reverse order
+of the matching lexemes. Omitted borders are inverted in case of negative distance.
+   
+
+SELECT 'big 1,2 cat'::tsquery;
+  tsquery  
+---
+ 'big' 1,2 'cat'
+(1 row)
+
+SELECT 'big ,2 cat'::tsquery;
+  tsquery  
+---
+ 'big' 0,2 'cat'
+(1 row)
+
+SELECT 'big 1, cat'::tsquery;
+tsquery
+---
+ 'big' 1,16384 'cat'
+(1 row)
+
+

 Parentheses can be used to control nesting of the tsquery
 operators.  Without parentheses, | binds least tightly,
@@ -3921,7 +3953,7 @@ Parser: "pg_catalog.default"
 
 
  The match distance in a N
- (FOLLOWED BY) tsquery operator cannot be more than
+ (FOLLOWED BY) tsquery operator cannot be less than -16,384 and more than
  16,384
 
 
diff --git a/src/backend/tsearch/to_tsany.c b/src/backend/tsearch/to_tsany.c
index 2474b723b4..6fc19e9c9b 100644
--- a/src/backend/tsearch/to_tsany.c
+++ b/src/backend/tsearch/to_tsany.c
@@ -487,12 +487,14 @@ pushval_morph(Datum opaque, TSQueryParserState state, char *strval, int lenval,
 cntvar = 0,
 cntpos = 0,
 cnt = 0;
+	OperatorData operator_data;
 	MorphOpaque *data = (MorphOpaque *) DatumGetPointer(opaque);
 
 	prs.lenwords = 4;
 	prs.curwords = 0;
 	prs.pos = 0;
 	prs.words = (ParsedWord *) palloc(sizeof(ParsedWord) * prs.lenwords);
+	OPERATOR_DATA_INITIALIZE(operator_data, 0);
 
 	parsetext(data->cfg_id, , strval, lenval);
 
@@ -511,7 +513,10 @@ pushval_morph(Datum opaque, TSQueryParserState state, char *strval, int lenval,
 	/* put placeholders for each missing stop word */
 	pushStop(state);
 	if (cntpos)
-		pushOperator(state, data->qoperator, 1);
+	{
+		OPERATOR_DATA_INITIALIZE(operator_data, 1);
+		pushOperator(state, data->qoperator, operator_data);
+	}
 	cntpos++;
 	pos++;
 }
@@ -539,20 +544,21 @@ pushval_morph(Datum opaque, TSQueryParserState state, char *strval, int lenval,
 			  ((prs.words[count].flags & TSL_PREFIX) || prefix));
 	pfree(prs.words[count].word);
 	if (cnt)
-		pushOperator(state, OP_AND, 0);
+		pushOperator(state, OP_AND, operator_data);
 	cnt++;
 	count++;
 }
 
 if (cntvar)
-	pushOperator(state, OP_OR, 0);
+	pushOperator(state, OP_OR, operator_data);
 cntvar++;
 			}
 
 			if (cntpos)
 			{
 /* distance may be useful */
-pushOperator(state, data->qoperator, 1);
+OPERATOR_DATA_INITIALIZE(operator_data, 1);
+pushOperator(state, data->qoperator, operator_data);
 			}
 
 			cntpos++;
diff --git a/src/backend/utils/adt/tsquery.c b/src/backend/utils/adt/tsquery.c
index 

Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

[Ashutosh Bapat]

On Thu, Apr 19, 2018 at 11:38 AM, Kyotaro HORIGUCHI
 wrote:
>
>> /* No rows should be returned if no rows were updated. */
>> Assert(n_rows_returned == 0 || n_rows_updated > 0);
>
> The assertion is correct but I think that we shouldn't crash
> server by any kind of protocol error. I think ERROR is suitable.
>

That's a good idea. Done.

>> I have attached a set of patches
>> 0001 adds a test case showing the issue.
>> 0002 modified patch based on your idea of throwing an error
>> 0003 WIP patch with a partial fix for the issue as discussed upthread
>>
>> The expected output in 0001 is set to what it would when the problem
>> gets fixed. The expected output in 0002 is what it would be when we
>> commit only 0002 without a complete fix.
>> >
>> >
>> >> There are two ways to fix this
>> >> 1. Use WHERE CURRENT OF with cursors to update rows. This means that
>> >> we fetch only one row at a time and update it. This can slow down the
>> >> execution drastically.
>> >> 2. Along with ctid use tableoid as a qualifier i.e. WHERE clause of
>> >> UPDATE/DELETE statement has ctid = $1 AND tableoid = $2 as conditions.
>> >>
>> >> PFA patch along the lines of 2nd approach and along with the
>> >> testcases. The idea is to inject tableoid attribute to be fetched from
>> >> the foreign server similar to ctid and then add it to the DML
>> >> statement being constructed.
>> >>
>> >> It does fix the problem. But the patch as is interferes with the way
>> >> we handle tableoid currently. That can be seen from the regression
>> >> diffs that the patch causes.  RIght now, every tableoid reference gets
>> >> converted into the tableoid of the foreign table (and not the tableoid
>> >> of the foreign table). Somehow we need to differentiate between the
>> >> tableoid injected for DML and tableoid references added by the user in
>> >> the original query and then use tableoid on the foreign server for the
>> >> first and local foreign table's oid for the second. Right now, I don't
>> >> see a simple way to do that.
>> >
>> > We cannot add no non-system (junk) columns not defined in foreign
>> > table columns.
>>
>> Why? That's a probable way of fixing this problem.
>
> In other words, tuples returned from ForeignNext
> (postgresIterateForeignScan) on a foreign (base) relation cannot
> contain a non-system column which is not a part of the relation,
> since its tuple descriptor doesn't know of and does error out it.
> The current 0003 stores remote tableoid in tuples' existing
> tableOid field (not a column data), which is not proper since
> remote tableoid is bogus for the local server. I might missing
> something here, though. If we can somehow attach an blob at the
> end of t_data and it is finally passed to
> ExecForeignUpdate/Delete, the problem would be resolved.

Attached 0003 uses HeapTupleData::t_tableoid to store remote tableoid
and local tableoid. Remote tableoid is stored there for a scan
underlying DELETE/UPDATE. Local tableoid is stored otherwise. We use a
flag fetch_foreign_tableoid, stand alone and in deparse_expr_cxt to
differentiate between these two usages.

>
> I don't think it is acceptable but (hopefully) almost solves this
> problem if we allow that. User always sees the conventional
> tableOid and all ExecForeignUpdate/Delete have to do is to use
> remote_tableoid as a part of remote tuple identifier. Required to
> consider how to propagate the remote_tableoid through joins or
> other intermediate executor nodes, though. It is partly similar
> to the way deciding join push down.

0003 does that. Fortunately we already have testing UPDATE/DELETE with joins.

>
> Another point is that, even though HeapTupleData is the only
> expected coveyer of the tuple identification, assuming tableoid +
> ctid is not adequite since FDW interface is not exlusive for
> postgres_fdw. The existig ctid field is not added for the purpose
> and just happened to (seem to) work as tuple identifier for
> postgres_fdw but I think tableoid is not.

I am not able to understand. postgresAddForeignUpdateTargets does that
specifically for postgres_fdw. I am using the same function to add
junk column for tableoid similar to ctid.

>
> The same can be said on ctid. Maybe my description was
> unclear. Specifically, I intended to say something like:
>
> - If we want to update/delete remote partitioned/inhtance tables
>   without direct modify, the foreign relation must have a columns
>   defined as "tableoid as remote_tableoid" or something. (We
>   could change the column name by a fdw option.)

Ok. I think, I misunderstood your proposal. IIUC, this way, SELECT *
FROM foreign_table is going to report remote_tableoid, which won't be
welcome by users.

Let me know what you think of the attached patches.

>
>
>> I think we should try getting 0001 and 0002 at least committed
>> independent of 0003.
>
> Agreed on 0002. 0001 should be committed with 0003?

0001 adds testcases which show the problem, so we have to 

Re: Is a modern build system acceptable for older platforms

[Hartmut Holzgraefe]

On 27.04.2018 10:45, Mark Kirkwood wrote:

I note that Mysql (yeah I know, we don't love 'em greatly, but their 
product niche is similar to ours) and Ceph (ok it is a distributed 
storage system but still a highly popular open src product) have 
switched to using cmake (relatively) recently. Both these projects were 
using autoconf etc related builds previously and seem to be doing just 
fine with cmake.


I lived through that transition at MySQL, and later at SkySQL/MariaDB

Windows builds have been using CMake since somewhere in the MySQL 5.0
series at least already.

For a while autotools and CMake build systems coexisted side by side,
until everything was unified to use CMake only in the 5.5 series which
became "GA" in 2010, so "(relatively) recently" is rather relative.

Having to maintain two different build systems, and keep them in sync,
obviously wasn't a good thing to do in the long run and CMake (plus
CPack and friends) has proven itself to be "good enough" for quite
a while.

There are several things that autotools provide out of the box that
I still miss with CMake.

The most important one being "make distcheck" to check that creating
a source distribution tarball, unpacking it, doing an "out of source"
build with it that does not modify the actual source tree, runs test
and applies some release best practice sanity checks by e.g. checking
whether the ChangeLog file looks up to date.

As far as I can tell there's no CMake equivalent to that at all,
which is especially "funny" as CMake used to advertise their preference
for out-of-source builds as an advantage over autotools. We often enough
ended up with builds writing to the source directory tree instead of the
build tree over the years.

Makefiles generated by automake are more feature rich in general,
which is understandable as its the only backend it has to support.

Some of the CMake choices there are just weird though, like their
refusal to have "make uninstall" supported out of the box.

Some may also think that getting rid of a mix of bash, m4, and Makefile
code snippets may be a good thing (which in itself is true), but CMake
replaces this with its own home grown language that's not used anywhere
else, and which comes with only a very rudimentary lex/yacc parser,
leading to several semi-consistent function argument parsing hacks.

The bundled pacakge libs that come with CMake made some builds more
easy, but this part didn't seem to be seeing much love anymore last
time I looked. Meanwhile the Autoconf Macro Archive has at least partly
closed that gap.

Also last time I looked CMake hat nothing really comparable to
autotools submodules (bundled sub-projects that come with their
own autotools infrastructure and could be built standalone, but
in a bunlded context will inherit all "configure" settings from
the top level invocation).

There was also some weird thing about CMake changing shared library
default locations in already built binaries on the fly on "make 
install", so that they work both in the install and build context,

e.g. for running tests before installing. Autotools handle this
by building for the install context right away, and setting up
wrapper scripts that set up load paths for libs in the build
context for pre-install testing. In this particular case I don't
really trust either approach, so that one's a tie.

What else?

CMakes more aggressive caching behavior can be confusing, but
then again that's really just a matter of preference.

It's command line argument parsing and help output is inferior
to autotools configure, all project specific options have to
start with a -D, and help output for these is strictly alphabetical,
while with autoconf you can group related options in help output,
and on modern Linux distributions there's good tab completion
for them, too.

cmake-gui is advertised to solve much of this, but it still
suffers from the alphabetic listing problem.

I could probably continue with this brain dump forever, but in the
end it comes down to:


There's no real alternative when you have to support windows, and
it is "good enough" on Unix, so that maintaining CMake and autotool
build setups in parallel isn't really justified in the long run"



PS: when you actually watch a full MariaDB CMake


--
Hartmut, former MySQL guy now working for MariaDB

Wrong plan with extra parallel workers

[Guilherme Pereira]

Hi,

Having a strange situation, where adding extra parallel workers
(max_parallel_workers_per_gather), the planner chooses a different plan,
with nested loops, which makes the query twice as slow. Strangely with the
COUNT_DISTINCT implementation from Tomas Vondra (
https://github.com/tvondra/count_distinct) it scales nicely (almost
linearly) with the workers.

Can provide the DDL's or more info if needed. Any ideas why this happens?

set max_parallel_workers_per_gather = 2;

EXPLAIN ANALYZE
SELECT
"f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
COUNT( DISTINCT "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
"c_fb839a9bd6f2015f"
FROM
"f_zendesktags_aakrjpgq72ad93i"
INNER JOIN
"f_zendesktickets_aaeljtllr5at3el"
ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
"f_zendesktickets_aaeljtllr5at3el"."id"
GROUP BY
1 ;

  GroupAggregate  (cost=31676816.72..32928717.16 rows=8 width=12) (actual
time=124072.467..210958.329 rows=9 loops=1)
   Group Key: f_zendesktickets_aaeljtllr5at3el.via_id
   ->  Sort  (cost=31676816.72..32094116.84 rows=166920048 width=8) (actual
time=124072.431..148808.161 rows=166920891 loops=1)
 Sort Key: f_zendesktickets_aaeljtllr5at3el.via_id
 Sort Method: external merge  Disk: 2939944kB
 ->  Hash Join  (cost=1919106.06..6597948.02 rows=166920048
width=8) (actual time=13063.794..85782.027 rows=166920891 loops=1)
   Hash Cond: (f_zendesktags_aakrjpgq72ad93i.ticket_id_id =
f_zendesktickets_aaeljtllr5at3el.id)
   ->  Seq Scan on f_zendesktags_aakrjpgq72ad93i
(cost=0.00..2571476.48 rows=166920048 width=4) (actual
time=0.016..20886.829 rows=166920891 loo
ps=1)
   ->  Hash  (cost=1774033.25..1774033.25 rows=11605825
width=8) (actual time=12981.920..12981.920 rows=11605822 loops=1)
 Buckets: 16777216  Batches: 1  Memory Usage: 584425kB
 ->  Seq Scan on f_zendesktickets_aaeljtllr5at3el
(cost=0.00..1774033.25 rows=11605825 width=8) (actual time=0.045..9262.223
rows=116058
22 loops=1)
 Planning Time: 1.426 ms
 Execution Time: 211441.893 ms
(13 rows)

-

set max_parallel_workers_per_gather = 6;

EXPLAIN ANALYZE
SELECT
"f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
COUNT( DISTINCT "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
"c_fb839a9bd6f2015f"
FROM
"f_zendesktags_aakrjpgq72ad93i"
INNER JOIN
"f_zendesktickets_aaeljtllr5at3el"
ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
"f_zendesktickets_aaeljtllr5at3el"."id"
GROUP BY
1 ;

 GroupAggregate  (cost=1001.10..31949141.56 rows=8 width=12) (actual
time=9.125..399880.451 rows=9 loops=1)
   Group Key: f_zendesktickets_aaeljtllr5at3el.via_id
   ->  Gather Merge  (cost=1001.10..31114541.24 rows=166920048 width=8)
(actual time=9.037..322148.967 rows=166920891 loops=1)
 Workers Planned: 6
 Workers Launched: 6
 ->  Nested Loop  (cost=1.01..10826616.89 rows=27820008 width=8)
(actual time=0.150..30867.494 rows=23845842 loops=7)
   ->  Parallel Index Scan using
f_zendesktickets_aaeljtllr5at3el_via_id_idx on
f_zendesktickets_aaeljtllr5at3el  (cost=0.43..5838596.19 rows=193
4304 width=8) (actual time=0.066..3750.816 rows=1657975 loops=7)
   ->  Index Only Scan using
f_zendesktags_aakrjpgq72ad93i_ticket_id_id_idx on
f_zendesktags_aakrjpgq72ad93i  (cost=0.57..2.02 rows=56 width=4) (
actual time=0.005..0.012 rows=14 loops=11605822)
 Index Cond: (ticket_id_id =
f_zendesktickets_aaeljtllr5at3el.id)
 Heap Fetches: 166920891
 Planning Time: 1.395 ms
 Execution Time: 400283.994 ms

--

Strangely with the count_distinct implementation from Tomas Vondra from
https://github.com/tvondra/count_distinct, it doesn' happen, and it scales
nicely with the extra workers.
set max_parallel_workers_per_gather = 6;

EXPLAIN ANALYZE
SELECT
"f_zendesktickets_aaeljtllr5at3el"."via_id" AS "a_1303",
COUNT_DISTINCT ( "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" ) AS
"c_fb839a9bd6f2015f"
FROM
"f_zendesktags_aakrjpgq72ad93i"
INNER JOIN
"f_zendesktickets_aaeljtllr5at3el"
ON  "f_zendesktags_aakrjpgq72ad93i"."ticket_id_id" =
"f_zendesktickets_aaeljtllr5at3el"."id"
GROUP BY
1 ;

 Finalize GroupAggregate  (cost=6674091.66..6882748.12 rows=8 width=12)
(actual time=24724.265..44768.274 rows=9 loops=1)
   Group Key: f_zendesktickets_aaeljtllr5at3el.via_id
   ->  Gather Merge  (cost=6674091.66..6882747.66 rows=48 width=36) (actual
time=22655.677..42954.758 rows=60 loops=1)
 Workers Planned: 6
 Workers Launched: 6
 ->  Partial GroupAggregate  (cost=6673091.57..6881741.73 rows=8
width=36) (actual time=21427.218..32385.322 rows=9 loops=7)
   Group Key: f_zendesktickets_aaeljtllr5at3el.via_id
   ->  Sort  (cost=6673091.57..6742641.59 rows=27820008
width=8) (actual time=20546.722..22817.024 rows=23845842 loops=7)
   

Re: Is a modern build system acceptable for older platforms

[Mark Kirkwood]

On 27/04/18 19:10, Yuriy Zhuravlev wrote:



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


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




I note that Mysql (yeah I know, we don't love 'em greatly, but their 
product niche is similar to ours) and Ceph (ok it is a distributed 
storage system but still a highly popular open src product) have 
switched to using cmake (relatively) recently. Both these projects were 
using autoconf etc related builds previously and seem to be doing just 
fine with cmake.


regards
Mark

Re: Is a modern build system acceptable for older platforms

[Yuriy Zhuravlev]

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

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

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

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

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

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 Tom> I would bet money that that "_SPI_current->internal_xact" thing is
 Tom> wrong/inadequate.

In particular this looks wrong to me: after doing:

do $$
  begin
execute 'declare foo cursor with hold for select 1/x as a from (values 
(1),(0)) v(x)';
commit;  -- errors within the commit
  end;
$$;
ERROR:  division by zero
CONTEXT:  PL/pgSQL function inline_code_block line 1 at COMMIT

the SPI stack is not cleaned up at all, and _SPI_connected is >= 0 even
when back at the main backend command loop.

-- 
Andrew (irc:RhodiumToad)

Re: GSoC 2018: Sorting Algorithm and Benchmarking

[Andrey Borodin]

Hi!

> 26 апр. 2018 г., в 0:12, Kefan Yang  написал(а):
>  
> My name is Kefan Yang. I am so excited that my proposal ‘Sorting Algorithm 
> and Benchmarking 2018’ has been accepted.
Welcome! I'm also glad you've chosen this project.

> I see on the Wiki page that you can mentor this proposal.
Yes, we with Atri Sharma will mentor this project.

> This is what I’ve done in these few days:
>   • I’ve carefully read through Submit a Patch and have a basic 
> understanding of the submission process.
Cool!
>   • I’ve set up the environment and ready to code. The sorting routine is 
> kind of separated from other modules so I don’t need much time to get 
> familiar with the code base. If things go smoothly, I can start coding ahead 
> of schedule
Great!
> Now I have some questions:
>   • If I understand it correctly, the sorting benchmark should be an 
> executable  under the src/bin/ folder just like pgbench?
Well, I think it is up to you how to make reproducible, precise and correct 
benchmarking :)
You can make exec (we will not have to merge it into PG, we need just results), 
or you can make Postgres extension (it is much easier) which will be executed 
from psql.
>   • Do you, or other community members, have any suggestions about the 
> final version of proposal?
As far as I know, proposal cannot be modified. Actual work can be slightly 
adjusted though.
>   • Are there any specific thing you expect me to do during this 
> community bonding period?

Let's establish communication. You can also build PostgreSQL from git and try 
pgbench (tool for benchmarking overall performance). Read something about 
TPC-(A,B,C) and YCSB, and may be even read something about sysbench.
I can think of following mediums of communications:
1. Slack channel
2. Telegram chat
3. Skype chat
4. E-mail

Atri, Kefar, how do you think, which of these will do best for us?


Best re