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

[Michael Paquier]

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-05 20:09:00 UTC]WARNING:  58030: could not write block 0 of 
> pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
> [2016-05-05 20:09:00 UTC]DETAIL:  Multiple failures --- write error might 
> be permanent.
> [2016-05-05 20:09:00 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799

This looks like corrupted data, but that's hard to tell with this
level of details. Rolling in a logical backup may be the best way to
go forward here.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[D'Arcy J.M. Cain]

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 virtually anything they want to.

I run both the client web server and the database server.  Outside
machines require passwords.

> when pg_hba.conf is searched, all thats known is the socket type
> (host or local), the database name, the requested(!) username, and if
> its 'host', the source IP address.   this is used to select the
> desired authentication method for that combination.

Yes, it is missing that one piece I suggested - the ability to select
based on the authenticated name.  That's what I am trying to work
around.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VoIP: sip:da...@druid.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[D'Arcy J.M. Cain]

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 user is
> > nobody but that they are connecting as user joe.  You would be able
> > to use "all" as well.  You don't even need to do an ident check
> > unless the auth method is "trust" which would be silly anyway.  In
> > fact "password" is the only method that even makes any sense at all.
> 
> So, at a high-level, you want:
> 
> - Users deploying php scripts in apache to require a password ( btw
> -- use md5, not password)

I was using "password" in the generic sense.

> - Users running php scripts from their shell accounts to connect with
> no password to the database
> 
>   Is that correct?

Absolutely not.  I am allowing ident authentication for users because I
trust the client machine but require password (md5, whatever) when they
want to connect to their database but I can't confirm who they are.

>   Why not just require that everyone use an (again: *md5*) to
> connect?  It would be significantly more secure.  Is their a
> requirement that shell account users be able to connect without
> providing a password?

They aren't actually shell account users.  I have a shell machine too
but this is from the web server.  If I require passwords then they have
to store them in their scripts.  The scripts can be secured from other
users but not admins and since we don't otherwise know their raw
passwords I wouldn't like to expose them, even to us.

Of course PHP scripts have to run as nobody so I have no choice other
than to have them store passwords in various config.php files but PHP
users are used to that.  I would like to fix that but that's a war for
another day.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VoIP: sip:da...@druid.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[rverghese]

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 table to insert into the partitions, GET
DIAGNOSTICS always returns 0. So there is no way of knowing whether a row
was inserted (I am catching the unique violation exception to do the
UPDATE). 

What is a good alternative? We are on 9.4, so the UPSERT is not yet
available to me. 
There should be some way to know if data was inserted into the partition.

Thanks
RV



--
View this message in context: 
http://postgresql.nabble.com/Inserting-into-a-master-table-with-partitions-does-not-return-rows-affected-tp5902708.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

"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
>> come in on different addresses.  Can you set up a secondary IP
>> interface that only the PHP server uses, for example?

> I did think of that but how do I define that in pg_hba?  The host field
> only specifies the remote IP, not the local one.

Right, but you'd be using it essentially as a loopback interface.
Say you set it up as 192.168.0.42 --- you'd tell PHP to connect to
Postgres on 192.168.0.42, and Postgres would also see the PHP connections
as coming in from 192.168.0.42.

I think on most modern OSes you can set up this sort of thing entirely in
software, not even needing a spare NIC card.  I haven't done it that way
though.


> 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   all 192.168.151.75/32   ident  

> The "all@nobody" field is meant to specify that the remote user is
> nobody but that they are connecting as user joe.

As John noted, we don't have any idea what the "remote username" is
at the time we're scanning pg_hba.conf.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[John R Pierce]

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   all 192.168.151.75/32   ident

The "all@nobody" field is meant to specify that the remote user is
nobody but that they are connecting as user joe.  You would be able to
use "all" as well.  You don't even need to do an ident check unless the
auth method is "trust" which would be silly anyway.  In fact "password"
is the only method that even makes any sense at all.



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 virtually anything they want to.



when pg_hba.conf is searched, all thats known is the socket type (host 
or local), the database name, the requested(!) username, and if its 
'host', the source IP address.   this is used to select the desired 
authentication method for that combination.




--
john r pierce, recycling bits in santa cruz

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

[Scott Mead]

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 method to use is to make them
> > come in on different addresses.  Can you set up a secondary IP
> > interface that only the PHP server uses, for example?
>
> I did think of that but how do I define that in pg_hba?  The host field
> only specifies the remote IP, not the local one.
>
> > There's no provision for saying "try this auth method, but if it
> > fails, try subsequent hba lines".  It might be interesting to have
> > that, particularly for methods like ident that don't involve any
> > client interaction.  (Otherwise, you're assuming that the client can
> > cope with multiple challenges, which seems like a large assumption.)
> > I don't have much of a feeling for how hard it would be to do in the
> > server.
>
> 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   all 192.168.151.75/32   ident
>
> The "all@nobody" field is meant to specify that the remote user is
> nobody but that they are connecting as user joe.  You would be able to
> use "all" as well.  You don't even need to do an ident check unless the
> auth method is "trust" which would be silly anyway.  In fact "password"
> is the only method that even makes any sense at all.
>


So, at a high-level, you want:

- Users deploying php scripts in apache to require a password ( btw -- use
md5, not password)
- Users running php scripts from their shell accounts to connect with no
password to the database

  Is that correct?

  Why not just require that everyone use an (again: *md5*) to connect?  It
would be significantly more secure.  Is their a requirement that shell
account users be able to connect without providing a password?

  (NB:
http://www.postgresql.org/docs/9.4/static/auth-methods.html#AUTH-PASSWORD
 password will send the password in cleartext, md5 will tell libpq to hash
the password for you.  No client-level change).



> --
> D'Arcy J.M. Cain  |  Democracy is three wolves
> http://www.druid.net/darcy/|  and a sheep voting on
> +1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
> IM: da...@vex.net, VoIP: sip:da...@druid.net
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

[D'Arcy J.M. Cain]

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 you set up a secondary IP
> interface that only the PHP server uses, for example?

I did think of that but how do I define that in pg_hba?  The host field
only specifies the remote IP, not the local one.

> There's no provision for saying "try this auth method, but if it
> fails, try subsequent hba lines".  It might be interesting to have
> that, particularly for methods like ident that don't involve any
> client interaction.  (Otherwise, you're assuming that the client can
> cope with multiple challenges, which seems like a large assumption.)
> I don't have much of a feeling for how hard it would be to do in the
> server.

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   all 192.168.151.75/32   ident  

The "all@nobody" field is meant to specify that the remote user is
nobody but that they are connecting as user joe.  You would be able to
use "all" as well.  You don't even need to do an ident check unless the
auth method is "trust" which would be silly anyway.  In fact "password"
is the only method that even makes any sense at all.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VoIP: sip:da...@druid.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Bruno Wolff III]

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 script and have a separate connection as the 
database user. Otherwise you are going to have to explain more about 
what you are doing.


The mapping is between system and postgres users. So that the system user 
nobody is allowed to login as any of the postgres users a, b or c.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

"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 the server I control) for the rest.  There does not appear
> to be a way to specif that.  Here was one attempt:

> hostall   nobody   192.168.151.75/32   password
> hostall   all  192.168.151.75/32   ident

> But that doesn't work.  The actual user according to ident is nobody
> but the request is for a specific user.  As a result it isn't
> recognized by the first line so it tries ident anyway and fails.

> Is there any way to accomplish what I want?  Any help appreciated.

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 you set up a secondary IP interface that only
the PHP server uses, for example?

There's no provision for saying "try this auth method, but if it fails,
try subsequent hba lines".  It might be interesting to have that,
particularly for methods like ident that don't involve any client
interaction.  (Otherwise, you're assuming that the client can cope
with multiple challenges, which seems like a large assumption.)
I don't have much of a feeling for how hard it would be to do in the
server.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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.  Meanwhile non-PHP scripts run as the user who owns the
site.


Is that the script's user permissions?


Sometimes.  The user has the choice to have everything owned by nobody
(which requires that they contact us for changes) or else as themself
but with world readable permissions on the files so that nobody can
serve them.


Or is that the database user the script is connecting as?


Yes.


Is 'nobody' defined as a database user?


Yes but each user has their own database with their own user and
password.  When they run PHP scripts they connect as nobody but they
attempt to login as themself.


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 script and have a separate connection as the database user. 
Otherwise you are going to have to explain more about what you are doing.




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

I would like it to connect with user x but drop to password
authentication.


Again this seems to assume a given connection can have two user names at 
the same time. As John pointed out there is mapping but it still 
resolves to only one name for the actual connection.







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[John R Pierce]

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

I would like it to connect with user x but drop to password
authentication.


'ident' is only secure over local 'domain' sockets, not over tcp/ip.

that said, you can use an ident user map to do what you want, this would 
say '"nobody" can log on as A, B, or C'





--
john r pierce, recycling bits in santa cruz

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

[D'Arcy J.M. Cain]

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 the
site.

> Is that the script's user permissions?

Sometimes.  The user has the choice to have everything owned by nobody
(which requires that they contact us for changes) or else as themself
but with world readable permissions on the files so that nobody can
serve them.

> Or is that the database user the script is connecting as?

Yes.

> Is 'nobody' defined as a database user?

Yes but each user has their own database with their own user and
password.  When they run PHP scripts they connect as nobody but they
attempt to login as themself.

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

I would like it to connect with user x but drop to password
authentication.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VoIP: sip:da...@druid.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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
ident (from the server I control) for the rest.  There does not appear
to be a way to specif that.  Here was one attempt:

hostall   nobody   192.168.151.75/32   password
hostall   all  192.168.151.75/32   ident

But that doesn't work.  The actual user according to ident is nobody
but the request is for a specific user.  As a result it isn't
recognized by the first line so it tries ident anyway and fails.

Is there any way to accomplish what I want?  Any help appreciated.


So define PHP runs as 'nobody'?

Is that the script's user permissions?

Or is that the database user the script is connecting as?

Is 'nobody' defined as a database user?



Cheers.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[D'Arcy J.M. Cain]

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.  There does not appear
to be a way to specif that.  Here was one attempt:

hostall   nobody   192.168.151.75/32   password
hostall   all  192.168.151.75/32   ident

But that doesn't work.  The actual user according to ident is nobody
but the request is for a specific user.  As a result it isn't
recognized by the first line so it tries ident anyway and fails.

Is there any way to accomplish what I want?  Any help appreciated.

Cheers.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VoIP: sip:da...@druid.net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Ubuntu/Debian PGDP

[Christoph Berg]

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) ...
> > Unescaped left brace in regex is deprecated, passed through in regex;
> > marked by <-- HERE in m/(? > /usr/sbin/pam_getenv line 78.
> 
> This issue is known and being worked on.  It's only a deprecation warning,
> so you can ignore it for now.

Furthermore, it's not even a bug in PostgreSQL, it's a warning from
pam_getenv which is used in the setup scripts.

Christoph


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Sterpu Victor]

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 some of these parameters, I will try all tomorow.

-- Original Message --
From: "Karl Czajkowski" 
To: "Sterpu Victor" 
Cc: "PostgreSQL General" 
Sent: 9/5/2016 8:47:12 PM
Subject: Re: 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 with postgresql
parameter tuning. These parameters in particular can have a
significant impact on the plan choice and execution time:

   work_mem
   effective_cache_size

   from_collapse_limit
   join_collapse_limit

   geqo_threshold
   geqo_effort

Setting these to appropriately large values can make analytic queries
run much faster. Of course, setting them too high can also make for
very bad plans which cause the DB server to over subscribe its memory
and start swapping... it requires a bit of reading and a bit of
experimentation to find ideal settings for your environment.


Karl





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Robert Anderson]

 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 taking?

backend_start| 2016-05-07 11:48:39.218398-03

More than 50 hours.

What is your maintenance_work_mem set to?

maintenance_work_mem = 352MB



2016-05-09 14:34 GMT-03:00 Joshua D. Drake :

> 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  | postgres
>> application_name | psql
>> client_addr  |
>> client_hostname  |
>> client_port  | -1
>> backend_start| 2016-05-07 11:48:39.218398-03
>> xact_start   | 2016-05-07 11:48:43.417734-03
>> query_start  | 2016-05-07 11:48:43.417734-03
>> state_change | 2016-05-07 11:48:43.417742-03
>> waiting  | f
>> state| active
>> query| CREATE INDEX CONCURRENTLY index_texto
>>   |   ON flip_pagina_edicao
>>   |   USING hash
>>   |   (texto COLLATE pg_catalog."default");
>>
>
> So it isn't blocked by a lock. You still haven't stated why you think it
> is blocked? How long has it been taking? What is your maintenance_work_mem
> set to?
>
>
> JD
>
>
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>

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

[Karl Czajkowski]

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 with postgresql
parameter tuning.  These parameters in particular can have a
significant impact on the plan choice and execution time:

   work_mem
   effective_cache_size

   from_collapse_limit
   join_collapse_limit

   geqo_threshold
   geqo_effort

Setting these to appropriately large values can make analytic queries
run much faster.  Of course, setting them too high can also make for
very bad plans which cause the DB server to over subscribe its memory
and start swapping...  it requires a bit of reading and a bit of
experimentation to find ideal settings for your environment.


Karl



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Joshua D. Drake]

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  | postgres
application_name | psql
client_addr  |
client_hostname  |
client_port  | -1
backend_start| 2016-05-07 11:48:39.218398-03
xact_start   | 2016-05-07 11:48:43.417734-03
query_start  | 2016-05-07 11:48:43.417734-03
state_change | 2016-05-07 11:48:43.417742-03
waiting  | f
state| active
query| CREATE INDEX CONCURRENTLY index_texto
  |   ON flip_pagina_edicao
  |   USING hash
  |   (texto COLLATE pg_catalog."default");


So it isn't blocked by a lock. You still haven't stated why you think it 
is blocked? How long has it been taking? What is your 
maintenance_work_mem set to?


JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Robert Anderson]

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_addr  |
client_hostname  |
client_port  | -1
backend_start| 2016-05-07 11:48:39.218398-03
xact_start   | 2016-05-07 11:48:43.417734-03
query_start  | 2016-05-07 11:48:43.417734-03
state_change | 2016-05-07 11:48:43.417742-03
waiting  | f
state| active
query| CREATE INDEX CONCURRENTLY index_texto
 |   ON flip_pagina_edicao
 |   USING hash
 |   (texto COLLATE pg_catalog."default");

postgres=#

2016-05-09 14:20 GMT-03:00 Tom Lane :

> 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-#t.schemaname || '.' || t.relname as tablename
> > postgres-#FROM pg_stat_activity w
> > postgres-#JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
> > postgres-#JOIN pg_locks l2 on (l1.relation = l2.relation and
> l2.granted)
> > postgres-#JOIN pg_stat_activity l ON (l2.pid = l.pid)
> > postgres-#JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
> > postgres-#WHERE w.waiting;
> >  waiting_query | waiting_pid | w_user | blocking_pid | blocking_user |
> > tablename
> >
> ---+-++--+---+---
> > (0 rows)
>
> This test proves little, because that last JOIN will discard locks on
> non-table objects, and what CREATE INDEX CONCURRENTLY would be most
> likely to be blocked on is transaction VXIDs.  However, since
> pg_stat_activity claims that "waiting" is false, probably there isn't
> anything in pg_locks.  Still, it'd be better to do
> "select * from pg_stat_activity where pid = 3990" and be sure.
>
> regards, tom lane
>

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

