Re: [GENERAL] pg_catalog tables don't have constraints?

[Neil Anderson]

>
> Of course. My plan is to copy the catalogs into a new schema so I can add fk
> constraints and then get something like navicat or datagrip to draw it.
>

Hi. I made some progress on this and I've added all the diagrams and
documentation I've been able to produce so far for v10beta as well as
the tools used here:

https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html

I would like to try SchemaSpy but it doesn't seem to like constraints
on oid columns too much so I'll have to work around it somehow. Might
be worth it though since it seems to have a new lease of life at
schemaspy.org

Neil


-- 
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] Inconsistent performance with LIKE and bind variable on long-lived connection

[David G. Johnston]

On Sun, Jun 11, 2017 at 1:10 AM, Steven Grimm  wrote:

> (first five iterations)
>
> Index Only Scan using test_pkey on test  (cost=0.29..476.29 rows=
> width=4) (actual time=0.058..2.439 rows=1 loops=1)
>   Index Cond: (col1 = 'xyz'::text)
>   Filter: (col2 ~~ '%'::text)
>   Heap Fetches: 0
> Execution time: 2.957 ms
>
> (iterations 6+)
>
> Sort  (cost=205.41..205.54 rows=50 width=4) (actual time=104.986..105.784
> rows=1 loops=1)
>   Sort Key: col2
>   Sort Method: quicksort  Memory: 853kB
>   ->  Seq Scan on test  (cost=0.00..204.00 rows=50 width=4) (actual
> time=0.014..2.100 rows=1 loops=1)
> Filter: ((col2 ~~ $2) AND (col1 = $1))
> Execution time: 106.282 ms
>

​Can you convince the planner to use the IOS for the generic plan too; by
setting "​enable_seqscan=false" and maybe "enable_sort=false"?

I get what Tom's saying generally but I'm surprised it would throw away an
IOS plan for a sequential scan + sort when it thinks there are fewer rows
that will actually match.  I've generally read that the closer to the whole
table you expect to retrieve the more advantageous a sequential scan is but
this exhibits the opposite behavior.

IOW, I'm wondering why of the various generic plans why this one is
considered the cheapest in the first place.  Is the measure "cost per row"
an invalid metric to consider - since in the above the specific plan is
0.048 compared to 0.244 for the generic one?

Or, should we at least add memory of actual executions for a given set of
bindings?  It would probably be acceptable for a pattern like: "5 4 5 6 4
20 4 5 6" to show up - we try the specific plan 5 times, then we try the
generic one and see that we got worse, and so we go back to the specific
plan.  Assuming the actual supplied inputs don't change as is the case in
the example.

David J.

Re: [GENERAL] JSON to INT[] or other custom type

[David G. Johnston]

On Sun, Jun 11, 2017 at 2:35 PM, Rory Campbell-Lange <
r...@campbell-lange.net> wrote:

> I'm hoping, in the plpgsql function, to unfurl the supplied json into a
> custom type or at least an array of ints, and I can't work out how to do
> that.


​You will be unable to cast the array itself.  You must use json functions
to unfurl the json into a result set with each row containing a single
value.  You can then explicitly cast that value to integer.  If the casting
doesn't fail you now have column of integers that can be
"array_agg(value_as_integer)​" to construct an SQL array of type integer[]
(or whatever you decide to cast it to).

You can write you own pl/pgsql function to do these operations.

As your example includes a two-dimensional array you probably will need
to json_array_elements_text twice, then array_agg twice, to get a similar
structure in the eventual SQL array.

Hopefully that moves you in a useful direction.

David J.

Re: [GENERAL] Unsubscription

[Alvaro Herrera]

chuma.of...@ww-cs.de wrote:
> Dear Sir/Madam
> 
>  
> 
> How would i unsubscribe the general post?

I have removed you from pgsql-general.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] JSON to INT[] or other custom type

[Bruno Wolff III]

On Sun, Jun 11, 2017 at 22:35:14 +0100,
 Rory Campbell-Lange  wrote:


I'm hoping, in the plpgsql function, to unfurl the supplied json into a
custom type or at least an array of ints, and I can't work out how to do
that.

   select * from json_array_elements_text('[[0, 1], [1, 2]]');
value
   
[0, 1]
[1, 2]
   (2 rows)

works fine, but I can't seem to turn those values into actual ints or
anything else for that matter, apart from text via the
json_array_elements_text() function.


