Re: [GENERAL] database is not accepting commands

[reem]

postgres 9.3 in ubuntu OS. 

Yes I did this : 
1- service postgresql stop 
2- /usr/lib/postgresql/9.3/bin/postgres --single -D
/var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf dbname
3-vacuum verbose 
4- vacumming processed and it shows tables being vacuumed but then suddenly
stopped with the same error above.

Please note we didn't try to VACUUM FULL because it takes a lot of the
resources . 






--
View this message in context: 
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831p5962030.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] database is not accepting commands

[reem]

The version is 9.3 in ubuntu.

yes i did it in standalone mode by using this command : 
//usr/lib/postgresql/9.3/bin/postgres --single -D
/var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf dbname/



--
View this message in context: 
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831p5962028.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] improvements/feedback sought for a working query that looks a bit ugly and might be inefficient

[David G. Johnston]

On Tuesday, May 16, 2017, jonathan vanasco  wrote:

>
> Everything here works fine - but after a handful of product iterations &
> production adjustments, a query that handles a "task queue" across a few
> tables looks a bit ugly.


This is a far cry from ugly.


>
> My concern is that the sort needs to happen 3x --
> in the subselect for 1000 items
> in the partition for row numbering
> in the final sort
>
>
Unless you can discard the 5 and 1000 limits you are going to be stuck
computing rank three times in order to compute and filter them.

Without understanding how the data is ultimately used its difficult to
suggest meaningful alternatives.  Views and/or functions can hide some of
the complexity behind meaningful names but changing away from your choice
of "window" and "partition" as relation aliases will give most of the same
effect.

The query is a solid reflection of the data model.  If the only concern is
stylistic I'd say you should move on.

Given the first limit 1000 a reasonable work_mem setting should (without
any evidence...) suffice to make the actual time spent sorting immaterial.
Lower level sorting is retained where possible so the upper levels don't
have as much to move around.  The lakck of the provider id in the final
sort was noticed though...

David J.

[GENERAL] improvements/feedback sought for a working query that looks a bit ugly and might be inefficient

[jonathan vanasco]

Everything here works fine - but after a handful of product iterations & 
production adjustments, a query that handles a "task queue" across a few tables 
looks a bit ugly.

I'm wondering if anyone can see obvious improvements.  

There are 3 tables:
upstream_provider
task
task_queue

Originally we needed to select 50 items off the top of the queue at a time.
Then we needed to set a max of 5 tasks per upstream provider (It's a cheap way 
to handle throttling).
The table is quite large, so a limit of the last 1000 items drastically 
improved performance.
The query got ugly when we needed to add a "priority" toggle to the queue -- 
basically to mark things as "process ASAP".  
The only way I could figure out how to do that, was to add a sort -- on 
"is_priority DESC NULLS LAST".
My concern is that the sort needs to happen 3x -- 
in the subselect for 1000 items
in the partition for row numbering
in the final sort


If anyone has a moment to look this over and suggest anything, I'd be very 
thankful.  A working reproduction is below.


SQL---
--

CREATE TABLE upstream_provider (id SERIAL PRIMARY KEY,
name VARCHAR(32),
is_paused BOOLEAN DEFAULT NULL
);
CREATE TABLE task (id SERIAL PRIMARY KEY,
   upstream_provider_id INT NOT NULL REFERENCES 
upstream_provider(id),
   name VARCHAR(32)
   );
CREATE TABLE task_queue (id SERIAL PRIMARY KEY,
 task_id INT NOT NULL REFERENCES task(id),
 upstream_provider_id INT NOT NULL REFERENCES 
upstream_provider(id),  # only here because it eliminates expensive joins 
elsewhere
 processing_status BOOLEAN DEFAULT NULL,
 is_priority BOOLEAN DEFAULT NULL
 );

SELECT  partition1.*
  , task.*
FROM (SELECT  window1.*
, row_number() OVER (PARTITION BY window1.upstream_provider_id
 ORDER BY window1.is_priority DESC NULLS LAST,
  window1.task_queue_id
 ) AS rownum
  FROM (SELECT   qu.id AS task_queue_id
   , qu.upstream_provider_id
   , qu.task_id
   , qu.is_priority
FROM
task_queue qu
JOIN
upstream_provider ON qu.upstream_provider_id = 
upstream_provider.id
WHERE (qu.processing_status IS NULL)
  AND (upstream_provider.is_paused IS NOT TRUE)
ORDER BY is_priority DESC NULLS LAST,
 qu.id DESC
LIMIT 1000
) window1
  ) partition1
JOIN task ON partition1.task_id = task.id
WHERE partition1.rownum < 5
ORDER BY is_priority DESC NULLS LAST,
 task_queue_id DESC
LIMIT
50
;

-- 
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] sorry, too many clients already error

[Tom Lane]

Sandeep Gupta  writes:
> I have set max_connection = 40.
> The usage is somewhat not typical. It is basically
> experiment runs that connect to the database
> and dump results there.

> The experiments connect through JDBC and
> they close the connection when they are done.

> I can verify that no more than 20 clients/experiments
> are active at any given time.

It takes nonzero time for a backend process to actually exit after
the client has closed the connection.  (A client that wants to wait
for backend exit can do so by waiting for EOF on the socket, but
I doubt that the JDBC driver does so.)  If you're spinning through
sessions rapidly enough, you might just have >20 sessions that are
still in process of exiting when a new one comes in and gets this
error.

It'd also be a good thing to see if the client code is sending an
actual Terminate protocol message ('X') or is just closing the
socket.  If the latter, maybe it isn't really closing because the
socket is shared with some child process, or something like that?

Turning on log_connections/log_disconnections might help clarify
what's happening.

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

[GENERAL] sorry, too many clients already error

[Sandeep Gupta]

I have set max_connection = 40.

The usage is somewhat not typical. It is basically
experiment runs that connect to the database
and dump results there.

The experiments connect through JDBC and
they close the connection when they are done.

I can verify that no more than 20 clients/experiments
are active at any given time.

What could be the cause of the database issuing
too many clients error. Could I have set resources too high?

Thanks.
-sandeep


-- 
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/16/2017 08:39 AM, Martin Goodson wrote:

On 16/05/2017 15:58, Adrian Klaver wrote:

For completeness what does:

ls -al /usr/lib64/libssl.so

show?