[Tom Lane]

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-#t.schemaname || '.' || t.relname as tablename
> postgres-#FROM pg_stat_activity w
> postgres-#JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
> postgres-#JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
> postgres-#JOIN pg_stat_activity l ON (l2.pid = l.pid)
> postgres-#JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
> postgres-#WHERE w.waiting;
>  waiting_query | waiting_pid | w_user | blocking_pid | blocking_user |
> tablename
> ---+-++--+---+---
> (0 rows)

This test proves little, because that last JOIN will discard locks on
non-table objects, and what CREATE INDEX CONCURRENTLY would be most
likely to be blocked on is transaction VXIDs.  However, since
pg_stat_activity claims that "waiting" is false, probably there isn't
anything in pg_locks.  Still, it'd be better to do
"select * from pg_stat_activity where pid = 3990" and be sure.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Robert Anderson]

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 tablename
postgres-#FROM pg_stat_activity w
postgres-#JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
postgres-#JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
postgres-#JOIN pg_stat_activity l ON (l2.pid = l.pid)
postgres-#JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
postgres-#WHERE w.waiting;
 waiting_query | waiting_pid | w_user | blocking_pid | blocking_user |
tablename
---+-++--+---+---
(0 rows)

How long I'm waiting:

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_activity where query like 'CREATE%';
-[ RECORD 1 ]+
datid| 16434
datname  | flip
pid  | 3990
usesysid | 10
usename  | postgres
application_name | psql
client_addr  |
client_hostname  |
client_port  | -1
backend_start| 2016-05-07 11:48:39.218398-03
xact_start   | 2016-05-07 11:48:43.417734-03
query_start  | 2016-05-07 11:48:43.417734-03
state_change | 2016-05-07 11:48:43.417742-03
waiting  | f
state| active
query| CREATE INDEX CONCURRENTLY index_texto
 |   ON flip_pagina_edicao
 |   USING hash
 |   (texto COLLATE pg_catalog."default");