Does this example help?

area=> select (a->>0)::int, (a->>1)::int from json_array_elements('[[0, 1], [1, 
2]]') as s(a);
int4 | int4 
--+--

   0 |1
   1 |2
(2 rows)



--
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] Huge Pages - setting the right value

[Andrew Kerber]

Yes, those should always be disabled using tuned or other methods. Using
tuned is described here (second method).  I think the grub.conf method
described is unique to RHEL/OEL/CENTOS.
http://houseofbrick.com/disabling-transparent-hugepages-using-tuned/



On Sun, Jun 11, 2017 at 5:00 PM, Lucas Possamai 
wrote:

>
>
> 2017-06-12 9:52 GMT+12:00 Andrew Kerber :
>
>> Was that transparent hugepages or standard hugepages?  databases commonly
>> have problems dealing with transparent hugepages.
>>
>>
>
>
> IN my case, it was the Transparent Hugepages
>
>
> Lucas
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Huge Pages - setting the right value

[Lucas Possamai]

2017-06-12 9:52 GMT+12:00 Andrew Kerber :

> Was that transparent hugepages or standard hugepages?  databases commonly
> have problems dealing with transparent hugepages.
>
>


IN my case, it was the Transparent Hugepages


Lucas

Re: [GENERAL] Huge Pages - setting the right value

[Andrew Kerber]

Was that transparent hugepages or standard hugepages?  databases commonly
have problems dealing with transparent hugepages.

On Sun, Jun 11, 2017 at 4:39 PM, Lucas Possamai 
wrote:

> 2017-06-12 7:52 GMT+12:00 Andrew Kerber :
>
>> I am sure it does not.
>>
>> Sent from my iPhone
>>
>> > On Jun 11, 2017, at 10:50 AM, pinker  wrote:
>> >
>> > Andrew Kerber wrote
>> >> I can't give you an absolutely authoritative answer, but because of the
>> >> way hugepages are implemented and allocated, I can't think how they
>> could
>> >> be used for other processes.  Linux hugepages are either 2m or 1g, far
>> too
>> >> large for any likely processes to require. They cannot be allocated in
>> >> partial pages.
>> >
>> > thank you for your help.
>> > My system is using 2MB pages for shared buffers. I have checked and one
>> of
>> > my processes has used 606788kB of memory, so potentially could use ~ 300
>> > huge pages, but does postgres can use it for non shared memory?
>> >
>> >
>> >
>> >
>> > --
>> > View this message in context: http://www.postgresql-archive.
>> org/Huge-Pages-setting-the-right-value-tp5952972p5965963.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
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> In my case, we had the HugePages enabled but not configured in our Master
> DB Server. When we increased the server resources (More RAM & CPU) we had
> lots of issues with HugePages. Specially I/O ones. Had to disabled it.
>
> Running Ubuntu 14.04 Server @ Amazon.
>
>
> Lucas
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Huge Pages - setting the right value

[Lucas Possamai]

2017-06-12 7:52 GMT+12:00 Andrew Kerber :

> I am sure it does not.
>
> Sent from my iPhone
>
> > On Jun 11, 2017, at 10:50 AM, pinker  wrote:
> >
> > Andrew Kerber wrote
> >> I can't give you an absolutely authoritative answer, but because of the
> >> way hugepages are implemented and allocated, I can't think how they
> could
> >> be used for other processes.  Linux hugepages are either 2m or 1g, far
> too
> >> large for any likely processes to require. They cannot be allocated in
> >> partial pages.
> >
> > thank you for your help.
> > My system is using 2MB pages for shared buffers. I have checked and one
> of
> > my processes has used 606788kB of memory, so potentially could use ~ 300
> > huge pages, but does postgres can use it for non shared memory?
> >
> >
> >
> >
> > --
> > View this message in context: http://www.postgresql-archive.
> org/Huge-Pages-setting-the-right-value-tp5952972p5965963.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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



In my case, we had the HugePages enabled but not configured in our Master
DB Server. When we increased the server resources (More RAM & CPU) we had
lots of issues with HugePages. Specially I/O ones. Had to disabled it.

Running Ubuntu 14.04 Server @ Amazon.


Lucas

[GENERAL] JSON to INT[] or other custom type

[Rory Campbell-Lange]

I'm playing with plpgsql function parameters to try and come up with a
neat way of sending an array of arrays or array of custom types to
postgres from python and PHP. 

