Re: [GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

From: Venkata B Nagothi 
Sent: Friday, September 30, 2016 02:48
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 10:16 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com>> wrote:



From: Venkata B Nagothi mailto:nag1...@gmail.com>>
Sent: Thursday, September 29, 2016 17:25
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com>>>
 wrote:

Hi

I would like to know which technique is better for supporting multi-tenancy=
 applications, going upto hundreds or even thousands of tenants.

1 - One database with difference schemas (one schema per tenant)
or
2 - One database per tenant.

Did you mean one database with-in a postgresql cluster ?

Yes.  Say something like this within a PG cluster

db4978
db6234
...
100s of such databases.

That would make things worst if you are going for one database per tenant. As 
said by John just now, it would end up in an very complex and bad design 
contributing to very poor performance and high maintenance overhead.
A schema per tenant would be a good idea and its hard to say without knowing 
the data isolation levels you require for each tenant.


We require complete data isolation. Absolutely nothing should be shared between 
two tenants.

WHy would multiple dbs be any worse than multiple schemas in performance?




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

Re: [GENERAL] Multi tenancy : schema vs databases

[John R Pierce]

On 9/30/2016 2:06 AM, Rakesh Kumar wrote:

We require complete data isolation. Absolutely nothing should be shared between 
two tenants.

WHy would multiple dbs be any worse than multiple schemas in performance?


complete?  use 1000s of seperate VM instances, one per tennant.


ok, thats ridiculous, isn't it.   so now its time to find a compromise.



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Thomas Kellerer]

Tom Lane schrieb am 29.09.2016 um 23:10:
> Thomas Kellerer  writes:
>> for some reason pg_upgrade failed on Windows 10 for me, with an error 
>> message that one specifc _vm file couldn't be copied.
> 
> Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
> code for 9.6 and hasn't really gotten that much testing.  Its error
> reporting is shamefully bad --- you can't tell which step failed, and
> I wouldn't even put a lot of faith in the errno being meaningful,
> considering that it does close() calls before capturing the errno.
> 
> But what gets my attention in this connection is that it doesn't
> seem to be taking the trouble to open the files in binary mode.
> Could that lead to the reported failure?  Not sure, but it seems
> like at the least it could result in corrupted VM files.

I did this on two different computers, one with Windows 10 the other with 
Windows 7. 
(only test-databases, so no real issue anyway)

In both cases running a "vacuum full" for the table in question fixed the 
problem and pg_upgrade finished without problems.




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

Re: [GENERAL] Multi tenancy : schema vs databases

[Achilleas Mantzios]

Via schemata if the tenants represent sub entities of the same organization.
This gives the top level mgmt the ability to have a consolidated view of the 
whole organization.

On 30/09/2016 12:06, Rakesh Kumar wrote:



From: Venkata B Nagothi 
Sent: Friday, September 30, 2016 02:48
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 10:16 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com>> wrote:



From: Venkata B Nagothi mailto:nag1...@gmail.com>>
Sent: Thursday, September 29, 2016 17:25
To: Rakesh Kumar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar 
mailto:rakeshkumar...@outlook.com>>>
 wrote:

Hi

I would like to know which technique is better for supporting multi-tenancy=
  applications, going upto hundreds or even thousands of tenants.

1 - One database with difference schemas (one schema per tenant)
or
2 - One database per tenant.

Did you mean one database with-in a postgresql cluster ?

Yes.  Say something like this within a PG cluster

db4978
db6234
...
100s of such databases.

That would make things worst if you are going for one database per tenant. As 
said by John just now, it would end up in an very complex and bad design 
contributing to very poor performance and high maintenance overhead.
A schema per tenant would be a good idea and its hard to say without knowing 
the data isolation levels you require for each tenant.


We require complete data isolation. Absolutely nothing should be shared between 
two tenants.

WHy would multiple dbs be any worse than multiple schemas in performance?







--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



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

Re: [GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

> ok, thats ridiculous, isn't it.   so now its time to find a compromise.

You don't understand how sales people pitch our products. We deal with 
financial data
and our customers are extremely sensitive to even imagining that their data 
will co-reside
with that of their competitors who also are our customers. A typical fear 
mongering Q from
them "what if due to a bug in your s/w, our competitors end up looking at our 
data" or 
something like that. That's why schema level vs db level discussion.

Just a reminder, I started this thread to learn more on the technical drawbacks 
of choosing
either option. For example, in SQL Server, having multiple databases in an 
instance does not
mean more significantly pressure on resources (as compared to multiple 
schemas). In DB2
it does since many resources like cache (buffers) are db specific. 

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

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Masahiko Sawada]

On Fri, Sep 30, 2016 at 6:40 PM, Thomas Kellerer  wrote:
> Tom Lane schrieb am 29.09.2016 um 23:10:
>> Thomas Kellerer  writes:
>>> for some reason pg_upgrade failed on Windows 10 for me, with an error 
>>> message that one specifc _vm file couldn't be copied.
>>
>> Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
>> code for 9.6 and hasn't really gotten that much testing.  Its error
>> reporting is shamefully bad --- you can't tell which step failed, and
>> I wouldn't even put a lot of faith in the errno being meaningful,
>> considering that it does close() calls before capturing the errno.
>>
>> But what gets my attention in this connection is that it doesn't
>> seem to be taking the trouble to open the files in binary mode.
>> Could that lead to the reported failure?  Not sure, but it seems
>> like at the least it could result in corrupted VM files.
>
> I did this on two different computers, one with Windows 10 the other with 
> Windows 7.
> (only test-databases, so no real issue anyway)
>
> In both cases running a "vacuum full" for the table in question fixed the 
> problem and pg_upgrade finished without problems.

Because vacuum full removes the _vm file, pg_upgrade completed job successfully.
If you still have the _vm file
("d:/Daten/db/pgdata95/base/16410/85358_vm") that lead an error, is it
possible that you check if there is '\r\n' [0d 0a] character in that
_vm file or share that _vm file with us?

Regards,

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


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

Re: [GENERAL] Multi tenancy : schema vs databases

[Jerome Wagner]

you could also use a hybrid approach :
 - have a systematic tenant_id field in your tables, allowing for 1 db / 1
schema multi-tenants
 - give your application the ability to set the schema path for a tenant,
so it will locate the tenant schema if it has a decidated schema
 - maybe go to the extreme to be able to specialize the db per tenant
 - ..

this would allow you to easily re-organize your tenants to find the best
compromise depending on their status (small tenants, huge tenant, security
freaks tenants, ..).

if going with schema based tenants, make sure you have administrative tasks
to check the diffs between the schemas because if 1000s schemas diverge it
will be bring technical debt down the line.