(Trimming things down a bit to keep things a little bit more readable, 
now we've got the package dependencies issue sorted. Hopefully.


  Hope that's OK?)


See this recent thread for why this is OK and recommended:

https://www.postgresql.org/message-id/CA%2BbJJbyutGGOKN-wiBDeLrNU-oB%3D_-81xEnfXRcY0wo%2BZRKvZw%40mail.gmail.com




I can see the following:

$ ls -l /usr/lib64/libssl.so

lrwxrwxrwx 1 root root 16 May 15 16:57 /usr/lib64/libssl.so -> 
libssl.so.1.0.1e




Seems okay to me.



OK, thank you. I'll pass that along to our UNIX team and ask for that to 
be installed, see what we get ...


I see from subsequent post that this did not work.

Per Tom's suggestion try

ldd /db_demo/app/postgres/9.6.2-3/lib/libldap_r-2.4.so.2





Regards,

Martin.



--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Tom Lane]

Martin Goodson  writes:
 /bin/ld: warning: libssl.so.1.0.0, needed by 
 /db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with 
 libssl.so.10
 /lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'
 collect2: error: ld returned 1 exit status

> Drat. Didn't work. openldap2-devel was installed OK, reran the make, got 
> the same error :(

What this looks like is a library versioning problem, maybe?  On my
system, ber_sockbuf_io_udp is exported by /lib64/liblber-2.4.so.2.10.3,
and "ldd" on /lib64/libldap_r-2.4.so.2.10.3 shows that that library will
be pulled in automatically.  Maybe it's not happening like that on your
box.  It's a bit weird though because both those libraries are supplied by
the same openldap package, so it's hard to see how they could be out of
sync.  Maybe you have another copy of one of those libraries floating
around, and the wrong copy is getting pulled into your link?

I'd suggest some investigation with "ldd" and "nm -D" to see what's what.

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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 16/05/2017 16:39, Martin Goodson wrote:

On 16/05/2017 15:58, Adrian Klaver wrote:
/bin/ld: warning: libssl.so.1.0.0, needed by 
/db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with 
libssl.so.10

/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'
collect2: error: ld returned 1 exit status
make: *** [repmgrd] Error 1
Not sure what is going on here. My usual reaction to a message like 
this when compiling is to install the --devel package for the library 
involved. In this case I believe it would be something like:


openldap2-devel


OK, thank you. I'll pass that along to our UNIX team and ask for that 
to be installed, see what we get ...




This is just an off the top of the head suggestion, no warranty:)

What? You mean I can't get my money back? :)

Many thanks for all the help so far. It's been very, very, helpful. 
I'm sure it's nearly there ...


Drat. Didn't work. openldap2-devel was installed OK, reran the make, got 
the same error :(



Here's what's installed according to rpm -qa

openldap-devel-2.4.40-9.e17_2.x86_64
openldap-2.4.40.9.e17_2.x86_64

Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."



--
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] storing large files in database - performance

[Peter J. Holzer]

On 2017-05-16 12:25:03 +, Eric Hill wrote:
> I searched and found a few discussions of storing large files in the database
> in the archives, but none that specifically address performance and how large
> of files can realistically be stored in the database.
> 
>  
> 
> I have a node.js application using PostgreSQL to store uploaded files.  The
> column in which I am storing the file contents is of type “bytea” with
> “Storage” type set to “EXTENDED”.

I have mentioned this little experiment before, but I finally put the
results on my web site: https://www.hjp.at/databases/blob-bench/

(Please note that so far I have run this only on one system.
Generalizing to other systems might be premature).


> Storing a 12.5 MB file is taking 10 seconds, and storing a 25MB file
> is taking 37 seconds.  Two notable things about those numbers:  It
> seems like a long time, and the time seems to grow exponentially with
> file size rather than linearly.
> 
>  
> 
> Do these numbers surprise you?

Yes. on my system, storing a 25 MB bytea value takes well under 1 second.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

Re: [GENERAL] Different query plan used for the same query depending on how parameters are passed

[Tom Lane]

David Chapman  writes:
> Here is the output of EXPLAIN ANALYZE on the two queries.

