Re: [GENERAL] Understanding pg_last_xlog_receive_location

[Michael Paquier]

On Wed, Mar 1, 2017 at 6:51 AM, Zach Walton  wrote:
> I'm following the documentation here (using postgresql 9.4.5):
> https://www.postgresql.org/docs/9.4/static/functions-admin.html

You should really update to a newer version of 9.4.X, you are missing
more than 1 year of bug fixes by staying on 9.4.5.

> I'm not sure I understand how this is possible. According to the docs,
> pg_last_xlog_receive_location can only be NULL when streaming is disabled
> (not the case) or hasn't started yet (doesn't seem possible when
> pg_last_xlog_replay_location is set).
>
> Could someone help shed some light on what state results in
> pg_last_xlog_receive_location being NULL when pg_last_xlog_replay_location
> is set?

That works for me for a streaming node:
=# SELECT pg_is_in_recovery(), pg_last_xlog_receive_location(),
pg_last_xlog_replay_location();
 pg_is_in_recovery | pg_last_xlog_receive_location |
pg_last_xlog_replay_location
---+---+--
 t | 0/30008E0 | 0/30008E0
(1 row)
-- 
Michael


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

[GENERAL] emitting all plans considered for a query (as opposed to just the winning one)

[Dan Hitt]

Suppose that i have a select query that involves a small number of
joins, say 3 or 4 and a few where conditions.

I want to list all the query plans that the postgres planner
considers.  I understand that for a small number of joins, the planner
actually considers all possible execution plans.

I haven't been able to find any command on the net that would just
dump all the plans out so that i could see what is possible in
principle (before taking into account the size of the tables or the
distribution of values within them).

And yet, such a list must exist, at least internally, and further,
when the planner is debugged or subjected to regression testing, there
must be a way to see what plans it is considering  although
conceivably it could all just be using gdb to step through internal
structures.

Anyhow, would appreciate this very much, and thanks in advance for any
info about how to do this, or where to search.  (Maybe i'm just
looking in all the wrong places.)

dan


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

[GENERAL] Understanding pg_last_xlog_receive_location

[Zach Walton]

I'm following the documentation here (using postgresql 9.4.5):
https://www.postgresql.org/docs/9.4/static/functions-admin.html

I'm attempting to fully understand the interplay
between pg_is_in_recovery() + pg_last_xlog_receive_location() +
pg_last_xlog_replay_location() so we can devise a reliable health check
script.

Here's a database that is configured as a hot standby for streaming
replication.

appdb=> SELECT pg_is_in_recovery(), pg_last_xlog_receive_location(),
pg_last_xlog_replay_location(); pg_is_in_recovery |
pg_last_xlog_receive_location | pg_last_xlog_replay_location
---+---+--
t | | 0/70A4C88 (1 row)

Note that the DB is in recovery, but that pg_last_xlog_receive_location is
NULL while pg_last_xlog_replay_location is 0/70A4C88.

I'm not sure I understand how this is possible. According to the docs,
pg_last_xlog_receive_location can only be NULL when streaming is disabled
(not the case) or hasn't started yet (doesn't seem possible when
pg_last_xlog_replay_location is set).

Could someone help shed some light on what state results in
pg_last_xlog_receive_location being NULL when pg_last_xlog_replay_location
is set?

Re: [GENERAL] json aggregation question

[Yasin Sari]

Hi Chris,

Maybe there is an another better solution;

1. sending values into jsonb_array_elements to getting elements (lateral
join)
2. distinct to eliminate duplicates
3. regexp_replace to remove malformed Array literals
4. Casting into text array

SELECT
 count(distinct tags ),
string_to_array(regexp_replace(string_agg(distinct elem::text ,
','),'\[*\"*\s*\]*','','g'),',') AS list
from thing as t, jsonb_array_elements(t.tags->'tag1') elem
where tags->'tag2'?'t2val1'

count | tag1
2| {val1,val2,val3}



28 Şub 2017 Sal, 19:22 tarihinde, Chris Withers 
şunu yazdı:

Hi All,

Given the following table:

# create table thing (id serial, tags jsonb);# \d thing
 Table "public.thing"
 Column |  Type   | Modifiers
+-+
 id | integer | not null default nextval('thing_id_seq'::regclass)
 tags   | jsonb   |

...and the following data:

insert into thing (tags) values ('{"tag1": ["val1", "val2"], "tag2":
["t2val1"]}');insert into thing (tags) values ('{"tag1": ["val3",
"val1"], "tag2": ["t2val1"]}');insert into thing (tags) values
('{"tag1": ["val2", "val1"], "tag2": ["t2val2"]}');

How can I aggregate the results of a query that equates to "show me the
number of matching rows and the set of tag1 value that have a tag2 value of
t2val1?

The closes I can get is:

# select count(*), json_agg(tags) from thing where tags->'tag2'?'t2val1';
 count | json_agg

---+--
 2 | [{"tag1": ["val1", "val2"], "tag2": ["t2val1"]}, {"tag1":
["val3", "val1"], "tag2": ["t2val1"]}](1 row)

...but I really want:

 count | tag1
---+-
 2 | ["val1", "val2", "val3"](1 row)

cheers,

Chris

[GENERAL] json aggregation question

[Chris Withers]

Hi All,

Given the following table:

|#createtablething (id serial,tags jsonb);#\d thing 
Table"public.thing"Column|Type |Modifiers 
+-+id 
|integer |notnulldefaultnextval('thing_id_seq'::regclass)tags |jsonb ||


...and the following data:

|insertintothing (tags)values('{"tag1": ["val1", "val2"], "tag2": 
["t2val1"]}');insertintothing (tags)values('{"tag1": ["val3", "val1"], 
"tag2": ["t2val1"]}');insertintothing (tags)values('{"tag1": ["val2", 
"val1"], "tag2": ["t2val2"]}');|


How can I aggregate the results of a query that equates to "show me the 
number of matching rows and the set of|tag1|value that have a|tag2|value 
of|t2val1|?


The closes I can get is:

|#selectcount(*),json_agg(tags)fromthing wheretags->'tag2'?'t2val1';count 
|json_agg 
---+--2|[{"tag1":["val1","val2"],"tag2":["t2val1"]},{"tag1":["val3","val1"],"tag2":["t2val1"]}](1row)|


...but I really want:

|count |tag1 ---+-2|["val1","val2","val3"](1row)|

cheers,

Chris

Re: [GENERAL] array_to_json - dealing with returning no rows

[Jong-won Choi]

I've just found array_remove!

Cheers

- Jong-won

On 01/03/17 12:31, Jong-won Choi wrote:

Hi all,


In my program, I generate SQLs from definitions, an example is:

(define-db-resource Event
  [{:oid{:type :bigserial :primary-key true}}
   {:name{:type :text :not-null true}}
   {:tour-oid {:type :bigint :not-null true :references [Tour :oid]}}
   {:tour   {:type :join :join-info {:home-key :tour-oid 
:foreign-key :oid :join-resource Tour :foreign-columns [:oid :name]

  :singular? true}}}
   {:campaigns {:type :join :join-info {:home-key :oid :foreign-key 
:event-oid :join-resource Campaign

:foreign-columns [:oid :type :name]}}}])


From definitions my code generate various SQLs and this is a 'select' 
example for the above definition:


SELECT event.oid,event.name,
 ROW_TO_JSON((SELECT sj_tour FROM (SELECT j_tour.oid, 
j_tour.name) sj_tour)) AS tour,
 ARRAY_TO_JSON(ARRAY_AGG((SELECT sj_campaigns FROM (SELECT 
j_campaign.oid,j_campaign.name,j_campaign.type WHERE j_campaign.oid IS 
NOT NULL) sj_campaigns)) AS campaigns

FROM event LEFT OUTER JOIN tour AS j_tour ON tour_oid = j_tour.oid
LEFT OUTER JOIN campaign AS j_campaign ON 
event.oid = j_campaign.event_oid

GROUP BY event.oid, j_tour.oid;


The problem I have is getting '[null]' as ARRAY_TO_JSON result when 
there is no rows.


Ideally, I want to get '[]' or null for no rows or '[{...some JSON 
keys and values ...},{ ... more ... }]' for some rows.



Also any suggestions will be great with above example query.


Thanks!


- Jong-won





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

[GENERAL] array_to_json - dealing with returning no rows

[Jong-won Choi]

Hi all,


In my program, I generate SQLs from definitions, an example is:

(define-db-resource Event
  [{:oid{:type :bigserial :primary-key true}}
   {:name{:type :text :not-null true}}
   {:tour-oid {:type :bigint :not-null true :references [Tour :oid]}}
   {:tour   {:type :join :join-info {:home-key :tour-oid 
:foreign-key :oid :join-resource Tour :foreign-columns [:oid :name]

  :singular? true}}}
   {:campaigns {:type :join :join-info {:home-key :oid :foreign-key 
:event-oid :join-resource Campaign

:foreign-columns [:oid :type :name]}}}])


From definitions my code generate various SQLs and this is a 'select' 
example for the above definition:


SELECT event.oid,event.name,
 ROW_TO_JSON((SELECT sj_tour FROM (SELECT j_tour.oid, 
j_tour.name) sj_tour)) AS tour,
 ARRAY_TO_JSON(ARRAY_AGG((SELECT sj_campaigns FROM (SELECT 
j_campaign.oid,j_campaign.name,j_campaign.type WHERE j_campaign.oid IS 
NOT NULL) sj_campaigns)) AS campaigns

FROM event LEFT OUTER JOIN tour AS j_tour ON tour_oid = j_tour.oid
LEFT OUTER JOIN campaign AS j_campaign ON event.oid 
= j_campaign.event_oid

GROUP BY event.oid, j_tour.oid;


The problem I have is getting '[null]' as ARRAY_TO_JSON result when 
there is no rows.


Ideally, I want to get '[]' or null for no rows or '[{...some JSON keys 
and values ...},{ ... more ... }]' for some rows.



Also any suggestions will be great with above example query.


Thanks!


- Jong-won



--
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] Shared WAL archive between master and standby: WALs not always identical

[Adrian Klaver]

On 02/28/2017 02:20 PM, Sasa Vilic wrote:

On 2017-02-28 16:41, Adrian Klaver wrote:

Seems to mean the simpler thing to do would be to set standby to
archive_mode = on, in which case the standby would not contribute WAL's
until it was promoted which would seem to be what you want.


Yes, that was my first thought. Except that documentation states following:

"""
If archive_mode is set to on, the archiver is not enabled during
recovery or standby mode. If the standby server is promoted, it will
start archiving after the promotion, *but will not archive any WAL it
did not generate itself*
"""

What happens with WAL that are started on primary but finished on
secondary?


I thought that was covered in your scenario?:

"
5. Primary server crashes, i.e. due to catastrophic disk failure
- everything stops and can't be recovered
- wal archiver is dead, but even if it were alive it wouldn't send 
WAL to archive anyway because 16MB of wal segment was not filled up

6. We promote our secondary server to master
- In secondary server's WAL we already got changes from primary
- Secondary continues appending new changes to wal segment
7. Eventually WAL segment on secondary fills up and then pushes it to 
wal archive.

"

So the WAL that is sent to the archive by the standby is the one it 
generated from the records it got via streaming replication from the master.




Regards,
Sasa




--
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] json aggregation question

[Paul Jungwirth]

On 02/28/2017 08:21 AM, Chris Withers wrote:

How can I aggregate the results of a query that equates to "show me the
number of matching rows and the set of |tag1| value that have
a |tag2| value of |t2val1|?

...but I really want:

|count |tag1 ---+-2|["val1","val2","val3"](1row)|


Seems like this does the trick?:

SELECT  COUNT(DISTINCT id),
json_agg(DISTINCT elem)
FROM(
  SELECT  id,
  jsonb_array_elements_text(tags->'tag1') AS elem
  FROMthing
  WHERE   tags->'tag2'?'t2val1'
) x;

You are looking to get always one result, right?

Yours,
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] Shared WAL archive between master and standby: WALs not always identical

[Sasa Vilic]

On 2017-02-28 16:57, Jon Nelson wrote:

What does pg_xlogdump say about the differences in the files?


What a nice tool. I didn't realize that it exists for 9.6.

Unfortunately, we gave up on shared WAL archive, so I don't if I will 
still have all both WALs. I have one conflicting WAL from one of the 
servers and there is the *whole* content:


rmgr: Standby len (rec/tot): 24/50, tx:  0, lsn: 
43/7428, prev 43/73000140, desc: RUNNING_XACTS nextXid 11617888 
latestCompletedXid 11617887 oldestRunningXid 11617888
rmgr: XLOGlen (rec/tot): 80/   106, tx:  0, lsn: 
43/7460, prev 43/7428, desc: CHECKPOINT_ONLINE redo 43/7428; 
tli 13; prev tli 13; fpw true; xid 0:11617888; oid 25304; multi 1; 
offset 0; oldest xid 1750 in DB 13322; oldest multi 1 in DB 1; 
oldest/newest commit timestamp xid: 0/0; oldest running xid 11617888; online
rmgr: Standby len (rec/tot): 24/50, tx:  0, lsn: 
43/74D0, prev 43/7460, desc: RUNNING_XACTS nextXid 11617888 
latestCompletedXid 11617887 oldestRunningXid 11617888
rmgr: XLOGlen (rec/tot):  8/34, tx:  0, lsn: 
43/74000108, prev 43/74D0, desc: BACKUP_END 43/7428
rmgr: XLOGlen (rec/tot):  0/24, tx:  0, lsn: 
43/74000130, prev 43/74000108, desc: SWITCH


I don't have WAL from other server, but I hope I will be able to find it 
tomorrow (if I haven't deleted it).


But I can share with you what I have observed by manually looking into 
WALs with hex editor:


* If I remember correctly, first page was same (what we see above)
* For one of the logs (the one I have right now), all first 16 pages 
except for the first had XLogPageHeaderData set (magic number=0xD093, 
tli=13, pageaddr) but WITHOUT any payload. Everything except header was 
zero. Remaining part of WAL WAS ALL ZEROED.
* Same WAL from other server was also similar. First page contained data 
and remaining part of WAL was filled with XLogPageHeaderData WITHOUT 
payload.
* The only different was that one WAL had XLogPageHeaderData without 
payload and other zeros instead.


This was on system during initial setup so for most time we didn't have 
any clients at all => not much to be logged in WAL. We were trying out 
replication, failover/switchover scenarios with repmgr and creating and 
restoring backup with barman.


Regards,
Sasa


--
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] Conferences for a DBA?