On Fri, Sep 30, 2016 at 11:47 AM, Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> Via schemata if the tenants represent sub entities of the same
> organization.
> This gives the top level mgmt the ability to have a consolidated view of
> the whole organization.
>
> On 30/09/2016 12:06, Rakesh Kumar wrote:
>
>>
>> 
>> From: Venkata B Nagothi 
>> Sent: Friday, September 30, 2016 02:48
>> To: Rakesh Kumar
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Multi tenancy : schema vs databases
>>
>> On Fri, Sep 30, 2016 at 10:16 AM, Rakesh Kumar <
>> rakeshkumar...@outlook.com> wrote:
>>
>>
>> 
>> From: Venkata B Nagothi mailto:nag1...@gmail.com>>
>> Sent: Thursday, September 29, 2016 17:25
>> To: Rakesh Kumar
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Multi tenancy : schema vs databases
>>
>> On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar > > >> wrote:
>>
>> Hi
>>
>> I would like to know which technique is better for supporting
>> multi-tenancy=
>>   applications, going upto hundreds or even thousands of tenants.
>>
>> 1 - One database with difference schemas (one schema per tenant)
>> or
>> 2 - One database per tenant.
>>
>> Did you mean one database with-in a postgresql cluster ?
>>
>> Yes.  Say something like this within a PG cluster
>>
>> db4978
>> db6234
>> ...
>> 100s of such databases.
>>
>> That would make things worst if you are going for one database per
>> tenant. As said by John just now, it would end up in an very complex and
>> bad design contributing to very poor performance and high maintenance
>> overhead.
>> A schema per tenant would be a good idea and its hard to say without
>> knowing the data isolation levels you require for each tenant.
>> 
>>
>> We require complete data isolation. Absolutely nothing should be shared
>> between two tenants.
>>
>> WHy would multiple dbs be any worse than multiple schemas in performance?
>>
>>
>>
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] executing os commands from a function

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Armand Pirvu (home)
Sent: Thursday, September 29, 2016 5:42 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] executing os commands from a function


All

I know this may sound like heresy since it involves executing an OS command 
from a function , but here goes

After an insert in a table, I want to touch a file

I.e

After insert into table test values (100) I want in a dir to have file 100

I used plsh extension but I had to use two functions and a trigger, see code 
below

CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS '
#!/bin/bash
 touch /home/postgres/$1;
' LANGUAGE plsh;
commit;

CREATE FUNCTION func1() RETURNS trigger AS '
BEGIN
perform   func2(NEW.col1);
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trigf1 BEFORE INSERT on test
FOR EACH ROW EXECUTE PROCEDURE func1();

testdb=# insert into test3 values (777); INSERT 0 1 testdb=# select * from 
test3;
 col1
--
  777

[postgres@edb1 ~]$ ls -ltr
-rw--- 1 postgres postgres 0 Sep 29 16:30 777


It works but can I be simpler ? Any other alternatives ? In Ingres for example 
I can use dbevent and an esqlc app which listens 


Thank you 

Armand
__

Similar mechanism exists in Postgresql.
Read about LISTEN/NOTIFY in the docs.

Regards,
Igor Neyman



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

Re: [GENERAL] Multi tenancy : schema vs databases

[Vick Khera]

On Fri, Sep 30, 2016 at 6:06 AM, Rakesh Kumar
 wrote:
> A typical fear mongering Q from
> them "what if due to a bug in your s/w, our competitors end up looking at our 
> data" or
> something like that. That's why schema level vs db level discussion.

So... if your software isolates customers based on a "customer_id"
field in a table, how is that less secure than isolating a customer
based on the schema? Ie, you've just moved the customer_id field from
a column to part of the table name itself. One step up from that, what
keeps your software from selecting the wrong customer_id database name
too? See, it is all just *where* you put the distinguishing name...

For your resource question, having multiple DBs require each to have
its own handle within your app. When you have many simultaneous users,
you have lots and lots of file handles to manage. You want to minimize
the number of connections to the postgres server itself.


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

Re: [GENERAL] Multi tenancy : schema vs databases

[Vick Khera]

On Fri, Sep 30, 2016 at 5:11 AM, John R Pierce  wrote:
> On 9/30/2016 2:06 AM, Rakesh Kumar wrote:
>>
>> We require complete data isolation. Absolutely nothing should be shared
>> between two tenants.
>>
>> WHy would multiple dbs be any worse than multiple schemas in performance?
>
>
> complete?  use 1000s of seperate VM instances, one per tennant.
>

Well, VM's don't always provide 100% isolation, so separate hardware,
with each on its own VLAN seems right to me. And then make sure your
switch doesn't leak across VLANs.

Your requirements need refinement, at the least :)


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

Re: [GENERAL] Multi tenancy : schema vs databases

[Colin Morelli]

Rakesh,

As long as one application knows how to connect to more than 1 tenant,
there will *always* be the possibility that a software bug in your
application causes one tenant to access another tenant's data. I think this
is why you're getting people asking you to refine your requirements. There
quite simply is no way to "guarantee" one tenant can't see another tenant
when they are sharing resources.

As Vick mentioned, whether you do this by database, schema, or a column in
a table, your application has to decide which tenant to connect to and
when. You could make the argument that using a column such as customer_id
increases the surface area of the potential failures, but generally
speaking - regardless of the solution you go with - your best investment
would be in a testing infrastructure for your application that ensures data
remains tenant-isolated.

I know none of this answers your most recent question, about the technical
tradeoffs between schema per tenant or database per tenant, but I think
it's still relevant to your original question. Both of those solutions will
be considerably harder to manage than a shared-everything infrastructure.
They'll certainly both require more resources, and they introduce the
problem for other issues (inconsistent schema and/or table definitions
across multiple tenants being one of the most problematic and difficult to
prevent).

Best,
Colin

Worth noting: in the shared everything infrastructure, it's *far* more
likely that a bug in your software results in one tenant seeing *all* data
across all tenants, as opposed to data for one wrong tenant. The good
news(?) here is that these kinds of bugs are generally very easy to spot
before they ever make it out to customers. In any case, the answer is
almost certainly going to be testing.

On Fri, Sep 30, 2016 at 10:34 AM Vick Khera  wrote:

> On Fri, Sep 30, 2016 at 5:11 AM, John R Pierce 
> wrote:
> > On 9/30/2016 2:06 AM, Rakesh Kumar wrote:
> >>
> >> We require complete data isolation. Absolutely nothing should be shared
> >> between two tenants.
> >>
> >> WHy would multiple dbs be any worse than multiple schemas in
> performance?
> >
> >
> > complete?  use 1000s of seperate VM instances, one per tennant.
> >
>
> Well, VM's don't always provide 100% isolation, so separate hardware,
> with each on its own VLAN seems right to me. And then make sure your
> switch doesn't leak across VLANs.
>
> Your requirements need refinement, at the least :)
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Multi tenancy : schema vs databases

[Paul Jungwirth]

On Fri, Sep 30, 2016 at 6:06 AM, Rakesh Kumar
 wrote:

A typical fear mongering Q from
them "what if due to a bug in your s/w, our competitors end up looking at our 
data" or
something like that. That's why schema level vs db level discussion.