> Index Scan using test_index_t1_t2 on test  (cost=0.43..684.11 rows=71
> width=245) (actual time=0.022..1.147 rows=99 loops=1)
>   Index Cond: ((t1 = 'X'::bpchar) AND (t2 = ANY ('{2286575,2139022,2139030,
 ^^

> Seq Scan on test  (cost=0.00..403725.30 rows=1 width=245) (actual
> time=47.543..5362.518 rows=99 loops=1)
>   Filter: (((t1)::text = 'X'::text) AND (t2 = ANY
 

There's your problem.  t1 is evidently of char(n) type, and when you
write "t1 = 'X'" the literal also becomes char(n) and so you have
a condition that can match the index.  But the parameter is evidently
being assigned type text, which wins the type conflict so then you have
"t1::text text-eq text-constant", and that operator doesn't match the index.

Probably, casting the parameter to char(n) explicitly would fix this.

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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 16/05/2017 15:58, Adrian Klaver wrote:

For completeness what does:

ls -al /usr/lib64/libssl.so

show?

(Trimming things down a bit to keep things a little bit more readable, 
now we've got the package dependencies issue sorted. Hopefully.


 Hope that's OK?)

I can see the following:

$ ls -l /usr/lib64/libssl.so

lrwxrwxrwx 1 root root 16 May 15 16:57 /usr/lib64/libssl.so -> 
libssl.so.1.0.1e


Just to expand on that a bit :

$ ls -l /usr/lib64/libssl.*

lrwxrwxrwx 1 root root   16 May 15 16:57 /usr/lib64/libssl.so -> 
libssl.so.1.0.1e
lrwxrwxrwx 1 root root   16 May 11 12:41 /usr/lib64/libssl.so.10 -> 
libssl.so.1.0.1e

-rwxr-xr-x 1 root root 440K Sep 22  2016 /usr/lib64/libssl.so.1.0.1e





Ah, sorry. A bit got truncated. Here you go:

/bin/ld: warning: libssl.so.1.0.0, needed by 
/db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with 
libssl.so.10

/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'
collect2: error: ld returned 1 exit status
make: *** [repmgrd] Error 1
Not sure what is going on here. My usual reaction to a message like 
this when compiling is to install the --devel package for the library 
involved. In this case I believe it would be something like:


openldap2-devel


OK, thank you. I'll pass that along to our UNIX team and ask for that to 
be installed, see what we get ...




This is just an off the top of the head suggestion, no warranty:)

What? You mean I can't get my money back? :)

Many thanks for all the help so far. It's been very, very, helpful. I'm 
sure it's nearly there ...


Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."



--
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] Different query plan used for the same query depending on how parameters are passed

[David Chapman]

Version is 'PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit'

Here is the output of EXPLAIN ANALYZE on the two queries.

Index Scan using test_index_t1_t2 on test  (cost=0.43..684.11 rows=71
width=245) (actual time=0.022..1.147 rows=99 loops=1)
  Index Cond: ((t1 = 'X'::bpchar) AND (t2 = ANY ('{2286575,2139022,2139030,
2139032,1912037,860952,2139035,2139037,2278996,2139043,72062644919750111,
2139045,2139047,1904847,2139049,2259635,2259633,2293287,2293281,2206950,
2127033,2206952,2146439,2139064,2139066,2139070,2139074,2305447,2139077,
2377493,2139079,2282669,2139083,2375729,2260985,2286579,2274454,2286583,
2286582,2281512,2286588,2286591,2286592,1912217,2286596,2307845,2307846,
2293294,287038,2293295,2293291,2356192,2317356,2356191,2356190,2356181,
2356195,2356197,2389077,2356199,2356217,2374842,2374844,2374846,2337988,
2374856,1950450,2374833,2374839,2282877,2374843,2374849,2374855,2390631,
2028018,2374834,2139011,2139004,2344765,2344767,2390620,2393192,2344771,
2344773,2344775,2390201,2344777,1893069,2344779,2344781,2344783,2374838,
2304999,2344762,2344764,2344766,2344768,2344772,867138,2276706}'::text[])))
Planning time: 0.779 ms
Execution time: 1.417 ms

Seq Scan on test  (cost=0.00..403725.30 rows=1 width=245) (actual
time=47.543..5362.518 rows=99 loops=1)
  Filter: (((t1)::text = 'X'::text) AND (t2 = ANY
('{2286575,2139022,2139030,2139032,1912037,860952,2139035,2139037,2278996,
2139043,72062644919750111,2139045,2139047,1904847,2139049,2259635,2259633,
2293287,2293281,2206950,2127033,2206952,2146439,2139064,2139066,2139070,
2139074,2305447,2139077,2377493,2139079,2282669,2139083,2375729,2260985,
2286579,2274454,2286583,2286582,2281512,2286588,2286591,2286592,1912217,
2286596,2307845,2307846,2293294,287038,2293295,2293291,2356192,2317356,
2356191,2356190,2356181,2356195,2356197,2389077,2356199,2356217,2374842,
2374844,2374846,2337988,2374856,1950450,2374833,2374839,2282877,2374843,
2374849,2374855,2390631,2028018,2374834,2139011,2139004,2344765,2344767,
2390620,2393192,2344771,2344773,2344775,2390201,2344777,1893069,2344779,
2344781,2344783,2374838,2304999,2344762,2344764,2344766,2344768,2344772,
867138,2276706}'::text[])))
  Rows Removed by Filter: 2327145
Planning time: 0.179 ms
Execution time: 5362.558 ms

As it turns out t1 was actually defined as character(1) rather than text. I
can see Postgres has cast the first parameter to bpchar in the first case
but I guess Npgsql is explicitly sending it as type text, which bypasses
the index.


On 16 May 2017 at 15:17, Adrian Klaver  wrote:

> On 05/16/2017 06:01 AM, David Chapman wrote:
>
>> I have a table that includes two text columns t1 and t2, and a composite
>> index on these columns. When issuing a query of the following form:
>>
>> SELECT * FROM test WHERE t1 = 'X' and t2 = ANY(ARRAY['Y1', 'Y2', ..])
>>
>> I have observed that it will use the index and have reasonable
>> performance if the whole query is passed as a single big string. However if
>> it is parameterised (I'm using Npgsql) it switches to doing a sequence scan
>> and performs terribly.
>>
>
> What Postgres version?
>
> Can you show the parametrized version?
>
> What is the output if you add EXPLAIN ANALYZE to the beginning of the
> query?:
>
> https://www.postgresql.org/docs/9.6/static/sql-explain.html
>
>
>
>
>> The table contains approx 2.3 million records and the query matches about
>> 20k records (i.e. there are 20k 'Y' values in the array).
>>
>> I have experimented with changing work_mem, preparing the statement in
>> advance, ANALYZEing the table, none of these change the behavior.
>>
>> Why does the query planner choose to ignore the index when the command is
>> parameterised?
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

-- 


This e-mail together with any attachments (the "Message") is confidential 
and may contain privileged information. If you are not the intended 
recipient or if you have received this e-mail in error, please notify the 
sender immediately and permanently delete this Message from your system. 
 Do not copy, disclose or distribute the information contained in this 
Message.

Maven Investment Partners Ltd (No. 07511928), Maven Derivatives Ltd (No. 
07511840) , MVN Asset Management Limited (No. 09659116), Maven Europe Ltd 
(No. 08966593), Maven Derivatives Asia Limited (No.10361312) & Maven 
Securities Holding Ltd (No. 07505438) are registered as companies in 
England and Wales and their registered address is Level 3, 6 Bevis Marks, 
London EC3A 7BA, United Kingdom. The companies’ VAT No. is 135539016. Maven 
Asia (Hong Kong) Ltd (No. 2444041) is registered in Hong Kong and its 
registered address is 20/F, 198 Wellington St, Hong Kong.  Only Maven 
Derivatives Ltd and MVN Asset Management Limited are authorised and 
regulated by the Financial Conduct Authority (Maven Derivatives Ltd FRN: 
607267, MVN Asset Management Limited FRN: 714429)

Re: [GENERAL] database is not accepting commands

[Melvin Davidson]

On Tue, May 16, 2017 at 11:03 AM, Adrian Klaver 
wrote:

> On 05/16/2017 01:28 AM, reem wrote:
>
>> We have 1.5 TB database that's shown an error and block all commands.
>> The error is :
>> "ERROR:  database is not accepting commands to avoid wraparound data loss
>> in
>> database "dbname"
>> HINT:  Stop the postmaster and use a standalone backend to vacuum that
>> database.
>> You might also need to commit or roll back old prepared transactions."
>>
>> I tried to do vacuum in the backend mode. Also I tried to set
>> zero_damaged_pages = on then do the vacuum again but same error appeared.
>> The error appeared after two hours of vacuuming where verbose shows
>> passing
>> tables.
>>
>
> Postgres version?
>
> So was all the above done in the standalone backend?
>
>
>> The cause of error could be :
>> 1- a lot of insertion queries for not existing table
>> or
>> 2- truncating data with 40 GB in a week,
>> we used to do that but this is the first time we had this error.
>> also, autovacuume is turned on.
>>
>> Please , I need help or any suggestion?
>>
>>
>>
>>
>>
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/database-is-not-accepting-commands-tp5961831.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> 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
>

As Adrian requested:
PostgreSQL Version?
O/S?

Did you ?
A. Stop PostgreSQL
B.postgres --single -D 
C
VACUUMDB  -f -d 
OR
VACUUMDB -f -a

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

Re: [GENERAL] storing large files in database - performance

[Eric Hill]

OK, thanks very much.  It seems like my process is somehow flawed.  I'll try 
removing some layers and see if I can figure out what is killing the 
performance.

Eric



>
> Do these numbers surprise you?  Are these files just too large for 
> storage in PostgreSQL to be practical?  Could there be something about 
> my methodology that is slowing things down?

Yes, it does surprise me. I just tested inserting an 11MB file using
psycopg2(Python) and it was less then a second.



-- 
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] database is not accepting commands

[Adrian Klaver]

On 05/16/2017 01:28 AM, reem wrote:

We have 1.5 TB database that's shown an error and block all commands.
The error is :
"ERROR:  database is not accepting commands to avoid wraparound data loss in
database "dbname"
HINT:  Stop the postmaster and use a standalone backend to vacuum that
database.
You might also need to commit or roll back old prepared transactions."

I tried to do vacuum in the backend mode. Also I tried to set
zero_damaged_pages = on then do the vacuum again but same error appeared.
The error appeared after two hours of vacuuming where verbose shows passing
tables.


Postgres version?

So was all the above done in the standalone backend?



The cause of error could be :
1- a lot of insertion queries for not existing table
or
2- truncating data with 40 GB in a week,
we used to do that but this is the first time we had this error.
also, autovacuume is turned on.

Please , I need help or any suggestion?

  





--
View this message in context: 
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/16/2017 07:22 AM, Martin Goodson wrote:

On 16/05/2017 14:42, Adrian Klaver wrote:


On 05/16/2017 04:36 AM, Martin Goodson wrote:

On 15/05/2017 00:17, Martin Goodson wrote:



That list would be:
systemd
libxslt-devel
pam-devel
openssl-devel
readline-devel
libmemcached-devel
libicu-devel

Did you also install?:

krb5-devel
libedit-devel


Hi.

This is what I see:

$ rpm -qa | grep -i -e "systemd" -e "devel"

systemd-sysv-219-19.el7_2.20.x86_64
keyutils-libs-devel-1.5.8-3.el7.x86_64
krb5-devel-1.13.2-12.el7_2.x86_64
readline-devel-6.2-9.el7.x86_64
libicu-devel-50.1.2-15.el7.x86_64
libgpg-error-devel-1.12-3.el7.x86_64
libxslt-devel-1.1.28-5.el7.x86_64
systemd-libs-219-19.el7_2.20.x86_64
libedit-devel-3.0-12.20121213cvs.el7.x86_64
pam-devel-1.1.8-12.el7_1.1.x86_64
pcre-devel-8.32-15.el7_2.1.x86_64
libsepol-devel-2.1.9-3.el7.x86_64
libverto-devel-0.2.5-4.el7.x86_64
zlib-devel-1.2.7-15.el7.x86_64
ncurses-devel-5.9-13.20130511.el7.x86_64
libgcrypt-devel-1.5.3-12.el7_1.1.x86_64
libxml2-devel-2.9.1-6.el7_2.3.x86_64
libmemcached-devel-1.0.16-5.el7.x86_64
systemd-219-19.el7_2.20.x86_64
libcom_err-devel-1.42.9-7.el7.x86_64
libselinux-devel-2.2.2-6.el7.x86_64
openssl-devel-1.0.1e-51.el7_2.7.x86_64
glibc-devel-2.17-106.el7_2.8.x86_64
xz-devel-5.1.2-12alpha.el7.x86_64cyrus-sasl-devel-2.1.26-20.el7_2.x86_64

So it looks like I've got pretty much everything covered in that 
respect? :)


Looks complete to me.



For that I get the following:

libssl: /usr/lib64/libssl3.so /usr/lib64/libssl.so


For completeness what does:

ls -al /usr/lib64/libssl.so

show?




Ah, sorry. A bit got truncated. Here you go:

/bin/ld: warning: libssl.so.1.0.0, needed by 
/db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with libssl.so.10

/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'
collect2: error: ld returned 1 exit status
make: *** [repmgrd] Error 1
Not sure what is going on here. My usual reaction to a message like this 
when compiling is to install the --devel package for the library 
involved. In this case I believe it would be something like:


openldap2-devel

This is just an off the top of the head suggestion, no warranty:)

All installed on the virtual machine I'm building this on. See the rpm 
list above. At my level of visibility it's just 'another server' - I 
just mentioned it was a virtual one rather than an actual bit of tin in 
case it were to somehow make a difference.


Just making sure, thanks.



Regards,

Martin.



--
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] storing large files in database - performance

[Thomas Kellerer]

John R Pierce schrieb am 16.05.2017 um 16:44:
> On 5/16/2017 7:35 AM, Thomas Kellerer wrote:
>> When my (JDBC based) SQL client and the database server are on the same 
>> computer...
> 
> node.js is Javascript, not java w/ jdbc

I know that. 

I mentioned JDBC so that it's clear that the timings were done using a 
different technology

Maybe it's Node.js or the JavaScript "driver" that causes the problems.




-- 
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] storing large files in database - performance

[Adrian Klaver]

On 05/16/2017 07:44 AM, John R Pierce wrote:

On 5/16/2017 7:35 AM, Thomas Kellerer wrote:
When my (JDBC based) SQL client and the database server are on the 
same computer...


node.js is Javascript, not java w/ jdbc


I think it was more a point of comparison, like my using a Python 
example. Something to show that is probably not on the Postgres end.








--
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] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

[Tom Lane]

Magnus Hagander  writes:
> On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz  wrote:
>> Not sure whether we should *fix* this or not on RPM side. This may break
>> some of the existing installations, right?

> Changing that in a minor version seems like a *really* bad idea, because
> things *will* break. The way it is now it only breaks in case of a major
> version upgrade, and there is an easy enough workaround present.

Yeah, you don't have a lot of room in a minor release to make changes
that would affect this.

What Red Hat did about this, when I worked there, was to back-port the
unix_socket_directories patch from 9.3 into earlier branches, and then
set up the default server configuration to create sockets in both
/var/run/postgresql and /tmp.  But even if you did that, it'd require
an upgrade of the 9.2 installation before it would play nice with a
9.6 libpq, so that might be surprising.  (It would also break existing
9.2 installations that were explicitly setting unix_socket_directory,
but we can hope there are very few of those.)

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] storing large files in database - performance

[John R Pierce]

On 5/16/2017 5:25 AM, Eric Hill wrote:
I do have the Sequelize ORM and the pg driver in between my code and 
the database. 



Can you try a similar test without the ORM, just going straight from 
node.js to sql ?



--
john r pierce, recycling bits in santa cruz



--
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] storing large files in database - performance

[John R Pierce]

On 5/16/2017 7:35 AM, Thomas Kellerer wrote:

When my (JDBC based) SQL client and the database server are on the same 
computer...


node.js is Javascript, not java w/ jdbc


--
john r pierce, recycling bits in santa cruz



--
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] Different query plan used for the same query depending on how parameters are passed

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Chapman
Sent: Tuesday, May 16, 2017 9:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Different query plan used for the same query depending on 
how parameters are passed


Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.

