Re: [GENERAL] Increased I/O / Writes

On 5/17/2016 3:56 PM, Lucas Possamai wrote: *FATAL*: sorry, too many clients already I believe that error means you've exceeded max_connections. query pg_stat_activity and see how many connections are in the various 'states', like.. select state, count(*) from pg_stat_activity group

Re: [GENERAL] Increased I/O / Writes

Following https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ and http://pgtune.leopard.in.ua/ and https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server I changed the shared_buffer from 51GB to 35GB. Now, I'm getting spikes every 15 minutes. *FATAL*: sorry,

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

On Tue, May 17, 2016 at 10:40 AM Alvaro Herrera wrote: > In 9.4, not really. In 9.5 there's a function mxid_age() that gives you > the age of a multixact, so you'd grab the oldest from > pg_database.datminmxid and compute the age of that one. Going from the > oldest

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On Tue, May 17, 2016 at 12:04 AM, Guyren Howe wrote: > On May 16, 2016, at 20:48 , David G. Johnston > wrote: > > > On Monday, May 16, 2016, Guyren Howe wrote: > >> I have this SELECT clause as part of a larger query: >> >>

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On Tue, May 17, 2016 at 2:03 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, May 17, 2016 at 1:36 PM, Guyren Howe wrote: > >> I am trying to use a window function, but it's not working. The subquery >> is effectively aggregating. >> >> > ​I would advise

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On Tue, May 17, 2016 at 1:36 PM, Guyren Howe wrote: > I am trying to use a window function, but it's not working. The subquery > is effectively aggregating. > > ​I would advise avoiding using select-list scalar (and particularly correlated) subqueries when the query level in

Re: [GENERAL] Thoughts on "Love Your Database"

On 17/05/2016 18:38, Guyren Howe wrote: > On May 17, 2016, at 2:22 , Achilleas Mantzios > wrote: >> >> Sorry if I missed something but what's wrong with pgadmin3 ? > > Apart from it's awful, clunky, bug-ridden and crash prone, nothing. In fairness to pgAdmin 3: -

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, What if we have a chain of slaves (master->slave->sec slave)? And if queries would run on each of them? or a config like master ->slave1 ->slave2 Is it ok to enable feedback on both slaves ? -- View this message in context:

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