[Jaime Soler]

whatever event list at this website https://www.postgresql.org/about/events/
is recommended for a postgres DBA.

2017-02-28 1:00 GMT+01:00 Nathan Stocks :

> Thank you for mentioning location, Josh.
>
>
> I should have noted that I am in the western United States.
> --
> *From:* Joshua D. Drake 
> *Sent:* Monday, February 27, 2017 4:39:08 PM
> *To:* Nathan Stocks; pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] Conferences for a DBA?
>
> On 02/27/2017 03:25 PM, Nathan Stocks wrote:
> > What worthwhile conferences should a PostgreSQL DBA consider going to?
> >
> >
> > There have been some good sessions at OSCON in the past, but I was
> > wondering about more DBA-specific events.
>
> If you are in North America, this is the largest and it is taking place
> in a Month.
>
> http://pgconf.us/
>
> There are others such as postgresopen and next week there is a two
> track, two day set of sessions at SCALE.
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
>  +1-503-667-4564 <(503)%20667-4564>
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
> Unless otherwise stated, opinions are my own.
>

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

[Sasa Vilic]

On 2017-02-28 16:41, Adrian Klaver wrote:

Seems to mean the simpler thing to do would be to set standby to
archive_mode = on, in which case the standby would not contribute WAL's
until it was promoted which would seem to be what you want.


Yes, that was my first thought. Except that documentation states following:

"""
If archive_mode is set to on, the archiver is not enabled during 
recovery or standby mode. If the standby server is promoted, it will 
start archiving after the promotion, *but will not archive any WAL it 
did not generate itself*

"""

What happens with WAL that are started on primary but finished on secondary?

Regards,
Sasa


--
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] Configuring ssl_crl_file

[Bruce Momjian]

On Tue, Feb 28, 2017 at 10:50:02PM +0100, Frazer McLean wrote:
> On 28 Feb 2017, at 21:51, Bruce Momjian wrote:
> >I have researched this and will post a blog and and document the fix in
> >the next few months.  The reason you have to supply the entire
> >certificate chain to the root CA on the client is because you have not
> >used the "-extensions v3_ca" flag to openssl when creating the CA x509
> >request.  You have to mark the certificates as CAs so they are passed
> >from the server to the client.  You are looking for the CA certificates
> >to say:
> >
> > X509v3 Basic Constraints:
> > CA:TRUE
> >
> 
> My `ca.cert.pem` file has
> 
> X509v3 Basic Constraints: critical
> CA:TRUE
> 
> The `intermediate.cert.pem` has
> 
> X509v3 Basic Constraints: critical
> CA:TRUE, pathlen:0
> 
> This intermediate cert was generated using the `v3_intermediate_ca`
> extension defined in [1]. I wouldn’t expect *not* to have to give the full
> certificate chain to the client, since both were created by me.
> 
> To summarise my problem and solution: the connection worked fine until
> `ssl_crl_file` was enabled. I was trying to use a CRL generated from the
> intermediate CA, assuming PostgreSQL would trust it since it knows about the
> full CA chain in `ssl_ca_file`. Apparently, it must be a CRL generated from
> the root concatenated to a CRL generated from the intermediate, and then it
> works.

Oh, OK, that is beyond my understanding.  Thanks.

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

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


-- 
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] Configuring ssl_crl_file

[Frazer McLean]

On 28 Feb 2017, at 21:51, Bruce Momjian wrote:
I have researched this and will post a blog and and document the fix 
in

the next few months.  The reason you have to supply the entire
certificate chain to the root CA on the client is because you have not
used the "-extensions v3_ca" flag to openssl when creating the CA x509
request.  You have to mark the certificates as CAs so they are passed
from the server to the client.  You are looking for the CA 
certificates

to say:

X509v3 Basic Constraints:
CA:TRUE



My `ca.cert.pem` file has

X509v3 Basic Constraints: critical
CA:TRUE

The `intermediate.cert.pem` has

X509v3 Basic Constraints: critical
CA:TRUE, pathlen:0

This intermediate cert was generated using the `v3_intermediate_ca` 
extension defined in [1]. I wouldn’t expect *not* to have to give the 
full certificate chain to the client, since both were created by me.


To summarise my problem and solution: the connection worked fine until 
`ssl_crl_file` was enabled. I was trying to use a CRL generated from the 
intermediate CA, assuming PostgreSQL would trust it since it knows about 
the full CA chain in `ssl_ca_file`. Apparently, it must be a CRL 
generated from the root concatenated to a CRL generated from the 
intermediate, and then it works.


[1]: 
https://github.com/RazerM/postgres_crl_test/blob/dd9ef3ac4dd74d1cdfc6403899a09d954fd9622a/intermediate-config.txt#L99


Kind regards,

Frazer McLean


--
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] Configuring ssl_crl_file

[Bruce Momjian]

On Mon, Feb 27, 2017 at 12:11:47AM +0100, Frazer McLean wrote:
> I found a solution to the problem, which I’l send here to help those who
> find the original email via search.
> 
> The intermediate CRL file must be concatenated to CRL files going back to
> the root CA.

I have researched this and will post a blog and and document the fix in
the next few months.  The reason you have to supply the entire
certificate chain to the root CA on the client is because you have not
used the "-extensions v3_ca" flag to openssl when creating the CA x509
request.  You have to mark the certificates as CAs so they are passed
from the server to the client.  You are looking for the CA certificates
to say:

X509v3 Basic Constraints:
CA:TRUE

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

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


-- 
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] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Scott Marlowe]

On Tue, Feb 28, 2017 at 10:00 AM, Lisandro  wrote:
> Hi Steve, thanks for your help.
> Your comment made me realise that maybe the problem is my pgBouncer
> configuration, specifically default_pool_size. It took me a while to
> understand pgbouncer, and I still had some doubts when I configured it.  Now
> I undesrtand better.
>
> I connect to all databases with the same user. However, I had set
> default_pool_size=10. So with more than 150 databases, it was very probable
> that postgresql reach max_connections=250 limit.
>
> I didn't have set reserve_pool_timeout or max_db_connections, but docs say
> their default values are reserve_pool_timeout=5 seconds,
> max_db_connections=unlimited.
>
> I've reviewed pgBouncer configuration and did some search. I've found this
> thread where the first person that responds gives a great explanation abount
> how pgbouncer do its maths:
> https://github.com/pgbouncer/pgbouncer/issues/174
>
> So, what I did for now was to set this in pgbouncer configuration:
> default_pool_size=1
> min_pool_size = 0
> server_idle_timeout = 30
> reserve_pool_size = 10
> reserve_pool_timeout = 5
> max_db_connections = 5
>
>
> I'll keep monitoring with this setup, but I can already tell you that the
> total number of connections in postgres has significantly reduced (from ~100
> to ~60). So I guess the problem was a bad setup of pgbouncer.

Those seem pretty reasonable. Note that if you need to you CAN set
default pool size and other settings per database etc. So if you have
a more active db that needs more connections etc you can adjust these
as needed per db and it will override the default overall settings.

As for monitoring I'd suggest setting up Nagios or Zabbix. They both
can give you some nice pretty graphs of what your system looks like
over time so you can do simple trend analysis and alerting to avoid
these problems in the future.