Psycopg works fine with an array of custom types:

In [108]: query = 'select * from fn_test03(%s, %s::dow_session[])'
In [109]: qargs = (5, [(0,2), (1, 3)])
In [110]: cur.execute(query, qargs)   
In [111]: rs = cur.fetchall()  # function plays back looped arrays
In [112]: rs
Out[112]: [(0, 2), (1, 3)]

However PHP's Postgresql support isn't very array friendly as far as I
can see, and requires some horrible manual escaping.

Consequently I wondered if I could serialise the array structure into
json and send that to the postgresql function. PHP seems to handle that
fine. My problem therefore move to handling the json in the plpgsql
function.

I'm hoping, in the plpgsql function, to unfurl the supplied json into a
custom type or at least an array of ints, and I can't work out how to do
that.

select * from json_array_elements_text('[[0, 1], [1, 2]]');
 value  

 [0, 1]
 [1, 2]
(2 rows)

works fine, but I can't seem to turn those values into actual ints or
anything else for that matter, apart from text via the
json_array_elements_text() function.

=>  select n::integer[]
from
json_array_elements_text('[[0, 1], [1, 2]]') as x(n);

ERROR:  malformed array literal: "[0, 1]"
DETAIL:  Missing "]" after array dimensions.

=>  select n::text[]::integer[]
from
json_array_elements_text('[[0, 1], [1, 2]]') as x(n);

ERROR:  malformed array literal: "[0, 1]"
DETAIL:  Missing "]" after array dimensions.

Thoughts gratefully received;
Rory



-- 
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] trying to program in PostgreSQL C a statistics function

[Paul Jungwirth]

Hi Andre,

I've written some C statistics functions for Postgres before, here:

https://github.com/pjungwir/aggs_for_arrays
https://github.com/pjungwir/aggs_for_vecs

They are all really simple, although they operate on arrays, so yours 
should be even simpler. The second link has aggregate functions, 
including one for sample variance, so that would probably be the easiest 
to adapt to your own needs.


In general Postgres C functions use macros to access their arguments 
(and to return something). You can read more about it here:


https://www.postgresql.org/docs/current/static/xfunc-c.html

I'm happy to help more if you need it. I've found extending Postgres in 
this way to be a lot of fun and very effective at gaining performance.


Good luck!

Paul


On 06/11/2017 09:57 AM, Andre Mikulec wrote:

SUMMARY
--

I am trying to program in PostgreSQL a statistics function.

My old method in SQL is here.

select ( avg(measure) - 0.0 ) / nullif(stddev_pop(case when measure >
0.00 then 0.00 else measure end ),0) sortino_true from TABLE/VIEW;

The logic is based on
SORTINO RATIO: ARE YOU CALCULATING IT WRONG?
SEPTEMBER 11, 2013
https://www.rcmalternatives.com/2013/09/sortino-ratio-are-you-calculating-it-wrong/

In PostgreSQL C, how do I get access to the 'X' (data) variable?

DETAILS
---

I see a similar function with other variables in here.
But I am not trying to re-program 'stddev_samp'.

float8_stddev_samp ( lines 2741 through 2767 )
https://github.com/postgres/postgres/blob/9a34123bc315e55b33038464422ef1cd2b67dab2/src/backend/utils/adt/float.c

  N = transvalues[0];
  sumX  = transvalues[1];
  sumX2 = transvalues[2];

The following article explains it and this makes sense.

Sum of Squares Formula Shortcut
https://www.thoughtco.com/sum-of-squares-formula-shortcut-3126266

I can match article symbols to PostgreSQL C varaibles.

numerator = N * sumX2 -sumX * sumX;
N * SIGMA(X**2) - (SIGMA(X))**2

BUT I NEED ...
--

However in my case,
I have this adjustement

"case when  measure > 0.00 then 0.00 else measure end"

So the case seems that I need access to 'X'
but access to sumX and sumX2 are only shown.

How would I get accress to X?

May anyone point me to a simple good working example?

Thanks,
Andre Mikulec
andre_miku...@hotmail.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] Limiting DB access by role after initial connection?

[Ken Tanzer]

On Sun, Jun 11, 2017 at 12:15 PM, Bruno Wolff III  wrote:

> On Fri, Jun 09, 2017 at 21:14:15 -0700,
>  Ken Tanzer  wrote:
>
>> On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III  wrote:
>>
>> Seems to me they are separate issues.   App currently has access to the
>> password for accessing the DB.  (Though I could change that to ident
>> access
>> and skip the password.)  App 1) connects to the DB, 2) authenticates the
>> user (within the app), then 3) proceeds to process input, query the DB,
>> produce output.  If step 2A becomes irrevocably changing to a
>> site-specific
>> role, then at least I know that everything that happens within 3 can't
>> cross the limitations of per-site access.  If someone can steal my
>> password
>> or break into my backend, that's a whole separate problem that already
>> exists both now and in this new scenario.
>>
>
> In situations where a person has enough access to the app (e.g. it is a
> binary running on their desktop) to do spurious role changes, they likely
> have enough acces to hijack the database connection before privileges are
> dropped.
>

Ah yes, I could see that.  In this case it's a web app, so only the server
has the DB credentials.  I'd really hate it if each client had to be able
to access those credentials!

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

[GENERAL] Unsubscription

[chuma.ofole]

Dear Sir/Madam

 

How would i unsubscribe the general post?

 

Chuma David Ofole

W+W Consulting GmbH

mob.: +49-152-1477 6857
mail.:   chuma.of...@ww-cs.de

 

Re: [GENERAL] Huge Pages - setting the right value

[Andrew Kerber]

I am sure it does not. 

Sent from my iPhone

> On Jun 11, 2017, at 10:50 AM, pinker  wrote:
> 
> Andrew Kerber wrote
>> I can't give you an absolutely authoritative answer, but because of the
>> way hugepages are implemented and allocated, I can't think how they could
>> be used for other processes.  Linux hugepages are either 2m or 1g, far too
>> large for any likely processes to require. They cannot be allocated in
>> partial pages.
> 
> thank you for your help. 
> My system is using 2MB pages for shared buffers. I have checked and one of
> my processes has used 606788kB of memory, so potentially could use ~ 300
> huge pages, but does postgres can use it for non shared memory?
> 
> 
> 
> 
> --
> View this message in context: 
> http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965963.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


-- 
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] Limiting DB access by role after initial connection?

[Bruno Wolff III]

On Fri, Jun 09, 2017 at 21:14:15 -0700,
 Ken Tanzer  wrote:

On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III  wrote:

Seems to me they are separate issues.   App currently has access to the
password for accessing the DB.  (Though I could change that to ident access
and skip the password.)  App 1) connects to the DB, 2) authenticates the
user (within the app), then 3) proceeds to process input, query the DB,
produce output.  If step 2A becomes irrevocably changing to a site-specific
role, then at least I know that everything that happens within 3 can't
cross the limitations of per-site access.  If someone can steal my password
or break into my backend, that's a whole separate problem that already
exists both now and in this new scenario.


In situations where a person has enough access to the app (e.g. it is a 
binary running on their desktop) to do spurious role changes, they likely 
have enough acces to hijack the database connection before privileges 
are dropped.



--
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] Inconsistent performance with LIKE and bind variable on long-lived connection

[Pavel Stehule]

2017-06-11 18:34 GMT+02:00 Steven Grimm :