Steve Kehlet wrote: > On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera > wrote: > > > Not really. Your best bet is to reduce the > > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to > > get rid of multixacts sooner (and/or reduce > >

Re: [GENERAL] Thoughts on "Love Your Database"

On May 17, 2016, at 2:22 , Achilleas Mantzios wrote: > > Sorry if I missed something but what's wrong with pgadmin3 ? Apart from it's awful, clunky, bug-ridden and crash prone, nothing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

I am trying to use a window function, but it's not working. The subquery is effectively aggregating. > On May 17, 2016, at 6:18 , David G. Johnston > wrote: > > On Tue, May 17, 2016 at 12:04 AM, Guyren Howe > wrote: >

Re: [GENERAL] Ascii Elephant for text based protocols - Final

> On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov wrote: > > I had a bit of fun with this SQL version and came up with this query: > > > > WITH src(s) AS ( > > VALUES > >

Re: [GENERAL] PG wire protocol question

On Sat, 14 May 2016 21:58:48 +0200, Boszormenyi Zoltan wrote: >Hi, > >it was a long time I have read this list or written to it. > >Now, I have a question. This blog post was written about 3 years ago: >https://aphyr.com/posts/282-jepsen-postgres > >Basically, it talks about the

Re: [GENERAL] edit wiki

OK, thx On Tue, May 17, 2016 at 6:19 PM, Daniel Gustafsson wrote: > > The wiki editor permission requests should go to the pgsql-www@ > mailinglist, > remember to include your community username in the email. > -- Willy-Bas Loos

Re: [GENERAL] edit wiki

> On 17 May 2016, at 18:10, Willy-Bas Loos wrote: > > Hi, > > I added some code to the script on > https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux > and i would like to share. > The code provides in backing up the globals by means of pg_dumpall -g > > The wiki

[GENERAL] edit wiki

Hi, I added some code to the script on https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux and i would like to share. The code provides in backing up the globals by means of pg_dumpall -g The wiki says i need to ask for editing privileges. BTW nice script. And it could yet be expanded

Re: [GENERAL] Ascii Elephant for text based protocols - Final

On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov wrote: > I had a bit of fun with this SQL version and came up with this query: > > WITH src(s) AS ( > VALUES >

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

Em 17/05/2016 11:07, Merlin Moncure escreveu: On Mon, Apr 25, 2016 at 8:48 PM, Adam Brusselback wrote: It is not difficult to simulate column store in a row store system if you're willing to decompose your tables into (what is essentially) BCNF fragments. It simply

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

On Mon, Apr 25, 2016 at 8:48 PM, Adam Brusselback wrote: >>It is not difficult to simulate column store in a row store system if >>you're willing to decompose your tables into (what is essentially) >>BCNF fragments. It simply is laborious for designers and programmers.

Re: [GENERAL] PG wire protocol question

On Tue, May 17, 2016 at 9:29 AM, Albe Laurenz wrote: > That blog post seems ill-informed - that has nothing to do with > two-phase commit. > > The problem - that the server may commit a transaction, but the client > never receives the server's response - is independent of

Re: [GENERAL] Ascii Elephant for text based protocols - Final

On Tuesday, May 17, 2016 9:41:51 AM EDT Bryan Nuse wrote: > On 05/17/2016 08:25 AM, Victor Yegorov wrote: > > I had a bit of fun with this SQL version and came up with this query: > > > > WITH src(s) AS ( > > > > VALUES > > > >

Re: [GENERAL] Ascii Elephant for text based protocols - Final

On 05/17/2016 08:25 AM, Victor Yegorov wrote: I had a bit of fun with this SQL version and came up with this query: WITH src(s) AS ( VALUES

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

Hello again > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles > Clavadetscher > Sent: Dienstag, 17. Mai 2016 14:50 > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Ascii Elephant for text based

Re: [GENERAL] PG wire protocol question

Boszormenyi Zoltan wrote: > it was a long time I have read this list or written to it. > > Now, I have a question. This blog post was written about 3 years ago: > https://aphyr.com/posts/282-jepsen-postgres > > Basically, it talks about the client AND the server as a system > and if the network

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

On Tue, May 17, 2016 at 12:04 AM, Guyren Howe wrote: > On May 16, 2016, at 20:48 , David G. Johnston > wrote: > > > On Monday, May 16, 2016, Guyren Howe wrote: > >> I have this SELECT clause as part of a larger query: >> >>

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

Hello all > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten Hilbert > Sent: Dienstag, 17. Mai 2016 09:23 > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Ascii Elephant for text based protocols -

Re: [GENERAL] Ascii Elephant for text based protocols - Final

2016-05-16 14:47 GMT+03:00 Charles Clavadetscher : > SQL version by Melvin Davidson: > > CREATE TABLE elephant > (row_num integer NOT NULL, > row_dat varchar(30) NOT NULL, > CONSTRAINT elephant_pk PRIMARY KEY (row_num) > ); > > INSERT INTO elephant > (row_num,

Re: [GENERAL] Increased I/O / Writes

On Tue, May 17, 2016 at 12:11 AM, Lucas Possamai wrote: > This is my postgresql.conf at the moment: > > shared_buffer(51605MB) + > effective_cache_size(96760MB) + > work_mem(32MB) + > max_connections(200) > > = 148397.08 MB You are comparing some very dissimilar settings.

Re: [GENERAL] Connections - Postgres 9.2

Hi I control this way if "state_change" is from longtime and "state" is idle then I use function: *pg_terminate_backend ( integer ) * ==> return TRUE if killed-successful else FALSE example: # select pg_terminate_backend ( pid ) from pg_stat_activity where state='idle' and state_change <

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

Alvaro, is this related or dependent on https://www.pgcon.org/2016/schedule/events/920.en.html ? On Mon, Apr 25, 2016 at 11:20 AM Alvaro Herrera wrote: > Bráulio Bhavamitra wrote: > > Hi all, > > > > I'm finally having performance issues with PostgreSQL when doing big

Re: [GENERAL] Thoughts on "Love Your Database"

On 17 May 2016 at 10:22, Achilleas Mantzios wrote: > On 17/05/2016 12:16, Geoff Winkless wrote: >> >> On 17 May 2016 at 09:34, Pierre Chevalier Géologue >> wrote: >>> >>> On this matter, I hear *very* often from such guys that the only

Re: [GENERAL] Thoughts on "Love Your Database"

On 17/05/2016 12:16, Geoff Winkless wrote: On 17 May 2016 at 09:34, Pierre Chevalier Géologue wrote: On this matter, I hear *very* often from such guys that the only reproach they have to PostgreSQL is that it does not come with a slick GUI like Access. PGAdmin

Re: [GENERAL] Thoughts on "Love Your Database"

On 17 May 2016 at 09:34, Pierre Chevalier Géologue wrote: > On this matter, I hear *very* often from such guys that the only reproach > they have to PostgreSQL is that it does not come with a slick GUI like > Access. PGAdmin does not suit their needs at all: they

Re: [GENERAL] Thoughts on "Love Your Database"

On 5/17/2016 1:34 AM, Pierre Chevalier Géologue wrote: On this matter, I hear *very* often from such guys that the only reproach they have to PostgreSQL is that it does not come with a slick GUI like Access. Access is a lot more than a slick GUI, its a rapid application development system

Re: [GENERAL] Thoughts on "Love Your Database"

Hi, Le 04/05/2016 17:55, Alban Hertroys a écrit : On 4 May 2016 at 17:08, John McKown wrote: I had a manager, long ago, who used a Lotus 1-2-3 spreadsheet to contain all his memos. I was glassy eyed in disbelief. He also would use his hand calculator to add up the

Re: [GENERAL] Thoughts on "Love Your Database"

Hello, (sorry for delay, I've been traveling) Le 04/05/2016 17:08, John McKown a écrit : ... ​Allowing PHBs direct access t​o company data is a nasty thing. Sorry, what is a PHB? Our friend google didn't help me much on this matter. ​Ah. Sorry. PHB is a "Pointy Haired

Re: [GENERAL] Increased I/O / Writes

Hi Why not use https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ to determine your optimal shared_buffers settings? ;-) Cheers Johnny From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Lucas Possamai Sent: Dienstag,

Re: [GENERAL] How to convert firebird stored procedures into postgresql functions

Hello Elusai, I very much doubt there’s a tool for this, given that the procedural language isn’t the same, so you’re looking at converting syntax. As far as I’ve seen PLPgSQL, it’s similar and it should be quite easy to convert Firebird PSQL to this language. With regards, Martijn Tonies

Re: [GENERAL] Ascii Elephant for text based protocols - Final

On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote: > A question to the naming. I find pg_logo() also a good name, but is the > prefix pg_* not reserved for system functions? Of course I could use the > name I want, but was wondering if there is a policy or a best practice in >

Re: [GENERAL] Fast way to delete big table?

Charles Clavadetscher wrote > Hello > > This is quite weird... It there any other process locking the record or > the table? > Bye > Charles Yes, weird. The table is using by other process (keep inserting rows to the table) at the same time but no one should lock the row as we dont touch rows

Re: [GENERAL] Fast way to delete big table?

On Mon, May 16, 2016 at 10:22 PM, hmzha2 wrote: > Works ok in my testing environment but not > on the production machine. Select * from tableA limit 1; takes milliseconds > to finish update summary table from the previous select result, takes > milliseconds delete