[GENERAL] ANN: Upscene releases Database Workbench 5.2.4

[Martijn Tonies (Upscene Productions)]

Upscene Productions is proud to announce the availability of
the next version of the popular multi-DBMS development tool:

" Database Workbench 5.2.4 "

The 5.2 release includes support for PostgreSQL and adds several other features 
and bugfixes.

Database Workbench 5 comes in multiple editions with different pricing models, 
there's always a version that suits you!



"Version 5 included many new features", says Martijn Tonies, founder of Upscene 
Productions. 
"It added code editor features, has diagramming improvements, multiple 
editions, is fully HiDPI aware and offers tunnelling for MySQL and MariaDB 
connections.
The most recent version adds support for PostgreSQL, as requested by many of 
our customers."


For more information, see What's new in Database Workbench 5?
( http://www.upscene.com/database_workbench/whatsnew )


Database Workbench supports MySQL, MariaDB, Firebird, Oracle, MS SQL Server,
SQL Anywhere, NexusDB, PostgreSQL and InterBase, comes in multiple editions and 
is licensed based on
selectable modules.

It includes tools for database design, database maintenance, testing, data 
transfer,
data import & export, database migration, database compare and numerous other 
tools.


About Database Workbench
Database Workbench is a database developer tool, over 12 years in the making and
is being used by thousands of developers across the globe who have come to rely 
on it
every day. From database design, implementation, to testing and debugging, it 
will aid you 
in your daily database work.

About Upscene Productions
Based in The Netherlands, Europe, this small but dedicated company has been 
providing
database developers with useful tools for over 14 years. Slowly expanding the 
product portfolio
and gaining recognition amongst InterBase and Firebird database developers, 
they now offer
tools for a whole range of database systems, including Oracle and Microsoft SQL 
Server.

[GENERAL] Create recursive view schema.name

[Lele Gaifax]

Hi all,

I'm using PG 9.6, learning the "recursive" queries.

I have a working recursive-CTE query, and I tried wrapping it in a view:
reading the documentation I found the "CREATE RECURSIVE VIEW" form, so I tried
it.

It works as far as I use a "simple" name for the view:

  CREATE OR REPLACE RECURSIVE VIEW procedure_sites (procedure_id, site_id) AS
SELECT pr.id, pr.site_id
FROM sop.procedures pr
  JOIN sop.phases ph on ph.procedure_id = pr.id

UNION ALL

SELECT s.procedure_id, ss.site_id
FROM procedure_sites s
  JOIN risk.company_sites ss ON ss.id = s.site_id
WHERE ss.site_id IS NOT NULL;

but I get an error when I create it in a specific schema:

  CREATE OR REPLACE RECURSIVE VIEW sop.procedure_sites (procedure_id, site_id) 
AS
SELECT pr.id, pr.site_id
FROM sop.procedures pr
  JOIN sop.phases ph on ph.procedure_id = pr.id

UNION ALL

SELECT s.procedure_id, ss.site_id
FROM sop.procedure_sites s
  JOIN risk.company_sites ss ON ss.id = s.site_id
WHERE ss.site_id IS NOT NULL;

  ERROR:  relation "sop.procedure_sites" does not exist
  RIGA 8: JOIN sop.procedure_sites s ON s.site_id = ss.id
   ^
   
Am I missing something?

Thanks in advance for any hint,
ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.



-- 
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] ANN: Upscene releases Database Workbench 5.2.4

[John R Pierce]

On 10/11/2016 12:09 AM, Martijn Tonies (Upscene Productions) wrote:

Upscene Productions is proud to announce the availability of
the next version of the popular multi-DBMS development tool:
" Database Workbench 5.2.4 "
The 5.2 release includes support for PostgreSQL and adds several other 
features and bugfixes.
Database Workbench 5 comes in multiple editions with different pricing 
models, there's always a version that suits you!


I'm not sure announcements of commercial software updates belong on the 
pgsql-general email list.   if every vendor of  apackage with postgres 
support posted announcements of each incremental update, we'd be buried 
in spam.




--
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

[GENERAL] ALTER TABLE without long waiting is possibly ?

[Condor]

Hello,

today I need to alter one of our biggest tables to add two new columns 
with default value 0.
Server version: PostgreSQL 9.5.4 on x86_64-slackware-linux-gnu, compiled 
by x86_64-slackware-linux-gcc (GCC) 5.3.0, 64-bit


when I do: ALTER TABLE stocks ADD COLUMN promo INTEGER DEFAULT 0;

long waiting is coming, so I try to find a way how to avoid that 
waiting. I know isn't possibly to alter table without lock it,

but Im wondering do will be more fast if I do:

ALTER TABLE stocks ADD COLUMN promo INTEGER;
UPDATE TABLE stocks SET promo = 0;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;


Unfortunately I can't test on product servers, so Im looking for some 
advice or some one to point me the right direction how I can alter table 
today without clients to notice their query is locked and need to wait.



Regards,
Hristo S.


--
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] Transactional-DDL DROP/CREATE TABLE

[Geoff Winkless]

On 10 October 2016 at 14:49, Merlin Moncure  wrote:
> MVCC rules (which DDL generally fall under) try to interleave work as
> much as possible which is the problem you're facing.

Mmff. Yes, that exposes a fundamental misunderstanding on my part: I
had thought that under MVCC things were done independently and
resolved at COMMIT time, as opposed to potentially-conflicting
transactions resulting in one transaction blocking _mid-transaction_
until the other resolves, as it does.

So I suppose the logic goes, it's not clear that the DROP / CREATE
results in a potential conflict until the table is created, so since
both transactions drop a non-existent table, they both then try to
create the table, and I get my error. I had thought that the DDL would
simply have its own view on the data and therefore be able to do all
of its work up to COMMIT, but on reconsidering I can see that the
amount of overhead involved in the COMMIT would be phenomenal.

Having said all of that, I'm confused as to why CREATE TABLE in tr1
doesn't block a subsequent DROP TABLE IF EXISTS in tr2.

So if, in two psql sessions you run (shown in order of execution):

tr1:
   BEGIN;
   DROP TABLE IF EXISTS mytable;
   CREATE TABLE mytable (test int);

tr2:
   BEGIN;
   DROP TABLE IF EXISTS mytable; -- could block here, no?
   CREATE TABLE mytable (test int); -- actually blocks here

> Also, this is not a good pattern.  You ought to be using temp tables
> or other mechanics to store transaction local data.

The data isn't transaction-local. Having said that, the _actual_
pattern is much worse than that, but it's not my design, I just have
to work with it.

Geoff


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

[GENERAL] confusion about user paring with pg_hba and pg_ident

[arnaud gaboury]

I am a little confused about some of my settings when it comes to map
linux/psql users.

I have two databases: mattermost and thetradinghall
I have two linux users: mattermost and dovecot. mattermost  is the one who
want to talk to mattermost and dovecot  the one who want to talk to
thetradinghall.
I have two postresql users: mmuser and mailman.

Here are the relevant part of my config files:

pg_hba.conf
---
 local   thetradinghall  mailman peer
map=mailmap
 local   mattermost  mmuser  peer
map=mattermap

pg_ident.conf
--
  mailmapdovecot mailman
  mattermap  mattermost  mmuser

* question: can I use same mapname for my both DB, or using two mapnames
like I did is the correct way?

Now testing:
-
bash-4.3$ whoami
mattermost
bash-4.3$ psql postgres:///mattermost?
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "mattermost",
database "mattermost", SSL off
---

I thought my settings told postgres that linux user mattermost was mapped
by psql user mmuser (which of course holds the mattermost DB), but it seems
it is not the case.
What do I do wrong?

Thank you for help.

Re: [GENERAL] ALTER TABLE without long waiting is possibly ?

[Vitaly Burovoy]

On 10/11/16, Condor  wrote:
>
> Hello,
>
> today I need to alter one of our biggest tables to add two new columns
> with default value 0.
> Server version: PostgreSQL 9.5.4 on x86_64-slackware-linux-gnu, compiled
> by x86_64-slackware-linux-gcc (GCC) 5.3.0, 64-bit
>
> when I do: ALTER TABLE stocks ADD COLUMN promo INTEGER DEFAULT 0;
>
> long waiting is coming,

The doc[1] explicitly explains why:
"Adding a column with a DEFAULT clause or changing the type of an
existing column will require the entire table and its indexes to be
rewritten."

> so I try to find a way how to avoid that
> waiting. I know isn't possibly to alter table without lock it,
> but Im wondering do will be more fast if I do:
>
> ALTER TABLE stocks ADD COLUMN promo INTEGER;
> UPDATE TABLE stocks SET promo = 0;
> ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;

You are close to the best solution but you should use "SET DEFAULT"
before update and split "UPDATE" into several commands to update
smaller parts of the table at a time, in the other case you ends up
with full rewrite of the table at once as the original "ALTER TABLE"
does.
All rows which has been updated are locked until the UPDATE commits,
so when your code tries to update or delete it, commands wait until
the UPDATE completes.

Usual solution looks like this (change _pk_column_ to a column(s) name
mentioned in the primary key of the table):

ALTER TABLE stocks ADD COLUMN promo INTEGER;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;  -- set for
inserted columns
CREATE INDEX CONCURRENTLY set_default_idx_tmp
ON stocks(_pk_column_) WHERE promo IS NULL;
-- repeat the next command (five lines!) until it returns 0 affected
rows (you can choose different LIMIT value):
UPDATE stocks s SET promo = 0 FROM (
SELECT _pk_column_ FROM stocks
WHERE promo IS NULL ORDER BY _pk_column_
FOR UPDATE LIMIT 1
)t WHERE s._pk_column_=t._pk_column_;


The "ORDER BY" clause allows you to decrease chance to block current
transactions by the UPDATE which sets the default value.

If you have PG 9.5 and higher, add "SKIP LOCKED" just after the "FOR
UPDATE" clause.

If your table is big enough you may run:
VACUUM VERBOSE stocks;
when 1/2 or 1/3 (and 2/3) table is done to mark old tuples as free
space and reuse it for new tuples generated by the next UPDATEs (and
prevent bloating table).

P.S.: then DROP INDEX CONCURRENTLY set_default_idx_tmp;

P.P.S.: If you have to add two columns you can update both of them by
one UPDATE:

ALTER TABLE stocks ADD COLUMN promo INTEGER;
ALTER TABLE stocks ADD COLUMN column2 INTEGER;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;  -- set for
inserted columns
ALTER TABLE stocks ALTER COLUMN column2 SET DEFAULT 65536;  -- whatever you need

CREATE INDEX CONCURRENTLY set_default_idx_tmp
ON stocks(_pk_column_) WHERE promo IS NULL AND column2 IS NULL;

-- repeat the next command (six lines!) until it returns 0 affected
rows (you can choose different LIMIT value):
UPDATE stocks s SET promo = DEFAULT, column2 = DEFAULT FROM (
SELECT _pk_column_ FROM stocks
WHERE promo IS NULL AND column2 IS NULL
ORDER BY _pk_column_
FOR UPDATE LIMIT 1
)t WHERE s._pk_column_=t._pk_column_;


> Unfortunately I can't test on product servers, so Im looking for some
> advice or some one to point me the right direction how I can alter table
> today without clients to notice their query is locked and need to wait.

[1] https://www.postgresql.org/docs/current/static/sql-altertable.html#AEN75605

-- 
Best regards,
Vitaly Burovoy


-- 
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] HA Cluster Solution?

[Andreas Kretschmer]

On 10 October 2016 21:14:55 CEST, Periko Support  
wrote:
>I'm trying to get better numbers, is a option in the table.
>Meanwhile I reading some system performance numbers.
>Yes odoo is strange sometimes.
>But a cluster will be good for HA.
>Thanks.
>
>

Please identify the problematic queries ( for instance using 
pg_stat_statements) and send this to the team behind odoo.

Andreas.
-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.


-- 
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] ALTER TABLE without long waiting is possibly ?

[Condor]

On 11-10-2016 15:59, Vitaly Burovoy wrote:

On 10/11/16, Condor  wrote:


Hello,

today I need to alter one of our biggest tables to add two new columns
with default value 0.
Server version: PostgreSQL 9.5.4 on x86_64-slackware-linux-gnu, 
compiled

by x86_64-slackware-linux-gcc (GCC) 5.3.0, 64-bit

when I do: ALTER TABLE stocks ADD COLUMN promo INTEGER DEFAULT 0;

long waiting is coming,


The doc[1] explicitly explains why:
"Adding a column with a DEFAULT clause or changing the type of an
existing column will require the entire table and its indexes to be
rewritten."


so I try to find a way how to avoid that
waiting. I know isn't possibly to alter table without lock it,
but Im wondering do will be more fast if I do:

ALTER TABLE stocks ADD COLUMN promo INTEGER;
UPDATE TABLE stocks SET promo = 0;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;


You are close to the best solution but you should use "SET DEFAULT"
before update and split "UPDATE" into several commands to update
smaller parts of the table at a time, in the other case you ends up
with full rewrite of the table at once as the original "ALTER TABLE"
does.
All rows which has been updated are locked until the UPDATE commits,
so when your code tries to update or delete it, commands wait until
the UPDATE completes.

Usual solution looks like this (change _pk_column_ to a column(s) name
mentioned in the primary key of the table):

ALTER TABLE stocks ADD COLUMN promo INTEGER;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;  -- set for
inserted columns
CREATE INDEX CONCURRENTLY set_default_idx_tmp
ON stocks(_pk_column_) WHERE promo IS NULL;
-- repeat the next command (five lines!) until it returns 0 affected
rows (you can choose different LIMIT value):
UPDATE stocks s SET promo = 0 FROM (
SELECT _pk_column_ FROM stocks
WHERE promo IS NULL ORDER BY _pk_column_
FOR UPDATE LIMIT 1
)t WHERE s._pk_column_=t._pk_column_;