postgres=#


In attachment follows a strace sample of the running process.

2016-05-09 13:25 GMT-03:00 Melvin Davidson :

> 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 tablename
>FROM pg_stat_activity w
>JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
>JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
>JOIN pg_stat_activity l ON (l2.pid = l.pid)
>JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
>WHERE w.waiting;
>
>
> On Mon, May 9, 2016 at 11:50 AM, Joshua D. Drake 
> wrote:
>
>> 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/
>> +1-503-667-4564
>> PostgreSQL Centered full stack support, consulting and development.
>> Everyone appreciates your honesty, until you are honest with them.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>


strace.txt.tar.gz
Description: GNU Zip compressed data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

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 tablename
   FROM pg_stat_activity w
   JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
   JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
   JOIN pg_stat_activity l ON (l2.pid = l.pid)
   JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
   WHERE w.waiting;


On Mon, May 9, 2016 at 11:50 AM, Joshua D. Drake 
wrote:

> 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/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Error in log file after database crash

[Rob Cowell]

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 postgres will be upgraded to newer versions in the 
coming weeks.
As part of a test-run I've upgraded the application to ensure that works, 
before I upgrade the database.
The application is undergoing some UAT testing prior to having the database 
upgraded.
Currently I'm running 9.1.10, and will be upgrading to the latest release of 
9.3.x (not my choice of version, but the application currently doesn't support 
9.5.x)

