Re: [GENERAL] BDR Alter table failing

On 28 April 2016 at 02:47, Will McCormick wrote: > So if I wanted to extend a column from 100 characters to 255 characters is > this permitted? The fact that I'm not making a change and the BDR kicked me > out makes me skeptical. > Off the top of my head I'm not sure and

Re: [GENERAL] [NOVICE] Fwd: Process scheduling in postgres

On 4/25/2016 12:40 AM, raghu vineel wrote: Yes, they query the same table. But all queries are *select* only. Postgres is 8.3 and OS is *LINUX 2.6.32-431.el6.x86_64. * Also I could see that pg_locks.granted is true for the queries I have submitted and lock mode is*AccessShareLock.* select

Re: [GENERAL] Background worker with Listen

I don’t want to create client to do some work in the DB if the DB can do it itself on server side much faster. The situation is that some data are inserted in to many tables and if these data need to be post processed a notification is generated. Then the worker needs to wake up and do its

Re: [GENERAL] Proper relational database?

On Apr 23, 2016, at 19:43 , David Bennett wrote: > ow...@postgresql.org] On Behalf Of Eric Schwarzenbach > If I had a few $million to spend in a philanthropical manner, I would hire some of the best PG devs to develop a proper relational database >> server.

[GENERAL] psql color hostname prompt

Hi everyone, The default psql prompt can be a little frustrating when managing many hosts. Typing the wrong command on the wrong host can ruin your day. ;-) I whipped up a psqlrc and companion shell script to provide a colored prompt with the hostname of the machine you're connected to. It

Re: [GENERAL] [NOVICE] Fwd: Process scheduling in postgres

On Sat, Apr 23, 2016 at 11:21 AM, Sameer Kumar wrote: > > > On Fri, Apr 22, 2016 at 5:21 PM raghu vineel > wrote: > >> >> Hi, >> >> I have a 4 core CPU for postgres and I have submitted 6 queries parallely >> in 6 different sessions. But I am

Re: [GENERAL] Proper relational database?

> ow...@postgresql.org] On Behalf Of Eric Schwarzenbach > >> If I had a few $million to spend in a philanthropical manner, I would > >> hire some of the best PG devs to develop a proper relational database > server. > >> Probably a query language that expressed the relational algebra in a > >>

Re: [GENERAL] Proper relational database?

> From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com] > FWIW standard SQL may not allow it but Postgres does, and it's even possible > to exclude duplicates by using an expression that references the whole row. Thank you. I didn't know that. I'll use it if I can verify it works right.

Re: [GENERAL] CREATE EXTENSION without superuser access

On 04/27/2016 04:06 PM, Steve Atkins wrote: On Apr 27, 2016, at 3:47 PM, Adrian Klaver wrote: On 04/27/2016 03:30 PM, Steve Atkins wrote: Is there any way to install an extension either from a SQL connection or from a user-defined directory instead of

Re: [GENERAL] CREATE EXTENSION without superuser access

> On Apr 27, 2016, at 3:47 PM, Adrian Klaver wrote: > > On 04/27/2016 03:30 PM, Steve Atkins wrote: >> Is there any way to install an extension either from a SQL connection or from a user-defined directory instead of .../extensions? >>> >>> Have not

Re: [GENERAL] CREATE EXTENSION without superuser access

On 04/27/2016 03:30 PM, Steve Atkins wrote: On Apr 27, 2016, at 2:47 PM, Adrian Klaver wrote: On 04/27/2016 01:22 PM, Steve Atkins wrote: I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management.

Re: [GENERAL] CREATE EXTENSION without superuser access

On Wed, Apr 27, 2016 at 2:47 PM, Adrian Klaver wrote: > On 04/27/2016 01:22 PM, Steve Atkins wrote: > >> I have an app that would benefit from being able to use pg_partman rather >> than doing it's own ad-hoc partition management. >> >> Unfortunately, some of the

Re: [GENERAL] CREATE EXTENSION without superuser access

> On Apr 27, 2016, at 2:47 PM, Adrian Klaver wrote: > > On 04/27/2016 01:22 PM, Steve Atkins wrote: >> I have an app that would benefit from being able to use pg_partman rather >> than doing it's own ad-hoc partition management. >> >> Unfortunately, some of the

Re: [GENERAL] Does this perf output seem 'normal'?

I have now done a recording for 60 seconds during a batch of 1000 requests and posted the results on a new issue on the Mapnik repo. Although Postgres still comes out on top in the perf results I struggle to believe this is a Postgres issue. But, if anyone is curious, the issue is here:

Re: [GENERAL] CREATE EXTENSION without superuser access

On 04/27/2016 01:22 PM, Steve Atkins wrote: I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management. Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem, so I

[GENERAL] CREATE EXTENSION without superuser access

I have an app that would benefit from being able to use pg_partman rather than doing it's own ad-hoc partition management. Unfortunately, some of the places where the app needs to run don't have root access to the database server filesystem, so I can't install the extension in the postgresql