The "ORDER BY" clause allows you to decrease chance to block current
transactions by the UPDATE which sets the default value.

If you have PG 9.5 and higher, add "SKIP LOCKED" just after the "FOR
UPDATE" clause.

If your table is big enough you may run:
VACUUM VERBOSE stocks;
when 1/2 or 1/3 (and 2/3) table is done to mark old tuples as free
space and reuse it for new tuples generated by the next UPDATEs (and
prevent bloating table).

P.S.: then DROP INDEX CONCURRENTLY set_default_idx_tmp;

P.P.S.: If you have to add two columns you can update both of them by
one UPDATE:

ALTER TABLE stocks ADD COLUMN promo INTEGER;
ALTER TABLE stocks ADD COLUMN column2 INTEGER;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;  -- set for
inserted columns
ALTER TABLE stocks ALTER COLUMN column2 SET DEFAULT 65536;  -- whatever 
you need


CREATE INDEX CONCURRENTLY set_default_idx_tmp
ON stocks(_pk_column_) WHERE promo IS NULL AND column2 IS NULL;

-- repeat the next command (six lines!) until it returns 0 affected
rows (you can choose different LIMIT value):
UPDATE stocks s SET promo = DEFAULT, column2 = DEFAULT FROM (
SELECT _pk_column_ FROM stocks
WHERE promo IS NULL AND column2 IS NULL
ORDER BY _pk_column_
FOR UPDATE LIMIT 1
)t WHERE s._pk_column_=t._pk_column_;



Unfortunately I can't test on product servers, so Im looking for some
advice or some one to point me the right direction how I can alter 
table
today without clients to notice their query is locked and need to 
wait.


[1] 
https://www.postgresql.org/docs/current/static/sql-altertable.html#AEN75605


--
Best regards,
Vitaly Burovoy




Thanks,
something like that was rotating in my mind, just was not sure do im in 
right direction.


Thanks again.

Hristo S.


--
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] confusion about user paring with pg_hba and pg_ident

[Adrian Klaver]

On 10/11/2016 04:25 AM, arnaud gaboury wrote:

I am a little confused about some of my settings when it comes to map
linux/psql users.

I have two databases: mattermost and thetradinghall
I have two linux users: mattermost and dovecot. mattermost  is the one
who want to talk to mattermost and dovecot  the one who want to talk to
thetradinghall.
I have two postresql users: mmuser and mailman.

Here are the relevant part of my config files:

pg_hba.conf
---
 local   thetradinghall  mailman peer
map=mailmap
 local   mattermost  mmuser  peer
map=mattermap

pg_ident.conf
--
  mailmapdovecot mailman
  mattermap  mattermost  mmuser

* question: can I use same mapname for my both DB, or using two mapnames
like I did is the correct way?


Yes:

https://www.postgresql.org/docs/9.5/static/auth-username-maps.html

"Since different mappings might be needed for different connections, the 
name of the map to be used is specified in the map-name parameter in 
pg_hba.conf to indicate which map to use for each individual connection."





Now testing:
-
bash-4.3$ whoami
mattermost
bash-4.3$ psql postgres:///mattermost?
psql: FATAL:  no pg_hba.conf entry for host "[local]", user
"mattermost", database "mattermost", SSL off
---

I thought my settings told postgres that linux user mattermost was
mapped by psql user mmuser (which of course holds the mattermost DB),
but it seems it is not the case.
What do I do wrong?


Did you remember to reload Postgres?



Thank you for help.






--
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] Create recursive view schema.name

[Tom Lane]

Lele Gaifax  writes:
> I have a working recursive-CTE query, and I tried wrapping it in a view:
> reading the documentation I found the "CREATE RECURSIVE VIEW" form, so I tried
> it.

> It works as far as I use a "simple" name for the view:
> but I get an error when I create it in a specific schema:

The manual says
CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
is equivalent to
CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) 
SELECT columns FROM name;

I guess it could be more explicit about the fact that the implied CTE just
has the base name of the view; but since CTE names can't be qualified,
that's not that hard to guess.  Short answer is that you don't qualify the
view's internal self-reference, even if you are using a schema name in the
CREATE.

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] confusion about user paring with pg_hba and pg_ident

[arnaud gaboury]

On Tue, Oct 11, 2016 at 3:45 PM Adrian Klaver 
wrote:

> On 10/11/2016 04:25 AM, arnaud gaboury wrote:
>
> > I am a little confused about some of my settings when it comes to map
>
> > linux/psql users.
>
> >
>
> > I have two databases: mattermost and thetradinghall
>
> > I have two linux users: mattermost and dovecot. mattermost  is the one
>
> > who want to talk to mattermost and dovecot  the one who want to talk to
>
> > thetradinghall.
>
> > I have two postresql users: mmuser and mailman.
>
> >
>
> > Here are the relevant part of my config files:
>
> >
>
> > pg_hba.conf
>
> > ---
>
> >  local   thetradinghall  mailman peer
>
> > map=mailmap
>
> >  local   mattermost  mmuser  peer
>
> > map=mattermap
>
> >
>
> > pg_ident.conf
>
> > --
>
> >   mailmapdovecot mailman
>
> >   mattermap  mattermost  mmuser
>
> >
>
> > * question: can I use same mapname for my both DB, or using two mapnames
>
> > like I did is the correct way?
>
>
>
> Yes:
>
>
>
> https://www.postgresql.org/docs/9.5/static/auth-username-maps.html
>
>
>
> "Since different mappings might be needed for different connections, the
>
> name of the map to be used is specified in the map-name parameter in
>
> pg_hba.conf to indicate which map to use for each individual connection."
>
>
>
>
>
> >
>
> > Now testing:
>
> > -
>
> > bash-4.3$ whoami
>
> > mattermost
>
> > bash-4.3$ psql postgres:///mattermost?
>
> > psql: FATAL:  no pg_hba.conf entry for host "[local]", user
>
> > "mattermost", database "mattermost", SSL off
>
> > ---
>
> >
>
> > I thought my settings told postgres that linux user mattermost was
>
> > mapped by psql user mmuser (which of course holds the mattermost DB),
>
> > but it seems it is not the case.
>
> > What do I do wrong?
>
>
>
> Did you remember to reload Postgres?
>

YES I did it this time

>
>
>
> >
>
> > Thank you for help.
>
> >
>
> >
>
> >
>
>
>
>
>
> --
>
> Adrian Klaver
>
> adrian.kla...@aklaver.com
>
>

Re: [GENERAL] confusion about user paring with pg_hba and pg_ident

[Adrian Klaver]

On 10/11/2016 06:47 AM, arnaud gaboury wrote:









Did you remember to reload Postgres?


YES I did it this time


My mistake, forget to look at your connection string:

psql postgres:///mattermost

You have not told Postgres what user you want mattermost to connect as.
It worked for you before, because you had this:

psql postgresql://mmuser:XXX@/mattermost?







>