I've taken a full file-system backup of the database directories (with the 
database shut down), and a pg_dumpall.
Having restarted the database everything seems okay until the logs start 
showing these errors :


[2016-05-05 20:08:55 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799
[2016-05-05 20:08:56 UTC]ERROR:  XX000: xlog flush request 36D0/E8301358 is 
not satisfied --- flushed only to 36CA/94E12DC0
[2016-05-05 20:08:56 UTC]CONTEXT:  writing block 0 of relation 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:56 UTC]LOCATION:  XLogFlush, xlog.c:2171
[2016-05-05 20:08:56 UTC]WARNING:  58030: could not write block 0 of 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:56 UTC]DETAIL:  Multiple failures --- write error might 
be permanent.
[2016-05-05 20:08:56 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799
[2016-05-05 20:08:57 UTC]ERROR:  XX000: xlog flush request 36D0/E8301358 is 
not satisfied --- flushed only to 36CA/94E12DC0
[2016-05-05 20:08:57 UTC]CONTEXT:  writing block 0 of relation 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:57 UTC]LOCATION:  XLogFlush, xlog.c:2171
[2016-05-05 20:08:57 UTC]WARNING:  58030: could not write block 0 of 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:57 UTC]DETAIL:  Multiple failures --- write error might 
be permanent.
[2016-05-05 20:08:57 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799
[2016-05-05 20:08:58 UTC]ERROR:  XX000: xlog flush request 36D0/E8301358 is 
not satisfied --- flushed only to 36CA/94E12DC0
[2016-05-05 20:08:58 UTC]CONTEXT:  writing block 0 of relation 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:58 UTC]LOCATION:  XLogFlush, xlog.c:2171
[2016-05-05 20:08:58 UTC]WARNING:  58030: could not write block 0 of 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:58 UTC]DETAIL:  Multiple failures --- write error might 
be permanent.
[2016-05-05 20:08:58 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799
[2016-05-05 20:08:59 UTC]ERROR:  XX000: xlog flush request 36D0/E8301358 is 
not satisfied --- flushed only to 36CA/94E12DC0
[2016-05-05 20:08:59 UTC]CONTEXT:  writing block 0 of relation 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:59 UTC]LOCATION:  XLogFlush, xlog.c:2171
[2016-05-05 20:08:59 UTC]WARNING:  58030: could not write block 0 of 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:08:59 UTC]DETAIL:  Multiple failures --- write error might 
be permanent.
[2016-05-05 20:08:59 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799
[2016-05-05 20:09:00 UTC]ERROR:  XX000: xlog flush request 36D0/E8301358 is 
not satisfied --- flushed only to 36CA/94E12DC0
[2016-05-05 20:09:00 UTC]CONTEXT:  writing block 0 of relation 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:09:00 UTC]LOCATION:  XLogFlush, xlog.c:2171
[2016-05-05 20:09:00 UTC]WARNING:  58030: could not write block 0 of 
pg_tblspc/16395/PG_9.1_201105231/16400/33044487_vm
[2016-05-05 20:09:00 UTC]DETAIL:  Multiple failures --- write error might 
be permanent.
[2016-05-05 20:09:00 UTC]LOCATION:  AbortBufferIO, bufmgr.c:2799