I've been reading this discussion with great interest, to see what other 
Postgres experts think. :-)


I've almost always taken the customer_id approach, and I prefer it---but 
I also agree it is easier to make mistakes, because you have to include 
that condition in your code everywhere. With per-schema or per-database, 
you can manage access simply by handing out connections.


If isolation is your goal, one drawback with one-database-many-schemas 
is that there is no way to prevent users from listing all the schemas in 
the database. In psql this is `\dn`, but you can also do it with SQL 
against the system catalog. You can forbid querying the tables in the 
schema, but anyone can see that the schema itself is there. So that 
would leak some information---at the very least the number of customers 
you have. You haven't said whether users will go through an application 
tier or have direct SQL access, but if it's the latter, this is 
something to be aware of. I believe it is possible to prevent, but only 
by taking away access from important catalog tables that would also 
break `\dt` or `\d foo`. (I would love to be corrected btw!) Also you 
can't use RLS against the system catalog, so there's no solution there.


Good luck!

Paul


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

Re: [GENERAL] executing os commands from a function

[Francisco Olarte]

Armand

On Thu, Sep 29, 2016 at 11:41 PM, Armand Pirvu (home)
 wrote:
> I know this may sound like heresy since it involves executing an OS command 
> from a function , but here goes
> After an insert in a table, I want to touch a file

> I used plsh extension but I had to use two functions and a trigger, see code 
> below

> It works but can I be simpler ? Any other alternatives ? In Ingres for 
> example I can use dbevent and an esqlc app which listens

If you are superuser ( which I supose you must be to execute plsh )
you could try to put code like this in a plpgsql, or may be even sql,
security definer ( or plain if not needed ) function:

( slighly sanitized )
In the psql prompt of a client machine:

n=# copy (select 1 as c where false) to '/tmp/ptxtst';
COPY 0

In the server machine:

postgres@server ~ $ stat /tmp/ptxtst
  File: '/tmp/ptxtst'
  Size: 0 Blocks: 0  IO Block: 4096   regular empty file
Device: 802h/2050dInode: 4721101 Links: 1
Access: (0644/-rw-r--r--)  Uid: (   70/postgres)   Gid: (   70/postgres)
Access: 2016-09-30 17:31:21.024617892 +0200
Modify: 2016-09-30 17:31:21.024617892 +0200
Change: 2016-09-30 17:31:21.024617892 +0200
 Birth: -

Further details left for the reader.

Francisco Olarte.


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

Re: [GENERAL] executing os commands from a function

[David G. Johnston]

On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home)  wrote:

> I used plsh extension but I had to use two functions and a trigger, see
> code below
>

​I don't see any way to not use a trigger given your requirement.  And as
written I don't see that you need the second function​ - just write the
trigger function in plsh.  Admittedly its probably better to have two
functions from an architecture standpoint.

Listen/Notify doesn't seem to make sense as you want the server to ensure
that the file exists - not some other client that may or may not be
connected.

​David J.

Re: [GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

> I've been reading this discussion with great interest, to see what other
> Postgres experts think. :-)

I am bit disappointed that most of the replies are questioning why we are
doing what we are doing. Once again, we (db designers) have no choice
in that.  What I would like to know that which one is better :- multiple db
vs multiple schema.  Read few interesting arguments and noted that
connection pooling works better with multiple schemas than dbs. Anything else?

thanks

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

[GENERAL] ZSON, PostgreSQL extension for compressing JSONB

[Aleksander Alekseev]

Hello.

I've just uploaded ZSON extension on GitHub:

https://github.com/afiskon/zson

ZSON learns on your common JSONB documents and creates a dictionary
with strings that are frequently used in all documents. After that you
can use ZSON type to compress documents using this dictionary. When
documents schema evolve and compression becomes inefficient you can
re-learn on new documents. New documents will be compressed with a new
dictionary, old documents will be decompressed using old dictionary.

In some cases ZSON can save half of your disk space and give you about
10% more TPS. Everything depends on your data and workload though.
Memory is saved as well. For more details see README.md.

Please don't hesitate to ask any questions. Any feedback and pull
requests are welcome too!

-- 
Best regards,
Aleksander Alekseev


pgpOLxOzqfQCg.pgp
Description: OpenPGP digital signature

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

[Jay Knight]

I've upped max_worker_processes to 16, but I still can't get it to launch
workers unless I use EXPLAIN ANALYZE.  I've also found that this simplified
setup exhibits the same behavior:

create table big as (
  SELECT generate_series(1,3000) AS id
);

explain analyze SELECT avg(id) from big where id % 17 = 0;

With explain analyze, the plan includes:

Workers Planned: 4
Workers Launched: 4

But without the explain analyze, it never launches workers:

Workers Planned: 4
Workers Launched: 0

I've tried this on a CentOS VM (VirtualBox on Windows) that I gave 2 cores,
and it worked as expected (it launched workers with and without explain
analyze), so I've only been able to reproduce this on Windows.

Thanks,
Jay K

On Thu, Sep 29, 2016 at 9:07 PM David Rowley 
wrote:

> On 30 September 2016 at 10:47, Jay Knight  wrote:
> >>What's max_worker_processes set to?
> >
> > 8
> >
> >>One theory would be that, the worker might not have been available
> >>when you performed the query execution, but it just happened to be
> >>when you did the EXPLAIN ANALYZE
> >
> > This happens consistently this way.  Every time I run it with explain
> > analyze it uses parallel workers, and every time I run it without it
> > doesn't.  I just enabled auto_explain, and see that it is "planning" to
> use
> > a worker, but doesn't launch it.  Is there a way to know why a planned
> > worker doesn't get launched?
>
> It seems not, at least not unless you're willing to attach a debugger.
>
> > 2016-09-29 16:45:44 CDT LOG:  duration: 50703.595 ms  plan:
> > Query Text: select count(*) from t1
> > join t2 on t1.item = t2.item and t1.low <= t2.high and t1.high >=
> t2.low
> > Finalize Aggregate  (cost=14609058.99..14609059.00 rows=1 width=8)
> > (actual time=50703.584..50703.584 rows=1 loops=1)
> >   ->  Gather  (cost=14609058.88..14609058.99 rows=1 width=8) (actual
> > time=50703.581..50703.581 rows=1 loops=1)
> > Workers Planned: 1
> > Workers Launched: 0
> > ->  Partial Aggregate  (cost=14608958.88..14608958.89 rows=1
> > width=8) (actual time=50703.579..50703.579 rows=1 loops=1)
> >   ->  Nested Loop  (cost=0.42..13608937.28 rows=48641
> > width=0) (actual time=0.534..50577.673 rows=3669891 loops=1)
> > ->  Parallel Seq Scan on t1  (cost=0.00..3386.71
> > rows=176471 width=12) (actual time=0.041..18.351 rows=30 loops=1)
> > ->  Index Only Scan using t2_item_low_high_idx
> on t2
> > (cost=0.42..63.77 rows=1333 width=12) (actual time=0.167..0.168 rows=12
> > loops=30)
> >   Index Cond: ((item = t1.item) AND (low <=
> > t1.high) AND (high >= t1.low))
> >   Heap Fetches: 0
>
> That's interesting.
>
> How about trying to increase max_worker_processes to say, 16. I know
> you've said you've tried multiple times and it seems consistent, but
> increasing this seems to be worth a try, if anything, to rule that
> out.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