> Thank you for help.

>

>

>





--

Adrian Klaver

adrian.kla...@aklaver.com 




--
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] Create recursive view schema.name

[Lele Gaifax]

Tom Lane  writes:

> The manual says
>   CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
>   is equivalent to
>   CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) 
> SELECT columns FROM name;
>
> I guess it could be more explicit about the fact that the implied CTE just
> has the base name of the view; but since CTE names can't be qualified,
> that's not that hard to guess.  Short answer is that you don't qualify the
> view's internal self-reference, even if you are using a schema name in the
> CREATE.

Thank you Tom, it works.

I agree with you that the doc could/should be fixed/enhanced, because the
explanation of "name" is immediately following the snippet you cited, and it
says "The name (optionally schema-qualified) of a view to be created": it
would never occurred to me that I could use a not-qualified name within the
view.

bye, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.



-- 
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] confusion about user paring with pg_hba and pg_ident

[Tom Lane]

arnaud gaboury  writes:
> I am a little confused about some of my settings when it comes to map
> linux/psql users.

I think you're misunderstanding what the user-mapping stuff does.
It does not silently translate the username in the connection request
to something else; rather, it checks whether a user having the given
external name is allowed to log in as a particular Postgres user.
So there's nothing particularly wrong with your config files, but your
expectation about how your Linux users should log in to the database is
mistaken.  dovecot needs to specify that it wants to log in as mailman,
and likewise mattermost needs to specify mmuser.

If it's not practical to make the client applications send non-default
user names, you'll need to rename the Postgres roles to match the
external user names.

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

[GENERAL] forcing a table (parent in inheritance) tor emain empty

[Rémi Cura]

Hey dear list,
I can't find a nice solution to enforce a necessary behaviour in my case :
I want a parent table to remain empty.

Of course I could define a trigger and return NULL in any case, but I'd
like a more elegant approach using check or constraints.

Any thought appreciated,
Cheers,
Rémi C

[GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[David A]

Hi,

My DB has has 5TB, it's on Google Cloud Compute, mostly on SSDs, part on
Standard Persistent Disks.

Querying is ok, but deleting, moving between tablespaces, dropping tables,
etc, etc is hugely slow
(note: I do have a number of indexes)

Instance RAM: 60GB
Instance CPU: 16Cores

I'm just wondering - anyone has any thoughts or suggestions about
performance?
Any would be much appreciated
(I'm wandering if I just need faster instances/disks or if I'm doing
something wrong)

Any one has similar DB sizes? what CPU/RAM/Disks do you have?

Thanks,
David
Scala Academy

[GENERAL] BDR: changing dsn on a running node

[Natan Abolafya]

Hi

 

Is it possible to change the dsn connection string of a node without leaving
the group? I couldn't find the related documentation unfortunately.

We're using BDR in a dynamic environment where the hostname/ip of a node may
be changed any time. Leaving and rejoining the BDR group seems to be a
solution but is a bit tedious and a slow process.

 

Natan

 

 

 

[GENERAL] Postgresql using lxd faild to find address

[Scottix]

Hi I am using postgresql 9.5 in lxd container on Ubuntu. Using the stock
64bit 16.04 ubuntu image. Postgres is working just fine within the
container, but when I try to assign the ip address associated to the
container it fails to recognize it at boot. When I restart postgresql it
recognizes it just fine. So it seems like it is not getting the ip address,
maybe it is a little bit slower. Anyway to delay the boot time?

Steps to reproduce:
1. Setup lxd container
2. Install postgresql-9.5
3. Configure ip address in the config
4. restart container

Symptoms:
1. postgresql running fine
2. No ip address assigned to postgresql

Logs:
2016-10-10 14:40:33 UTC [143-1] LOG:  could not bind IPv4 socket: Cannot
assign requested address
2016-10-10 14:40:33 UTC [143-2] HINT:  Is another postmaster already
running on port 5432? If not, wait a few seconds and retry.
2016-10-10 14:40:33 UTC [143-3] WARNING:  could not create listen socket
for "10.0.3.51"
2016-10-10 14:40:33 UTC [144-1] LOG:  database system was shut down at
2016-10-07 23:05:34 UTC
2016-10-10 14:40:33 UTC [144-2] LOG:  MultiXact member wraparound
protections are now enabled
2016-10-10 14:40:33 UTC [143-4] LOG:  database system is ready to accept
connections
2016-10-10 14:40:33 UTC [148-1] LOG:  autovacuum launcher started

#systemctl restart postgresql

2016-10-10 15:17:33 UTC [2353-1] LOG:  database system was shut down at
2016-10-10 15:17:32 UTC
2016-10-10 15:17:33 UTC [2353-2] LOG:  MultiXact member wraparound
protections are now enabled
2016-10-10 15:17:33 UTC [2352-1] LOG:  database system is ready to accept
connections
2016-10-10 15:17:33 UTC [2357-1] LOG:  autovacuum launcher started

Thanks

Re: [GENERAL] forcing a table (parent in inheritance) tor emain empty

[Manuel Gómez]

On Tue, Oct 11, 2016 at 12:27 PM, Rémi Cura  wrote:
> Hey dear list,
> I can't find a nice solution to enforce a necessary behaviour in my case :
> I want a parent table to remain empty.
>
> Of course I could define a trigger and return NULL in any case, but I'd like
> a more elegant approach using check or constraints.

You could probably do it with a simple constraint:

postgres=# create table dum(check (false));
CREATE TABLE
postgres=# insert into dum default values;
ERROR:  new row for relation "dum" violates check constraint "dum_check"
DETAIL:  Failing row contains ().


-- 
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] BDR: changing dsn on a running node

[Sylvain MARECHAL]

Le 07/10/2016 à 23:54, Natan Abolafya a écrit :


Hi

Is it possible to change the dsn connection string of a node without 
leaving the group? I couldn’t find the related documentation 
unfortunately.


We’re using BDR in a dynamic environment where the hostname/ip of a 
node may be changed any time. Leaving and rejoining the BDR group 
seems to be a solution but is a bit tedious and a slow process.


Natan


Hi,

Just to say I did it with bdr 0.9.3 to change the password of the 
connection string.
I needed to manually modify the bdr.bdr_nodes and bdr.bdr_connections 
tables for all nodes but finally it works.

What I found strange is that the changes were not replicated automatically.
(Note there is a bdr.bdr_connections_changed() stored procedure, I am 
not sure this is needed, I did not use it)


My 2 cents,
Sylvain

Re: [GENERAL] forcing a table (parent in inheritance) tor emain empty

[Rémi Cura]

This solution is very nice.
Sadly the check is inherited by the children
(I only want the parent to be empty, not the children).

It seems the element that are not inherited are

   - Indexes
   - Unique constraints
   - Primary Keys
   - Foreign keys
   - Rules and Triggers


thanks anyway for the fast answer,
Cheers,
Rémi C

2016-10-11 18:33 GMT+02:00 Manuel Gómez :