Cheers,
Rob.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Joshua D. Drake]

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/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Robert Anderson]

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, compiled by gcc (GCC) 4.4.7
2012
0313 (Red Hat 4.4.7-16), 64-bit
(1 row)


Index creation:

CREATE INDEX CONCURRENTLY index_texto
  ON flip_pagina_edicao
  USING hash
  (texto COLLATE pg_catalog."default");  -- texto is a text data type.

Size of the table:

flip=# select pg_size_pretty(pg_total_relation_size('flip_pagina_edicao'));
 pg_size_pretty

 956 GB
(1 row)


Process strace:


semop(622611, {{6, 1, 0}}, 1)   = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(622611, {{6, 1, 0}}, 1)   = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(622611, {{6, 1, 0}}, 1)   = 0
semop(622611, {{6, 1, 0}}, 1)   = 0
semop(622611, {{6, 1, 0}}, 1)   = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(622611, {{6, 1, 0}}, 1)   = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(622611, {{6, 1, 0}}, 1)   = 0
semop(557073, {{2, 1, 0}}, 1)   = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
semop(655380, {{5, -1, 0}}, 1)  = 0
...


Thanks in advance.

Robert

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

[Johann Spies]

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 is now doing it's job.

Regards
Johann

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

[Sterpu Victor]

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" ; "PostgreSQL General" 
; "David G. Johnston" 


Sent: 9/5/2016 10:04:54 AM
Subject: 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 clauses would influence 
CPU.




 PostgreSQL is smart enough to optimize away stuff that it knows 
doesn't

 impact the final query result.


To be more accurate with what David is saying, PostgreSQL will remove
unused LEFT JOINed relations where the left joined relation can be
proved to not duplicate rows from the right hand side. It would just
be a matter of comparing the EXPLAINs from the query with all the
SELECT items to the one with the single SELECT item to prove that this
is what's happening.

Please also note that this only occurs with LEFT JOINs

It would also be quite helpful for people if you were to include a
copy of the query. It's impossible to reverse engineer what that is
from this EXPLAIN output. I see that your using a windowing function
and performing a LIMIT 1, there may be ways to improve that just by
selecting the single highest j1031101.validfrom row and performing the
joins to the other table on that single row, but that will depend on
which windowing function you're using as the function may require the
other rows in the window frame to calculate the correct result.

--
 David Rowley http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Sterpu Victor]

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.

-- Original Message --
From: "Sterpu Victor" 
To: "Sterpu Victor" ; "David Rowley" 

Cc: "Rob Imig" ; "PostgreSQL General" 
; "David G. Johnston" 


Sent: 9/5/2016 11:01:56 AM
Subject: Re[2]: [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 ,
TO_CHAR(J1033359.validto , '-MM-DD HH24:MI') AS validto ,
CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10) THEN(1) ELSE(0) END AS 
semnat_internare ,
CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10) THEN(1) 
ELSE(0) END AS semnat_externare ,

J1031076.name AS sex ,
J1031074.id AS id ,
J1031074.siui_appid AS siui_appid ,
J1031074.data_adeverinta AS data_adeverinta ,
J1031074.is_paliativ AS text_paliativ ,
J1031074.cardno AS cardno ,
J1031074.cardno_externare AS cardno_externare ,
J1031074.sign_date AS sign_date ,
J1031074.sign_date_externare AS sign_date_externare ,
J1031074.unsigned_string AS unsigned_string ,
J1031074.unsigned_string_externare AS unsigned_string_externare ,
J1031074.signhash AS signhash ,
J1031074.signhash_externare AS signhash_externare ,
J1031074.signature AS signature ,
J1031074.signature_externare AS signature_externare ,
J1031074.send_xml AS send_xml ,
J1031074.send_xml_externare AS send_xml_externare ,
J1031074.received_xml AS received_xml ,
J1031074.received_xml_externare AS received_xml_externare ,
J1031074.error AS error ,
J1031074.error_externare AS error_externare ,
J1031074.validat AS validat ,
J1031074.validat_externare AS validat_externare ,
J1031074.online AS online ,
J1031074.online_externare AS online_externare ,
J1031074.serie_bilet_internare AS text_serie_bilet_internare ,
J1031074.nr_bilet_internare AS text_numar_bilet_internare ,
J1031074.idpatient AS _popup_cnp_pacient ,
J1031075.cnp AS popup_cnp_pacient ,
J1031075.name AS text_nume_pacient ,
J1031075.surname AS text_prenume_pacient ,
J1031074.nrfo AS text_numar_fosz ,
J1031074.greutate_nastere AS text_greutate_nastere ,
J1031078.value AS popup_tip_asigurare ,
J1031074.idensuredstatustype AS _popup_tip_asigurare ,
J1031079.value AS popup_statut_asigurat ,
J1031074.idensuredstatus AS _popup_statut_asigurat ,
J1031080.code AS popup_cas_asigurat ,
J1031074.id_org_unit AS _popup_cas_asigurat ,
J1031081.code AS popup_categorie_asigurare ,
J1031074.id_categorie_asigurat AS _popup_categorie_asigurare ,
J1031082.name AS popup_tip_internare ,
J1031074.id_focg_tip_internare AS _popup_tip_internare ,
J1031083.name AS popup_criteriu_internare ,
J1031074.id_focg_criteriu_internare AS _popup_criteriu_internare ,
J1031084.stencil_no AS popup_medic_curant ,
J1031084.id AS _popup_medic_curant ,
J1031089.value AS popup_nivel_instruire ,
J1031074.id_education_level AS _popup_nivel_instruire ,
J1031074.greutate AS text_greutate_internare ,
J1031090.nume AS popup_situatii_speciale ,
J1031074.id_focg_situatii_speciale AS _popup_situatii_speciale ,
J1031091.nume AS popup_internat_prin ,
J1031091.id AS _popup_internat_prin ,
J1031092.nume AS popup_formulare_europene ,
J1031074.id_formulare_europene AS _popup_formulare_europene ,
J1031074.id_cnp_mama AS _popup_cnp_mama ,
J1031094.cnp AS popup_cnp_mama ,
J1031093.nrfo AS popup_fo_mama ,
J1031074.id_focg AS _popup_fo_mama ,
J1031074.nr_card_euro AS text_nr_card_european ,
J1031074.nr_pasaport AS text_nr_pasaport ,
J1031074.nr_card_national AS text_nr_card_national ,
J1031088.id AS _popup_ocupatia ,
J1031088.name AS popup_ocupatia ,
J1031074.export_drg AS export_drg ,
J1031074.drgcaseid AS drgcaseid ,
J1031074.export_ecosoft AS export_ecosoft ,
J1031074.mesaj_drg AS mesaj_drg ,
J1031074.uid AS uid ,
J1031074.mesaj_ecosoft AS mesaj_ecosoft ,
J1031074.id_address_domiciliu AS text_id_address_domiciliu ,
J1031074.id_address_domiciliu AS _text_id_address_domiciliu ,
J1031074.id_address_resedinta AS _text_id_address_resedinta ,
J1031074.id_address_resedinta AS text_id_address_resedinta ,
'0' AS marcator ,
J1031095.id AS _popup_sursa_internare ,
J1031095.denumire AS popup_sursa_internare ,
J1031096.id AS _popup_diseasecategory ,
J1031096.code AS popup_diseasecategory ,
J1031097.id AS _popup_diagnostic_internare_icd10 ,
J1031097.name AS popup_diagnostic_internare_icd10 ,
J1031098.id AS _popup_mod_contract ,
J1031098.description AS popup_mod_contract ,
J1031099.id AS _popup_criteriu_urgenta ,
J1031099.name AS popup_criteriu_urgenta ,

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

