the installation of pgadmin4 makes me weep in frustration

[robert]

Hi There


first, thanks a lot for the great an beautiful software we get from 
PostgreSQL and all people around it.


But I wonder how it comes, that installing pgadmin4 is so incredibly hard?

And no documentation.

I would like to install pgadmin4 to my ubuntu 18.1 laptop.

Where do I find doku on how to install pgadmin4. Preferably I would like 
to install it using pip?



thanks

robert

--
Robert Rottermann CEO

031 333 10 20
rob...@redo2oo.ch 
Sickingerstrasse 3, 3014 Bern 


https://Redo2oo.ch 

*Ihr Partner wenn es um ERP Lösungen geht.*

Array_agg and dimensions in Array

[Mike Martin]

I have come across a problem which I cant seem to solve in a nice way
Basically I have a (small) table of tags
What I need to is combine two concatenated fields with a literal value as
an array element.

First thought was using array_agg with a pre-created array as

select array_agg(ARRAY['-metadata',optname||'='||optvalue]))  metaopt from
encodeopts
where  alias is not null and opttype in ('tag','tagn')
group by transref,fileid ) a

However this results in a multi-dimensional array, rather than a single
dimensioned one, which makes it impossible to join with the rest of an
array created elsewhere in the query

This works, but is very cludgy

select ARRAY['-map_metadata','-1']||array_agg(metaopt) from
(select unnest(array_agg(ARRAY['-metadata',optname||'='||optvalue]))
metaopt from encodeopts
where  alias is not null and opttype in ('tag','tagn')
group by transref,fileid ) a

So does this

select
string_to_array(string_agg('-metadata',||'||'||optname||'='||optvalue])),'||')
metaopt from encodeopts
where  alias is not null and opttype in ('tag','tagn')
group by transref,fileid

but again cludgy

Any ideas appreciated

Mike

Re: Static PostgreSQL Binaries (Linux + Windows)

[Zach van Rijn]

On Sun, 2019-01-13 at 09:35 -0500, Tom Lane wrote:
> Zach van Rijn  writes:
> > ...
> > The workaround is simply to ignore these errors during build
> > until I or someone else can get around to supplying patches
> > (in the next week or so; I have other commitments).
>
> TBH, there's going to be zero community interest in such
> patches.

Hi Tom,


Thank you for writing.

Given that several others have raised the topic before, there is
at least a little interest. It might not be for the general use-
case, but in certain scenarios static binaries are quite useful.

I wrote previously to offer this option and to solicit help in
testing and finding ways to improve these binaries.

If this doesn't align with the broader PostgreSQL community then
I apologize in advance for the mail.

> There is no reason to avoid shared libraries,

Why not?

  * It eliminates a whole class of vulnerabilities, namely those
