Re: [GENERAL] Is there a way to fix this ugliness

[Tim Uckun]

I could not make coalesce in the top level select to work even though
people on the internet say it should work. I'll do the sub select that
seems like it would work fine.

On Sat, Sep 10, 2016 at 3:22 AM, Karl Czajkowski  wrote:

> On Sep 10, Tim Uckun modulated:
> > I am trying to get the child elements of a one to many table to be
> > rolled up into a json field in the parent table. The query I am running
> > is...
>
> The problem is aggregating over the results of the left-outer join,
> which introduces NULLs. You can try pushing that down into a sub-query
> to create one image row per observation prior to joining:
>
>   SELECT
> ob.id,
> im.images
>   FROM observations ob
>   LEFT OUTER JOIN (
> SELECT
>   observation_id,
>   json_agg(row_to_json(im.*)) AS images
> FROM images im
> GROUP BY observation_id
>   ) im ON (ob.id = im.observation_id) ;
>
> you might use COALESCE in the top-level SELECT if you want to replace
> any NULL im.images with a different empty value constant...
>
>
> Karl
>
>

Re: [GENERAL] Setup pgpool-II with streaming replication

[Ashish Chauhan]

Thanks Venkata for your reply.

Currently, we have two Postgres server with master-slave streaming replication. 
All application servers are directly pointing to master server only. I am 
planning to setup new server for PgPool, at the time of divert app server to 
PgPool, I will pool out application server one by one from Load balancer and 
point it to PgPool which will not affect our application.

Could you pls send me steps how to install PgPool without PgPoolAdmin?

Thanks,
Ashish

From: Venkata B Nagothi [mailto:nag1...@gmail.com]
Sent: Wednesday, September 07, 2016 5:46 PM, 17:46
To: Ashish Chauhan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setup pgpool-II with streaming replication



I gone through below articles but some or other way require DB server restart 
which is not possible in our case. Could you please provide some documentation 
or steps how to achive connection pooling without any downtime?

DB server restart ? Why do you think you need to restart the database ? that 
can be confirmed if you could help us know your planned architecture and the 
existing database configuration settings.
Yes, downtime is definitely needed as the Application needs to connect to the 
database using pgPool port instead of DB port which is something to do with the 
Application server.
Regards,
Venkata B N
Fujitsu Australia

Re: [GENERAL] Is there a way to fix this ugliness

[Karl Czajkowski]

On Sep 10, Tim Uckun modulated:
> I am trying to get the child elements of a one to many table to be
> rolled up into a json field in the parent table. The query I am running
> is...

The problem is aggregating over the results of the left-outer join,
which introduces NULLs. You can try pushing that down into a sub-query
to create one image row per observation prior to joining:

  SELECT 
ob.id, 
im.images
  FROM observations ob
  LEFT OUTER JOIN (
SELECT 
  observation_id, 
  json_agg(row_to_json(im.*)) AS images
FROM images im
GROUP BY observation_id
  ) im ON (ob.id = im.observation_id) ;

you might use COALESCE in the top-level SELECT if you want to replace
any NULL im.images with a different empty value constant...


Karl



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

[GENERAL] Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

[Alexander Farber]

Good afternoon,

I have a question please.

In one table I store user ids and their IP addresses -

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL
);

And in another table I keep 2-player games and timestamps of last moves
(NULL if a player hasn't played yet):

CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
finished timestamptz,

player1 integer REFERENCES words_users(uid) ON DELETE
CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE
CASCADE,

played1 timestamptz,
played2 timestamptz
);

When a user wants to start a new game, I first check if there is maybe a
new game already available - with just 1 player while the other "seat" is
vacant:

UPDATE words_games g1
SETplayer2 = in_uid
FROM (
SELECT gid
FROM   words_games
WHERE  finished IS NULL
ANDplayer1 <> in_uid
ANDplayed1 IS NOT NULL
ANDplayer2 IS NULL
LIMIT  1
FORUPDATE SKIP LOCKED
) g2
WHERE g1.gid = g2.gid
RETURNING g1.gid
INTO  out_gid;

This code works great, but now I am trying to add an (obviously not solving
all cheating/proxy/etc. problems) check, that the IP addresses of both
users must be different.

Fetching "ip" in the internal SELECT statement is trivial with:

UPDATE words_games g1
SETplayer2 = in_uid
FROM (
SELECT g.gid, u.ip
FROM   words_games g, words_users u
WHERE  g.finished IS NULL
ANDg.player1 <> in_uid
ANDg.played1 IS NOT NULL
ANDg.player2 IS NULL
ON (g.player1 = u.uid)
LIMIT  1
FORUPDATE SKIP LOCKED
) g2
WHERE g1.gid = g2.gid
RETURNING g1.gid
INTO  out_gid;