> On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane  wrote:
>
>> Yeah, I've been watching this thread and trying to figure out how to
>> explain that part; I suspected a cause of this form but couldn't
>> make that theory match the 9-iterations observation.  (I still can't.)
>>
>
> I walked through the Java code in a debugger just now and have an
> explanation for the 5 vs. 9 discrepancy. The JDBC driver keeps a cache of
> queries that have been passed to a connection's prepareStatement() method,
> and inlines the bind values the first 4 times it sees a query in the hopes
> of reducing overhead on one-off queries. So I believe the sequence ends up
> being:
>
> 1-4: JDBC driver inlines the values, server sees no bind variables
>

yes - this is client side prepared statement -  prepareThreshold
https://jdbc.postgresql.org/documentation/head/connect.html

Regards

Pavel

5: JDBC driver prepares the statement, server sees bind variables and tries
> generic plan
> 6+: JDBC driver reuses the existing prepared statement from iteration 5
> 10: Server has seen the query 5 times before and switches to the custom
> plan
>
> As for the broader problem, at the risk of being hopelessly naive about
> how all this works internally: Could the discrepancy between the estimated
> and actual row counts be tracked and fed back into the planner somehow?
>
> -Steve
>
>

[GENERAL] trying to program in PostgreSQL C a statistics function

[Andre Mikulec]

SUMMARY
--

I am trying to program in PostgreSQL a statistics function.

My old method in SQL is here.

select ( avg(measure) - 0.0 ) / nullif(stddev_pop(case when measure > 0.00 then 
0.00 else measure end ),0) sortino_true from TABLE/VIEW;

The logic is based on
SORTINO RATIO: ARE YOU CALCULATING IT WRONG?
SEPTEMBER 11, 2013
https://www.rcmalternatives.com/2013/09/sortino-ratio-are-you-calculating-it-wrong/

In PostgreSQL C, how do I get access to the 'X' (data) variable?

DETAILS
---

I see a similar function with other variables in here.
But I am not trying to re-program 'stddev_samp'.

float8_stddev_samp ( lines 2741 through 2767 )
https://github.com/postgres/postgres/blob/9a34123bc315e55b33038464422ef1cd2b67dab2/src/backend/utils/adt/float.c

  N = transvalues[0];
  sumX  = transvalues[1];
  sumX2 = transvalues[2];

The following article explains it and this makes sense.

Sum of Squares Formula Shortcut
https://www.thoughtco.com/sum-of-squares-formula-shortcut-3126266

I can match article symbols to PostgreSQL C varaibles.

numerator = N * sumX2 -sumX * sumX;
N * SIGMA(X**2) - (SIGMA(X))**2

BUT I NEED ...
--

However in my case,
I have this adjustement

"case when  measure > 0.00 then 0.00 else measure end"

So the case seems that I need access to 'X'
but access to sumX and sumX2 are only shown.

How would I get accress to X?

May anyone point me to a simple good working example?

Thanks,
Andre Mikulec
andre_miku...@hotmail.com

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

[Steven Grimm]

On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane  wrote:

> Yeah, I've been watching this thread and trying to figure out how to
> explain that part; I suspected a cause of this form but couldn't
> make that theory match the 9-iterations observation.  (I still can't.)
>

I walked through the Java code in a debugger just now and have an
explanation for the 5 vs. 9 discrepancy. The JDBC driver keeps a cache of
queries that have been passed to a connection's prepareStatement() method,
and inlines the bind values the first 4 times it sees a query in the hopes
of reducing overhead on one-off queries. So I believe the sequence ends up
being:

1-4: JDBC driver inlines the values, server sees no bind variables
5: JDBC driver prepares the statement, server sees bind variables and tries
generic plan
6+: JDBC driver reuses the existing prepared statement from iteration 5
10: Server has seen the query 5 times before and switches to the custom plan

As for the broader problem, at the risk of being hopelessly naive about how
all this works internally: Could the discrepancy between the estimated and
actual row counts be tracked and fed back into the planner somehow?

-Steve

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Adrian Klaver]

On 06/11/2017 08:34 AM, Tom Lane wrote:

Alvaro Herrera  writes:

I'm unable to run this file.  Maybe it was corrupted in transit, given
that it was considered to be text.


My copy came through fine, so I tried to forward it to you off-list,
but gmail rejected it as a possible security hazard.  Do you know
which binary mime types they won't reject?


Well this is a list that they do reject:

https://support.google.com/mail/answer/6590?hl=en

So I guess you can work backwards from there.

Their suggestion is to upload to Google Drive. That or use a third party 
site, like Dropbox.




regards, tom lane





--
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] Huge Pages - setting the right value

[pinker]

Andrew Kerber wrote
> I can't give you an absolutely authoritative answer, but because of the
> way hugepages are implemented and allocated, I can't think how they could
> be used for other processes.  Linux hugepages are either 2m or 1g, far too
> large for any likely processes to require. They cannot be allocated in
> partial pages.

thank you for your help. 
My system is using 2MB pages for shared buffers. I have checked and one of
my processes has used 606788kB of memory, so potentially could use ~ 300
huge pages, but does postgres can use it for non shared memory?




--
View this message in context: 
http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965963.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] Inconsistent performance with LIKE and bind variable on long-lived connection

[Tom Lane]

I wrote:
> Right.  The plancache code is designed to switch to a generic plan if
> that doesn't seem to save anything compared to a custom plan that's
> built for the specific parameter value(s).

Er, -ENOCAFFEINE.  That's backwards of course.  I think the rest of
what I wrote is okay.

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] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Tom Lane]