> On Tue, Oct 11, 2016 at 12:27 PM, Rémi Cura  wrote:
> > Hey dear list,
> > I can't find a nice solution to enforce a necessary behaviour in my case
> :
> > I want a parent table to remain empty.
> >
> > Of course I could define a trigger and return NULL in any case, but I'd
> like
> > a more elegant approach using check or constraints.
>
> You could probably do it with a simple constraint:
>
> postgres=# create table dum(check (false));
> CREATE TABLE
> postgres=# insert into dum default values;
> ERROR:  new row for relation "dum" violates check constraint "dum_check"
> DETAIL:  Failing row contains ().
>

Re: [GENERAL] forcing a table (parent in inheritance) tor emain empty

[Julien Rouhaud]

On 11/10/2016 19:04, Rémi Cura wrote:
> This solution is very nice.
> Sadly the check is inherited by the children
> (I only want the parent to be empty, not the children).
> 
> It seems the element that are not inherited are
> 
>   * Indexes
>   * Unique constraints
>   * Primary Keys
>   * Foreign keys
>   * Rules and Triggers 
> 

you can specify a "NO INHERIT" on the check constraint, that should
solve your issue.

-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


-- 
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] forcing a table (parent in inheritance) tor emain empty

[Rémi Cura]

Perfect !

Cheers,
Rémi C

2016-10-11 19:12 GMT+02:00 Julien Rouhaud :

> On 11/10/2016 19:04, Rémi Cura wrote:
> > This solution is very nice.
> > Sadly the check is inherited by the children
> > (I only want the parent to be empty, not the children).
> >
> > It seems the element that are not inherited are
> >
> >   * Indexes
> >   * Unique constraints
> >   * Primary Keys
> >   * Foreign keys
> >   * Rules and Triggers
> >
>
> you can specify a "NO INHERIT" on the check constraint, that should
> solve your issue.
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
>

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Francisco Olarte]

On Thu, Oct 6, 2016 at 12:36 AM, David A  wrote:
> My DB has has 5TB, it's on Google Cloud Compute, mostly on SSDs, part on
> Standard Persistent Disks.
> Querying is ok, but deleting, moving between tablespaces, dropping tables,
> etc, etc is hugely slow
> (note: I do have a number of indexes)

Are you sure it's slow and not just lengthy? Hostings tend to have and
publish limits on their bandwidths and similar things, and may be you
are just saturating your capacity. If you can at least compare the
time to move a tablespace against the time of copying a similarly
sized file between the same disks someone may be able to say
something.

> Instance RAM: 60GB
> Instance CPU: 16Cores

Cores do not help, postgres is single-threaded. RAM MAY help, but I
suspect your operations are IO bound. Of course, with the sparseness
of the details, one can not say too much.

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] Postgresql using lxd faild to find address

[Scott Mead]

On Mon, Oct 10, 2016 at 11:25 AM, Scottix  wrote:

> Hi I am using postgresql 9.5 in lxd container on Ubuntu. Using the stock
> 64bit 16.04 ubuntu image. Postgres is working just fine within the
> container, but when I try to assign the ip address associated to the
> container it fails to recognize it at boot. When I restart postgresql it
> recognizes it just fine. So it seems like it is not getting the ip address,
> maybe it is a little bit slower. Anyway to delay the boot time?
>
> Steps to reproduce:
> 1. Setup lxd container
> 2. Install postgresql-9.5
> 3. Configure ip address in the config
> 4. restart container
>
> Symptoms:
> 1. postgresql running fine
> 2. No ip address assigned to postgresql
>
> Logs:
> 2016-10-10 14:40:33 UTC [143-1] LOG:  could not bind IPv4 socket: Cannot
> assign requested address
> 2016-10-10 14:40:33 UTC [143-2] HINT:  Is another postmaster already
> running on port 5432? If not, wait a few seconds and retry.
> 2016-10-10 14:40:33 UTC [143-3] WARNING:  could not create listen socket
> for "10.0.3.51"
>

Set your listen_addresses='*'
  This means, any and all available IP addresses.  If you hardcode the IP
in the config, you'll get this type of error.  This way, no matter what
interfaces you have, you'll get a bind.  The downside is that it will
listen on ALL network interfaces you have in the box.  Sometimes you
explicitly don't want a certain interface to be listening.  You can fix
this with a firewall or pg_hba.conf however.

--Scott



> 2016-10-10 14:40:33 UTC [144-1] LOG:  database system was shut down at
> 2016-10-07 23:05:34 UTC
> 2016-10-10 14:40:33 UTC [144-2] LOG:  MultiXact member wraparound
> protections are now enabled
> 2016-10-10 14:40:33 UTC [143-4] LOG:  database system is ready to accept
> connections
> 2016-10-10 14:40:33 UTC [148-1] LOG:  autovacuum launcher started
>
> #systemctl restart postgresql
>
> 2016-10-10 15:17:33 UTC [2353-1] LOG:  database system was shut down at
> 2016-10-10 15:17:32 UTC
> 2016-10-10 15:17:33 UTC [2353-2] LOG:  MultiXact member wraparound
> protections are now enabled
> 2016-10-10 15:17:33 UTC [2352-1] LOG:  database system is ready to accept
> connections
> 2016-10-10 15:17:33 UTC [2357-1] LOG:  autovacuum launcher started
>
> Thanks
>
>
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Melvin Davidson]

On Tue, Oct 11, 2016 at 2:17 PM, Francisco Olarte 
wrote:

> On Thu, Oct 6, 2016 at 12:36 AM, David A  wrote:
> > My DB has has 5TB, it's on Google Cloud Compute, mostly on SSDs, part on
> > Standard Persistent Disks.
> > Querying is ok, but deleting, moving between tablespaces, dropping
> tables,
> > etc, etc is hugely slow
> > (note: I do have a number of indexes)
>
> Are you sure it's slow and not just lengthy? Hostings tend to have and
> publish limits on their bandwidths and similar things, and may be you
> are just saturating your capacity. If you can at least compare the
> time to move a tablespace against the time of copying a similarly
> sized file between the same disks someone may be able to say
> something.
>
> > Instance RAM: 60GB
> > Instance CPU: 16Cores
>
> Cores do not help, postgres is single-threaded. RAM MAY help, but I
> suspect your operations are IO bound. Of course, with the sparseness
> of the details, one can not say too much.
>
> 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
>

You have stated a very generic problem and not provided  much detail.
Perhaps you could be a "little" more specific and give needed info we might
be able to help.
IE:
Which version of PostgreSQL?
What are the memory parameters in postgresql.conf (shared_memory,
max_connections, work_mem, maintenance_work_mem, etc..) ?
How big are the tables being moved?
What type of disks
etc.

FYI, moving between tablespaces requires an exclusive table lock, so it's
naturally going to be slow.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Rakesh Kumar]

>Cores do not help, postgres is single-threaded. RAM MAY help, but I

I hope this is no longer true from 9.6 for those queries where PG can use 
parallelism.