But how to fetch the "ip" column in the surrounding UPDATE statement?

Thank you
Alex

Re: [GENERAL] londiste re-create leaf node

[Tom Lane]

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?="  writes:
> Mmm, built and reinstalled skytools-3.2, but now I'm getting this:

> InternalError: could not load library 
> "/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so": 
> /usr/lib/postgresql/9.5/lib/pgq_lowlevel.so: undefined symbol: oid_hash

Looks like you built against a set of backend headers that is older than
the server you're trying to run in.

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] londiste re-create leaf node

[Adrian Klaver]

On 09/09/2016 07:15 AM, Leonardo M. Ramé wrote:

El 09/09/16 a las 10:53, Leonardo M. Ramé escribió:

Hi, I had a londiste3 slave server that no longer works and I want to
re-create.

I dropped all the pgq and londiste schemas in the leaf node
manually...now I want to create the node again, but I'm getting this
error:

londiste3 /home/londiste/replicacion/db_esclavo.ini create-leaf
node_esclavo "dbname=database host=192.65.214.199"
--provider="dbname=database host=192.65.214.204"

ProgrammingError: schema "pgq_node" does not exist

This is obvious, the question is how can I re-create that schema.

I tried generating the leaf node again, from the master database, but
I get this:

2016-09-09 10:58:41,925 9680 INFO plpgsql is installed
2016-09-09 10:58:41,926 9680 INFO Installing pgq
2016-09-09 10:58:41,957 9680 INFO   Reading from
/usr/local/share/skytools3/pgq.sql
2016-09-09 10:58:45,130 9680 ERROR Job job_repli_ris_master got error
on connection 'db': could not access file "$libdir/pgq_lowlevel": No
such file or directory.

This is weird, because master is working without issues.

Regards,


Mmm, built and reinstalled skytools-3.2, but now I'm getting this:

InternalError: could not load library
"/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so":
/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so: undefined symbol: oid_hash


Well if I read correctly:

http://pgfoundry.org/forum/forum.php?forum_id=1951

support only goes up to 9.4







--
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] londiste re-create leaf node

[Leonardo M . Ramé]

El 09/09/16 a las 10:53, Leonardo M. Ramé escribió:
Hi, I had a londiste3 slave server that no longer works and I want to 
re-create.


I dropped all the pgq and londiste schemas in the leaf node 
manually...now I want to create the node again, but I'm getting this 
error:


londiste3 /home/londiste/replicacion/db_esclavo.ini create-leaf 
node_esclavo "dbname=database host=192.65.214.199" 
--provider="dbname=database host=192.65.214.204"


ProgrammingError: schema "pgq_node" does not exist

This is obvious, the question is how can I re-create that schema.

I tried generating the leaf node again, from the master database, but 
I get this:


2016-09-09 10:58:41,925 9680 INFO plpgsql is installed
2016-09-09 10:58:41,926 9680 INFO Installing pgq
2016-09-09 10:58:41,957 9680 INFO   Reading from 
/usr/local/share/skytools3/pgq.sql
2016-09-09 10:58:45,130 9680 ERROR Job job_repli_ris_master got error 
on connection 'db': could not access file "$libdir/pgq_lowlevel": No 
such file or directory.


This is weird, because master is working without issues.

Regards,


Mmm, built and reinstalled skytools-3.2, but now I'm getting this:

InternalError: could not load library 
"/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so": 
/usr/lib/postgresql/9.5/lib/pgq_lowlevel.so: undefined symbol: oid_hash



--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


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

[GENERAL] londiste re-create leaf node

[Leonardo M . Ramé]

Hi, I had a londiste3 slave server that no longer works and I want to 
re-create.


I dropped all the pgq and londiste schemas in the leaf node 
manually...now I want to create the node again, but I'm getting this error:


londiste3 /home/londiste/replicacion/db_esclavo.ini create-leaf 
node_esclavo "dbname=database host=192.65.214.199" 
--provider="dbname=database host=192.65.214.204"


ProgrammingError: schema "pgq_node" does not exist

This is obvious, the question is how can I re-create that schema.

I tried generating the leaf node again, from the master database, but I 
get this:


2016-09-09 10:58:41,925 9680 INFO plpgsql is installed
2016-09-09 10:58:41,926 9680 INFO Installing pgq
2016-09-09 10:58:41,957 9680 INFO   Reading from 
/usr/local/share/skytools3/pgq.sql
2016-09-09 10:58:45,130 9680 ERROR Job job_repli_ris_master got error on 
connection 'db': could not access file "$libdir/pgq_lowlevel": No such 
file or directory.