I have a table that includes two text columns t1 and t2, and a composite index 
on these columns. When issuing a query of the following form:

SELECT * FROM test WHERE t1 = 'X' and t2 = ANY(ARRAY['Y1', 'Y2', ..])

I have observed that it will use the index and have reasonable performance if 
the whole query is passed as a single big string. However if it is 
parameterised (I'm using Npgsql) it switches to doing a sequence scan and 
performs terribly.

The table contains approx 2.3 million records and the query matches about 20k 
records (i.e. there are 20k 'Y' values in the array).

I have experimented with changing work_mem, preparing the statement in advance, 
ANALYZEing the table, none of these change the behavior.

Why does the query planner choose to ignore the index when the command is 
parameterised?


It’s because when optimizer builds execution plan for parametrized queiry, it 
doesn’t know what values for t1 and t2 will be provided for WHERE clause.

Regards,

Igor Neyman

Re: [GENERAL] storing large files in database - performance

[Adrian Klaver]

On 05/16/2017 05:25 AM, Eric Hill wrote:

Hey,

I searched and found a few discussions of storing large files in the 
database in the archives, but none that specifically address performance 
and how large of files can realistically be stored in the database.


I have a node.js application using PostgreSQL to store uploaded files.  
The column in which I am storing the file contents is of type “bytea” 
with “Storage” type set to “EXTENDED”.  Storing a 12.5 MB file is taking 
10 seconds, and storing a 25MB file is taking 37 seconds.  Two notable 
things about those numbers:  It seems like a long time, and the time 
seems to grow exponentially with file size rather than linearly.


Do these numbers surprise you?  Are these files just too large for 
storage in PostgreSQL to be practical?  Could there be something about 
my methodology that is slowing things down?


Yes, it does surprise me. I just tested inserting an 11MB file using 
psycopg2(Python) and it was less then a second.




I do have the Sequelize ORM and the pg driver in between my code and the 
database.


Thanks,

Eric




--
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] storing large files in database - performance