>suspect your operations are IO bound. Of course, with the sparseness
>of the details, one can not say too much.



-- 
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] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Lucas Possamai]

>
> Instance RAM: 60GB
> Instance CPU: 16Cores



I got a 3TB Postgres Database running with a Dual Socket Octo Core Intel
Xeon E5-2630v3 with 128GB of RAM and SATA disks.

I think yes, you could improve your server's RAM. However Melvin is right,
there is no enough information.

Lucas

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Francisco Olarte]

Melvin:

On Tue, Oct 11, 2016 at 8:33 PM, Melvin Davidson  wrote:

> FYI, moving between tablespaces requires an exclusive table lock, so it's 
> naturally going to be slow.

Requiring and exclusive table lock does not imply slownes. Just try
'lock table x in exclusive mode' on an idle system. Pretty fast.

The lock may lengthen the operation due to potential delays.

And moving between tablespaces on a big db may well be a fast ( speed
) but long ( time ) process, lots of things have to be done, but they
can be done rather fast. But even if light is fast, it still needs 550
years to reach Antares. You can have a short slow operation ( like
needing a second to get an indexed tuple ) or a fast long one ( like
needing an hour to move a petabyte of data ).

To judge if its going slow we will need a reference, like, how much
time does it take to copy and sync a big uncached file between the
affected volumes. If move does say, 1.5 times slower I wouldn't say it
is that slow ( given copy is optimized for this kind of transfers and
a database not so much ).

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] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Francisco Olarte]

Rakesh:

On Tue, Oct 11, 2016 at 9:00 PM, Rakesh Kumar
 wrote:
>>Cores do not help, postgres is single-threaded. RAM MAY help, but I
> I hope this is no longer true from 9.6 for those queries where PG can use 
> parallelism.