related to LD_PRELOAD;

  * It ensures programs behave consistently ("this file produced
those results") which is significantly more difficult to do
when a shared library is updated (e.g., on a shared system)
and the user is left wondering why the output is different
after their company performs "scheduled maintenance" and can
not reproduce their results (even if the deviation is due to
a bug in the underlying library);

  * It ensures programs behave consistently across compatible
machines, regardless of the underlying system configuration,
as in the case of poorly-managed computing clusters, which
is unfortunately a reality for some people;

among others, including for performance reasons.

When scientific papers are published, one expects to be able to
reproduce the results exactly. That last bullet point is from my
personal experience, whereby a bug in one of the system's math
libraries caused slight variations in simulation output even
though our own code was later verified to be correct. The system
libc is not usually the first place one looks for errors, though
it cannot be ruled out.

> and they're an essential part of the modern Postgres build
> architecture --- particularly our extensibility story.

If you're referring to the loading of shared "user-code" modules
then that's a fair argument. In which case, you're correct that
it will be difficult or impossible to dynamically load modules
without some non-trivial design changes. But not all users will
need or want this feature.

Otherwise, would you kindly elaborate on "extensibility story"
and how that factors into why shared libraries are essential to
everyone who uses PostgreSQL? I'm not too familiar with its
internals or system architecture and this is a sincere request.

> Personally, I find your claim that purely-static linking is
> somehow a security advantage to be quite bizarre.

I said "may have additional performance / safety benefits." --
though I should have added "in certain applications."

Have you seen this post [1]? That situation is quite bizarre. If
they knew of this issue before proceeding with the upgrades, how
would they have avoided it? What if some machines could not be
upgraded to matching libc versions? These sort of issues are why
xstatic is being developed.

My position is, PostgreSQL is just one package of many, that I'd
like to support in my free time. It's not practical to expect or
achieve perfection the first time around, but it is one step
forward. What will computing look like 30 years from now?

> All modern Linux distros actually forbid static linking, I
> believe, because it'd put them in an impossible rebuild
> situation when some low-level component requires an update ---
> possibly for security reasons.

I haven't heard this before. Could you please point me to some
documentation on this? Debian permits it (see 8.2-3) [2], Gentoo
recommends against it [3]; others argue similarly and provide
context and further justification based on their intended uses.

Most of the reasons outlined in those links are valid, for
general-purpose Linux distros. I'm not going for that use case,
nor is this initiative intended to benefit all PostgreSQL users.

While it is true that shared libraries make system-wide updates
easier, there are several "modern" Linux distributions which
leverage static linking, e.g.: Sabotage Linux [4]. These are not
intended to be general-purpose either. And that's perfectly OK.

As to the "impossible rebuild situation", that is not necessary:
they only need to be re-linked. Future versions of xstatic will
cache build artifacts for fast updates. Perhaps it would still
be inconvenient, but it is certainly not impossible. Compression
techniques could be used to minimize wasted disk space.

> Are you going to promise immediate updates anytime glibc gets
> patched, across all the platforms you're proposing to support
> this on?

No; xstatic packages use musl libc [5], not glibc, so even if a
security-related bug in the libc came up, statistically speaking
it's 

Logical replication issue with row level trigger

[Aleš Zelený]

Hello,

I've found an issue with logical replication causing
1) large memory allocation by wal sender process (RSS 60GB)
2) large amount of "xid" files in $PGDATA/pg_replslot/
directory - seems to be amount of rows handled by trigger + few more

Having several millions of files in a single directory for replication slot
makes it hard to work on (tested ext4 and zfs).
Regarding the memory - such large allocation was no expected and as result
swap was used with all performance impact expected.

Environment and conditions to reproduce:

PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