[Thomas Kellerer]

Eric Hill schrieb am 16.05.2017 um 14:25:
> I have a node.js application using PostgreSQL to store uploaded
> files. The column in which I am storing the file contents is of type
> “bytea” with “Storage” type set to “EXTENDED”. Storing a 12.5 MB file
> is taking 10 seconds, and storing a 25MB file is taking 37 seconds.
> Two notable things about those numbers: It seems like a long time,
> and the time seems to grow exponentially with file size rather than
> linearly.
> 
> Do these numbers surprise you? 

When my (JDBC based) SQL client and the database server are on the same 
computer, I can store a 45MB file about 4 seconds, a 240MB file in about 
20seconds 
When I do the same with a server on the (same) network, the 45MB take about 9 
seconds, the 240MB take about 60 seconds.

So yes, these numbers sound a bit surprising. 

> Could there be something about my methodology that is slowing things down?

How far are your app server and the database server apart? 
Maybe it's just a case of a slow network

Thomas



-- 
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 16/05/2017 14:42, Adrian Klaver wrote:


On 05/16/2017 04:36 AM, Martin Goodson wrote:

On 15/05/2017 00:17, Martin Goodson wrote:



Tomorrow I'll have to see about getting that set-up on an RHEL 7 box :)

Thank you so much, everybody, for your help! It's been invaluable!

Regards,

Martin.



*Sigh*. And things were going so well. With Adrian and Devrim's help 
I was able to get repmgr compiled on my little ubuntu box at home, 
and I cheerfully went into work on Monday all ready to get things 
compiled nicely.


I forwarded Devrim's list of redhat/CentOS packages needed to my Unix 
support colleagues, who installed the libraries listed. I've now got 
nothing telling me such and such is missing. I now have this instead:


That list would be:
systemd
libxslt-devel
pam-devel
openssl-devel
readline-devel
libmemcached-devel
libicu-devel

Did you also install?:

krb5-devel
libedit-devel


Hi.

This is what I see:

$ rpm -qa | grep -i -e "systemd" -e "devel"

systemd-sysv-219-19.el7_2.20.x86_64
keyutils-libs-devel-1.5.8-3.el7.x86_64
krb5-devel-1.13.2-12.el7_2.x86_64
readline-devel-6.2-9.el7.x86_64
libicu-devel-50.1.2-15.el7.x86_64
libgpg-error-devel-1.12-3.el7.x86_64
libxslt-devel-1.1.28-5.el7.x86_64
systemd-libs-219-19.el7_2.20.x86_64
libedit-devel-3.0-12.20121213cvs.el7.x86_64
pam-devel-1.1.8-12.el7_1.1.x86_64
pcre-devel-8.32-15.el7_2.1.x86_64
libsepol-devel-2.1.9-3.el7.x86_64
libverto-devel-0.2.5-4.el7.x86_64
zlib-devel-1.2.7-15.el7.x86_64
ncurses-devel-5.9-13.20130511.el7.x86_64
libgcrypt-devel-1.5.3-12.el7_1.1.x86_64
libxml2-devel-2.9.1-6.el7_2.3.x86_64
libmemcached-devel-1.0.16-5.el7.x86_64
systemd-219-19.el7_2.20.x86_64
libcom_err-devel-1.42.9-7.el7.x86_64
libselinux-devel-2.2.2-6.el7.x86_64
openssl-devel-1.0.1e-51.el7_2.7.x86_64
glibc-devel-2.17-106.el7_2.8.x86_64
xz-devel-5.1.2-12alpha.el7.x86_64cyrus-sasl-devel-2.1.26-20.el7_2.x86_64