[Victor Yegorov]

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 another, but not both, it is difficult to
comment on your case.


You can use http://explain.depesz.com/ to post a link to your execution
plan here.


-- 
Victor Y. Yegorov

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

[Sterpu Victor]

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_CHAR(J1033359.validto , '-MM-DD HH24:MI')  AS  validto ,
CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10)  THEN(1)  ELSE(0) END  
AS  semnat_internare ,
CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10)  THEN(1)  
ELSE(0) END  AS  semnat_externare ,

J1031076.name  AS  sex ,
J1031074.id  AS  id ,
J1031074.siui_appid  AS  siui_appid ,
J1031074.data_adeverinta  AS  data_adeverinta ,
J1031074.is_paliativ  AS  text_paliativ ,
J1031074.cardno  AS  cardno ,
J1031074.cardno_externare  AS  cardno_externare ,
J1031074.sign_date  AS  sign_date ,
J1031074.sign_date_externare  AS  sign_date_externare ,
J1031074.unsigned_string  AS  unsigned_string ,
J1031074.unsigned_string_externare  AS  unsigned_string_externare ,
J1031074.signhash  AS  signhash ,
J1031074.signhash_externare  AS  signhash_externare ,
J1031074.signature  AS  signature ,
J1031074.signature_externare  AS  signature_externare ,
J1031074.send_xml  AS  send_xml ,
J1031074.send_xml_externare  AS  send_xml_externare ,
J1031074.received_xml  AS  received_xml ,
J1031074.received_xml_externare  AS  received_xml_externare ,
J1031074.error  AS  error ,
J1031074.error_externare  AS  error_externare ,
J1031074.validat  AS  validat ,
J1031074.validat_externare  AS  validat_externare ,
J1031074.online  AS  online ,
J1031074.online_externare  AS  online_externare ,
J1031074.serie_bilet_internare  AS  text_serie_bilet_internare ,
J1031074.nr_bilet_internare  AS  text_numar_bilet_internare ,
J1031074.idpatient  AS  _popup_cnp_pacient ,
J1031075.cnp  AS  popup_cnp_pacient ,
J1031075.name  AS  text_nume_pacient ,
J1031075.surname  AS  text_prenume_pacient ,
J1031074.nrfo  AS  text_numar_fosz ,
J1031074.greutate_nastere  AS  text_greutate_nastere ,
J1031078.value  AS  popup_tip_asigurare ,
J1031074.idensuredstatustype  AS  _popup_tip_asigurare ,
J1031079.value  AS  popup_statut_asigurat ,
J1031074.idensuredstatus  AS  _popup_statut_asigurat ,
J1031080.code  AS  popup_cas_asigurat ,
J1031074.id_org_unit  AS  _popup_cas_asigurat ,
J1031081.code  AS  popup_categorie_asigurare ,
J1031074.id_categorie_asigurat  AS  _popup_categorie_asigurare ,
J1031082.name  AS  popup_tip_internare ,
J1031074.id_focg_tip_internare  AS  _popup_tip_internare ,
J1031083.name  AS  popup_criteriu_internare ,
J1031074.id_focg_criteriu_internare  AS  _popup_criteriu_internare ,
J1031084.stencil_no  AS  popup_medic_curant ,
J1031084.id  AS  _popup_medic_curant ,
J1031089.value  AS  popup_nivel_instruire ,
J1031074.id_education_level  AS  _popup_nivel_instruire ,
J1031074.greutate  AS  text_greutate_internare ,
J1031090.nume  AS  popup_situatii_speciale ,
J1031074.id_focg_situatii_speciale  AS  _popup_situatii_speciale ,
J1031091.nume  AS  popup_internat_prin ,
J1031091.id  AS  _popup_internat_prin ,
J1031092.nume  AS  popup_formulare_europene ,
J1031074.id_formulare_europene  AS  _popup_formulare_europene ,
J1031074.id_cnp_mama  AS  _popup_cnp_mama ,
J1031094.cnp  AS  popup_cnp_mama ,
J1031093.nrfo  AS  popup_fo_mama ,
J1031074.id_focg  AS  _popup_fo_mama ,
J1031074.nr_card_euro  AS  text_nr_card_european ,
J1031074.nr_pasaport  AS  text_nr_pasaport ,
J1031074.nr_card_national  AS  text_nr_card_national ,
J1031088.id  AS  _popup_ocupatia ,
J1031088.name  AS  popup_ocupatia ,
J1031074.export_drg  AS  export_drg ,
J1031074.drgcaseid  AS  drgcaseid ,
J1031074.export_ecosoft  AS  export_ecosoft ,
J1031074.mesaj_drg  AS  mesaj_drg ,
J1031074.uid  AS  uid ,
J1031074.mesaj_ecosoft  AS  mesaj_ecosoft ,
J1031074.id_address_domiciliu  AS  text_id_address_domiciliu ,
J1031074.id_address_domiciliu  AS  _text_id_address_domiciliu ,
J1031074.id_address_resedinta  AS  _text_id_address_resedinta ,
J1031074.id_address_resedinta  AS  text_id_address_resedinta ,
'0'  AS  marcator ,
J1031095.id  AS  _popup_sursa_internare ,
J1031095.denumire  AS  popup_sursa_internare ,
J1031096.id  AS  _popup_diseasecategory ,
J1031096.code  AS  popup_diseasecategory ,
J1031097.id  AS  _popup_diagnostic_internare_icd10 ,
J1031097.name  AS  popup_diagnostic_internare_icd10 ,
J1031098.id  AS  _popup_mod_contract ,
J1031098.description  AS  popup_mod_contract ,
J1031099.id  AS  _popup_criteriu_urgenta ,
J1031099.name  AS  popup_criteriu_urgenta ,
J1031100.id  AS  _popup_exceptie_bi ,
J1031100.code  AS  popup_exceptie_bi ,
J1031074.scrisoare_medicala_parafa  AS  text_parafa_scrisoare_medicala ,
J1031074.scrisoare_medicala_contract  AS  
text_contract_scrisoare_medicala ,
J1031074.scrisoare_medicala_tip_contract  AS  
text_tip_contract_scrisoare_medicala ,