[Tom Lane]

Jay Knight  writes:
> I've tried this on a CentOS VM (VirtualBox on Windows) that I gave 2 cores,
> and it worked as expected (it launched workers with and without explain
> analyze), so I've only been able to reproduce this on Windows.

FWIW, I tried your original example on Linux yesterday, and it seemed to
work fine --- it only wanted to use 1 worker, but the speedup was just
about exactly 2X with or without "explain analyze".  So this is somehow
Windows specific.  That's darn odd; in a quick look at the relevant code
I see nothing that looks platform dependent.

Somebody will need to trace through this on Windows and see where it's
going off the rails.

regards, tom lane


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

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Tim Clarke]

On 29/09/16 15:29, Dave Page wrote:
> The pgAdmin Development Team are pleased to announce the release of
> pgAdmin 4 version 1.0.
>
> pgAdmin is the leading Open Source management and administration tool
> for PostgreSQL.
>
> pgAdmin 4 is a complete rewrite of pgAdmin, built using Python and
> Javascript/jQuery. A desktop runtime written in C++ with Qt allows it
> to run standalone for individual users, or the web application code
> may be deployed directly on a webserver for use by one or more users
> through their web browser. The software has the look and feel of a
> desktop application whatever the runtime environment, and vastly
> improves on pgAdmin III with updated user interface elements,
> multi-user/web deployment options, dashboards and a more modern
> design.
>
> The application was designed as a pluggable framework that can easily
> be extended with plugin modules to provide additional functionality to
> support other software in the PostgreSQL ecosystem, or functionality
> of forks of PostgreSQL.
>
> For more information, screenshots, documentation and downloads, please
> see the website at:
>
> https://www.pgadmin.org/
>
> pgAdmin 4 is also bundled in the PostgreSQL 9.6 installers from EDB at:
>
> http://www.enterprisedb.com/products-services-training/pgdownload
>
> I'd like to take this opportunity to thank all those involved in the
> design, implementation, testing and documentation of pgAdmin 4,
> estimated at well over 10,000 hours of effort over the last year. For
> more information about the project, please see the blog post at:
>
> http://pgsnake.blogspot.co.uk/2016/04/pgadmin-4-elephant-nears-finish-line.html
>
> Finally, I'd like to thank the management at EDB
> (http://www.enterprisedb.com) for supporting this project and allowing
> me to enlist the efforts of over 15 employees from multiple teams.
> Without their support pgAdmin 4 would not exist today.
>

Marvellous Dave, thanks to the team for their hard work.


I can't compile it though, can anyone help with:


timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ qmake
Project MESSAGE: Building for QT5+...
Project MESSAGE: Building for Linux/Mac...
Project MESSAGE: Using /usr/bin/python-config
Project MESSAGE: Python2 detected.
Project ERROR: Unknown module(s) in QT: webkitwidgets
timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ export QT_SELECT="4"
timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ qmake
Project MESSAGE: Building for QT4...
Project MESSAGE: Building for Linux/Mac...
Project MESSAGE: Using /usr/bin/python-config
Project MESSAGE: Python2 detected.
timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ make
g++ -c -m64 -pipe -I/usr/include/python2.7
-I/usr/include/x86_64-linux-gnu/python2.7 -O2 -Wall -W -D_REENTRANT
-DPYTHON2 -DQT_NO_DEBUG -DQT_WEBKIT_LIB -DQT_GUI_LIB -DQT_NETWORK_LIB
-DQT_CORE_LIB -I/usr/share/qt4/mkspecs/linux-g++-64 -I.
-I/usr/include/qt4/QtCore -I/usr/include/qt4/QtNetwork
-I/usr/include/qt4/QtGui -I/usr/include/qt4/QtWebKit -I/usr/include/qt4
-I. -I. -o pgAdmin4.o pgAdmin4.cpp
In file included from TabWindow.h:16:0,
 from BrowserWindow.h:16,
 from pgAdmin4.cpp:29:
WebViewWindow.h:20:20: fatal error: QWebView: No such file or directory
compilation terminated.
Makefile:266: recipe for target 'pgAdmin4.o' failed


Tim Clarke




smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] isnull() function in pgAdmin3

[dudedoe01]

Select version(); shows 

postgreSQL 9.6beta3, compiled by Visual C++ build 1800, 64-bit



--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923791.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Devrim Gündüz]

Hi,

On Fri, 2016-09-30 at 18:10 +0100, Tim Clarke wrote:
> WebViewWindow.h:20:20: fatal error: QWebView: No such file or directory

These are the -devel packages that I installed to build pgadmin4 RPMs:

BuildRequires:  mesa-libGL-devel
BuildRequires:  gcc-c++
BuildRequires:  qt5-qtbase-devel >= 5.1
BuildRequires:  qt5-qtwebkit-devel
BuildRequires:  qt-devel >= 4.6
BuildRequires:  qtwebkit-devel
BuildRequires:  python3-devel
BuildRequires:  python-devel

In your case, I think qt-devel and qtwebkit-devel packages are needed.

Regards,

-- 
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Multi tenancy : schema vs databases

[Jeff Janes]

On Fri, Sep 30, 2016 at 2:06 AM, Rakesh Kumar 
wrote:

>
> We require complete data isolation. Absolutely nothing should be shared
> between two tenants.
>

Then you need different clusters per tenant.  Otherwise, the WAL records of
different tenants are inextricably mingled together.

Cheers,

Jeff

Re: [GENERAL] Multi tenancy : schema vs databases

[Stephen Frost]

* Jeff Janes (jeff.ja...@gmail.com) wrote:
> On Fri, Sep 30, 2016 at 2:06 AM, Rakesh Kumar 
> wrote:
> > We require complete data isolation. Absolutely nothing should be shared
> > between two tenants.
> 
> Then you need different clusters per tenant.  Otherwise, the WAL records of
> different tenants are inextricably mingled together.

Different clusters are also required to have independent file-level
backups, independent roles, independent tablespaces, etc.

It's also far easier to move a single cluster from one system to another
to adjust for growth than to try and move an individual schema or
database.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

>Then you need different clusters per tenant.  Otherwise, the WAL records
> of different tenants are inextricably mingled together.

Yes we are aware of it .This part is OK as it is not deemed as user table data. 

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

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Tim Clarke]

