Re: Format an Update with calculation

[Condor]

On 18-12-2018 15:51, Adrian Klaver wrote:

On 12/17/18 11:14 PM, Bret Stern wrote:

My statement below updates the pricing no problem, but I want it to be
formatted with 2 dec points eg (43.23).

Started playing with to_numeric but can't figure it out. Lots of 
examples with to_char in the

manual, but still searching for answer.

Can it be done?

I want suggested_retail_price to be formatted to 2 decimal points

UPDATE im_ci_item_transfer
    SET suggested_retail_price=(suggested_retail_price + 
(suggested_retail_price * .13))

WHERE item_code='0025881P2';

Feeling lazy, sorry guys


In addition to what Pavel posted:

select round(43.2335, 2);

 round
---
 43.23



Beware with round and numeric

select round(43.2375, 2);
 round
---
 43.24


 select 43.2375::numeric(17, 2);
 numeric
-
   43.24

Regards,
HS

How to compare dates from two tables with blanks values

[Mike Martin]

I have a situation where I need to update dates in a primary table from
regular imports of data, eg: this is the base select query

select d.row_id,
fname||lname,'joineddate',d.joineddate,'joineddate',s.joineddate,0 as bool1
from import s join  members d on d.contact_id=s.contact_id where

cast(nullif(d.joineddate,NULL) as timestamp) !=
cast(nullif(s.joineddate,'') as timestamp)

This gives zero records, however I cant seem to get a query that works.
For non-date fields I just use
Coalesce(fieldprime,'')!=coalesce(fieldiimport,'') which works fine but
chokes on dates where there is a blank value

thanks in advance

Re: How to compare dates from two tables with blanks values

[Arnaud L.]

Le 19/12/2018 à 11:41, Mike Martin a écrit :
cast(nullif(d.joineddate,NULL) as timestamp) != cast(nullif(s.joineddate,'') as timestamp) 

Try with
d.joineddate IS DISTINCT FROM s.joineddate

https://www.postgresql.org/docs/current/functions-comparison.html

Cheers
--
Arnaud

Re: Format an Update with calculation

[Ron]

On 12/19/2018 02:12 AM, Condor wrote:

On 18-12-2018 15:51, Adrian Klaver wrote:

[snip]

In addition to what Pavel posted:

select round(43.2335, 2);

 round
---
 43.23



Beware with round and numeric

select round(43.2375, 2);
 round
---
 43.24


 select 43.2375::numeric(17, 2);
 numeric
-
   43.24


Beware of what?


--
Angular momentum makes the world go 'round.

Re: Error on insert xml

[Oleksandr Shulgin]

On Wed, Dec 19, 2018 at 1:38 PM Михаил Яремчук 
wrote:

> When I insert this data in the column with type xml, I get an error "SSL
> SYSCALL error: EOF detected" and the gap of all current connections. When
> I insert a simpler xml, but larger everything is ok.
> version: PostgreSQL 9.6.11 on x86_64-pc-linux-gnu (Ubuntu
> 9.6.11-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10)
> 5.4.0 20160609, 64-bit
>

(-bugs, +general)

Assuming the server is running remotely, maybe you should check link's MTU
on the client?  Or does the server backend crash when you run the
query--did you check the server logs?

Regards,
--
Alex

pg_stat_sql_plans ALPHA released

[PAscal l]

PG_STAT_SQL_PLANS is an extension mixing
pg_stat_statements and auto_explain with a planid.

It is implementing many Oracle like features:
- queryid is based on normalized sql text (no jumbling),
- stored query text isn't normalized,
- a text normalization SQL function is provided,
- sql not finished in success (cancelled, timeout, errors) is tracked,
- planid is based on normalized explain plan text,
- explain plan text is saved in logs (as auto_explain do)
 but only one time per queryid/planid,
- first_call, last_call informations are kept for each entry,
- provides a SQL function retrieving queryid for a pid
 permitting to join pg_stat_activity with pg_stat_sql_plan
- includes specific wait events for planing and extension activities


Some ideas where found in other postgres extensions like pg_store_plans,
pg_stat_plans, ... and patches from pgsql-hackers mailing list.


See https://github.com/legrandlegrand/pg_stat_sql_plans
Feedbacks are welcome

Regards
PAscal

Re: Format an Update with calculation

[Ken Tanzer]

On Tue, Dec 18, 2018 at 5:51 AM Adrian Klaver 
wrote:

> On 12/17/18 11:14 PM, Bret Stern wrote:
> > My statement below updates the pricing no problem, but I want it to be
> > formatted with 2 dec points eg (43.23).
> >
> > Started playing with to_numeric but can't figure it out. Lots of
> > examples with to_char in the
> > manual, but still searching for answer.
> >
> > Can it be done?
> >
> > I want suggested_retail_price to be formatted to 2 decimal points
> >
> > UPDATE im_ci_item_transfer
> > SET suggested_retail_price=(suggested_retail_price +
> > (suggested_retail_price * .13))
> > WHERE item_code='0025881P2';
> >
> > Feeling lazy, sorry guys
>
> In addition to what Pavel posted:
>
> select round(43.2335, 2);
>
>   round
> ---
>   43.23
>
>
>
I think this discussion is missing an important point, which is the
difference between how a value is stored (i.e., what ends up as
suggested_retail_price in your table), and how it is formatted when you
select or use it.  Whatever value you select in this update is going to get
converted to the column's datatype anyway.  You haven't told us what the
datatype for s_r_p is, but consider this example:

CREATE TEMP TABLE price (
my_dec_2 DECIMAL(8,2),
my_numeric NUMERIC,
my_numeric_2 NUMERIC (8,2),
my_money MONEY
);
WITH num as (SELECT 4.2375914 AS base)
INSERT INTO price
SELECT base,base,base,base FROM num;

SELECT * FROM price;

 my_dec_2 | my_numeric | my_numeric_2 | my_money
--++--+--
 4.24 |  4.2375914 | 4.24 |$4.24



So if you want your prices to be limited to 2 decimal places, just define
the columns as such.  All the formatting mentioned in this thread is only
needed for outputting, selecting on the fly, etc.

Cheers,
Ken



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

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

Re: new stored procedure with OUT parameters

[Anton Shen]

Thank you Pavel, Adrian! That makes a lot of sense. I wasn't aware that in
Oracle you can overload a procedure by its OUT parameters. I had thought in
Postgres procedure overloading would definitely be the same as function
overloading. Looks like the door is still open.

Regards,
Anton

On Sun, Dec 16, 2018 at 12:05 PM Adrian Klaver 
wrote:

> On 12/16/18 11:33 AM, Anton Shen wrote:
> > Thanks for the thoughts. The part I'm missing is that why procedures
> > with OUT param 'will not be called from SQL environments'?
>
> Pretty sure Pavel was referring to:
>
> https://www.postgresql.org/docs/11/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
>
> "Notice that output parameters are not included in the calling argument
> list when invoking such a function from SQL. This is because PostgreSQL
> considers only the input parameters to define the function's calling
> signature. ..."
>
>
>  From this commit:
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e4128ee767df3c8c715eb08f8977647ae49dfb59
>
> "SQL procedures
>
> This adds a new object type "procedure" that is similar to a function
> but does not have a return type and is invoked by the new CALL statement
> instead of SELECT or similar.
> ...
>
> While this commit is mainly syntax sugar around existing functionality,
> future features will rely on having procedures as a separate object
> type."
>
> I read this to mean that since SQL functions don't have OUT in the
> signature at this time, SQL procedures do not either.
>
> >
> > Thanks,
> > Anton
> >
> > On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule  > > wrote:
> >
> > Hi
> >
> > út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175geo...@gmail.com
> > > napsal:
> >
> > Hi all,
> >
> > I was playing around with the stored procedure support in v11
> > and found that pure OUT parameters are not supported. Is there
> > any reason we only support INOUT but not OUT parameters?
> >
> >
> > The procedure implementation in v11 is initial stage - only
> > functionality with some simple implementation or without design
> > issues was implemented.
> >
> > If I remember there was not clean what is correct and expected
> > behave of usage of OUT variable when it is called from SQL
> > environment, and when it is called from plpgsql.
> >
> > On Oracle - the OUT variables are part of procedure signature - you
> > can write procedures P1(OUT a int), P1(OUT a text). Currently we
> > have not a variables in SQL environment. So if Peter implemented OUT
> > variables now then
> >
> > a) only IN parameters will be part of signature - like functions -
> > but it is different than on Oracle, and we lost a possibility to use
> > interesting feature
> > b) the procedures with OUT variables will not be callable from SQL
> > environment - that be messy for users.
> > c) disallow it.
> >
> > I hope so PostgreSQL 12 will have schema variables, and then we can
> > implement OUT variables. Now, it is not possible (do it most
> > correct) due missing some other feature. INOUT parameters are good
> > enough, and we have opened door for future correct design.
> >
> > Regards
> >
> > Pavel
> >
> >
> > psql (11.0 (Homebrew petere/postgresql))
> > dev=# CREATE PROCEDURE test_sp(a OUT int) LANGUAGE plpgsql AS $$
> > dev$# BEGIN
> > dev$# a = 5;
> > dev$# END; $$;
> > ERROR:  procedures cannot have OUT arguments
> > HINT:  INOUT arguments are permitted.
> >
> > Thanks,
> > Anton
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