J1031074.export_siui  AS  export_siui ,
J1031074.mesaj_siui  AS  mesaj_siui ,
J1031087.id  AS  _popup_intocmit ,
J1031087.stencil_no  AS  popup_intocmit ,

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

[Sterpu Victor]

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 increased to 6.4 seconds.


SELECT
row_number() OVER (ORDER BY J1031101.validfrom DESC)  AS  nrcrt ,
CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10)  THEN(1)  ELSE(0) END  
AS  semnat_internare ,
CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10)  THEN(1)  
ELSE(0) END  AS  semnat_externare ,

J1031076.name  AS  sex ,
J1031074.id  AS  id ,
J1031074.siui_appid  AS  siui_appid ,
J1031074.data_adeverinta  AS  data_adeverinta ,
J1031074.is_paliativ  AS  text_paliativ ,
J1031074.cardno  AS  cardno ,
J1031074.cardno_externare  AS  cardno_externare ,
J1031074.sign_date  AS  sign_date ,
J1031074.sign_date_externare  AS  sign_date_externare ,
J1031074.unsigned_string  AS  unsigned_string ,
J1031074.unsigned_string_externare  AS  unsigned_string_externare ,
J1031074.signhash  AS  signhash ,
J1031074.signhash_externare  AS  signhash_externare ,
J1031074.signature  AS  signature ,
J1031074.signature_externare  AS  signature_externare ,
J1031074.send_xml  AS  send_xml ,
J1031074.send_xml_externare  AS  send_xml_externare ,
J1031074.received_xml  AS  received_xml ,
J1031074.received_xml_externare  AS  received_xml_externare ,
J1031074.error  AS  error ,
J1031074.error_externare  AS  error_externare ,
J1031074.validat  AS  validat ,
J1031074.validat_externare  AS  validat_externare ,
J1031074.online  AS  online ,
J1031074.online_externare  AS  online_externare ,
J1031074.serie_bilet_internare  AS  text_serie_bilet_internare ,
J1031074.nr_bilet_internare  AS  text_numar_bilet_internare ,
J1031074.idpatient  AS  _popup_cnp_pacient ,
J1031075.cnp  AS  popup_cnp_pacient ,
J1031075.name  AS  text_nume_pacient ,
J1031075.surname  AS  text_prenume_pacient ,
J1031074.nrfo  AS  text_numar_fosz ,
J1031074.greutate_nastere  AS  text_greutate_nastere ,
J1031078.value  AS  popup_tip_asigurare ,
J1031074.idensuredstatustype  AS  _popup_tip_asigurare ,
J1031079.value  AS  popup_statut_asigurat ,
J1031074.idensuredstatus  AS  _popup_statut_asigurat ,
J1031080.code  AS  popup_cas_asigurat ,
J1031074.id_org_unit  AS  _popup_cas_asigurat ,
J1031081.code  AS  popup_categorie_asigurare ,
J1031074.id_categorie_asigurat  AS  _popup_categorie_asigurare ,
J1031082.name  AS  popup_tip_internare ,
J1031074.id_focg_tip_internare  AS  _popup_tip_internare ,
J1031083.name  AS  popup_criteriu_internare ,
J1031074.id_focg_criteriu_internare  AS  _popup_criteriu_internare ,
J1031084.stencil_no  AS  popup_medic_curant ,
J1031084.id  AS  _popup_medic_curant ,
J1031089.value  AS  popup_nivel_instruire ,
J1031074.id_education_level  AS  _popup_nivel_instruire ,
J1031074.greutate  AS  text_greutate_internare ,
J1031090.nume  AS  popup_situatii_speciale ,
J1031074.id_focg_situatii_speciale  AS  _popup_situatii_speciale ,
J1031091.nume  AS  popup_internat_prin ,
J1031091.id  AS  _popup_internat_prin ,
J1031092.nume  AS  popup_formulare_europene ,
J1031074.id_formulare_europene  AS  _popup_formulare_europene ,
J1031074.id_cnp_mama  AS  _popup_cnp_mama ,
J1031094.cnp  AS  popup_cnp_mama ,
J1031093.nrfo  AS  popup_fo_mama ,
J1031074.id_focg  AS  _popup_fo_mama ,
J1031074.nr_card_euro  AS  text_nr_card_european ,
J1031074.nr_pasaport  AS  text_nr_pasaport ,
J1031074.nr_card_national  AS  text_nr_card_national ,
J1031088.id  AS  _popup_ocupatia ,
J1031088.name  AS  popup_ocupatia ,
J1031074.export_drg  AS  export_drg ,
J1031074.drgcaseid  AS  drgcaseid ,
J1031074.export_ecosoft  AS  export_ecosoft ,
J1031074.mesaj_drg  AS  mesaj_drg ,
J1031074.uid  AS  uid ,
J1031074.mesaj_ecosoft  AS  mesaj_ecosoft ,
J1031074.id_address_domiciliu  AS  text_id_address_domiciliu ,
J1031074.id_address_domiciliu  AS  _text_id_address_domiciliu ,
J1031074.id_address_resedinta  AS  _text_id_address_resedinta ,
J1031074.id_address_resedinta  AS  text_id_address_resedinta ,
'0'  AS  marcator ,
J1031095.id  AS  _popup_sursa_internare ,
J1031095.denumire  AS  popup_sursa_internare ,
J1031096.id  AS  _popup_diseasecategory ,
J1031096.code  AS  popup_diseasecategory ,
J1031097.id  AS  _popup_diagnostic_internare_icd10 ,
J1031097.name  AS  popup_diagnostic_internare_icd10 ,
J1031098.id  AS  _popup_mod_contract ,
J1031098.description  AS  popup_mod_contract ,
J1031099.id  AS  _popup_criteriu_urgenta ,
J1031099.name  AS  popup_criteriu_urgenta ,
J1031100.id  AS  _popup_exceptie_bi ,
J1031100.code  AS  popup_exceptie_bi ,
J1031074.scrisoare_medicala_parafa  AS  text_parafa_scrisoare_medicala ,
J1031074.scrisoare_medicala_contract  AS  
text_contract_scrisoare_medicala ,
J1031074.scrisoare_medicala_tip_contract  AS  
text_tip_contract_scrisoare_medicala ,

