Re: [GENERAL] Error in log file after database crash

On Tue, May 10, 2016 at 1:11 AM, Rob Cowell wrote: > Currently I'm running 9.1.10, 9.1.10 has been released in 2013. You are missing 2 years and a half worth of various fixes, so you may want to update to 9.1.22 first. > [2016-05-05 20:09:00 UTC]LOCATION:

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, 9 May 2016 14:56:14 -0700 John R Pierce wrote: > over a tcp socket, there's no way of knowing *WHAT* the system user > is short of querying the unreliable service 'authd' (113/tcp) and > hoping that it A) exists and B) returns something meaningful. > authd/ident

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, 9 May 2016 17:50:52 -0400 Scott Mead wrote: > > was thinking of something like this: > > > > hostall joe@nobody 192.168.151.75/32 password > > hostall all 192.168.151.75/32 ident > > > > The "all@nobody" field is meant to

[GENERAL] Inserting into a master table with partitions does not return rows affected.

I am moving towards a partitioned schema. I use a function to insert into the table. If the INSERT fails because of duplicates I do an UPDATE. This works fine currently on the non-partitioned table because I can use GET DIAGNOSTICS to get the row count on the INSERT. But when I use the Master

Re: [GENERAL] Using both ident and password in pg_hba.conf

"D'Arcy J.M. Cain" writes: > On Mon, 09 May 2016 17:12:22 -0400 > Tom Lane wrote: >> If the same user id + database combinations might be valid in both >> cases (from both PHP and manual connections) I think your only other >> option for distinguishing which

Re: [GENERAL] Using both ident and password in pg_hba.conf

On 5/9/2016 2:42 PM, D'Arcy J.M. Cain wrote: I had an idea that that wouldn't be so easy else we would have had it by now. However, I am not sure that that is what is needed. I was thinking of something like this: hostall joe@nobody 192.168.151.75/32 password hostall

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, May 9, 2016 at 5:42 PM, D'Arcy J.M. Cain wrote: > On Mon, 09 May 2016 17:12:22 -0400 > Tom Lane wrote: > > If the same user id + database combinations might be valid in both > > cases (from both PHP and manual connections) I think your only other > >

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, 09 May 2016 17:12:22 -0400 Tom Lane wrote: > If the same user id + database combinations might be valid in both > cases (from both PHP and manual connections) I think your only other > option for distinguishing which auth method to use is to make them > come in on

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

Robert Anderson writes: > Only one line returned: > postgres=# select * from pg_stat_activity where pid=3990; Aaah, sorry, that was a brain fade. I meant to ask about rows in pg_locks with that pid. regards, tom lane -- Sent via pgsql-general

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, May 09, 2016 at 13:39:48 -0700, Adrian Klaver wrote: The above does not make sense to me. Maybe I am not understanding if you mean connect and login as the same thing or not? I could see connecting as 'nobody' and then doing SET ROLE as user. Or connect as

Re: [GENERAL] Using both ident and password in pg_hba.conf

"D'Arcy J.M. Cain" writes: > Here's my situation. I have a mix of users. Some are running PHP > sites and some are not. PHP runs as the web server owner, "nobody." > Everyone else runs as their own user. > Since the PHP sites run as nobody I want to require password but

Re: [GENERAL] Using both ident and password in pg_hba.conf

On 05/09/2016 01:18 PM, D'Arcy J.M. Cain wrote: On Mon, 9 May 2016 13:02:53 -0700 Adrian Klaver wrote: So define PHP runs as 'nobody'? Because of the way PHP and Apache works PHP script have to run as the Apache user which, in my case anyway, is "nobody" so every

Re: [GENERAL] Using both ident and password in pg_hba.conf