It does, AFAIK, but for queries, not AFAIK for this kind of data
moving ops ( and I doubt it will, as presently you can easily saturate
the channels with a single core for that kind of simple ops, and
normally if you want to optimize this kind of op is better to target
concurrency ( table can be used while moving ) than pure speed .

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

[GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

[Jason Dusek]

Hi All,

I notice the following oddity:

 =# CREATE TABLE with_pk (i integer PRIMARY KEY);CREATE TABLE

 =# BEGIN;BEGIN
 =# INSERT INTO with_pk VALUES (1) ON CONFLICT DO NOTHING;INSERT 0 1
 =# INSERT INTO with_pk VALUES (1) ON CONFLICT DO NOTHING;INSERT 0 0
 =# END;COMMIT

 =# BEGIN;BEGIN
 =# INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING;
ERROR:  could not serialize access due to concurrent update
 =# END;ROLLBACK

How are these two transactions different?

Kind Regards,

  Jason Dusek
​

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

[Melvin Davidson]

On Tue, Oct 11, 2016 at 3:16 PM, Francisco Olarte 
wrote:

> Rakesh:
>
> On Tue, Oct 11, 2016 at 9:00 PM, Rakesh Kumar
>  wrote:
> >>Cores do not help, postgres is single-threaded. RAM MAY help, but I
> > I hope this is no longer true from 9.6 for those queries where PG can
> use parallelism.
>
> It does, AFAIK, but for queries, not AFAIK for this kind of data
> moving ops ( and I doubt it will, as presently you can easily saturate
> the channels with a single core for that kind of simple ops, and
> normally if you want to optimize this kind of op is better to target
> concurrency ( table can be used while moving ) than pure speed .
>
> Francisco Olarte.
>

>Requiring and exclusive table lock does not imply slownes. Just try
>'lock table x in exclusive mode' on an idle system. Pretty fast.

Sure on an idle system, you will get a table lock right away, but OP's
statements imply a large busy system.
And if there are transactions occurring against that table, there is no
telling how long it will take. Since we
do not have enough specific info, I stand by my statement.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

[Chris Richards]

What more would you like--I'm happy to oblige? The upgrade steps I listed
installed into a new directory, new conf file. I didn't even migrate my
(skeletal) 9.3 database. Here's the 9.5 command-line. I've attached the
referenced config file.

$ chrisr@ff1:~$ ps ax | grep bin/post
10 ?S  0:00 /usr/lib/postgresql/9.5/bin/postgres -D
/var/lib/postgresql/9.5/main -c
config_file=/etc/postgresql/9.5/main/postgresql.conf

Cheers,
Chris


On Mon, Oct 10, 2016 at 5:21 PM, Tom Lane  wrote:

> Chris Richards  writes:
> > Setting up postgresql-9.5 (9.5.4-1.pgdg14.04+2) ...
> > Creating new cluster 9.5/main ...
> >   config /etc/postgresql/9.5/main
> >   data   /var/lib/postgresql/9.5/main
> >   locale en_US.UTF-8
> > LOG:  munmap(0x7fff8000) failed: Invalid argument
> > [... snip 14 or so repeats ...]
> > LOG:  munmap(0x7fff8000) failed: Invalid argument
> >   socket /var/run/postgresql
> >   port   5433
> > update-alternatives: using
> > /usr/share/postgresql/9.5/man/man1/postmaster.1.gz to provide
> > /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
> >  * Starting PostgreSQL 9.5 database server
> >   [ OK ]
> > Processing triggers for libc-bin (2.19-0ubuntu6.6) ...
>
> > I'm able to connect and I dumped a few default relations.
>
> > Is the munmap error of concern? It remains upon rebooting / restarting
> the
> > server.
>
> Seems pretty fishy to me; I don't know what would be causing it.
>
> [ digs in code... ]  One theory is that PGSharedMemoryDetach is getting
> called more than once, but I'm not sure how that would happen.  Can you
> characterize where this happens more precisely?  What nondefault settings
> have you got in postgresql.conf?
>
> regards, tom lane
>


postgresql.conf
Description: Binary 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] LOG: munmap(0x7fff80000000) failed: Invalid argument

[Chris Richards]

One extra bit, if this helps:

chrisr@ff1:~$ cat /proc/meminfo
MemTotal:8163104 kB
MemFree: 2183692 kB
MemAvailable:3648680 kB
Buffers:  170080 kB
Cached:  1231708 kB
SwapCached:0 kB
Active:  1083596 kB
Inactive: 442312 kB
Active(anon): 125128 kB
Inactive(anon): 3584 kB
Active(file): 958468 kB
Inactive(file):   438728 kB
Unevictable:   0 kB
Mlocked:   0 kB
SwapTotal:499708 kB
SwapFree: 499708 kB
Dirty: 0 kB
Writeback: 0 kB
AnonPages:124124 kB
Mapped:42472 kB
Shmem:  4596 kB
Slab: 144788 kB
SReclaimable:  97612 kB
SUnreclaim:47176 kB
KernelStack:   10672 kB
PageTables: 6332 kB
NFS_Unstable:  0 kB
Bounce:0 kB
WritebackTmp:  0 kB
CommitLimit: 2484108 kB
Committed_AS: 900632 kB
VmallocTotal:   34359738367 kB
VmallocUsed:  202276 kB
VmallocChunk:   34359464540 kB
HardwareCorrupted: 0 kB
AnonHugePages: 0 kB
HugePages_Total:   4
HugePages_Free:3
HugePages_Rsvd:0
HugePages_Surp:0
Hugepagesize:1048576 kB
DirectMap4k:   83776 kB
DirectMap2M: 4110336 kB
DirectMap1G: 6291456 kB

On Mon, Oct 10, 2016 at 5:21 PM, Tom Lane  wrote:

> Chris Richards  writes:
> > Setting up postgresql-9.5 (9.5.4-1.pgdg14.04+2) ...
> > Creating new cluster 9.5/main ...
> >   config /etc/postgresql/9.5/main
> >   data   /var/lib/postgresql/9.5/main
> >   locale en_US.UTF-8
> > LOG:  munmap(0x7fff8000) failed: Invalid argument
> > [... snip 14 or so repeats ...]
> > LOG:  munmap(0x7fff8000) failed: Invalid argument
> >   socket /var/run/postgresql
> >   port   5433
> > update-alternatives: using
> > /usr/share/postgresql/9.5/man/man1/postmaster.1.gz to provide
> > /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
> >  * Starting PostgreSQL 9.5 database server
> >   [ OK ]
> > Processing triggers for libc-bin (2.19-0ubuntu6.6) ...
>
> > I'm able to connect and I dumped a few default relations.
>
> > Is the munmap error of concern? It remains upon rebooting / restarting
> the
> > server.
>
> Seems pretty fishy to me; I don't know what would be causing it.
>
> [ digs in code... ]  One theory is that PGSharedMemoryDetach is getting
> called more than once, but I'm not sure how that would happen.  Can you
> characterize where this happens more precisely?  What nondefault settings
> have you got in postgresql.conf?
>
> regards, tom lane
>

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

[Kevin Grittner]

On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek  wrote:

> I notice the following oddity:

>  =# CREATE TABLE with_pk (i integer PRIMARY KEY);
> CREATE TABLE

>  =# BEGIN;
> BEGIN
>  =# INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING;
> ERROR:  could not serialize access due to concurrent update
>  =# END;
> ROLLBACK

I don't see that on development HEAD.  What version are you
running?  What is your setting for default_transaction_isolation?

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


-- 
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] LOG: munmap(0x7fff80000000) failed: Invalid argument

[Chris Richards]

Oh the email spam :P

I did another reboot test to validate the error. It does exist; it does not
spam munmap error like it did with the aptitude install.

I then marked the log file, shutdown (1 munmap during), marked it again and
then started it (1 munmap during).

MARK PRE-SHUTDOWN
2016-10-11 20:02:45 UTC [1656-2] LOG:  received fast shutdown request
2016-10-11 20:02:45 UTC [1656-3] LOG:  aborting any active transactions
2016-10-11 20:02:45 UTC [1707-2] LOG:  autovacuum launcher shutting down
2016-10-11 20:02:45 UTC [1704-1] LOG:  shutting down
2016-10-11 20:02:45 UTC [1704-2] LOG:  database system is shut down
2016-10-11 20:02:45 UTC [1656-4] LOG:  munmap(0x7fff8000) failed:
Invalid argument
MARK PRE-START
2016-10-11 20:03:02 UTC [9894-1] LOG:  database system was shut down at
2016-10-11 20:02:45 UTC
2016-10-11 20:03:02 UTC [9894-2] LOG:  MultiXact member wraparound
protections are now enabled
2016-10-11 20:03:02 UTC [9893-1] LOG:  database system is ready to accept
connections
2016-10-11 20:03:02 UTC [9898-1] LOG:  autovacuum launcher started
2016-10-11 20:03:02 UTC [9899-1] LOG:  munmap(0x7fff8000) failed:
Invalid argument
2016-10-11 20:03:02 UTC [9900-1] [unknown]@[unknown] LOG:  incomplete
startup packet


On Mon, Oct 10, 2016 at 5:21 PM, Tom Lane  wrote:

> Chris Richards  writes:
> > Setting up postgresql-9.5 (9.5.4-1.pgdg14.04+2) ...
> > Creating new cluster 9.5/main ...
> >   config /etc/postgresql/9.5/main
> >   data   /var/lib/postgresql/9.5/main
> >   locale en_US.UTF-8
> > LOG:  munmap(0x7fff8000) failed: Invalid argument
> > [... snip 14 or so repeats ...]
> > LOG:  munmap(0x7fff8000) failed: Invalid argument
> >   socket /var/run/postgresql
> >   port   5433
> > update-alternatives: using
> > /usr/share/postgresql/9.5/man/man1/postmaster.1.gz to provide
> > /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
> >  * Starting PostgreSQL 9.5 database server
> >   [ OK ]
> > Processing triggers for libc-bin (2.19-0ubuntu6.6) ...
>
> > I'm able to connect and I dumped a few default relations.
>
> > Is the munmap error of concern? It remains upon rebooting / restarting
> the
> > server.
>
> Seems pretty fishy to me; I don't know what would be causing it.
>
> [ digs in code... ]  One theory is that PGSharedMemoryDetach is getting
> called more than once, but I'm not sure how that would happen.  Can you
> characterize where this happens more precisely?  What nondefault settings
> have you got in postgresql.conf?
>
> regards, tom lane
>

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

[Jason Dusek]

SELECT version(),
   (SELECT setting FROM pg_settings WHERE name =
'default_transaction_deferrable') AS default_transaction_deferrable,
   (SELECT setting FROM pg_settings WHERE name =
'default_transaction_isolation') AS default_transaction_isolation;
─[ RECORD 1 
]──┬─
version│ PostgreSQL 9.5.4 on
x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0
(clang-800.0.38), 64-bit
default_transaction_deferrable │ on
default_transaction_isolation  │ serializable

​

On Tue, 11 Oct 2016 at 13:00 Kevin Grittner  wrote:

> On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek 
> wrote:
>
> > I notice the following oddity:
>
> >  =# CREATE TABLE with_pk (i integer PRIMARY KEY);
> > CREATE TABLE
>
> >  =# BEGIN;
> > BEGIN
> >  =# INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING;
> > ERROR:  could not serialize access due to concurrent update
> >  =# END;
> > ROLLBACK
>
> I don't see that on development HEAD.  What version are you
> running?  What is your setting for default_transaction_isolation?
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

[Tom Lane]

Chris Richards  writes:
> Oh the email spam :P

No problem.  I'm starting to think that this isn't actually a Postgres
bug, but rather something funny with your kernel.  The code in sysv_shmem
is certainly as fragile as can be, but I'm darned if I can find an
existing code path that would actually break it --- and the lack of other
complaints is pointing towards something funny with your system, too.

First off, does changing "huge_pages" in postgresql.conf alter the
behavior?  (It defaults to "try", see what happens with "on" or "off".)

Second, I notice that you've got

> Hugepagesize:1048576 kB

which is just enormous.  PG is only going to request circa 140MB given
the settings you mentioned.  We've seen reports of kernel bugs that cause
mmap() to fail for requests that aren't a multiple of the hugepage size,
but I've not heard that mmap() might succeed and then munmap() fail.
That seems like what's happening to you though.

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] BDR: changing dsn on a running node