create trigger function with:
1) DML modifying data in another table
2) use EXCEPTION clause (implicit subtransaction [
https://www.postgresql.org/docs/10/plpgsql-structure.html])

Note, that is does not matter, whether tables used for testcase are
replicated or no (this is what I use in testcase).

# publication side
pg_createcluster -d /pgsql/pgclusterzfs/i_pub/10/ipub -p 5532 10 ipub -- -k
pg_ctlcluster 10 ipub start
psql -p 5532 -c "alter system set wal_level=logical;"
pg_ctlcluster 10 ipub restart

psql -p 5532 <

Question about MemoryContextRegisterResetCallback

[Michel Pelletier]

Hello,

I'm working on an extension to wrap the GraphBLAS linear algebra package.
GraphBLAS provides a very flexible API over adjacency matrices for solving
graph problems.  I've got Matrix and Vector types wrapped, build
aggregators and extraction functions to pivot tables into matrices and
back, and may of the core operations are supported for just one of the 960
different semirings that GraphBLAS supports, but i'm making good progress
and with some advanced macro'ing I hope to provide complete API access.

This is no doubt the most complex bit of C wrapper I've done for postgres,
and I've run into a bit of a snag.  GraphBLAS objects are opaque handles
that have their own new/free functions.  After reading mmgr/README I have
registered a callback with CurrentMemoryContext during my aggregator
function that builds values.

https://github.com/michelp/pggraphblas/blob/master/src/matrix.c#L80

I've got tests that work very well, up until I declare a matrix or vector
in a plpgsql function.

https://github.com/michelp/pggraphblas/blob/master/test.sql#L103

When using these objects from a function, their free function seems be be
called prematurely, as GraphBLAS raises an error that the object isn't
initialized when it tries to compare two matrices with 'matrix_eq' (the
free function "uninitializes" a handle).  If I use CurTransactionContext
instead of CurrentMemoryContext, the function doesn't fail, but the server
segfaults on rollback.

For the brave and curious the test can reproduce the error, if you have
docker installed, just clone the repo and run './test.sh'.  (The first
build takes a while due to compiling GraphBLAS). Here's an example failure:

https://gist.github.com/michelp/1ba3cc79996b8d3a963d974224a78f2d

Obviously there is something I'm doing wrong about these callbacks,
thinking my free function is getting called immediately after the statement
that creates it, so I'm not sure what context to register it under.  Should
I create a new one?  Register it to the CurrentMemoryContext parent maybe?
Any help from the gurus on this would be greatly appreciated!

Thanks,

-Michel

Re: Static PostgreSQL Binaries (Linux + Windows)

[Tom Lane]

Zach van Rijn  writes:
> Under the hood, the only major "technique" is wrapping the 'gcc'
> command with flags such as '-static' to ensure that everything
> is built correctly, and using reliable toolchains [1].
> There is one minor issue in that the postgres build scripts no
> longer appear to support static building [2,3] so it'll attempt
> to build files such as 'POSIX.so' etc. and these cause errors.

Yup.

> The workaround is simply to ignore these errors during build
> until I or someone else can get around to supplying patches (in
> the next week or so; I have other commitments).

TBH, there's going to be zero community interest in such patches.
There is no reason to avoid shared libraries, and they're an
essential part of the modern Postgres build architecture ---
particularly our extensibility story.

Personally, I find your claim that purely-static linking is somehow
a security advantage to be quite bizarre.  All modern Linux distros
actually forbid static linking, I believe, because it'd put them in
an impossible rebuild situation when some low-level component
requires an update --- possibly for security reasons.  Are you going
to promise immediate updates anytime glibc gets patched, across all
the platforms you're proposing to support this on?

regards, tom lane

Re: Static PostgreSQL Binaries (Linux + Windows)

[Pratik Parikh]

Thanks, I'll check it out.

On Sun, Jan 13, 2019, 9:13 AM Zach van Rijn  On Sun, 2019-01-13 at 00:51 -0500, Pratik Parikh wrote:
> > Than you. nice to hear from you .  Would be able to share the
> > build scripts with community? are they open source?
>
> Hi Pratik,
>
>
> Yes; the build scripts were linked at the bottom of my original
> mail message in this chain: https://git.zv.io/xstatic/builder/
>
>   $ git clone https://git.zv.io/xstatic/builder.git
>   $ cd builder
>   $ ./build postgresql- # 11.1, etc.
>
> Under the hood, the only major "technique" is wrapping the 'gcc'
> command with flags such as '-static' to ensure that everything
> is built correctly, and using reliable toolchains [1].
>
> There is one minor issue in that the postgres build scripts no
> longer appear to support static building [2,3] so it'll attempt
> to build files such as 'POSIX.so' etc. and these cause errors.
>
> The workaround is simply to ignore these errors during build
> until I or someone else can get around to supplying patches (in
> the next week or so; I have other commitments).
>
> >
> > Regards,
> > Pratik Parikh
>
> ZV
>
>
> [1]: https://musl.cc/
>
> [2]: https://www.postgresql.org/message-id/CABFfbXuxyO20JN8T%2BC
> yfSe29T-GTON69FrKHQ%3Dc9jDMxnm6C_w%40mail.gmail.com
>
> [3]: https://www.postgresql.org/message-id/CABFfbXvONEKE2Bpnbfm5
> %3Df3fVVLpv6jLVUAhF7iGoWN7a_EeRw%40mail.gmail.com
>
>

Re: Static PostgreSQL Binaries (Linux + Windows)

[Zach van Rijn]

On Sun, 2019-01-13 at 00:51 -0500, Pratik Parikh wrote:
> Than you. nice to hear from you .  Would be able to share the
> build scripts with community? are they open source?

Hi Pratik,


Yes; the build scripts were linked at the bottom of my original
mail message in this chain: https://git.zv.io/xstatic/builder/

  $ git clone https://git.zv.io/xstatic/builder.git
  $ cd builder
  $ ./build postgresql- # 11.1, etc.

Under the hood, the only major "technique" is wrapping the 'gcc'
command with flags such as '-static' to ensure that everything
is built correctly, and using reliable toolchains [1].

There is one minor issue in that the postgres build scripts no
longer appear to support static building [2,3] so it'll attempt
to build files such as 'POSIX.so' etc. and these cause errors.

The workaround is simply to ignore these errors during build
until I or someone else can get around to supplying patches (in
the next week or so; I have other commitments).

> 
> Regards,
> Pratik Parikh

ZV


[1]: https://musl.cc/

[2]: https://www.postgresql.org/message-id/CABFfbXuxyO20JN8T%2BC
yfSe29T-GTON69FrKHQ%3Dc9jDMxnm6C_w%40mail.gmail.com

[3]: https://www.postgresql.org/message-id/CABFfbXvONEKE2Bpnbfm5
%3Df3fVVLpv6jLVUAhF7iGoWN7a_EeRw%40mail.gmail.com

Re: Static PostgreSQL Binaries (Linux + Windows)

[Brent Wood]

Does this support extensions such as Postgis & Timescale?

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529



[cid:image419b22.PNG@6e8846bb.46b21a94]


Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
T +64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade, Greta Point, Wellington
Connect with NIWA: niwa.co.nz 
Facebook Twitter 
LinkedIn 
Instagram

To ensure compliance with legal requirements and to maintain cyber security 
standards, NIWA's IT systems are subject to ongoing monitoring, activity 
logging and auditing. This monitoring and auditing service may be provided by 
third parties. Such third parties can access information transmitted to, 
processed by and stored on NIWA's IT systems.



From: Zach van Rijn 
Sent: Sunday, January 13, 2019 16:57
To: pgsql-general@lists.postgresql.org
Subject: Static PostgreSQL Binaries (Linux + Windows)

Hi all,


I've been busy porting popular open-source software to various
platforms (Linux + Windows) via musl- and/or MinGW- based tools,
as part of a project (and future distro?) called 'xstatic' [1].

All packages are statically linked and have zero dependencies,
they can be easily reproduced and audited, they are best suited
for use in environments where software must behave consistently,
and may have additional performance / safety benefits.

I am pleased to announce the immediate availability of binaries
(and source code / build scripts) for the following releases:

release  datelocation
---  --  --
latest   n/a https://xstatic.musl.cc/postgresql/
11.1 2018-11-08  https://xstatic.musl.cc/postgresql-11.1/
10.6 2018-11-08  https://xstatic.musl.cc/postgresql-10.6/
 9.6.11  2018-11-08  https://xstatic.musl.cc/postgresql-9.6.11/
 9.5.15  2018-11-08  https://xstatic.musl.cc/postgresql-9.5.15/
 9.4.20  2018-11-08  https://xstatic.musl.cc/postgresql-9.4.20/
 9.3.25  2018-11-08  https://xstatic.musl.cc/postgresql-9.3.25/


Within each top-level directory, you will find pertaining to an
architecture/ABI combination such as 'riscv32-linux-musl' (this
is the target platform where binaries should run), either:

(1) Directory tree (browse / download individual binaries); or
   e.g., https://xstatic.musl.cc/postgresql/riscv32-linux-musl/

(2) Tarball containing the above contents, with a sha512sum that
   is verifiable against '//SHA512SUMS'. Just extract
   and run (or build/link your own software against libraries).


PostgreSQL has been built for the following platforms, however,
not all platforms have been tested (please feel free to help):

 * aarch64-linux-musleabi
 * aarch64_be-linux-musl
 * arm-linux-musleabi
 * arm-linux-musleabihf
 * armeb-linux-musleabi
 * armeb-linux-musleabihf
 * armel-linux-musleabi
 * armel-linux-musleabihf
 * armv5l-linux-musleabihf
 * armv7l-linux-musleabihf
 * armv7m-linux-musleabi
 * armv7r-linux-musleabihf
 * i486-linux-musl
 * i686-linux-musl
 * i686-w64-mingw32
 * m68k-linux-musl
 * microblaze-linux-musl
 * microblazeel-linux-musl
 * mips-linux-musl
 * mips-linux-musln32sf
 * mips-linux-muslsf
 * mips64-linux-musl
 * mips64-linux-musln32
 * mips64-linux-musln32sf
 * mips64el-linux-musl
 * mips64el-linux-musln32
 * mips64el-linux-musln32sf
 * mipsel-linux-musl
 * mipsel-linux-musln32
 * mipsel-linux-musln32sf
 * mipsel-linux-muslsf
 * or1k-linux-musl
 * powerpc-linux-musl
 * powerpc-linux-muslsf
 * powerpc64-linux-musl
 * powerpc64le-linux-musl
 * powerpcle-linux-musl
 * powerpcle-linux-muslsf
 * riscv32-linux-musl
 * riscv64-linux-musl
 * s390x-linux-musl
 * sh2-linux-musl
 * sh2-linux-muslfdpic
 * sh2eb-linux-musl
 * sh2eb-linux-muslfdpic
 * sh4-linux-musl
 * sh4eb-linux-musl
 * x86_64-linux-musl
 * x86_64-linux-muslx32
 * x86_64-w64-mingw32


Quickly testing on Ubuntu 14.04 LTS (GNU/Linux 3.4.98 armv7l):

 $ file ./armv7l-linux-musleabihf/bin/psql
 psql: ELF 32-bit LSB  executable, ARM, EABI5 version 1 (SYSV),
 statically linked, stripped

 $ ./armv7l-linux-musleabihf/bin/psql --version
 psql (PostgreSQL) 11.1

 $ ./armv7l-linux-musleabihf/bin/psql \
   -h pellefant.db.elephantsql.com\
   -U abcdefgh
 Password:
 psql (11.1, server 9.5.2)
 Type "help" for help.

 abcdefgh=>


The directory listing looks something like: http://ix.io/1yaV

That said, if you find bugs or encounter issues, please file a
bug report here [2]. Windows support may need tweaking.


Regards,

ZV


[1]: https://xstatic.musl.cc/

[2]: https://git.zv.io/xstatic/builder/issues

Re: How to always run UPDATE FROM despite missing records in the source table?

[Andrew Gierth]

> "Alexander" == Alexander Farber  writes:

 Alexander> even though I wonder what is the (VALUES
 Alexander> ('20.20.20.20'::inet)) v(ip) construct there, some temporary
 Alexander> table which is then LEGT JOINed to the geoip table?

The SQL spec calls it a . The VALUES clause
behaves like a SELECT that returns a fixed number of rows (1 or more)
whose columns contain the results of the specified expressions. The
v(ip) part is just a table and column alias (I omitted the optional AS
keyword out of long habit) to name the constructed table.

-- 
Andrew (irc:RhodiumToad)

Re: How to always run UPDATE FROM despite missing records in the source table?

[Alexander Farber]

Thank you Adrian -

On Fri, Jan 11, 2019 at 4:55 PM Adrian Klaver 
wrote:

> On 1/11/19 4:50 AM, Alexander Farber wrote:
> > https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0
>
> Why not put a test for the block in the function and then use different
> UPDATE's depending on the result?
>

I didn't want to use IF and switch that statement to PL/pgSQL from pure
SQL, so Andrew's answer

 UPDATE users u SET
visited = now(),
ip = v.ip,
lat = i.lat,
lng = i.lng
FROM (VALUES ('20.20.20.20'::inet)) v(ip)
  LEFT JOIN geoip i ON (v.ip <<= i.block)
WHERE u.uid = 2;

suits me better, even though I wonder what is the (VALUES
('20.20.20.20'::inet)) v(ip) construct there, some temporary table which is
then LEGT JOINed to the geoip table?

Also, Andrew you have been right - with spgist index my queries against
geoip are fast enough, I was looking at the wrong spot in my EXPLAIN
ANALYZE output (the average values are slow, I am going to cache them soon)

Regards
Alex