[Joshua White]

>
> In my application, the idle sessions are consuming cpu and ram. refer the
> ps command output.
>

If you connect to the database, does select * from pg_stat_activity() show
a lot of idle connections?

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

[Michael Paquier]

On Thu, Dec 20, 2018 at 11:32:22AM +1100, Joshua White wrote:
>> In my application, the idle sessions are consuming cpu and ram. refer the
>> ps command output.
>>
> 
> If you connect to the database, does select * from pg_stat_activity() show
> a lot of idle connections?

Each backend stores its own copy of the relation cache, so if you have
idle connections which have been used for other work in the past then
the memory of those caches is still around.  Idle connections also have
a CPU cost in Postgres when building snapshots for example, and their
entries need to be scanned from a wider array, but usually the relation
cache bloat is a wider problem.  This can be countered with pgbouncer as
connection pooling.
--
Michael


signature.asc
Description: PGP signature

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

[Ron]

On 12/19/18 7:27 PM, Michael Paquier wrote:
[snip]

Each backend stores its own copy of the relation cache, so if you have
idle connections which have been used for other work in the past then
the memory of those caches is still around.  Idle connections also have
a CPU cost in Postgres when building snapshots for example, and their
entries need to be scanned from a wider array, but usually the relation
cache bloat is a wider problem.


So it's best to kill connections that have been idle for a while?

--
Angular momentum makes the world go 'round.

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

[Pavel Stehule]

čt 20. 12. 2018 v 2:41 odesílatel Ron  napsal:

> On 12/19/18 7:27 PM, Michael Paquier wrote:
> [snip]
> > Each backend stores its own copy of the relation cache, so if you have
> > idle connections which have been used for other work in the past then
> > the memory of those caches is still around.  Idle connections also have
> > a CPU cost in Postgres when building snapshots for example, and their
> > entries need to be scanned from a wider array, but usually the relation
> > cache bloat is a wider problem.
>
> So it's best to kill connections that have been idle for a while?
>

sure - one hour idle connection is too old.


>
> --
> Angular momentum makes the world go 'round.
>
>

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

[Joshua White]

On Thu, 20 Dec 2018 at 14:35, Pavel Stehule  wrote:

> čt 20. 12. 2018 v 2:41 odesílatel Ron  napsal:
>
>> On 12/19/18 7:27 PM, Michael Paquier wrote:
>> [snip]
>> > Each backend stores its own copy of the relation cache, so if you have
>> > idle connections which have been used for other work in the past then
>> > the memory of those caches is still around.  Idle connections also have
>> > a CPU cost in Postgres when building snapshots for example, and their
>> > entries need to be scanned from a wider array, but usually the relation
>> > cache bloat is a wider problem.
>>
>> So it's best to kill connections that have been idle for a while?
>>
>
> sure - one hour idle connection is too old.
>

I'd also assess closing the connection from the client end once its task is
done - that would reduce the number of idle connections in the first place.

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

[Tom Lane]

Joshua White  writes:
> On Thu, 20 Dec 2018 at 14:35, Pavel Stehule  wrote:
>> čt 20. 12. 2018 v 2:41 odesílatel Ron  napsal:
>>> So it's best to kill connections that have been idle for a while?

>> sure - one hour idle connection is too old.

> I'd also assess closing the connection from the client end once its task is
> done - that would reduce the number of idle connections in the first place.

IMO, "has it been idle a long time" is the wrong question.  The right
question is "how likely is it to start doing something useful soon".

Certainly, leaving sessions sit doing nothing for a long time isn't
helpful.  They consume RAM, they have to be accounted for by other
sessions, and if you're doing any DDL, they consume CPU time
maintaining their own caches in response to catalog changes.  But it's
also true that starting a new session has a lot of overhead --- so you
don't want to kill a session that was just about to start doing some
useful work.

regards, tom lane