So it looks like I've got pretty much everything covered in that respect? :)


What does

ldd /db_demo/app/postgres/9.6.2-3/lib/libpq.so show?


I get the following:

linux-vdso.so.1 =>  (0x7ffd631ae000)
libssl.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/libssl.so.1.0.0 (0x7f1002aa1000)
libcrypto.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/libcrypto.so.1.0.0 (0x7f100266a000)
libgssapi_krb5.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/libgssapi_krb5.so.2 (0x7f100241)
libldap_r-2.4.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/libldap_r-2.4.so.2 (0x7f10021ae000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x7f1001f88000)
libc.so.6 => /lib64/libc.so.6 (0x7f1001bc6000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f10019c2000)
libkrb5.so.3 => /db_demo/app/postgres/9.6.2-3/lib/libkrb5.so.3 
(0x7f10016cc000)
libk5crypto.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/libk5crypto.so.3 (0x7f1001486000)
libcom_err.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/libcom_err.so.3 (0x7f1001283000)
libkrb5support.so.0 => 
/db_demo/app/postgres/9.6.2-3/lib/libkrb5support.so.0 (0x7f1001075000)

libresolv.so.2 => /lib64/libresolv.so.2 (0x7f1000e5b000)
liblber-2.4.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/liblber-2.4.so.2 (0x7f1000c4b000)
libsasl2.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/libsasl2.so.3 (0x7f1000a28000)

/lib64/ld-linux-x86-64.so.2 (0x7f1002f3f000)


whereis libssl.so


For that I get the following:

libssl: /usr/lib64/libssl3.so /usr/lib64/libssl.so




/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'



Is that the end of the messages or are there more after the above?


Ah, sorry. A bit got truncated. Here you go:

/bin/ld: warning: libssl.so.1.0.0, needed by 
/db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with libssl.so.10

/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'
collect2: error: ld returned 1 exit status
make: *** [repmgrd] Error 1




So above when you said your colleagues installed the packages for you, 
was that on the host or the virtual machine?




All installed on the virtual machine I'm building this on. See the rpm 
list above. At my level of visibility it's just 'another server' - I 
just mentioned it was a virtual one rather than an actual bit of tin in 
case it were to somehow make a difference.


Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."


--
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] Different query plan used for the same query depending on how parameters are passed

[Adrian Klaver]

On 05/16/2017 06:01 AM, David Chapman wrote:
I have a table that includes two text columns t1 and t2, and a composite 
index on these columns. When issuing a query of the following form:


SELECT * FROM test WHERE t1 = 'X' and t2 = ANY(ARRAY['Y1', 'Y2', ..])

I have observed that it will use the index and have reasonable 
performance if the whole query is passed as a single big string. However 
if it is parameterised (I'm using Npgsql) it switches to doing a 
sequence scan and performs terribly.


What Postgres version?

Can you show the parametrized version?

What is the output if you add EXPLAIN ANALYZE to the beginning of the 
query?:


https://www.postgresql.org/docs/9.6/static/sql-explain.html




The table contains approx 2.3 million records and the query matches 
about 20k records (i.e. there are 20k 'Y' values in the array).


I have experimented with changing work_mem, preparing the statement in 
advance, ANALYZEing the table, none of these change the behavior.


Why does the query planner choose to ignore the index when the command 
is parameterised?







--
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] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

[Adrian Klaver]

On 05/16/2017 01:00 AM, Devrim Gündüz wrote:


Hi,

On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote:

https://redmine.postgresql.org/issues/2409


Not sure whether we should *fix* this or not on RPM side. This may break some
of the existing installations, right?

I'm not objecting, just asking for opinions.


To me the principle of least surprise says that it should be fixed. At 
this point a pre-9.4 server is putting its socket where the primary 
client library(libpq) to said server cannot find it if a 9.4+ server is 
installed. The options seem to be:


1) Use the libpq appropriate for each Postgres version.

2) Modify the postgresql.conf to point at the socket directory that the 
controlling libpq is looking for. I could see this being messy.


3) Document the change in behavior. Possibly here:

https://www.postgresql.org/download/linux/redhat/

PostgreSQL Yum Repository



Regards,




--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/16/2017 04:36 AM, Martin Goodson wrote:

On 15/05/2017 00:17, Martin Goodson wrote:



Tomorrow I'll have to see about getting that set-up on an RHEL 7 box :)

Thank you so much, everybody, for your help! It's been invaluable!

Regards,

Martin.



*Sigh*. And things were going so well. With Adrian and Devrim's help I 
was able to get repmgr compiled on my little ubuntu box at home, and I 
cheerfully went into work on Monday all ready to get things compiled nicely.


I forwarded Devrim's list of redhat/CentOS packages needed to my Unix 
support colleagues, who installed the libraries listed. I've now got 
nothing telling me such and such is missing. I now have this instead:


That list would be:
systemd
libxslt-devel
pam-devel
openssl-devel
readline-devel
libmemcached-devel
libicu-devel

Did you also install?:

krb5-devel
libedit-devel





$ PATH=/db_demo/app/postgres/9.6.2-3/bin:$PATH make USE_PGXS=1 clean all
rm -f *.o
rm -f repmgrd
rm -f repmgr
make -C sql clean
make[1]: Entering directory `/home/pginst/repmgr-3.3.1/sql'

rm -f repmgr_funcs.so   librepmgr_funcs.a  librepmgr_funcs.pc
rm -f repmgr_funcs.sql
rm -f repmgr_funcs.o

make[1]: Leaving directory `/home/pginst/repmgr-3.3.1/sql'

gcc -Wall -Wmissing-prototypes -Wpointer-arith 




/bin/ld: warning: libssl.so.1.0.0, needed by 
/db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with libssl.so.10


Not sure this is an issue, still see below.

What does