On 30/09/16 18:35, Devrim Gündüz wrote:
> These are the -devel packages that I installed to build pgadmin4 RPMs:
> BuildRequires:mesa-libGL-devel
> BuildRequires:gcc-c++
> BuildRequires:qt5-qtbase-devel >= 5.1
> BuildRequires:qt5-qtwebkit-devel
> BuildRequires:qt-devel >= 4.6
> BuildRequires:qtwebkit-devel
> BuildRequires:python3-devel
> BuildRequires:python-devel
>
> In your case, I think qt-devel and qtwebkit-devel packages are needed.
>
> Regards,
>

Many thanks for that list Devrim, I always get slightly irritated as a
developer on many platforms that the build process isn't slightly more
transport sometimes. Since I'm running Ubuntu here, for the benefit of
others, I had to:

sudo apt-get install qt4-dev-tools libqtwebkit-dev

Now I've compiled it with only:

> /usr/lib/x86_64-linux-gnu/qt4/bin/rcc -name pgAdmin4 pgAdmin4.qrc -o
> qrc_pgAdmin4.cpp
> g++ -c -m64 -pipe -I/usr/include/python2.7
> -I/usr/include/x86_64-linux-gnu/python2.7 -O2 -Wall -W -D_REENTRANT
> -DPYTHON2 -DQT_NO_DEBUG -DQT_WEBKIT_LIB -DQT_GUI_LIB -DQT_NETWORK_LIB
> -DQT_CORE_LIB -DQT_SHARED -I/usr/share/qt4/mkspecs/linux-g++-64 -I.
> -I/usr/include/qt4/QtCore -I/usr/include/qt4/QtNetwork
> -I/usr/include/qt4/QtGui -I/usr/include/qt4/QtWebKit
> -I/usr/include/qt4 -I. -I. -o qrc_pgAdmin4.o qrc_pgAdmin4.cpp
> In file included from qrc_pgAdmin4.cpp:9:0:
> qrc_pgAdmin4.cpp:4142:44: warning:
> ‘qInitResources_pgAdmin4__init_variable__’ defined but not used
> [-Wunused-variable]
>  Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
> ^
> /usr/include/qt4/QtCore/qglobal.h:941:21: note: in definition of macro
> ‘Q_CONSTRUCTOR_FUNCTION0’
> static const int AFUNC ## __init_variable__ = AFUNC();
>  ^
> qrc_pgAdmin4.cpp:4142:1: note: in expansion of macro
> ‘Q_CONSTRUCTOR_FUNCTION’
>  Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
>  ^
> qrc_pgAdmin4.cpp:4142:24: note: in expansion of macro
> ‘QT_MANGLE_NAMESPACE’
>  Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
> ^