J1031074.export_siui  AS  export_siui ,
J1031074.mesaj_siui  AS  mesaj_siui ,
J1031087.id  AS  _popup_intocmit ,
J1031087.stencil_no  AS  

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

[David Rowley]

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 smart enough to optimize away stuff that it knows doesn't
> impact the final query result.

To be more accurate with what David is saying, PostgreSQL will remove
unused LEFT JOINed relations where the left joined relation can be
proved to not duplicate rows from the right hand side. It would just
be a matter of comparing the EXPLAINs from the query with all the
SELECT items to the one with the single SELECT item to prove that this
is what's happening.

Please also note that this only occurs with LEFT JOINs

It would also be quite helpful for people if you were to include a
copy of the query. It's impossible to reverse engineer what that is
from this EXPLAIN output. I see that your using a windowing function
and performing a LIMIT 1, there may be ways to improve that just by
selecting the single highest j1031101.validfrom row and performing the
joins to the other table on that single row, but that will depend on
which windowing function you're using as the function may require the
other rows in the window frame to calculate the correct result.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 knows doesn't
impact the final query result.

David J.

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

[John R Pierce]

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 looks like a really complicated query with lots of joins 
and sorts etc etc..




--
john r pierce, recycling bits in santa cruz

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

[Achilleas Mantzios]

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 problem with learning HQL, which in fact is quite similar. I really have no idea why it is like that.




Similar here. IMHO it is called "job trends hype". Look at all the linkedin ads, less than 1% is about Pgsql/DBs, 99% is about app coders. Ok rough numbers, but it reflects reality. One of my past 
programmers (a fine kid always looking to learn) now writes IOS and Android apps in another country. Another one who didn't do much well with SQL, but rock-star programmer otherwise, now writes 
javascript in another company.



--

regards Szymon Lipiński



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

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

[Rob Imig]

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 solving this query. I tried to run the
> query on a HDD and on a SSD with no difference. HDD show about 10% usage
> while the query runs.
>
> The query has a big "select" list and no "group by" clause. If I delete
> all selects except one the query runs in under a second(it doesn't matter
> what field remains selected).
> It seems that the query is slow because of the long select, can I do
> something to make the query faster?
>
> Thank you.
>
>