-- 
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] json aggregation question

[Chris Withers]

Thanks, this is closer, but regex really scares me for something like 
this...


On 28/02/2017 17:19, Yasin Sari wrote:

Hi Chris,

Maybe there is an another better solution;

1. sending values into jsonb_array_elements to getting elements 
(lateral join)

2. distinct to eliminate duplicates
3. regexp_replace to remove malformed Array literals
4. Casting into text array

SELECT
 count(distinct tags ),
string_to_array(regexp_replace(string_agg(distinct elem::text , 
','),'\[*\"*\s*\]*','','g'),',') AS list

from thing as t, jsonb_array_elements(t.tags->'tag1') elem
where tags->'tag2'?'t2val1'

count | tag1
2| {val1,val2,val3}



28 Şub 2017 Sal, 19:22 tarihinde, Chris Withers 
> şunu yazdı:


Hi All,

Given the following table:

|#createtablething (id serial,tags jsonb);#\d thing
Table"public.thing"Column|Type |Modifiers
+-+id
|integer |notnulldefaultnextval('thing_id_seq'::regclass)tags
|jsonb ||

...and the following data:

|insertintothing (tags)values('{"tag1": ["val1", "val2"], "tag2":
["t2val1"]}');insertintothing (tags)values('{"tag1": ["val3",
"val1"], "tag2": ["t2val1"]}');insertintothing
(tags)values('{"tag1": ["val2", "val1"], "tag2": ["t2val2"]}');|

How can I aggregate the results of a query that equates to "show
me the number of matching rows and the set of|tag1|value that have
a|tag2|value of|t2val1|?

The closes I can get is:

|#selectcount(*),json_agg(tags)fromthing
wheretags->'tag2'?'t2val1';count |json_agg

---+--2|[{"tag1":["val1","val2"],"tag2":["t2val1"]},{"tag1":["val3","val1"],"tag2":["t2val1"]}](1row)|

...but I really want:

|count |tag1
---+-2|["val1","val2","val3"](1row)|

cheers,

Chris

Re: [GENERAL] Making a unique constraint deferrable?

[David G. Johnston]

On Tue, Feb 28, 2017 at 10:05 AM, Ivan Voras  wrote:

> On 28 February 2017 at 18:03, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras  wrote:
>>
>>>
>>> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
>>> ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
>>> constraint
>>>
>>>
>> ​A more clear error message would be:
>>
>> EROR:  cannot alter non-foreign key constraint "foo_a_b_key"​ of relation
>> "foo"
>>
>> Though I'm not sure how that meshes with the error message style guides...
>>
>
>
> Any idea what underlying technical reason prohibits marking non-fk
> constraints as deferrable?
>
>
Not off hand - but a unique (and PK by extension) constraint is implemented
by creating an underlying unique index​ and the ALTER CONSTRAINT command
probably doesn't want to go messing around with that.  While the columns
involved in a FK constraint can also be indexed the two concepts are not
physically linked together.

David J.

Re: [GENERAL] Making a unique constraint deferrable?

[Ivan Voras]

On 28 February 2017 at 18:03, David G. Johnston 
wrote:

> On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras  wrote:
>
>>
>> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
>> ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
>> constraint
>>
>>
> ​A more clear error message would be:
>
> EROR:  cannot alter non-foreign key constraint "foo_a_b_key"​ of relation
> "foo"
>
> Though I'm not sure how that meshes with the error message style guides...
>


Any idea what underlying technical reason prohibits marking non-fk
constraints as deferrable?

Re: [GENERAL] Making a unique constraint deferrable?

[David G. Johnston]

On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras  wrote:

>
> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
> ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
> constraint
>
>
​A more clear error message would be:

EROR:  cannot alter non-foreign key constraint "foo_a_b_key"​ of relation
"foo"

Though I'm not sure how that meshes with the error message style guides...

David J.

[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Lisandro]

Hi Steve, thanks for your help. 
Your comment made me realise that maybe the problem is my pgBouncer
configuration, specifically default_pool_size. It took me a while to
understand pgbouncer, and I still had some doubts when I configured it.  Now
I undesrtand better.

I connect to all databases with the same user. However, I had set
default_pool_size=10. So with more than 150 databases, it was very probable
that postgresql reach max_connections=250 limit.

I didn't have set reserve_pool_timeout or max_db_connections, but docs say
their default values are reserve_pool_timeout=5 seconds, 
max_db_connections=unlimited. 

I've reviewed pgBouncer configuration and did some search. I've found this
thread where the first person that responds gives a great explanation abount
how pgbouncer do its maths:
https://github.com/pgbouncer/pgbouncer/issues/174

So, what I did for now was to set this in pgbouncer configuration:
default_pool_size=1
min_pool_size = 0
server_idle_timeout = 30
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 5


I'll keep monitoring with this setup, but I can already tell you that the
total number of connections in postgres has significantly reduced (from ~100
to ~60). So I guess the problem was a bad setup of pgbouncer.

Thank you all for your help!



--
View this message in context: 
http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946827.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] Making a unique constraint deferrable?

[David G. Johnston]

On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras  wrote:

> Hello,
>
> If I'm interpreting the manual correctly, this should work:
>
> ivoras=# create table foo(a integer, b integer, unique(a,b));
> CREATE TABLE
> ivoras=# \d foo
>   Table "public.foo"
>  Column |  Type   | Modifiers
> +-+---
>  a  | integer |
>  b  | integer |
> Indexes:
> "foo_a_b_key" UNIQUE CONSTRAINT, btree (a, b)
>
> ivoras=# insert into foo(a,b) values(1,2);
> INSERT 0 1
> ivoras=# insert into foo(a,b) values(1,2);
> ERROR:  duplicate key value violates unique constraint "foo_a_b_key"
> DETAIL:  Key (a, b)=(1, 2) already exists.
> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
> ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
> constraint
>
> The manual says this for SET CONSTRAINTS:
>
> Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE
> constraints are affected by this setting. NOT NULL and CHECK constraints
> are always checked immediately when a row is inserted or modified (not at
> the end of the statement). Uniqueness and exclusion constraints that have
> not been declared DEFERRABLE are also checked immediately.
>
>
> I'm puzzled by the "...is not a foreign key constraint" error message.
> Doesn't "deferrable" also work on unique constraints?
>
>
​The error is pointing out the documented behavior that only FK constraints
can be altered.

​https://www.postgresql.org/docs/9.6/static/sql-altertable.html (see ALTER
CONSTRAINT note)

So, while you can make a PK constraint deferrable it must be done as part
of the initial constraint construction and not via ALTER CONSTRAINT.

David J.

Re: [GENERAL] Making a unique constraint deferrable?

[David G. Johnston]

On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras  wrote:

> Hello,
>
> If I'm interpreting the manual correctly, this should work:
>
> ivoras=# create table foo(a integer, b integer, unique(a,b));
> CREATE TABLE
> ivoras=# \d foo
>   Table "public.foo"
>  Column |  Type   | Modifiers
> +-+---
>  a  | integer |
>  b  | integer |
> Indexes:
> "foo_a_b_key" UNIQUE CONSTRAINT, btree (a, b)
>
> ivoras=# insert into foo(a,b) values(1,2);
> INSERT 0 1
> ivoras=# insert into foo(a,b) values(1,2);
> ERROR:  duplicate key value violates unique constraint "foo_a_b_key"
> DETAIL:  Key (a, b)=(1, 2) already exists.
> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
> ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
> constraint
>
> The manual says this for SET CONSTRAINTS:
>
> Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE
> constraints are affected by this setting. NOT NULL and CHECK constraints
> are always checked immediately when a row is inserted or modified (not at
> the end of the statement). Uniqueness and exclusion constraints that have
> not been declared DEFERRABLE are also checked immediately.
>
>
> I'm puzzled by the "...is not a foreign key constraint" error message.
> Doesn't "deferrable" also work on unique constraints?
>
>
​The error is pointing out the documented behavior that only FK constraints
can be altered.

​https://www.postgresql.org/docs/9.6/static/sql-altertable.html (see ALTER
CONSTRAINT note)

So, while you can make a PK constraint deferrable it must be done as part
of the initial constraint construction and not via ALTER CONSTRAINT.

David J.

Re: [GENERAL] Making a unique constraint deferrable?

[Adrian Klaver]

On 02/28/2017 08:50 AM, Ivan Voras wrote:

Hello,

If I'm interpreting the manual correctly, this should work:

ivoras=# create table foo(a integer, b integer, unique(a,b));
CREATE TABLE
ivoras=# \d foo
  Table "public.foo"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
 b  | integer |
Indexes:
"foo_a_b_key" UNIQUE CONSTRAINT, btree (a, b)

ivoras=# insert into foo(a,b) values(1,2);
INSERT 0 1
ivoras=# insert into foo(a,b) values(1,2);
ERROR:  duplicate key value violates unique constraint "foo_a_b_key"
DETAIL:  Key (a, b)=(1, 2) already exists.
ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
constraint

The manual says this for SET CONSTRAINTS:

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and
EXCLUDE constraints are affected by this setting. NOT NULL and CHECK
constraints are always checked immediately when a row is inserted or
modified (not at the end of the statement). Uniqueness and exclusion
constraints that have not been declared DEFERRABLE are also checked
immediately.


I'm puzzled by the "...is not a foreign key constraint" error message.
Doesn't "deferrable" also work on unique constraints?


https://www.postgresql.org/docs/9.6/static/sql-altertable.html

"ALTER CONSTRAINT

This form alters the attributes of a constraint that was previously 
created. Currently only foreign key constraints may be altered.

"







--
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] ERROR: functions in index expression must be marked IMMUTABLE

[David G. Johnston]

On Tue, Feb 28, 2017 at 9:35 AM, Geoff Winkless  wrote:

> On 28 February 2017 at 15:59, Adrian Klaver 
> wrote:
>
>> On 02/28/2017 07:30 AM, Sven R. Kunze wrote:
>>
>>> On 28.02.2017 15:40, Adrian Klaver wrote:
>>>
 [explanation of why date casting and to_datetime don't work]

>>>
>>> Why is to_date not immutable?
>>>
>>
>> Not sure, but if I where to hazard a guess, from the source code in
>> formatting.c:
>>
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f
>> =src/backend/utils/adt/formatting.c;h=e552c8d20b61a082049068
>> d2f8d776e35fef1179;hb=HEAD
>>
>> ​Would the fact that you can have month names in to_date strings make it
> dependent on current locale?
>
>
​That would seem to be it.

cache_locale_time() at the top of DCH_to_char which is in the call stack of
the shared parsing code for both to_date and to_timestamp.

​
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a082049068d2f8d776e35fef1179;hb=HEAD#l2363

Supposedly one could provide a version of to_date that accepts a locale in
which to interpret names in the input data - or extend the format string
with some kind of "{locale=en_US}" syntax to avoid changing the function
signature.

David J.

[GENERAL] Making a unique constraint deferrable?

[Ivan Voras]

Hello,

If I'm interpreting the manual correctly, this should work:

ivoras=# create table foo(a integer, b integer, unique(a,b));
CREATE TABLE
ivoras=# \d foo
  Table "public.foo"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
 b  | integer |
Indexes:
"foo_a_b_key" UNIQUE CONSTRAINT, btree (a, b)

ivoras=# insert into foo(a,b) values(1,2);
INSERT 0 1
ivoras=# insert into foo(a,b) values(1,2);
ERROR:  duplicate key value violates unique constraint "foo_a_b_key"
DETAIL:  Key (a, b)=(1, 2) already exists.
ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable;
ERROR:  constraint "foo_a_b_key" of relation "foo" is not a foreign key
constraint

The manual says this for SET CONSTRAINTS:

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE
constraints are affected by this setting. NOT NULL and CHECK constraints
are always checked immediately when a row is inserted or modified (not at
the end of the statement). Uniqueness and exclusion constraints that have
not been declared DEFERRABLE are also checked immediately.


I'm puzzled by the "...is not a foreign key constraint" error message.
Doesn't "deferrable" also work on unique constraints?

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

[Geoff Winkless]

On 28 February 2017 at 15:59, Adrian Klaver 
wrote:

> On 02/28/2017 07:30 AM, Sven R. Kunze wrote:
>
>> On 28.02.2017 15:40, Adrian Klaver wrote:
>>
>>> [explanation of why date casting and to_datetime don't work]
>>>
>>
>> Why is to_date not immutable?
>>
>
> Not sure, but if I where to hazard a guess, from the source code in
> formatting.c:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;
> f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a08204906
> 8d2f8d776e35fef1179;hb=HEAD
>
> ​Would the fact that you can have month names in to_date strings make it
dependent on current locale?

​Geoff

Re: [GENERAL] Querying JSON Lists

[Adrian Klaver]

On 02/26/2017 03:26 AM, Sven R. Kunze wrote:

Hello everyone,

playing around with jsonb and coming from this SO question
http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string
I wonder why PostgreSQL behaves differently for text and integers on the
? and @> operators.


Let's have a look at 4 different but similar queries:

-- A) ? + text
select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12';
 ?column?
--
 t

-- B) ? + integer
select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
ERROR:  operator does not exist: jsonb ? integer
LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
 ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.


https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT

"jsonb also has an existence operator, which is a variation on the theme 
of containment: it tests whether a string (given as a text value) 
appears as an object key or array element at the top level of the jsonb 
value. These examples return true except as noted


-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
"



-- C) @> + text
select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]',
'{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food":
["12","34","45"]}'::jsonb->'food' @> '12';
 ?column? | ?column? | ?column?
--+--+--
 t| t| f

-- D) @> + integer
select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food":
[12,34,45]}'::jsonb->'food' @> '12';--, '{"food":
[12,34,45]}'::jsonb->'food' @> 12;
 ?column? | ?column?
--+--
 t| t


Now my questions:

1) Why does A) work? Docs tells us that ? works for keys, not values.
2) Why does B) not work although A) works?
3) Why do the variants without the brackets on the right side of @> work
in C) and D)? Is there json data where their results differ from the
ones with the brackets?
4) What is the recommended way of testing inclusion in json lists?


I have not worked through your examples, but I suspect the answer's lie 
here:


https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT

8.14.3. jsonb Containment and Existence



Related docs: https://www.postgresql.org/docs/9.5/static/datatype-json.html

Regards,
Sven



--
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] ERROR: functions in index expression must be marked IMMUTABLE

[David G. Johnston]

On Tue, Feb 28, 2017 at 8:59 AM, Adrian Klaver 
wrote:

> On 02/28/2017 07:30 AM, Sven R. Kunze wrote:
>
>> On 28.02.2017 15:40, Adrian Klaver wrote:
>>
>>> [explanation of why date casting and to_datetime don't work]
>>>
>>
>> Why is to_date not immutable?
>>
>
> Not sure, but if I where to hazard a guess, from the source code in
> formatting.c:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;
> f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a08204906
> 8d2f8d776e35fef1179;hb=HEAD
>
> to_date(PG_FUNCTION_ARGS)
> {
> text   *date_txt = PG_GETARG_TEXT_P(0);
> text   *fmt = PG_GETARG_TEXT_P(1);
> DateADT result;
> struct pg_tm tm;
> fsec_t  fsec;
>
> do_to_timestamp(date_txt, fmt, , );
> 
>
> /*
>  * do_to_timestamp: shared code for to_timestamp and to_date
>
> The shared code makes it not immutable:
>

​Further on that reads:

"​* Parse the 'date_txt' according to 'fmt', return results as a struct
pg_tm
 * and fractional seconds."

Which makes it sound like a pure text parsing routine that applies minimal
logic to the values that it is parsing.  In fact, its doesn't even accept a
TZ/OF formatting codes that could be used to determine shift.  to_date is
always to going to output a date value that reflects the literal input text
with "positions" determined by the input format code.

Per Tom Lane [1] while the current implementation is in fact immutable at
some point in the future we may wish to add additional environmental logic
which will require that it be marked STABLE.

1. https://www.postgresql.org/message-id/4177.1322537...@sss.pgh.pa.us

I would be considering a trigger that populates a date column and a normal
index on said date column.

David J.

[GENERAL] json aggregation question

[Chris Withers]

Hi All,

Given the following table:

|#createtablething (id serial,tags jsonb);#\d thing 
Table"public.thing"Column|Type |Modifiers 
+-+id 
|integer |notnulldefaultnextval('thing_id_seq'::regclass)tags |jsonb ||


...and the following data:

|insertintothing (tags)values('{"tag1": ["val1", "val2"], "tag2": 
["t2val1"]}');insertintothing (tags)values('{"tag1": ["val3", "val1"], 
"tag2": ["t2val1"]}');insertintothing (tags)values('{"tag1": ["val2", 
"val1"], "tag2": ["t2val2"]}');|


How can I aggregate the results of a query that equates to "show me the 
number of matching rows and the set of|tag1|value that have a|tag2|value 
of|t2val1|?


The closes I can get is:

|#selectcount(*),json_agg(tags)fromthing wheretags->'tag2'?'t2val1';count 
|json_agg 
---+--2|[{"tag1":["val1","val2"],"tag2":["t2val1"]},{"tag1":["val3","val1"],"tag2":["t2val1"]}](1row)|


...but I really want:

|count |tag1 ---+-2|["val1","val2","val3"](1row)|

cheers,

Chris

Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Adrian Klaver]

On 02/28/2017 06:01 AM, Lisandro wrote:

Thank you Adrian.

Yes, I confirm that all the databases are running in one PostgreSQL
server/instance. I'm running this version:
PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit

Let me ask: is there a way to monitor the total connections to postgresql
through time? Or should I make my own script for that? I ask because every
time the error is thrown, I check the total connections with "select
count(*) from pg_stat_activity;" but the number is always far from the
configured max_connections.


I have not used any as my needs are fairly simple. All I can do is point 
you at:


https://wiki.postgresql.org/wiki/Monitoring

Hopefully someone with more experience with this can help you out.


A question I forgot to ask previously:

Is there any app/client that uses the server that is not going through 
pgBouncer?





Maybe the problem is with pgBouncer, so I'll post this issue in their forum.
I'm not really a sysop, so it's hard for me to fully understand the issue.
The strange part is that the error appears in random hours, I mean, it
doesn't correspond with the hours of higher db activity. Instead, the error
appears in both scenarios: with high db activity and with very low db
activity.






--
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] GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Steve Crawford]

On Sat, Feb 25, 2017 at 4:19 AM, lisandro 
wrote:

> Hi there! Please tell me if this isn't the place to post my question, I'm
> new
> in the list.
>
> I'm using PostgreSQL 9.3, I have around 150 databases, and I use pgBouncer
> for connection pooling.
> My server is a VPS with 8cpus and 24gb of RAM.
>
> My current postgreSQL configuration (resumed) is this:
>
> listen_addresses = '*'
> port = 6543
> max_connections = 250
> shared_buffers = 2GB
> effective_cache_size = 6GB
> work_mem = 10485kB
> maintenance_work_mem = 512MB
> checkpoint_segments = 32
> checkpoint_completion_target = 0.7
> wal_buffers = 16MB
> default_statistics_target = 100
>
>
> In the other hand, my pgBouncer configuration (resumed) is this:
>
> listen_addr = localhost
> listen_port = 5432
> pool_mode = transaction
> server_reset_query = DISCARD ALL
> max_client_conn = 1
> default_pool_size = 10
> min_pool_size = 2
> server_idle_timeout = 30
> ...


Note that pgBouncer pool size is per user/database pair. With these
settings and 150 databases I'm actually surprised that you aren't running
out of connections more often. Perhaps there are per-database settings that
haven't been shown. We are also missing info on reserve_pool_timeout,
max_db_connections, etc. which could all play a role, here.

Cheers,
Steve

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

[Adrian Klaver]

On 02/28/2017 07:30 AM, Sven R. Kunze wrote:

On 28.02.2017 15:40, Adrian Klaver wrote:

[explanation of why date casting and to_datetime don't work]


Why is to_date not immutable?


Not sure, but if I where to hazard a guess, from the source code in 
formatting.c:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a082049068d2f8d776e35fef1179;hb=HEAD

to_date(PG_FUNCTION_ARGS)
{
text   *date_txt = PG_GETARG_TEXT_P(0);
text   *fmt = PG_GETARG_TEXT_P(1);
DateADT result;
struct pg_tm tm;
fsec_t  fsec;

do_to_timestamp(date_txt, fmt, , );


/*
 * do_to_timestamp: shared code for to_timestamp and to_date

The shared code makes it not immutable:


test=> select * from pg_proc where proname ilike 'to_date';
...

provolatile | s




https://www.postgresql.org/docs/9.6/static/catalog-pg-proc.html

provolatile char

provolatile tells whether the function's result depends only on its 
input arguments, or is affected by outside factors. It is i for 
"immutable" functions, which always deliver the same result for the same 
inputs. It is s for "stable" functions, whose results (for fixed inputs) 
do not change within a scan. It is v for "volatile" functions, whose 
results might change at any time. (Use v also for functions with 
side-effects, so that calls to them cannot get optimized away.)





Regards,
Sven






--
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] Shared WAL archive between master and standby: WALs not always identical

[Jon Nelson]

On Tue, Feb 28, 2017 at 9:41 AM, Adrian Klaver 
wrote:

> On 02/27/2017 11:14 PM, Sasa Vilic wrote:
> ...
>


> "My problem is that sometimes WAL uploaded from master and from slave are
> not 100% identical. In most cases they are but occasionally they are not. I
> have written small script that ensures that upload is free of race
> condition and I log md5 sum of each WAL."
>

The wisdom (or not!) in archiving WAL to the same location from multiple
sources (even if they share a common ancestor) notwithstanding, I must
admit to having my curiosity piqued.

Let's assume a different situation:
- a master and one or more standby units each archiving every WAL file but
to it's own archive
- we check to see if identically named WAL files are content identical

Does it surprise anybody else that, sometimes, an identically named WAL
file from the master and from a standby have different contents?  It
surprises me.

I would love to know if the differences are due to some oversight in the
WAL archiving mechanism chosen by the OP or if, in fact, a master and a
standby generate different WAL files!

What does pg_xlogdump say about the differences in the files?


-- 
Jon

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

[Adrian Klaver]

On 02/27/2017 11:14 PM, Sasa Vilic wrote:

On 2017-02-28 06:14, Adrian Klaver wrote:

On 02/27/2017 05:52 PM, Sasa Vilic wrote:

Because standby is running in syncronous replication, whereby wal
archiver is asynchronous. Therefore there is a small window where slave
has received the data but master has not pushed it yet to wal archive.


Exactly. The standby already has the latest information, it would gain
nothing from fetching it from the archive and anything it wrote to the
archive would only have the information it got from the master at the
point of failure. That is before you promoted it, after it would be on
its own path independent of the master.


Hi Adrian,

I am afraid that you don't understand me.

Standby is not fetching WAL from archive, it fetches it directly from
master and is done synchronously, which means that master will only
confirm transaction to client when WAL is streamed and applied at
standby. On the other hand, master does not have to wait for WAL
archiver. If master crashes before WAL archiver is able to send WAL, we
would still have it on standby.

Let us for the sake of demonstration consider that we have same very low
busy but very critical system:

1. Your client connects to primary server (master) and performs changes
on data
2. It just happen that this is a moment where PostgreSQL opens new WAL
segment. It writes few kilobytes in this new WAL segment but it has
almost 16MB to write before segment is complete. So the wal archiver has
to wait before it can push wal segment in wal archive
3. Secondary server (standby) is doing same, it is writing changes to
newly created WAL segment
4. Your client issues COMMIT
- primary waits until changes are applied at secondary
- primary flushes changes to WAL
- secondary confirms transaction to primary
- primary confirms transaction to client
- WAL is still not processed by wal archiver because it is only i.e.
1 MB big and we are still left 15MB to go
5. Primary server crashes, i.e. due to catastrophic disk failure
- everything stops and can't be recovered
- wal archiver is dead, but even if it were alive it wouldn't send
WAL to archive anyway because 16MB of wal segment was not filled up
6. We promote our secondary server to master
- In secondary server's WAL we already got changes from primary
- Secondary continues appending new changes to wal segment
7. Eventually WAL segment on secondary fills up and then pushes it to
wal archive.
8. Although primary is dead, we didn't loose anything because lost WAL
data was pushed by secondary.


I understand the above, what I did not understand, from your original post:

"My problem is that sometimes WAL uploaded from master and from slave 
are not 100% identical. In most cases they are but occasionally they are 
not. I have written small script that ensures that upload is free of 
race condition and I log md5 sum of each WAL."


To me that reads as you sending WALs to the archive from both the master 
and the standby in parallel, instead of sequentially as you imply in the 
outline above. It would seem to be confirmed by the setting of 
archive_mode = always:


https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING

https://www.postgresql.org/docs/9.6/static/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY

Seems to mean the simpler thing to do would be to set standby to 
archive_mode = on, in which case the standby would not contribute WAL's 
until it was promoted which would seem to be what you want.




Regards,
Sasa




--
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] ERROR: functions in index expression must be marked IMMUTABLE

[Sven R. Kunze]

On 28.02.2017 15:40, Adrian Klaver wrote:

[explanation of why date casting and to_datetime don't work]


Why is to_date not immutable?

Regards,
Sven



--
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 (not) distinct from

[Adrian Klaver]

On 02/28/2017 12:08 AM, Johann Spies wrote:

When I query table a I get 18 rows.
The same query on table b results in 28 rows.

Both tables have the same structure.

When I export the results to csv-files and do a diff it confirms that
all 18 rows from a are also in b. Table b has 10 new rows.

When I combine these queries and use "is (not) distinct from"
I get strange results:

with a as (select citing_article, cited_article, pubyear, year_cited,
cited_author, cited_title, cited_work, doi
from wos_2017_1.citation
where citing_article='abcdefg'
order by 3,4,5,6,8),
b as (
select citing_article, cited_article, pubyear, year_cited, cited_author,
cited_title, cited_work, doi
from wos_2017_1.citationbackup
where citing_article='abcdefg'
order by 3,4,5,6,8)
select distinct b.* from b , a
where
( B.citing_article,
 B.cited_article,
 B.pubyear,
 B.year_cited,
 B.cited_author,
 B.cited_title,
 B.cited_work,
B.doi)
 is distinct from
(A.citing_article,
 A.cited_article,
 A.pubyear,
 A.year_cited,
 A.cited_author,
 A.cited_title,
 A.cited_work, A.doi)

The result of this query is 28 rows - thus that of b.
I expected this to be 10.

If I change the query to "is not distinct from" it results in 18 rows
which is what I would have expected.


I have not worked through all this but at first glance I suspect:

select distinct b.* from b ...

is distinct from ...

constitutes a double negative.

What happens if you eliminate the first distinct?




Regards
Johann.
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)



--
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] ERROR: functions in index expression must be marked IMMUTABLE

[Adrian Klaver]

On 02/28/2017 01:35 AM, Sven R. Kunze wrote:

On 27.02.2017 18:17, Adrian Klaver wrote:

Yes, but is not about timezone dependency, it is about the other
dependencies listed in the second and third points. Namely the
datestyle setting and magic strings e.g. 'now'


I am sorry, I still don't understand. to_date and to_timestamp require
datestyle settings per se and magic strings don't work.


See here:

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

"There are multiple reasons why the text-to-datetime conversion 
functions are not immutable"


Tom was referring to the text --> date cast you where attempting in your 
original index definition:


create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::date));

So:

test=> select 'today'::date;
date

 2017-02-28
(1 row)

test=> select 'now'::date;
date

 2017-02-28
(1 row)

test=> set datestyle = 'SQL, DMY';
SET
test=> select 'today'::date;
date

 28/02/2017
(1 row)

test=> select 'now'::date;
date

 28/02/2017
(1 row)


Now you tried to work around the casting issue by using to_timestamp:

create index docs_birthdate_idx ON docs using btree
((to_timestamp(meta->>'birthdate', '-MM-DD') at time zone 'UTC'));

but that introduced the issue that to_timestamp returns a timestamptz 
and so you end up with a dependency on timezones.





=# -- required datestyle
=# select to_date('2000-01-01');
ERROR:  function to_date(unknown) does not exist
LINE 1: select to_date('2000-01-01');
   ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.


=# -- magic strings don't work
=# select to_date('');
ERROR:  invalid value "epoc" for ""
DETAIL:  Value must be an integer.
=# select to_date('epoch', '-MM-DD');
ERROR:  invalid value "epoc" for ""
DETAIL:  Value must be an integer.
=# select to_date('infinity', '-MM-DD');
ERROR:  invalid value "infi" for ""
DETAIL:  Value must be an integer.
=# select to_date('-infinity', '-MM-DD');
ERROR:  invalid value "-inf" for ""
DETAIL:  Value must be an integer.
=# select to_date('now', '-MM-DD');
ERROR:  invalid value "now" for ""
DETAIL:  Value must be an integer.
=# select to_date('today', '-MM-DD');
ERROR:  invalid value "toda" for ""
DETAIL:  Value must be an integer.
=# select to_date('tomorrow', '-MM-DD');
ERROR:  invalid value "tomo" for ""
DETAIL:  Value must be an integer.
=# select to_date('yesterday', '-MM-DD');
ERROR:  invalid value "yest" for ""
DETAIL:  Value must be an integer.
=# select to_date('allballs', '-MM-DD');
ERROR:  invalid value "allb" for ""
DETAIL:  Value must be an integer.

Regards,
Sven



--
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] Question about TOAST table - PostgreSQL 9.2

[Albe Laurenz]

Patrick B wrote:
> I have a database which is 4TB big. We currently store binary data in a bytea 
> data type column
> (seg_data BYTEA). The column is behind binary_schema and the files types 
> stored are: pdf, jpg, png.

> Questions:
> 
> 1 - If I take out 500GB of bytea data ( by updating the column seg_data and 
> setting it to null ), will
> I get those 500GB of free disk space? or do I need to run vacuum full or 
> either pg_dump?

You'll need VACUUM (FULL) or dump/restore.

> 2 - If I choose going ahead with VACUUM FULL, I have 3 streaming replication 
> slaves, Will I need to
> run the vacuum full on them too?

No, and indeed you cannot.
The changes made by VACUUM on the primary will be replicated.

> 3 - [2] vacuum full needs some free disk space as same size as the target 
> table. It locks the table
> (cannot be used while running vacuum full) and a REINDEX might be needed 
> after. AM I right?

It locks the table for all concurrent access, but a REINDEX is not necessary, 
as the
indexes are rewritten as well.

Yours,
Laurenz Albe

-- 
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] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Tom Lane]

Lisandro  writes:
> Let me ask: is there a way to monitor the total connections to postgresql
> through time? Or should I make my own script for that? I ask because every
> time the error is thrown, I check the total connections with "select
> count(*) from pg_stat_activity;" but the number is always far from the
> configured max_connections. 

You could enable log_connections and log_disconnections.

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] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Lisandro]

Thank you Adrian.

Yes, I confirm that all the databases are running in one PostgreSQL
server/instance. I'm running this version:
PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit

Let me ask: is there a way to monitor the total connections to postgresql
through time? Or should I make my own script for that? I ask because every
time the error is thrown, I check the total connections with "select
count(*) from pg_stat_activity;" but the number is always far from the
configured max_connections. 


Maybe the problem is with pgBouncer, so I'll post this issue in their forum.
I'm not really a sysop, so it's hard for me to fully understand the issue.
The strange part is that the error appears in random hours, I mean, it
doesn't correspond with the hours of higher db activity. Instead, the error
appears in both scenarios: with high db activity and with very low db
activity.


For example, early this morning in postgresql log:
2017-02-28 06:26:33 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:48 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:50 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:50 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections

And in pgbouncer log:
2017-02-28 06:26:39.035 4080 LOG Stats: 7 req/s, in 322589 b/s, out 2281293
b/s,query 307926 us
2017-02-28 06:27:31.510 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.511 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.511 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.514 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:39.036 4080 LOG Stats: 10 req/s, in 334173 b/s, out 2187475
b/s,query 220037 us
2017-02-28 06:28:39.036 4080 LOG Stats: 7 req/s, in 335683 b/s, out 2287722
b/s,query 370778 us
2017-02-28 06:28:46.595 4080 WARNING C-0x8d56390:
radionew/medios@127.0.0.1:40910 Pooler Error: pgbouncer cannot connect to
server
2017-02-28 06:29:39.037 4080 LOG Stats: 7 req/s, in 275963 b/s, out 1976669
b/s,query 261484 us


Anyway, I'll post a thread in the pgBouncer forum. 
I'll search for some tool to monitor the total number of postgresql
connections through time.

Any comment or suggestion will be appreciated.
Thank you very much for your time!

Best regards,
Lisandro.



--
View this message in context: 
http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946775.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] ERROR: functions in index expression must be marked IMMUTABLE

[Sven R. Kunze]

On 27.02.2017 18:17, Adrian Klaver wrote:
Yes, but is not about timezone dependency, it is about the other 
dependencies listed in the second and third points. Namely the 
datestyle setting and magic strings e.g. 'now'


I am sorry, I still don't understand. to_date and to_timestamp require 
datestyle settings per se and magic strings don't work.



=# -- required datestyle
=# select to_date('2000-01-01');
ERROR:  function to_date(unknown) does not exist
LINE 1: select to_date('2000-01-01');
   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.



=# -- magic strings don't work
=# select to_date('');
ERROR:  invalid value "epoc" for ""
DETAIL:  Value must be an integer.
=# select to_date('epoch', '-MM-DD');
ERROR:  invalid value "epoc" for ""
DETAIL:  Value must be an integer.
=# select to_date('infinity', '-MM-DD');
ERROR:  invalid value "infi" for ""
DETAIL:  Value must be an integer.
=# select to_date('-infinity', '-MM-DD');
ERROR:  invalid value "-inf" for ""
DETAIL:  Value must be an integer.
=# select to_date('now', '-MM-DD');
ERROR:  invalid value "now" for ""
DETAIL:  Value must be an integer.
=# select to_date('today', '-MM-DD');
ERROR:  invalid value "toda" for ""
DETAIL:  Value must be an integer.
=# select to_date('tomorrow', '-MM-DD');
ERROR:  invalid value "tomo" for ""
DETAIL:  Value must be an integer.
=# select to_date('yesterday', '-MM-DD');
ERROR:  invalid value "yest" for ""
DETAIL:  Value must be an integer.
=# select to_date('allballs', '-MM-DD');
ERROR:  invalid value "allb" for ""
DETAIL:  Value must be an integer.

Regards,
Sven

[GENERAL] is (not) distinct from

[Johann Spies]

When I query table a I get 18 rows.
The same query on table b results in 28 rows.

Both tables have the same structure.

When I export the results to csv-files and do a diff it confirms that all
18 rows from a are also in b. Table b has 10 new rows.

When I combine these queries and use "is (not) distinct from"
I get strange results:

with a as (select citing_article, cited_article, pubyear, year_cited,
cited_author, cited_title, cited_work, doi
from wos_2017_1.citation
where citing_article='abcdefg'
order by 3,4,5,6,8),
b as (
select citing_article, cited_article, pubyear, year_cited, cited_author,
cited_title, cited_work, doi
from wos_2017_1.citationbackup
where citing_article='abcdefg'
order by 3,4,5,6,8)
select distinct b.* from b , a
where
( B.citing_article,
 B.cited_article,
 B.pubyear,
 B.year_cited,
 B.cited_author,
 B.cited_title,
 B.cited_work,
B.doi)
 is distinct from
(A.citing_article,
 A.cited_article,
 A.pubyear,
 A.year_cited,
 A.cited_author,
 A.cited_title,
 A.cited_work, A.doi)

The result of this query is 28 rows - thus that of b.
I expected this to be 10.

If I change the query to "is not distinct from" it results in 18 rows
which is what I would have expected.

Regards
Johann.
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)