This is weird, because master is working without issues.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Is there a way to fix this ugliness

[Merlin Moncure]

On Fri, Sep 9, 2016 at 8:30 AM, Tim Uckun  wrote:
> I am trying to get the child elements of a one to many table to be rolled up
> into a json field in the parent table. The query I am running is
>
> select
> ob.id
>  ,case when array_position(array_agg(im.image_type), null) = 1  then
> '[]' else  json_agg(row_to_json(im.*)) end as images
>  from observations ob
>left join images im on ob.id = im.observation_id
> group by 1
>
>
> The reason I have the case statement there is because some observations
> don't have images but the json_agg(row_to_json function returns [NULL]
> instead of [] which is what I really want.
>
> Is there a more elegant way to do this?

not exactly.  More elegant approaches are frustrated by the lack of a
json operator.  However, you can wrap that in a function.

create or replace function fixnull(j json) returns json as
$$
  select case when j::text = '[null]'::text
  then '[]'::json
  else j
end;
$$ language sql immutable;

select
ob.id,
fixnull(json_agg(to_json(im.*))) as images
 from observations ob
   left join images im on ob.id = im.observation_id
group by 1;

merlin


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

[GENERAL] Is there a way to fix this ugliness

[Tim Uckun]

I am trying to get the child elements of a one to many table to be rolled
up into a json field in the parent table. The query I am running is

select
ob.id
 ,case when array_position(array_agg(im.image_type), null) = 1  then
'[]' else  json_agg(row_to_json(im.*)) end as images
 from observations ob
   left join images im on ob.id = im.observation_id
group by 1


The reason I have the case statement there is because some observations
don't have images but the json_agg(row_to_json function returns [NULL]
instead of [] which is what I really want.

Is there a more elegant way to do this?

I searched on the internet and somebody suggested coalesce but no matter
what combination I tried I could not make it happen.

Thanks.

[GENERAL] Very slow queries to stats on 9.3

[hubert depesz lubaczewski]

Hi,
So, we have this situation, where there is cluster with 5 smallish
databases:
$ select oid, pg_database_size(oid) from pg_database;
  oid  | pg_database_size 
---+--
 1 |  6752440
 12035 |  6760632
 16428 |  59779475640
 16427 |294947000
 12030 |  6455812
(5 rows)

But the 16428 database has quite a lot of objects:

$ select count(*) from pg_class;
  count  
-
 1032761
(1 row)

This is reflected in stats:

# ls -l $( su -c "psql -qAtX -c 'show stats_temp_directory'" - postgres )
total 127452
-rw--- 1 postgres postgres  4230 Sep  9 12:02 db_0.stat
-rw--- 1 postgres postgres 20792 Sep  9 12:02 db_12035.stat
-rw--- 1 postgres postgres 30932 Sep  9 12:02 db_16427.stat
-rw--- 1 postgres postgres 130413431 Sep  9 12:03 db_16428.stat
-rw--- 1 postgres postgres 20792 Sep  9 12:02 db_1.stat
-rw--- 1 postgres postgres  1026 Sep  9 12:03 global.stat

This directory is on tmpfs (ramdisk).

And getting any kind of stats takes non-trivial time:

$ explain analyze SELECT sum(xact_commit+xact_rollback) as txs FROM 
pg_stat_database;   
 
 QUERY PLAN 
 
-
 Aggregate  (cost=1.12..1.13 rows=1 width=4) (actual time=460.917..460.918 
rows=1 loops=1)
   ->  Seq Scan on pg_database d  (cost=0.00..1.06 rows=6 width=4) (actual 
time=0.003..0.015 rows=5 loops=1)
 Total runtime: 460.946 ms
(3 rows)

This is repeatable, and quick strace shows that when dealing with stats, it
looks that pg has to read all stat files, in whole, parse, and return results.


Is there anything that could be done, aside from dropping 90% objects, to make
stat-relating queries faster?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] qustion about pgaudit

[hubert depesz lubaczewski]

On Thu, Sep 08, 2016 at 03:19:59PM +, PICCI Guillermo  SNOOP wrote:
> Hi,
> we are trying to install pgaudit in order to check its funcionality, and we'd 
> like to know if there is any available rpm to do this.

as far as quick google search shows, pgaudit is a tool from 2ndQuadrant,
available here:
https://github.com/2ndQuadrant/pgaudit
so it would bo probably better to ask them, possibly using
https://github.com/2ndQuadrant/pgaudit/issues
then mail *PostgreSQL* mailing list.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/


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