ldd /db_demo/app/postgres/9.6.2-3/lib/libpq.so

show?

And:

whereis libssl.so




/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'



Is that the end of the messages or are there more after the above?



I've forwarded that to our UNIX support team, just in case this is some 
manner of known issue - and they've come back today saying, essentially, 
we got nothing :)


FWIW, I'm trying this on a tiny virtual development server running RHEL 
7.2 with 2 cpus and 4gb of RAM. PostgreSQL was built using the 
EnterpriseDB installer and is in /db_demo/app/postgres/9.6.2-3.


So above when you said your colleagues installed the packages for you, 
was that on the host or the virtual machine?




Any ideas? Is this a new thing, an existing issue that's popped up 
before, or (yet again! :) ) a case of me missing something that is 
blindingly obvious to everyone else?


Regards,

Martin.
--
Martin Goodson





--
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] storing large files in database - performance

[Daniel Verite]

Eric Hill wrote:

> I am storing the file contents is of type "bytea" with "Storage" type set to
> "EXTENDED".  Storing a 12.5 MB file is taking 10 seconds

That seems really slow indeed.
Can you import the same file to the same server with psql's
\lo_import command and see how much time it takes?
That would give a baseline to compare against.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


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

[GENERAL] Different query plan used for the same query depending on how parameters are passed

[David Chapman]

I have a table that includes two text columns t1 and t2, and a composite
index on these columns. When issuing a query of the following form:

SELECT * FROM test WHERE t1 = 'X' and t2 = ANY(ARRAY['Y1', 'Y2', ..])

I have observed that it will use the index and have reasonable performance
if the whole query is passed as a single big string. However if it is
parameterised (I'm using Npgsql) it switches to doing a sequence scan and
performs terribly.

The table contains approx 2.3 million records and the query matches about
20k records (i.e. there are 20k 'Y' values in the array).

I have experimented with changing work_mem, preparing the statement in
advance, ANALYZEing the table, none of these change the behavior.

Why does the query planner choose to ignore the index when the command is
parameterised?

-- 


This e-mail together with any attachments (the "Message") is confidential 
and may contain privileged information. If you are not the intended 
recipient or if you have received this e-mail in error, please notify the 
sender immediately and permanently delete this Message from your system. 
 Do not copy, disclose or distribute the information contained in this 
Message.

Maven Investment Partners Ltd (No. 07511928), Maven Derivatives Ltd (No. 
07511840) , MVN Asset Management Limited (No. 09659116), Maven Europe Ltd 
(No. 08966593), Maven Derivatives Asia Limited (No.10361312) & Maven 
Securities Holding Ltd (No. 07505438) are registered as companies in 
England and Wales and their registered address is Level 3, 6 Bevis Marks, 
London EC3A 7BA, United Kingdom. The companies’ VAT No. is 135539016. Maven 
Asia (Hong Kong) Ltd (No. 2444041) is registered in Hong Kong and its 
registered address is 20/F, 198 Wellington St, Hong Kong.  Only Maven 
Derivatives Ltd and MVN Asset Management Limited are authorised and 
regulated by the Financial Conduct Authority (Maven Derivatives Ltd FRN: 
607267, MVN Asset Management Limited FRN: 714429)

[GENERAL] PostgreSQL RPMs for PPC64LE are released

[Devrim Gündüz]

The PostgreSQL YUM Repository Project is happy to announce RPMs of PostgreSQL
9.6 and related software for Power8 Little Endian (PPC64LE) platforms on RHEL 7
and CentOS 7. We want to thank IBM and EnterpriseDB for sponsoring hardware and
manpower for this project.

These packages follow the same update and maintenance policies of the packages
already distributed at https://yum.postgresql.org, and will also follow the
lifecycle of the released PostgreSQL versions.

We currently cover more than 110 RPMs, and more will come soon. PostgreSQL 9.5
and PostgreSQL 10 support will also be available soon.

In order to use the RPMs, please first install the repository RPM from:

https://yum.postgresql.org/repopackages.php#pg96

and then run install the PostgreSQL RPMs as usual:

yum install postgresql96-server (and others)

Please visit https://yum.postgresql.org/9.6/redhat/rhel-7-ppc64le/repoview/ for
the list of all current packages (use the links on the right top for full list)

Please report any packaging related errors to us: pgsql-pkg-...@postgresql.org 

Regards,
-- 
Devrim Gündüz
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

[GENERAL] storing large files in database - performance

[Eric Hill]

Hey,

I searched and found a few discussions of storing large files in the database 
in the archives, but none that specifically address performance and how large 
of files can realistically be stored in the database.

I have a node.js application using PostgreSQL to store uploaded files.  The 
column in which I am storing the file contents is of type "bytea" with 
"Storage" type set to "EXTENDED".  Storing a 12.5 MB file is taking 10 seconds, 
and storing a 25MB file is taking 37 seconds.  Two notable things about those 
numbers:  It seems like a long time, and the time seems to grow exponentially 
with file size rather than linearly.

Do these numbers surprise you?  Are these files just too large for storage in 
PostgreSQL to be practical?  Could there be something about my methodology that 
is slowing things down?

I do have the Sequelize ORM and the pg driver in between my code and the 
database.

Thanks,

Eric

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 15/05/2017 00:17, Martin Goodson wrote:



Tomorrow I'll have to see about getting that set-up on an RHEL 7 box :)

Thank you so much, everybody, for your help! It's been invaluable!

Regards,

Martin.



*Sigh*. And things were going so well. With Adrian and Devrim's help I 
was able to get repmgr compiled on my little ubuntu box at home, and I 
cheerfully went into work on Monday all ready to get things compiled nicely.


I forwarded Devrim's list of redhat/CentOS packages needed to my Unix 
support colleagues, who installed the libraries listed. I've now got 
nothing telling me such and such is missing. I now have this instead:


$ PATH=/db_demo/app/postgres/9.6.2-3/bin:$PATH make USE_PGXS=1 clean all
rm -f *.o
rm -f repmgrd
rm -f repmgr
make -C sql clean
make[1]: Entering directory `/home/pginst/repmgr-3.3.1/sql'

rm -f repmgr_funcs.so   librepmgr_funcs.a  librepmgr_funcs.pc
rm -f repmgr_funcs.sql
rm -f repmgr_funcs.o

make[1]: Leaving directory `/home/pginst/repmgr-3.3.1/sql'

gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 
-DMAP_HUGETLB=0x4 -I/db_demo/app/postgres/9.6.2-3/include -I. -I./ 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/server 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/internal 
-D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include  -c -o dbutils.o dbutils.c


gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 
-DMAP_HUGETLB=0x4 -I/db_demo/app/postgres/9.6.2-3/include -I. -I./ 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/server 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/internal 
-D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include  -c -o config.o config.c


gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 
-DMAP_HUGETLB=0x4 -I/db_demo/app/postgres/9.6.2-3/include -I. -I./ 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/server 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/internal 
-D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include  -c -o repmgrd.o repmgrd.c


gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 
-DMAP_HUGETLB=0x4 -I/db_demo/app/postgres/9.6.2-3/include -I. -I./ 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/server 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/internal 
-D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include  -c -o log.o log.c


gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 
-DMAP_HUGETLB=0x4 -I/db_demo/app/postgres/9.6.2-3/include -I. -I./ 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/server 
-I/db_demo/app/postgres/9.6.2-3/include/postgresql/internal 
-D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include  -c -o strutil.o strutil.c


gcc -o repmgrd -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 
-DMAP_HUGETLB=0x4 dbutils.o config.o repmgrd.o log.o strutil.o 
-L/db_demo/app/postgres/9.6.2-3/lib -lpgcommon -lpgport 
-L/db_demo/app/postgres/9.6.2-3/lib -lpq 
-L/db_demo/app/postgres/9.6.2-3/lib -L/opt/local/Current/lib 
-Wl,--as-needed 
-Wl,-rpath,'/db_demo/app/postgres/9.6.2-3/lib',--enable-new-dtags 
-lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz 
-ledit -lrt -lcrypt -ldl -lm


/bin/ld: warning: libssl.so.1.0.0, needed by 
/db_demo/app/postgres/9.6.2-3/lib/libpq.so, may conflict with libssl.so.10


/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'


I've forwarded that to our UNIX support team, just in case this is some 
manner of known issue - and they've come back today saying, essentially, 
we got nothing :)