for notes (which I don't think are a problem but I could be wrong), I get:

> timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ ./pgAdmin4 &
> [1] 9195
> timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ Python path:  "/usr/bin/python"
> Python Home:  ""
> Webapp path:  "/home/timc/dev/pgadmin4-1.0/web/pgAdmin4.py"
> Traceback (most recent call last):
>   File "/home/timc/dev/pgadmin4-1.0/web/pgAdmin4.py", line 24, in 
> from pgadmin import create_app
>   File "/home/timc/dev/pgadmin4-1.0/web/pgadmin/__init__.py", line 17,
> in 
> from flask import Flask, abort, request, current_app
> ImportError: No module named flask
> "Failed to launch the application server, server thread exiting."
>
> [1]+  Exit 1  ./pgAdmin4


when I try to run it despite an attempt to recover by:

> sudo apt-get ^Cstall python-flask

Sigh

-- 
Tim



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Graphical entity relation model

> Does anybody know a Software for generating graphical entity relation models 
> from existing postgresql databases?
> 
> Best regards Johannes

I use dbWrench (dbwrench.com). It's not free, but they do have a free trial 
version so you can see if you like it before you buy it. It's also not 
expensive compared to many of these sorts of tools. It also runs on all 3 major 
platforms (it's written in Java) and the developer is responsive if you find a 
problem.

If money is no object, you can look at Power Designer (by Sybase). I used to 
use it years ago and liked it even if it was MS-Windows only, but the price has 
gone up so much only companies can really afford it now, IMO.

HTH,
Kevin


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

Re: [GENERAL] Multi tenancy : schema vs databases

>> I've been reading this discussion with great interest, to see what 
>> other Postgres experts think. :-)
>
>I am bit disappointed that most of the replies are questioning why we are 
>doing what we are doing. Once again, we (db designers) have no choice in that. 
> What I would like to know that which one is better :- multiple db vs multiple 
>schema.  Read few interesting arguments and noted that connection pooling 
>works better with multiple schemas than dbs. Anything else?


I've been curious to see what the others would tell you too. :) There's a lot 
about the admin side I can't advise you on, but I'll take a shot anyway from an 
overall-app view...

Your situation sounds somewhat similar to my previous job. There, we hosted 
multiple customers on the same physical server. We also used Mysql, so your 
question wouldn't have applied there. But translating that situation to if they 
had used Postgres, I think I'd have told them to do 1 DB and many schemas 
because of the resource sharing. That would have worked for them because the 
DBs were completely internal; i.e. the customer could not get to the DB 
directly -- the customer could only see the data thru our app. Given that, then 
each DB server would have hosted between 1-50 customers (depending on their 
size).

The difficult spot with Postgres (AFAICT) is that if your customer has direct 
access to the DB, then 1 DB to many schema would break your security 
requirements. We had a situation at my present job recently where one of 
customers wanted access to our log tables. The first idea was to grant them 
select-only privs to the logging schema thinking that would be safe enough as 
they couldn't get to the main data schema. However, in testing, we found that 
wasn't good enough as it allowed them to at least look at table designs even if 
they couldn't get to the data. That was bad so in a sense Postgres failed us 
(to the experts if there is a way to do this, I'd love to know how to do that). 
We considered creating a separate DB for the logging data, but decided that 
would make things too difficult and we didn't want to "waste" server resources 
in that way. We ended up writing a small app that the customer could query and 
it read the log files for them, ensuring security was maintained. This is why 
others are asking you about your [security] requirements.

BTW, if you go the 1 DB and many schema way, be sure you fully understand 
"search_path".

I don't know if that's helpful to you or not, but hopefully it was at least a 
little.

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

[GENERAL] System crashed: fatal error restarting postgres

[Rich Shepard]

  A java application threw a major error when closing and it crashed my
system (including wiping out most of /tmp). After rebooting I su'd to user
postgres and tried to start postgresql-9.5.4, but this error displays:

postgres@salmo:~$ FATAL:  could not open shared memory segment
"/PostgreSQL.1804289383": Permission denied

  Please advise me on how I proceed to clear this and restart the
application.

TIA,

Rich


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

Re: [GENERAL] System crashed: fatal error restarting postgres

[Adrian Klaver]

On 09/30/2016 01:15 PM, Rich Shepard wrote:

  A java application threw a major error when closing and it crashed my
system (including wiping out most of /tmp). After rebooting I su'd to user
postgres and tried to start postgresql-9.5.4, but this error displays:

postgres@salmo:~$ FATAL:  could not open shared memory segment
"/PostgreSQL.1804289383": Permission denied

  Please advise me on how I proceed to clear this and restart the
application.


See here:

https://www.postgresql.org/message-id/24208.1473724630%40sss.pgh.pa.us


TIA,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Adrian Klaver]

On 09/30/2016 11:32 AM, Tim Clarke wrote:

On 30/09/16 18:35, Devrim Gündüz wrote:

These are the -devel packages that I installed to build pgadmin4 RPMs:
BuildRequires:  mesa-libGL-devel
BuildRequires:  gcc-c++
BuildRequires:  qt5-qtbase-devel >= 5.1
BuildRequires:  qt5-qtwebkit-devel
BuildRequires:  qt-devel >= 4.6
BuildRequires:  qtwebkit-devel
BuildRequires:  python3-devel
BuildRequires:  python-devel

In your case, I think qt-devel and qtwebkit-devel packages are needed.

Regards,



Many thanks for that list Devrim, I always get slightly irritated as a
developer on many platforms that the build process isn't slightly more
transport sometimes. Since I'm running Ubuntu here, for the benefit of
others, I had to:

sudo apt-get install qt4-dev-tools libqtwebkit-dev

Now I've compiled it with only:


/usr/lib/x86_64-linux-gnu/qt4/bin/rcc -name pgAdmin4 pgAdmin4.qrc -o
qrc_pgAdmin4.cpp
g++ -c -m64 -pipe -I/usr/include/python2.7
-I/usr/include/x86_64-linux-gnu/python2.7 -O2 -Wall -W -D_REENTRANT
-DPYTHON2 -DQT_NO_DEBUG -DQT_WEBKIT_LIB -DQT_GUI_LIB -DQT_NETWORK_LIB
-DQT_CORE_LIB -DQT_SHARED -I/usr/share/qt4/mkspecs/linux-g++-64 -I.
-I/usr/include/qt4/QtCore -I/usr/include/qt4/QtNetwork
-I/usr/include/qt4/QtGui -I/usr/include/qt4/QtWebKit
-I/usr/include/qt4 -I. -I. -o qrc_pgAdmin4.o qrc_pgAdmin4.cpp
In file included from qrc_pgAdmin4.cpp:9:0:
qrc_pgAdmin4.cpp:4142:44: warning:
‘qInitResources_pgAdmin4__init_variable__’ defined but not used
[-Wunused-variable]
 Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
^
/usr/include/qt4/QtCore/qglobal.h:941:21: note: in definition of macro
‘Q_CONSTRUCTOR_FUNCTION0’
static const int AFUNC ## __init_variable__ = AFUNC();
 ^
qrc_pgAdmin4.cpp:4142:1: note: in expansion of macro
‘Q_CONSTRUCTOR_FUNCTION’
 Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
 ^
qrc_pgAdmin4.cpp:4142:24: note: in expansion of macro
‘QT_MANGLE_NAMESPACE’
 Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
^



for notes (which I don't think are a problem but I could be wrong), I get:


timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ ./pgAdmin4 &
[1] 9195
timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ Python path:  "/usr/bin/python"
Python Home:  ""
Webapp path:  "/home/timc/dev/pgadmin4-1.0/web/pgAdmin4.py"
Traceback (most recent call last):
  File "/home/timc/dev/pgadmin4-1.0/web/pgAdmin4.py", line 24, in 
from pgadmin import create_app
  File "/home/timc/dev/pgadmin4-1.0/web/pgadmin/__init__.py", line 17,
in 
from flask import Flask, abort, request, current_app
ImportError: No module named flask
"Failed to launch the application server, server thread exiting."

[1]+  Exit 1  ./pgAdmin4



when I try to run it despite an attempt to recover by:


sudo apt-get ^Cstall python-flask


What happens if you move the pgAdmin4 runtime outside the build 
environment and run it?


Can you import Flask in a Python interpreter?

Do you have multiple versions of Python and if so are you sure the 
apt-get is installing Flask into the same version that pgAdmin4 is using?




Sigh




--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] System crashed: fatal error restarting postgres

[Tom Lane]

Rich Shepard  writes:
>A java application threw a major error when closing and it crashed my
> system (including wiping out most of /tmp). After rebooting I su'd to user
> postgres and tried to start postgresql-9.5.4, but this error displays:

> postgres@salmo:~$ FATAL:  could not open shared memory segment
> "/PostgreSQL.1804289383": Permission denied

Wrong permissions on /dev/shm, perhaps?  On Linux I believe it should
always be

drwxrwxrwt. 2 root root 160 Sep 30 15:23 /dev/shm/

Although TBH, the first question you should be asking yourself is why
you're running *any* java application with root privileges, which is
what I think would be needed to let this happen.

regards, tom lane


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

Re: [GENERAL] isnull() function in pgAdmin3

[Adrian Klaver]

On 09/30/2016 08:18 AM, dudedoe01 wrote:

Select version(); shows

postgreSQL 9.6beta3, compiled by Visual C++ build 1800, 64-bit


There is no isnull() in 9.6 either, if that is what you are asking?

Otherwise what is the question you want answered?

--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] System crashed: fatal error restarting postgres

[Adrian Klaver]

On 09/30/2016 01:15 PM, Rich Shepard wrote:
>   A java application threw a major error when closing and it crashed my
> system (including wiping out most of /tmp). After rebooting I su'd to user
> postgres and tried to start postgresql-9.5.4, but this error displays:
> 
> postgres@salmo:~$ FATAL:  could not open shared memory segment
> "/PostgreSQL.1804289383": Permission denied
> 
>   Please advise me on how I proceed to clear this and restart the
> application.

Actually more to the point:

https://www.postgresql.org/message-id/alpine.LNX.2.11.1609121701270.1140%40localhost
> 
> TIA,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] System crashed: fatal error restarting postgres [FIXED]

[Rich Shepard]

On Fri, 30 Sep 2016, Adrian Klaver wrote:


See here:
https://www.postgresql.org/message-id/24208.1473724630%40sss.pgh.pa.us


  Ah, shoot! I completely forgot about this. Yes, I reset the perms on
/dev/shm and that fixed the problem.

My apologies to all,

Rich


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

Re: [GENERAL] System crashed: fatal error restarting postgres

[Rich Shepard]

On Fri, 30 Sep 2016, Tom Lane wrote:


Wrong permissions on /dev/shm, perhaps?


Tom,

  Yes. I keep forgetting about this since I don't reboot this
server/workstation often.

Thanks,

Rich


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

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

[David Rowley]

