Re: [GENERAL] Understanding pg_last_xlog_receive_location

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

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

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

[GENERAL] Understanding pg_last_xlog_receive_location

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

Re: [GENERAL] json aggregation question

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

[GENERAL] json aggregation question

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

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

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

[GENERAL] array_to_json - dealing with returning no rows

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

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

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

Re: [GENERAL] json aggregation question

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

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

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

Re: [GENERAL] Conferences for a DBA?

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

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

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

Re: [GENERAL] Configuring ssl_crl_file

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

Re: [GENERAL] Configuring ssl_crl_file

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

Re: [GENERAL] Configuring ssl_crl_file

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

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

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

Re: [GENERAL] json aggregation question

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.

Re: [GENERAL] Making a unique constraint deferrable?

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

Re: [GENERAL] Making a unique constraint deferrable?

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

Re: [GENERAL] Making a unique constraint deferrable?

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

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

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

Re: [GENERAL] Making a unique constraint deferrable?

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 |

Re: [GENERAL] Making a unique constraint deferrable?

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 |

Re: [GENERAL] Making a unique constraint deferrable?

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

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

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

[GENERAL] Making a unique constraint deferrable?

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:

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

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,

Re: [GENERAL] Querying JSON Lists

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

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

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

[GENERAL] json aggregation question

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

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

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

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

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

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

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:

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

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

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

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

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

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:

Re: [GENERAL] is (not) distinct from

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.

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

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

Re: [GENERAL] Question about TOAST table - PostgreSQL 9.2

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

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

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

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

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

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

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

[GENERAL] is (not) distinct from

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)