On 5/9/2016 1:18 PM, D'Arcy J.M. Cain wrote: Basically I think that pg_hba.conf is missing a feature. We can specify the database, the user and the address but we can't specify the authenticated user. When it sees this; provided user name (x) and authenticated user name (nobody) do not match

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, 9 May 2016 13:02:53 -0700 Adrian Klaver wrote: > So define PHP runs as 'nobody'? Because of the way PHP and Apache works PHP script have to run as the Apache user which, in my case anyway, is "nobody" so every PHP script runs as nobody. Meanwhile non-PHP

Re: [GENERAL] Using both ident and password in pg_hba.conf

On 05/09/2016 12:44 PM, D'Arcy J.M. Cain wrote: Here's my situation. I have a mix of users. Some are running PHP sites and some are not. PHP runs as the web server owner, "nobody." Everyone else runs as their own user. Since the PHP sites run as nobody I want to require password but accept

[GENERAL] Using both ident and password in pg_hba.conf

Here's my situation. I have a mix of users. Some are running PHP sites and some are not. PHP runs as the web server owner, "nobody." Everyone else runs as their own user. Since the PHP sites run as nobody I want to require password but accept ident (from the server I control) for the rest.

Re: [GENERAL] Ubuntu/Debian PGDP

Re: Peter Eisentraut 2016-05-07 <6f86345a-0658-2cd9-27d9-c381846eb...@2ndquadrant.com> > On 5/7/16 2:43 AM, Vincenzo Romano wrote: > > In a fresh new install of PostgreSQL 9.5.2 on Ubuntu 16.04 I am getting > > this: > > > > ... > > Setting up postgresql-9.5 (9.5.2-1.pgdg16.04+1) ... > >

Re: [GENERAL] Slow query when the select list is big

It works fine now, on my test server execution time went down from 6.4 seconds to 1.4 seconds and on the production server went down from 3.2 sec to 600ms. To optimize the query I changed the order of some joins(the joins that where used to limit rows are at the begining of the query) I tried

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

You still haven't stated why you think it is blocked? Ouput from iotop: 3990 be/4 postgres0.00 B/s0.00 B/s 0.00 % 0.00 % postgres: postgres flip [local] CREATE INDEX The process isn't reading or writing anything for many hours, but it's using almost 90% of CPU. How long has it been

Re: [GENERAL] Slow query when the select list is big

On May 09, Sterpu Victor modulated: > I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if > I don't select from the joined tables. > Now is clear why the query is so mutch more efficient when I select > less data. > > Thank you > With so many joins, you may want to experiment

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

On 05/09/2016 10:32 AM, Robert Anderson wrote: Only one line returned: postgres=# select * from pg_stat_activity where pid=3990; -[ RECORD 1 ]+ datid| 16434 datname | flip pid | 3990 usesysid | 10 usename

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

Only one line returned: postgres=# select * from pg_stat_activity where pid=3990; -[ RECORD 1 ]+ datid| 16434 datname | flip pid | 3990 usesysid | 10 usename | postgres application_name | psql

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

Robert Anderson writes: > There aren't transactions blocking: > postgres=# SELECT > postgres-#w.query as waiting_query, > postgres-#w.pid as waiting_pid, > postgres-#w.usename as w_user, > postgres-#l.pid as blocking_pid, > postgres-#l.usename as

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

Hi, There aren't transactions blocking: postgres=# SELECT postgres-#w.query as waiting_query, postgres-#w.pid as waiting_pid, postgres-#w.usename as w_user, postgres-#l.pid as blocking_pid, postgres-#l.usename as blocking_user, postgres-#t.schemaname || '.' || t.relname

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

Try the following query. See if it shows you if another transaction is blocking the needed locks to create the index. SELECT w.query as waiting_query, w.pid as waiting_pid, w.usename as w_user, l.pid as blocking_pid, l.usename as blocking_user, t.schemaname || '.' || t.relname

[GENERAL] Error in log file after database crash

Hi, One of my test databases "crashed" over the weekend after one of the sysadmin's in the team shut down the systemd container before stopping the database. My question is, how bad is this :) Some background : I have an application that uses Postgres as it's database. Both the application,