Alvaro Herrera  writes:
> I'm unable to run this file.  Maybe it was corrupted in transit, given
> that it was considered to be text.

My copy came through fine, so I tried to forward it to you off-list,
but gmail rejected it as a possible security hazard.  Do you know
which binary mime types they won't reject?

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] Huge Pages - setting the right value

[Andrew Kerber]

I can't give you an absolutely authoritative answer, but because of the way 
hugepages are implemented and allocated, I can't think how they could be used 
for other processes.  Linux hugepages are either 2m or 1g, far too large for 
any likely processes to require. They cannot be allocated in partial pages.

Sent from my iPad

> On Jun 11, 2017, at 10:02 AM, pinker  wrote:
> 
> We are experiencing some performance issues because of high CPU load. So I
> would like to ask one more time. The exact question is:
> Does PostgreSQL can use huge pages for processes or only for shared buffers?
> (Does it make any sense to set the number of huge pages above the
> shared_buffers?)
> Any help or suggestions would be much appreciated! 
> 
> 
> 
> --
> View this message in context: 
> http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965956.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


-- 
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] Inconsistent performance with LIKE and bind variable on long-lived connection

[Tom Lane]

Steven Grimm  writes:
> That seems to fit the behavior. Thanks; I wasn't aware of that feature of
> prepared statements. I changed the Python code to do EXPLAIN ANALYZE
> EXECUTE rather than EXECUTE, and I do indeed see a change in plan after the
> fifth iteration:

> (first five iterations)

> Index Only Scan using test_pkey on test  (cost=0.29..476.29 rows=
> width=4) (actual time=0.058..2.439 rows=1 loops=1)
>   Index Cond: (col1 = 'xyz'::text)
>   Filter: (col2 ~~ '%'::text)
>   Heap Fetches: 0
> Execution time: 2.957 ms

> (iterations 6+)

> Sort  (cost=205.41..205.54 rows=50 width=4) (actual time=104.986..105.784
> rows=1 loops=1)
>   Sort Key: col2
>   Sort Method: quicksort  Memory: 853kB
>   ->  Seq Scan on test  (cost=0.00..204.00 rows=50 width=4) (actual
> time=0.014..2.100 rows=1 loops=1)
> Filter: ((col2 ~~ $2) AND (col1 = $1))
> Execution time: 106.282 ms

> So the problem here may be that the cost estimate for the generic execution
> plan is way off in the case of a LIKE bind variable that matches a large
> number of rows. I did make sure to have the Java code do a VACUUM ANALYZE
> after doing its inserts, just to eliminate lack of statistics as a possible
> explanation. Maybe the incorrect row count estimate (50 instead of 1)
> is causing it to think the quicksort will be a lot cheaper than it ends up
> being with the actual rows?

Right.  The plancache code is designed to switch to a generic plan if
that doesn't seem to save anything compared to a custom plan that's
built for the specific parameter value(s).  But there's an implicit
assumption in that design that knowledge of the specific values can
only make the plan better not worse.  In this case, knowing the values
causes the planner to know that the plan will scan much more of the
table than its generic estimate would guess, so it ends up with a
higher cost estimate.  But that's not because the plan is "worse",
it's because the rowcount estimate is closer to reality.

I've seen this problem once or twice before, but I'm not entirely sure
what to do about it.  Disentangling the two effects seems hard.  One idea
is to reject the generic plan if it comes out cheaper than the average
custom plan, on the grounds that this must reflect less expensive (but
inaccurate) conclusions about the rowcount not a genuinely better plan.
But I have a feeling that that's too simplistic --- in particular I'm not
sure it does the right thing when the custom plan number is indeed an
average over several different custom plans.  (That is, if you were
throwing a mix of selective and unselective patterns at this query,
so that some of the custom plans were indexscans and some were seqscans,
I'm not very sure what would happen with a rule like that.)

> Interesting that the Java version switches to the suboptimal plan after 9
> iterations rather than 5. I don't know how to get the JDBC driver to do an
> EXPLAIN on a prepared statement, so I can't confirm that the same thing is
> happening there, but it seems plausible. Happy to try that if there's a way
> to do it.

Yeah, I've been watching this thread and trying to figure out how to
explain that part; I suspected a cause of this form but couldn't
make that theory match the 9-iterations observation.  (I still can't.)

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] Huge Pages - setting the right value

[pinker]

We are experiencing some performance issues because of high CPU load. So I
would like to ask one more time. The exact question is:
Does PostgreSQL can use huge pages for processes or only for shared buffers?
(Does it make any sense to set the number of huge pages above the
shared_buffers?)
Any help or suggestions would be much appreciated! 



--
View this message in context: 
http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965956.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] Removing null bytes from a json column

