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: XLogFlush, xlog.c:2171
> [2016-05-0
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 services can return virtual
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 specify that the remote use
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 tabl
"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 auth method to use is to make them
>>
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
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
> > option for distinguishing which auth
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 different addresses. Can
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 mailing list (pgsql-gener
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
'nobody' for the PHP scrip
"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 accept
> ident (from th
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 PHP script
runs as nobody. M
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
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 scripts run as the user who owns
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
id
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. The
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) ...
> > Unescap
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 s
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 t
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 wit
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
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
client_add
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 blocking_user,
> postgres-#
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 as
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 as
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, and
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/
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 x86_64-unknown-linux-gnu
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 code
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 Victor"
Cc: "Rob Imig" ; "Postgr
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.
--
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 provide either one or an
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 ,
TO_CH
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
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 clauses would influence CPU.
>>
>
> PostgreSQL is s
35 matches
Mail list logo