Re: [GENERAL] BDR Alter table failing

If you change the length of a character varying, it should work. I'm almost sure I have done that before on my BDR cluster. It may work as long as it does not require a full table rewrite. I think, the length change for a character varying won't need a full table rewrite, as the length is only

Re: [GENERAL] BDR Alter table failing

So if I wanted to extend a column from 100 characters to 255 characters is this permitted? The fact that I'm not making a change and the BDR kicked me out makes me skeptical. On Wed, Apr 27, 2016 at 11:56 AM, Craig Ringer wrote: > On 27 April 2016 at 23:43, Alvaro Aguayo

Re: [GENERAL] psql color hostname prompt

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford wrote: > On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte > wrote: >> >> Hi Cal: >> >> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand wrote: >> ... >> > 2) %M vs shell

Re: [GENERAL] psql color hostname prompt

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford wrote: > On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte > wrote: >> >> Hi Cal: >> >> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand wrote: >> ... >> > 2) %M vs shell

Re: [GENERAL] Background worker with Listen

On 4/27/2016 1:40 AM, Ihnat Peter | TSS Group a.s. wrote: I don’t want to create client to do some work in the DB if the DB can do it itself on server side much faster. The situation is that some data are inserted in to many tables and if these data need to be post processed a notification

Re: [GENERAL] BDR Alter table failing

On 27 April 2016 at 23:43, Alvaro Aguayo Garcia-Rada < aagu...@opensysperu.com> wrote: > Based on my experience, I can say BDR does not performs pre-DDL checks. > For example, if you try to CREATE TABLE with the name of an existing table, > BDR will acquire lock anyway, and then will fail when

Re: [GENERAL] Does this perf output seem 'normal'?

> If you really want to profile this, you should fire it off in a tight loop, > using wget or ab2 or curl. Thanks Jeff, that sounds like a smart idea. I will try later when I have access to the server. >Hi! What do you want to see in perf stats? Maybe you can explain your problem >more in

Re: [GENERAL] BDR Alter table failing

But this is the exact column definition that exists on the table when I execute the statement It's like it does not check the pre-existing state of the column. Our code is expecting a column already exists error but this error predicates that. On Wed, Apr 27, 2016 at 10:21 AM, Adrian Klaver

Re: [GENERAL] Slow join over three tables

On Apr 27, Tim van der Linden modulated: ... > I'm joining three fairly large tables together, and it is slow. The tables > are: > > - "reports": 6 million rows > - "report_drugs": 20 million rows > - "report_adverses": 20 million rows > ... > All tables have indexes on the "id"/"rid" columns

Re: [GENERAL] PG 9.5.2, freetds + tds_fdw => server crash

>> postgres@pgreporting:/home/postgres/ [PGREP] cat /etc/centos-release >> CentOS Linux release 7.2.1511 (Core) >> >> Any ideas? >File an issue here: >https://github.com/tds-fdw/tds_fdw/issues Thanks, issue created

Re: [GENERAL] Does PG support in place upgrade

On Wed, Apr 27, 2016 at 10:30:41AM -0400, Bruce Momjian wrote: > On Wed, Apr 27, 2016 at 10:24:36AM -0400, Rakesh Kumar wrote: > > [PUsaBSKn_n] Compose (@composeio) > > 4/26/16, 1:24 PM > > You can now upgrade your #PostgreSQL 9.4 to 9.5 easily at Compose. buff.ly/ > > 1WRsFFu #RDBMS > > > > > >

Re: [GENERAL] Does PG support in place upgrade

On Wed, Apr 27, 2016 at 10:24:36AM -0400, Rakesh Kumar wrote: > [PUsaBSKn_n] Compose (@composeio) > 4/26/16, 1:24 PM > You can now upgrade your #PostgreSQL 9.4 to 9.5 easily at Compose. buff.ly/ > 1WRsFFu #RDBMS > > > Based on the above tweet it seems that PG has no native way of doing an >

[GENERAL] Does PG support in place upgrade

Compose (@composeio) 4/26/16, 1:24 PM You can now upgrade your #PostgreSQL 9.4 to 9.5 easily at Compose. buff.ly/1WRsFFu #RDBMS Based on the above tweet it seems that PG has no native way of doing an inplace upgrade of a db. How do users upgrade db of tera byte size. -- Sent from

Re: [GENERAL] PG 9.5.2, freetds + tds_fdw => server crash

On 04/27/2016 07:11 AM, Daniel Westermann wrote: Hi, I have installed freetds and can connect to the remote mssql server: postgres@pgreporting:/home/postgres/ [PGREP] tsql -S mssql -U ds2user -P xxx -D ds2 -o v locale is

Re: [GENERAL] BDR Alter table failing

On 04/27/2016 07:13 AM, Will McCormick wrote: Why does this not work? From what I read only default values should cause issue. I'm on release 9.4.4: bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH TIME ZONE; ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect

[GENERAL] BDR Alter Table

Why does this not work:

Re: [GENERAL] BDR Alter Table

Hi All, And sorry about that damn thumb pad! Premature send! On Wed, Apr 27, 2016 at 10:15 AM, Adrian Klaver wrote: > On 04/27/2016 07:11 AM, Will McCormick wrote: > >> Why does this not work: >> >> > Because it is NULL :)? > > -- > Adrian Klaver >

Re: [GENERAL] BDR Alter Table

On 04/27/2016 07:11 AM, Will McCormick wrote: Why does this not work: Because it is NULL :)? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] BDR Alter table failing

Why does this not work? From what I read only default values should cause issue. I'm on release 9.4.4: bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH TIME ZONE; ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect UNLOGGED or TEMPORARY tables when BDR is active; trap

[GENERAL] PG 9.5.2, freetds + tds_fdw => server crash

Hi, I have installed freetds and can connect to the remote mssql server: postgres@pgreporting:/home/postgres/ [PGREP] tsql -S mssql -U ds2user -P xxx -D ds2 -o v locale is

Re: [GENERAL] Does this perf output seem 'normal'?

On 27.04.2016 2:27, Peter Devoy wrote: Hi all, I am trying to work out why a piece of software, Mapnik, is executing slowly. All it is doing is loading a config file which causes about 12 preparation queries (i.e. with LIMIT 0) to be executed. I can see from pg_stat_statements these only

[GENERAL] Filtering the results of UNION ALL vs filtering the separate queries

Hi. I'm searching in a medium-sized table (135k rows, 29 columns). Some of the records point to other (parent) records, whose data values have to be used for filtering as well as for joins, instead of the record's own fields. Grouping the different types of records into "subset" views, the query

Re: [GENERAL] Slow join over three tables

On Wed, 27 Apr 2016 13:48:06 +0200 Alban Hertroys wrote: Hi Alban Thanks for chiming in! > Since you're not using age and gender in this (particular) query until the > rows are combined into a result set already, it doesn't make a whole lot of > sense to add them to the

Re: [GENERAL] Slow join over three tables

> On 27 Apr 2016, at 4:09, David Rowley wrote: > > On 27 April 2016 at 11:27, Tim van der Linden wrote: >> The query: >> >> SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug >> FROM reports r >> JOIN report_drugs d ON d.rid = r.id >>

Re: [GENERAL] Slow join over three tables

On Wed, 27 Apr 2016 22:40:43 +1200 David Rowley wrote: Hi David > > ... > > Planning time: 15.968 ms > > Execution time: 4313.755 ms > > > > Both the (rid, adverse) and the (id, age, gender, created) indexes are now > > used. > > > > Seems the (rid, adverse) is

Re: [GENERAL] Slow join over three tables

On 27 April 2016 at 22:29, Tim van der Linden wrote: > Sort (cost=372968.28..372969.07 rows=317 width=41) (actual > time=9308.174..9308.187 rows=448 loops=1) >Sort Key: r.created > Sort (cost=66065.73..66066.59 rows=344 width=41) (actual > time=4313.679..4313.708

Re: [GENERAL] Slow join over three tables

On Wed, 27 Apr 2016 09:14:27 +0300 Victor Yegorov wrote: Hi Victor > > ... > > Can you post output of `EXPLAIN (analyze, buffers)`, please? > It'd be good to check how many buffers are hit/read during Index Scans. Happy to, here it is: Sort (cost=107727.85..107728.71

Re: [GENERAL] Slow join over three tables

On Wed, 27 Apr 2016 14:09:04 +1200 David Rowley wrote: Hi David Thanks for your time on this. I tried your proposals with the results below. > > ... > > Under 5 ms. The same goes for querying the "adverse" column in the > > "report_adverses" table: under 20 ms. >

Re: [GENERAL] Slow join over three tables

On Wed, 27 Apr 2016 01:45:55 + Sameer Kumar wrote: Hi Sameer Thanks for taking the time to look into this! > > ... > Quite clearly the nested loop joins are the most costly operations here. Indeed. > > ... > I suppose. It might help if the filters are performed

Re: [GENERAL] Background worker with Listen

I don’t want to create client to do some work in the DB if the DB can do it itself on server side much faster. The situation is that some data are inserted in to many tables and if these data need to be post processed a notification is generated. Then the worker needs to wake up and do its

Re: [GENERAL] psql color hostname prompt

Hi Cal: On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand wrote: ... > 2) %M vs shell call > %M on when connected to the local machine displays the string "[local]" > which I didn't like. I wanted a real hostname to show no matter which > client/server pair I was using. Zero

Re: [GENERAL] Slow join over three tables

2016-04-27 2:27 GMT+03:00 Tim van der Linden : > The plan: > > Sort (cost=105773.63..105774.46 rows=333 width=76) (actual > time=5143.162..5143.185 rows=448 loops=1) >Sort Key: r.created >Sort Method: quicksort Memory: 60kB >-> Nested Loop (cost=1.31..105759.68