[Craig Ringer]

On 12 October 2016 at 00:55, Sylvain MARECHAL
 wrote:
> Le 07/10/2016 à 23:54, Natan Abolafya a écrit :
>
> Is it possible to change the dsn connection string of a node without leaving
> the group? I couldn’t find the related documentation unfortunately.
>
> We’re using BDR in a dynamic environment where the hostname/ip of a node may
> be changed any time. Leaving and rejoining the BDR group seems to be a
> solution but is a bit tedious and a slow process.

You can update the bdr.bdr_connections entry and then kill the apply workers.

Right now bdr.bdr_connections_changed() doesn't know to check for a
changed DSN. I'd welcome a patch to address that, since I probably
won't have time to get to it soon.

We should have a bdr.bdr_connection_set_dsn(...) function, really.
Again, a patch would be welcomed.

-- 
 Craig Ringer   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

[GENERAL] pgpool fail to load balance after database restart

[Dylan Luong]

Hi,
I am new to pgpool. I have just installed pgpool and configured  Master/Slave 
mode with "stream" sub_mode. I have enabled load balance as that is the main 
feature we want test out. I have enabled Health Check. The load balance appears 
to work as I can see connections to both Master and Slave database.
pgpool is installed on a separate server. (linux Redhat 6)
ie: server1 = pgpool
server2 = Master
server3= Slave
(wal streaming replication)

I tested two scenarios and it failing:

1.  When I stop/start the slave database, pgpool stops load balancing. It 
looks like it doesn't know that it available again after the slave comes backup 
online. Even after I restart pgpool it doesn't load balance. It just sends all 
queries to the Master.

2.  I tried shutting down slave db and then shut down master db (no 
failover) and restart master db and restart slave, pgpool failed to reconnect 
to the master or the slave. I get database unavailable errors. However, if I 
restart pgpool for this scenario, its all fine.

It appears that pgpool is not doing the healthcheck correctly?? Or have I 
missed something here??

Here are my main pgpool.conf changes:

#--
# CONNECTIONS
#--

# - pgpool Connection Settings -

listen_addresses = '*'
   # Host name or IP address to listen on:
   # '*' for all, '' for no TCP/IP connections
   # (change requires restart)
#port = 
port = 5432
   # Port number
   # (change requires restart)
socket_dir = '/var/run/postgresql'
   # Unix domain socket path
   # The Debian package defaults to
   # /var/run/postgresql
   # (change requires restart)
listen_backlog_multiplier = 2
   # Set the backlog parameter of listen(2) to
   # 
num_init_children * listen_backlog_multiplier.
   # (change requires restart)
serialize_accept = off
   # whether to serialize accept() call to 
avoid thundering herd problem
   # (change requires restart)

# - pgpool Communication Manager Connection Settings -

pcp_listen_addresses = '*'
   # Host name or IP address for pcp process to 
listen on:
   # '*' for all, '' for no TCP/IP connections
   # (change requires restart)
pcp_port = 9898
   # Port number for pcp
   # (change requires restart)
pcp_socket_dir = '/var/run/postgresql'
   # Unix domain socket path for pcp
   # The Debian package defaults to
   # /var/run/postgresql
   # (change requires restart)

# - Backend Connection Settings -

backend_hostname0 = '10.69.20.11'
   # Host name or IP address to connect to for 
backend 0
backend_port0 = 5432
   # Port number for backend 0
backend_weight0 = 1
   # Weight for backend 0 (only in load 
balancing mode)
backend_data_directory0 = '/var/lib/pgsql/9.5/data'
   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
   # Controls various backend behavior
   # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = '10.68.20.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.5/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'


#--
# POOLS
#--

# - Concurrent session and pool size -

num_init_children = 32
   # Number of concurrent sessions allowed
   # (change requires restart)
max_pool = 4
   # Number of connection pool caches per 
connection
   # (change requires restart)

# - Life time -

child_life_time = 300
   # Pool exits after being idle for this many 
seconds
child_max_connections = 0
   # Pool exits after receiving that many 
connections
   # 0 means no exit
connection_life_time = 0
   # Connection to b

Re: [GENERAL] pgpool fail to load balance after database restart

[Michael Paquier]

On Wed, Oct 12, 2016 at 10:04 AM, Dylan Luong  wrote:
> I am new to pgpool.

You may want to ask directly on the mailing lists of pgpool, even if
Tatsuo-san or anybody involved in it are looking at this mailing list:
http://pgpool.net/mediawiki/index.php/Mailing_lists
-- 
Michael


-- 
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] ANN: Upscene releases Database Workbench 5.2.4

[Michael Paquier]

On Tue, Oct 11, 2016 at 5:18 PM, John R Pierce  wrote:
> On 10/11/2016 12:09 AM, Martijn Tonies (Upscene Productions) wrote:
>>
>> Upscene Productions is proud to announce the availability of
>> the next version of the popular multi-DBMS development tool:
>> " Database Workbench 5.2.4 "
>> The 5.2 release includes support for PostgreSQL and adds several other
>> features and bugfixes.
>> Database Workbench 5 comes in multiple editions with different pricing
>> models, there's always a version that suits you!
>
>
> I'm not sure announcements of commercial software updates belong on the
> pgsql-general email list.   if every vendor of  apackage with postgres
> support posted announcements of each incremental update, we'd be buried in
> spam.

Yeah.. If it applies somewhere that would be pgsql-announce.
-- 
Michael


-- 
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] pgpool fail to load balance after database restart

[Tatsuo Ishii]

Michael,

He already posted the identical question to the Pgpool-II mailing list
yesterday.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> On Wed, Oct 12, 2016 at 10:04 AM, Dylan Luong  
> wrote:
>> I am new to pgpool.
> 
> You may want to ask directly on the mailing lists of pgpool, even if
> Tatsuo-san or anybody involved in it are looking at this mailing list:
> http://pgpool.net/mediawiki/index.php/Mailing_lists
> -- 
> Michael
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] ANN: Upscene releases Database Workbench 5.2.4

[Martijn Tonies (Upscene Productions)]

Upscene Productions is proud to announce the availability of
the next version of the popular multi-DBMS development tool:
" Database Workbench 5.2.4 "
The 5.2 release includes support for PostgreSQL and adds several other
features and bugfixes.
Database Workbench 5 comes in multiple editions with different pricing
models, there's always a version that suits you!



I'm not sure announcements of commercial software updates belong on the
pgsql-general email list.   if every vendor of  apackage with postgres
support posted announcements of each incremental update, we'd be buried 
in

spam.


Yeah.. If it applies somewhere that would be pgsql-announce.


For the record, it was my understanding pgsql-announce was by PostgreSQL
only.



With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird. 




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