FWIW, I'm trying this on a tiny virtual development server running RHEL 
7.2 with 2 cpus and 4gb of RAM. PostgreSQL was built using the 
EnterpriseDB installer and is in /db_demo/app/postgres/9.6.2-3.


Any ideas? Is this a new thing, an existing issue that's popped up 
before, or (yet again! :) ) a case of me missing something that is 
blindingly obvious to everyone else?


Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

[Magnus Hagander]

On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz  wrote:

>
> Hi,
>
> On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote:
> > https://redmine.postgresql.org/issues/2409
>
> Not sure whether we should *fix* this or not on RPM side. This may break
> some
> of the existing installations, right?
>
> I'm not objecting, just asking for opinions.
>
>
Changing that in a minor version seems like a *really* bad idea, because
things *will* break. The way it is now it only breaks in case of a major
version upgrade, and there is an easy enough workaround present.

But it should perhaps be more clearly documented somewhere.


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

[GENERAL] database is not accepting commands

[reem]

We have 1.5 TB database that's shown an error and block all commands.
The error is :
"ERROR:  database is not accepting commands to avoid wraparound data loss in
database "dbname"
HINT:  Stop the postmaster and use a standalone backend to vacuum that
database.
You might also need to commit or roll back old prepared transactions."

I tried to do vacuum in the backend mode. Also I tried to set
zero_damaged_pages = on then do the vacuum again but same error appeared.
The error appeared after two hours of vacuuming where verbose shows passing
tables. 

The cause of error could be : 
1- a lot of insertion queries for not existing table 
or 
2- truncating data with 40 GB in a week,
we used to do that but this is the first time we had this error.
also, autovacuume is turned on. 

Please , I need help or any suggestion?

 




--
View this message in context: 
http://www.postgresql-archive.org/database-is-not-accepting-commands-tp5961831.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] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

[Devrim Gündüz]

Hi,

On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote:
> https://redmine.postgresql.org/issues/2409

Not sure whether we should *fix* this or not on RPM side. This may break some
of the existing installations, right?

I'm not objecting, just asking for opinions.

Regards,
-- 
Devrim Gündüz
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[Tom Lane]

Vamsi Patchipulusu  writes:
>   Error: psql.bin: could not connect to server: Cannot assign 
> requested address
> Is the server running on host "abchost.corp.xyz.com" 
> (xxx.xxx.xxx.xxx) and accepting
> TCP/IP connections on port 5432?

Googling suggests that this could occur if you're recycling client-side
connections so fast that the old port number assignments haven't timed out
yet.  That's not a Postgres bug, it's inherent in the TCP protocol specs.

> The  shell script does the following:
> a)  Connects to postgres database server using psql.
> b)  Issues a single select statement on table with 200 rows .
> c)   Writes the results to a text file.

There is a large body of evidence to the effect that issuing only one
query per connection attempt is a performance-killer.  Don't do that, at
least not more often than you have to.  This particular symptom is a new
one on me, but there are lots of other reasons not to do it.

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] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[David G. Johnston]

On Monday, May 15, 2017, Vamsi Patchipulusu 
wrote:
>
>   Error: psql.bin: could not connect to server: Cannot assign
> requested address
>
> Is the server running on host "abchost.corp.xyz.com"
> (xxx.xxx.xxx.xxx) and accepting
>
> TCP/IP connections on port 5432?
>
> Jmeter is the tool we are using for orchestrating the tests. It  executes
> a shell script on 50 concurrent  threads.
>
The  shell script does the following:
>
> a)  Connects to postgres database server using psql.
>
> b)  Issues a single select statement on table with 200 rows .
>
> c)   Writes the results to a text file.
>
Jmeter client and database host are sitting in the same data center.
>
Database OS: RedHat 6.x,
>
> DB Version:  Postgres 9.6
>
> Database is of decent config. 16 CPU, 64GB RAM, SAN storage.
>
As you noted this is a client-side error so server specs are immaterial
unless the client and server are the same machine.

> While some calls are failing with the error*:   psql.bin: could not
> connect to server: Cannot assign requested address*
>
> Other calls are successful around the same timeframe may be one to two
> seconds later..
>
This points to an O/S problem, possibly configuration.  Or it might just be
a problem with JMeter.  In any case failing to bind to an address
sporadically isn't really something psql can be blamed for.

Googling jmeter for this turns up a variety of programs and scripts that
seem to have this problem when run within JMeter.  Exploring those
results may prove fruitful.

David J.