On 1 October 2016 at 05:47, Tom Lane  wrote:
> FWIW, I tried your original example on Linux yesterday, and it seemed to
> work fine --- it only wanted to use 1 worker, but the speedup was just
> about exactly 2X with or without "explain analyze".  So this is somehow
> Windows specific.  That's darn odd; in a quick look at the relevant code
> I see nothing that looks platform dependent.
>
> Somebody will need to trace through this on Windows and see where it's
> going off the rails.

I had assumed this was something very specific to Jay's machine, and
tracing through the code I didn't really see why EXPLAIN ANALYZE would
behave any differently from just the SELECT.

I tried the test case on 9.6.0 on a Windows 8.1 machine, and it works
fine for me.

Full test case below, if anyone else would like to try.

postgres=# create table big as (
postgres(#   SELECT generate_series(1,3000) AS id
postgres(# );
SELECT 3000

postgres=# set max_parallel_workers_per_gather=8;
SET
postgres=# load 'auto_explain';
LOAD
postgres=# set auto_explain.log_min_duration=0;
SET
postgres=# set auto_explain.log_analyze=1;
SET
postgres=# \timing
Timing is on.
postgres=# explain analyze SELECT avg(id) from big where id % 17 = 0;
   QUERY PLAN

 Finalize Aggregate  (cost=223819.96..223819.97 rows=1 width=32)
(actual time=1030.778..1030.778 rows=1 loops=1)
   ->  Gather  (cost=223819.43..223819.94 rows=5 width=32) (actual
time=1029.691..1030.762 rows=6 loops=1)
 Workers Planned: 5
 Workers Launched: 5
 ->  Partial Aggregate  (cost=222819.43..222819.44 rows=1
width=32) (actual time=1004.021..1004.021 rows=1 loops=6)
   ->  Parallel Seq Scan on big  (cost=0.00..222744.43
rows=3 width=4) (actual time=0.132..974.408 rows=294118 loops=6)
 Filter: ((id % 17) = 0)
 Rows Removed by Filter: 4705883
 Planning time: 0.066 ms
 Execution time: 1045.064 ms
(10 rows)

Time: 1045.486 ms (00:01.045)

postgres=# SELECT avg(id) from big where id % 17 = 0;
  avg
---
 1501.
(1 row)


Time: 1061.304 ms (00:01.061)

The logs show:

2016-10-01 09:39:30 NZDT [7084]: [11-1]
user=postgres,db=postgres,app=psql,client=::1 LOG:  duration: 1060.801
ms  plan:
Query Text: SELECT avg(id) from big where id % 17 = 0;
Finalize Aggregate  (cost=223819.96..223819.97 rows=1 width=32)
(actual time=1046.424..1046.424 rows=1 loops=1)
 ->  Gather  (cost=223819.43..223819.94 rows=5 width=32) (actual
time=1045.358..1046.414 rows=6 loops=1)
   Workers Planned: 5
   Workers Launched: 5
   ->  Partial Aggregate  (cost=222819.43..222819.44 rows=1
width=32) (actual time=1023.197..1023.197 rows=1 loops=6)
 ->  Parallel Seq Scan on big  (cost=0.00..222744.43
rows=3 width=4) (actual time=0.143..992.890 rows=294118 loops=6)
   Filter: ((id % 17) = 0)
   Rows Removed by Filter: 4705883



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


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

Re: [GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

I don't know if that's helpful to you or not, but hopefully it was at least a 
little.
===
yes it was.  thanks

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

[GENERAL] pgadmin vs psql output

[Rakesh Kumar]

I have a bytea col which stores Java UUID data. When I run

Select cast(uuid_col as varchar) from table limit 1 

Output in pgadmin
-===
"A001"

Output in psql

 \x4130303030303030303030303030303030303030303030303030303030303031

is there some setting in psql output I need to take care of.


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

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

[Tom Lane]

David Rowley  writes:
> On 1 October 2016 at 05:47, Tom Lane  wrote:
>> Somebody will need to trace through this on Windows and see where it's
>> going off the rails.

> I tried the test case on 9.6.0 on a Windows 8.1 machine, and it works
> fine for me.

Hm, but if we assume that EXPLAIN ANALYZE is somehow affecting the
behavior, it's entirely plausible that having auto_explain active
would have the same effect.

Please try it without auto_explain, and just rely on noting the runtime
with psql's \timing to infer whether workers were active or not.

regards, tom lane


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

Re: [GENERAL] System crashed: fatal error restarting postgres

[Tom Lane]

Rich Shepard  writes:
> On Fri, 30 Sep 2016, Tom Lane wrote:
>> Wrong permissions on /dev/shm, perhaps?

>Yes. I keep forgetting about this since I don't reboot this
> server/workstation often.

You ought to do some investigation and figure out what is causing
it to come up with the wrong permissions in the first place; that
is certainly not normal Linux behavior.

Or at least stick a chmod into /etc/rc.d/rc.local, if you just
want to hit the problem over the head with a hammer.

regards, tom lane


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

Re: [GENERAL] pgadmin vs psql output

[Tom Lane]

Rakesh Kumar  writes:
> I have a bytea col which stores Java UUID data. When I run
> Select cast(uuid_col as varchar) from table limit 1 

> Output in pgadmin
> -===
> "A001"

> Output in psql
> 
>  \x4130303030303030303030303030303030303030303030303030303030303031

> is there some setting in psql output I need to take care of.

See "bytea_output" parameter.

regards, tom lane


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

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

[David Rowley]

On 1 October 2016 at 10:10, Tom Lane  wrote:
> David Rowley  writes:
>> On 1 October 2016 at 05:47, Tom Lane  wrote:
>>> Somebody will need to trace through this on Windows and see where it's
>>> going off the rails.
>
>> I tried the test case on 9.6.0 on a Windows 8.1 machine, and it works
>> fine for me.
>
> Hm, but if we assume that EXPLAIN ANALYZE is somehow affecting the
> behavior, it's entirely plausible that having auto_explain active
> would have the same effect.
>
> Please try it without auto_explain, and just rely on noting the runtime
> with psql's \timing to infer whether workers were active or not.

It's certainly worth trying that, but in Jay's case auto_explain made
no difference.

Results without auto_explain:

postgres=# set max_parallel_workers_per_gather=8;
SET
Time: 0.257 ms
postgres=# SELECT avg(id) from big where id % 17 = 0;
  avg
---
 1501.
(1 row)


Time: 1027.599 ms (00:01.028)
postgres=# set max_parallel_workers_per_gather=0;
SET
Time: 0.163 ms
postgres=# SELECT avg(id) from big where id % 17 = 0;
  avg
---
 1501.
(1 row)


Time: 3262.519 ms (00:03.263)

postgres=# select version();
 version
-
 PostgreSQL 9.6.0 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5,
Built by MSYS2 project) 4.9.2, 64-bit
(1 row)


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


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

Re: [GENERAL] Frequent "pg_ctl status" removing(?) semaphores (unlikely)

[raf]

Michael Paquier wrote:

> On Tue, Sep 27, 2016 at 2:13 PM, raf  wrote:
> > So, my qestion is, is it possible that "pg_ctl status" could be
> > removing postgres's semaphores and can I stop it? It seems
> > extremely unlikely. So, if it isn't, what else could it be?
> > Systemd perhaps? It's been known to kill screen/tmux/nohup
> > processes when a user logs out in its keenness to clean up but
> > that may be clutching at straws.
> 
> systemd sometimes has fun removing semaphores. See here for example:
> https://www.postgresql.org/message-id/cak7teys9-o4bterbs3xuk2bffnnd55u2sm9j5r2fi7v6bhj...@mail.gmail.com
> -- 
> Michael

Thanks, Michael. That's it. I added RemoveIPC=no to
/etc/systemd/logind.conf, restarted systemd-logind,
and re-enabled the cron job, and it's been fine for
a whole day.

Now to do the same on all my other systemd-afflicted
hosts (just on principal). :-)

It looks to me like a bug in systemd. If it's removing
IPC resources because it thinks that the postgres user has
completely "logged out", why does it take over 100 "logins"
(i.e. cronjobs) before it decides that the postgres user
is completely logged out. What was the difference between all
the previous cronjobs and the one where it finally decided
to remove the semaphores? Why didn't it remove them after the
first cronjob had completed? And why did it think the postgres
user was completely "logged out" when it's also running the
postgres server processes? It might be because it sees the
postgres server processes as belonging to my user "slice"
because I started it even though it's running as the postgres
user. That doesn't seem like a useful way of viewing things.

But this is all rhetorical. No doubt there are reasons for this
but I don't care as long as I can turn it off.

Thanks again,
raf



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

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Adrian Klaver]

