Re: [GENERAL] wal_retrieve_retry_interval

On Mon, May 29, 2017 at 3:58 PM, Ludovic Vaugeois-Pepin wrote: > < 2017-05-29 22:42:17.026 CEST > DEBUG: switched WAL source from > archive to stream after failure > ... (15 seconds later) ... > < 2017-05-29 22:42:32.042 CEST > DEBUG: switched WAL source from > stream to

[GENERAL] PGConf.ASIA and VISA

Hi all, The largest PostgreSQL conference in Japan "PGConf.ASIA" will be held in December 4th to 6th this year. http://www.pgconf.asia/EN/2017/ If you are planning to join and you need a visa to enter Japan, please contact me as soon as possible. I am in charge of issuing invitation letters

Re: [GENERAL] could not fdatasync log file: Input/output error

On Mon, Oct 16, 2017 at 11:47 PM, said assemlal wrote: > Just before we restart the server today, I found only one line as: > > PANIC: could not fdatasync log file 000101760083: Input/output > error > the database system is in recovery mode Ouch. I would not

Re: [GENERAL] REASSIGN OWNED simply doesn't work

David G. Johnston wrote: > ​You could at least fix the documentation bug since this superuser-only > restriction doesn't show up and is in fact contradicted by the sentence > ​"REASSIGN OWNED requires privileges on both the source role(s) and the > target role." The error message that comes back

Re: [GENERAL] REASSIGN OWNED simply doesn't work

I wasn't under the impression that open source meant "totally unsupported, undocumented, and fix it yourself if it's broken." If your attitude is going to be "it's good enough for my needs so I'll just rudely dismiss any problems with it," why merge it at all? And for what it is worth, postgresql

Re: [GENERAL] time series data

Thanks a lot Jeremy, we ended up integrating the code you provided into our software (just before you patent it) :) Best regards, Kkh On Tue, Oct 3, 2017 at 7:58 PM, Schneider wrote: > On Mon, Oct 2, 2017 at 10:27 AM, Khalil Khamlichi >

Re: [GENERAL] Delete Duplicates with Using

FYI, On 10/16/2017 8:58 AM, Igal @ Lucee.org wrote: test=*# with keep as (select max(ctid) as ctid from dubletten group by c1,c2,c3) delete from dubletten where ctid not in (select ctid from keep); I like this solution, but would using a subquery be much slower than the implicit join

Re: [GENERAL] Non-overlapping updates blocking each other

> > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote: > > I'd bet on the last one, especially since you found that the problem > > was a page-level lock. Did you look to see which relation the page > > lock was in? On Mon, Oct 16, 2017, at 12:34 PM, Seamus Abshere wrote: > The specific relation

Re: [GENERAL] Delete Duplicates with Using

Andreas, On 10/15/2017 11:53 PM, Andreas Kretschmer wrote: other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) are identical) test=*# select * from dubletten ;  c1 | c2 | c3 ++   1 |  1 |  1   1 |  1 |  1   1 |  2 |  3   2 |  3 |  4   3 |  4 |  5   4 |  5 |  5  

[GENERAL] ORDER with CASE and Random for each case

Hi, If have a view that I would like to sort where I divide the return in 3 different groups. These 3 groups then should have a random sort order each. As I am I using it with an offset, and limit, the randomness should be the same. For example: SELECT user_id, age FROM view_users ORDER BY CASE

Re: [GENERAL] Non-overlapping updates blocking each other

> Seamus Abshere writes: > > * I do have some exotic indexes [2]. gist, gin, postgis, fillfactor... > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote: > I'd bet on the last one, especially since you found that the problem > was a page-level lock. Did you look to see which

Re: [GENERAL] Using Substitution Variables In PostgreSQL

Thanks for the information. On Mon, Oct 16, 2017 at 3:27 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware > wrote: > >> Hi All, >> >> I wanted to find out how to use a substitution variable in an SQL >> statement

Re: [GENERAL] could not fdatasync log file: Input/output error

Just before we restart the server today, I found only one line as: PANIC: could not fdatasync log file 000101760083: Input/output error the database system is in recovery mode On Mon, Oct 16, 2017 at 10:43 AM said assemlal wrote: > Hi, > > The postgresql

[GENERAL] could not fdatasync log file: Input/output error

Hi, The postgresql crashed on friday due to IO errors. It seems that the filesystem puked. PANIC: could not fdatasync log file 000101760077: Input/output error LOG: database system was interrupted; last known up at 2017-10-13 15:26:28 EDT WARNING: terminating connection because of

Re: [GENERAL] Using Substitution Variables In PostgreSQL

On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware wrote: > Hi All, > > I wanted to find out how to use a substitution variable in an SQL > statement that would cause the user to be prompted for a value. Something > similar to the ampersand (&&) in ORACLE. > > For example,

Re: [GENERAL] Using Substitution Variables In PostgreSQL

On 16/10/2017 17:08, Osahon Oduware wrote: Hi All, I wanted to find out how to use a substitution variable in an SQL statement that would cause the user to be prompted for a value. Something similar to the ampersand (&&) in ORACLE. For example, given the SQL statement below:     SELECT ,,    

Re: [GENERAL] Force SSL connection

rakeshkumar464 writes: > In PG 9.6 or PG 10, is there a way to force only SSL based connections coming > from pgadmin or dbeaver. I think you could set that up with a custom pg_hba.conf entry or two. Something like hostnossl ... pgadmin ... reject before the line

[GENERAL] Using Substitution Variables In PostgreSQL

Hi All, I wanted to find out how to use a substitution variable in an SQL statement that would cause the user to be prompted for a value. Something similar to the ampersand (&&) in ORACLE. For example, given the SQL statement below: SELECT ,, FROM WHERE = 35 I want the user to be

[GENERAL] Force SSL connection

In PG 9.6 or PG 10, is there a way to force only SSL based connections coming from pgadmin or dbeaver. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Non-overlapping updates blocking each other

Seamus Abshere writes: > I hesitate to share my query and indexes because it makes this question > seem more esoteric than I think it really is... but here we go. > * Version 9.6.3. > * I don't have any foreign key constraints. > * I don't use serializable. > * My update query

Re: [GENERAL] Non-overlapping updates blocking each other

Did you try using SELECT FOR NO KEY UPDATE instead of SELECT FOR UPDATE? However: Seamus Abshere wrote: > My current theory is that, since the table is not clustered by id, rows > with very distant ids get stored in the same page, and the whole page is > locked during an update. But we only

Re: [GENERAL] Non-overlapping updates blocking each other

On 2017-10-14 16:32:33 Tom Lane wrote: > More likely explanations for the OP's problem involve foreign key > constraints that cause two different row updates to need to lock > the same referenced row, or maybe he's using some index type that > has greater locking demands than a btree, or he's

[GENERAL] Postgres 10 manual breaks links with anchors

I don't know if this is intentional, but the Postgres 10 manual started to use lowercase IDs as anchors in the manual. So, if I have e.g.: the following URL open in my browser: https://www.postgresql.org/docs/current/static/sql-createindex.html#sql-createindex-concurrently I cannot simply

Re: [GENERAL] Delete Duplicates with Using

Am 14.10.2017 um 08:20 schrieb Igal @ Lucee.org: Hello, I run the SQL query below to delete duplicates from a table. The subquery is used to identify the duplicated rows (row_num is a BIGSERIAL column). other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) are