[Tom Lane]

Timothy Garnett  writes:
> Does anyone have some tips on how to deal with an existing json type column
> that has some null bytes ( \u) in it? It seems like anything I do that
> touches any row with a null byte just errors.  I'd love to just remove them
> if I could find some way to find them, but I'm having trouble even figuring
> out how to do that.

Doesn't it work to cast to text and do a LIKE or regex search?

regression=# select '{"z":"\u"}'::json::text ~ '\\u';
 ?column? 
--
 t
(1 row)

regression=# select '{"z":"\u0001"}'::json::text ~ '\\u';
 ?column? 
--
 f
(1 row)

It's true that we won't let you cast such a value to JSONB or do any
exciting JSON-ish manipulations on it, but I'm not seeing an error
in cast-to-text.

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] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Alvaro Herrera]

Harry Ambrose wrote:
> Hi,
> 
> Please find the jar attached (renamed with a .txt extension as I know some
> email services deem jars a security issue).
> 
> The jar accepts the following arguments:
> 
> $1 = host
> $2 = database
> $3 = username
> $4 = password
> $5 = port
> 
> It returns its logging to STDOUT. Please let me know if you require further
> info.

I'm unable to run this file.  Maybe it was corrupted in transit, given
that it was considered to be text.

$ md5sum toast-corrupter-aio.jar 
7b1f5854c286f9b956b9442afd455b7a  toast-corrupter-aio.jar

$ java -jar toast-corrupter-aio.jar 
Error: Invalid or corrupt jarfile toast-corrupter-aio.jar

Even unzip complains (after extracting a bunch of JDBC .class files)

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Inconsistent performance with LIKE and bind variable on long-lived connection

[Steven Grimm]

On Sat, Jun 10, 2017 at 11:10 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

>  https://www.postgresql.org/docs/current/static/sql-prepare.html
>
> Specifically, the notes section.


That seems to fit the behavior. Thanks; I wasn't aware of that feature of
prepared statements. I changed the Python code to do EXPLAIN ANALYZE
EXECUTE rather than EXECUTE, and I do indeed see a change in plan after the
fifth iteration:

(first five iterations)

Index Only Scan using test_pkey on test  (cost=0.29..476.29 rows=
width=4) (actual time=0.058..2.439 rows=1 loops=1)
  Index Cond: (col1 = 'xyz'::text)
  Filter: (col2 ~~ '%'::text)
  Heap Fetches: 0
Execution time: 2.957 ms

(iterations 6+)

Sort  (cost=205.41..205.54 rows=50 width=4) (actual time=104.986..105.784
rows=1 loops=1)
  Sort Key: col2
  Sort Method: quicksort  Memory: 853kB
  ->  Seq Scan on test  (cost=0.00..204.00 rows=50 width=4) (actual
time=0.014..2.100 rows=1 loops=1)
Filter: ((col2 ~~ $2) AND (col1 = $1))
Execution time: 106.282 ms

So the problem here may be that the cost estimate for the generic execution
plan is way off in the case of a LIKE bind variable that matches a large
number of rows. I did make sure to have the Java code do a VACUUM ANALYZE
after doing its inserts, just to eliminate lack of statistics as a possible
explanation. Maybe the incorrect row count estimate (50 instead of 1)
is causing it to think the quicksort will be a lot cheaper than it ends up
being with the actual rows?

Interesting that the Java version switches to the suboptimal plan after 9
iterations rather than 5. I don't know how to get the JDBC driver to do an
EXPLAIN on a prepared statement, so I can't confirm that the same thing is
happening there, but it seems plausible. Happy to try that if there's a way
to do it.

-Steve

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

[David G. Johnston]

On Saturday, June 10, 2017, Steven Grimm  wrote:

> The problem doesn't appear to be specific to the JDBC driver. Tried a
> quick version of this in Python for grins with a database that was already
> populated by the Java code (sadly, the psycopg2 library doesn't directly
> support prepared statements):
>

 https://www.postgresql.org/docs/current/static/sql-prepare.html

Specifically, the notes section.

David J.