Re: [GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

On 05/09/2016 05:04 AM, Robert Anderson wrote: Hi, We are trying to create a index concurrently but, at least apparently, it hangs in a infinite loop and never ends. Apparently how? How long did you wait? JD -- Command Prompt, Inc. http://the.postgres.company/

[GENERAL] Create index concurrently hanging with big table on pgsql 9.3.12

Hi, We are trying to create a index concurrently but, at least apparently, it hangs in a infinite loop and never ends. Our version: flip=# select version(); version PostgreSQL 9.3.12 on

Re: [GENERAL] xml-file as foreign table?

Thanks Arjen, > def q(v): > return b'"' + v.replace(b'"', b'""') + b'"' > > return b','.join(q(f) for f in fields) + b'\n' > > In the end I also had some other problems with the XML (namespaces), so I > used: > > etree.tostring(element, method='c14n', exclusive=True) > This helped. My

Re: [GENERAL] Slow query when the select list is big

I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if I don't select from the joined tables. Now is clear why the query is so mutch more efficient when I select less data. Thank you -- Original Message -- From: "David Rowley" To: "Sterpu

Re: [GENERAL] Slow query when the select list is big

Acctualy the optimization should be cauzed by the filters on the joins that have been moved at the begining of the query. So now postres is making a filter before joining a lot of data. The fact that these join produce multiple rows is not relevant. At least this is what I think is heapening.

Re: [GENERAL] Slow query when the select list is big

2016-05-09 11:01 GMT+03:00 Sterpu Victor : > I went to 2.4 seconds by joining first the tables that produce many rows. As you're changing your query quite often, it'd be handy, if you could post both: - new query version - it's `EXECUTE (analyze, buffers)` output If you

Re: [GENERAL] Slow query when the select list is big

I went to 2.4 seconds by joining first the tables that produce many rows. SELECT row_number() OVER (ORDER BY J1031101.validfrom DESC) AS nrcrt , J1033386.name AS sectie_internare , J1033387.name AS sectie_externare , TO_CHAR(J1031101.validfrom , '-MM-DD HH24:MI') AS validfrom ,

Re: [GENERAL] Slow query when the select list is big

I solved the problem patialy by swithing the order of a join. I tested on a slower server to see better the difference. After moving a single join the query runs in 4.1 seconds insted 6.4 seconds. I pasted the optimized query down. When I move the join J1033704 at the end(last join) the time is

Re: [GENERAL] Slow query when the select list is big

On 9 May 2016 at 18:46, David G. Johnston wrote: > On Sunday, May 8, 2016, Sterpu Victor wrote: >> >> Yes but it is very big. >> I don't understand why the select list is influencing the CPU usage. >> I was expecting that only the join and where

Re: [GENERAL] Slow query when the select list is big

On Sunday, May 8, 2016, Sterpu Victor wrote: > Yes but it is very big. > I don't understand why the select list is influencing the CPU usage. > I was expecting that only the join and where clauses would influence CPU. > > PostgreSQL is smart enough to optimize away stuff that it

Re: [GENERAL] Slow query when the select list is big

On 5/8/2016 11:09 PM, Sterpu Victor wrote: Yes but it is very big. I don't understand why the select list is influencing the CPU usage. I was expecting that only the join and where clauses would influence CPU. what was the query that generated that really complicated execution plan? it sure

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

On 04/05/2016 15:55, Szymon Lipiński wrote: at my previous jobs I was working with many programmers, and almost none of them understood SQL. The problem was even deeper. They didn't want to learn it. When I was working among java programmers, I noticed that they hated SQL, but there was no

Re: [GENERAL] Slow query when the select list is big

Can you share the full query and output of EXPLAIN ? Not much data here yet. On Mon, May 9, 2016 at 6:58 AM Sterpu Victor wrote: > I have a big query that takes about 7 seconds to run(time sending the data > to the client is not counted). > Postgres uses 100% of 1 CPU when