On 09/30/2016 11:32 AM, Tim Clarke wrote:

On 30/09/16 18:35, Devrim Gündüz wrote:

These are the -devel packages that I installed to build pgadmin4 RPMs:
BuildRequires:  mesa-libGL-devel
BuildRequires:  gcc-c++
BuildRequires:  qt5-qtbase-devel >= 5.1
BuildRequires:  qt5-qtwebkit-devel
BuildRequires:  qt-devel >= 4.6
BuildRequires:  qtwebkit-devel
BuildRequires:  python3-devel
BuildRequires:  python-devel

In your case, I think qt-devel and qtwebkit-devel packages are needed.

Regards,



Many thanks for that list Devrim, I always get slightly irritated as a
developer on many platforms that the build process isn't slightly more
transport sometimes. Since I'm running Ubuntu here, for the benefit of
others, I had to:

sudo apt-get install qt4-dev-tools libqtwebkit-dev

Now I've compiled it with only:


/usr/lib/x86_64-linux-gnu/qt4/bin/rcc -name pgAdmin4 pgAdmin4.qrc -o
qrc_pgAdmin4.cpp
g++ -c -m64 -pipe -I/usr/include/python2.7
-I/usr/include/x86_64-linux-gnu/python2.7 -O2 -Wall -W -D_REENTRANT
-DPYTHON2 -DQT_NO_DEBUG -DQT_WEBKIT_LIB -DQT_GUI_LIB -DQT_NETWORK_LIB
-DQT_CORE_LIB -DQT_SHARED -I/usr/share/qt4/mkspecs/linux-g++-64 -I.
-I/usr/include/qt4/QtCore -I/usr/include/qt4/QtNetwork
-I/usr/include/qt4/QtGui -I/usr/include/qt4/QtWebKit
-I/usr/include/qt4 -I. -I. -o qrc_pgAdmin4.o qrc_pgAdmin4.cpp
In file included from qrc_pgAdmin4.cpp:9:0:
qrc_pgAdmin4.cpp:4142:44: warning:
‘qInitResources_pgAdmin4__init_variable__’ defined but not used
[-Wunused-variable]
 Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
^
/usr/include/qt4/QtCore/qglobal.h:941:21: note: in definition of macro
‘Q_CONSTRUCTOR_FUNCTION0’
static const int AFUNC ## __init_variable__ = AFUNC();
 ^
qrc_pgAdmin4.cpp:4142:1: note: in expansion of macro
‘Q_CONSTRUCTOR_FUNCTION’
 Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
 ^
qrc_pgAdmin4.cpp:4142:24: note: in expansion of macro
‘QT_MANGLE_NAMESPACE’
 Q_CONSTRUCTOR_FUNCTION(QT_MANGLE_NAMESPACE(qInitResources_pgAdmin4))
^



for notes (which I don't think are a problem but I could be wrong), I get:


timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ ./pgAdmin4 &
[1] 9195
timc@tim-573P:~/dev/pgadmin4-1.0/runtime$ Python path:  "/usr/bin/python"
Python Home:  ""
Webapp path:  "/home/timc/dev/pgadmin4-1.0/web/pgAdmin4.py"
Traceback (most recent call last):
  File "/home/timc/dev/pgadmin4-1.0/web/pgAdmin4.py", line 24, in 
from pgadmin import create_app
  File "/home/timc/dev/pgadmin4-1.0/web/pgadmin/__init__.py", line 17,
in 
from flask import Flask, abort, request, current_app
ImportError: No module named flask
"Failed to launch the application server, server thread exiting."

[1]+  Exit 1  ./pgAdmin4



when I try to run it despite an attempt to recover by:


sudo apt-get ^Cstall python-flask


It's more complicated then that as I found out.

First you will need to go into ~/.pgadmin and move pgadmin4.db out of 
the way.


Then follow the instructions here:

https://www.pgadmin.org/docs4/1.x/desktop_deployment.html#configuration

Before you run the python setup.py part you will then have to install a 
lot of  Flask dependencies:


Flask-Babel==0.11.1
Flask-Gravatar==0.4.2
Flask-Login==0.3.2
Flask-Mail==0.9.1
Flask-Principal==0.4.0
Flask-Security==1.7.5
Flask-SQLAlchemy==2.1
Flask-WTF==0.13

also:

django-htmlmin==0.9.1

Then run python setup.py.

Personally I would go this route:

https://www.pgadmin.org/download/pip4.php



Sigh




--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] executing os commands from a function

[Armand Pirvu (home)]

Thanks for replies.
Good deal of info

While plsh seems to be an easier approach , I am also inclined in a an ESQL/C 
type app to listen to an event

@Igor Neyman
It is just maybe I have missed a sample of a C app  in that area, similar like 
ESQLC

Cheers
Armand




On Sep 30, 2016, at 10:45 AM, David G. Johnston  
wrote:

> On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home)  
> wrote:
> I used plsh extension but I had to use two functions and a trigger, see code 
> below
> 
> ​I don't see any way to not use a trigger given your requirement.  And as 
> written I don't see that you need the second function​ - just write the 
> trigger function in plsh.  Admittedly its probably better to have two 
> functions from an architecture standpoint.
> 
> Listen/Notify doesn't seem to make sense as you want the server to ensure 
> that the file exists - not some other client that may or may not be connected.
> 
> ​David J.