Re: iterate over partitions

[Achilleas Mantzios]

On 28/6/19 10:11 μ.μ., Glenn Schultz wrote:

Hi All,

I have a large table partioned by month.  I would like to run a query - which 
adds derived data to the current data and inserts the data into a new table.  
The new table is the target for users.

How can I iterate over the partition tables to insert data and build the new 
table?
This is done monthly, am I better off creating a materalized view each month?
Well not using partitions yet in production, I can't say I can fully grasp your use case, but anyways, in order to iterate over partitions you'll have to look at : pg_partitioned_table , pg_inherits 
or just enable log_statement , issue \d+  and look at the SQL produced.

Or just do :
select inhrelid::regclass from pg_inherits where inhparent = 'your partitioned 
table'::regclass;



Best,
Glenn



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

Re: Use ctid in where clause in update from statement

[Thomas Kellerer]

> I come from the Oracle world and am trying to convert some queries to
> PostgreSQL syntax. One of these queries is a MERGE statement, which I
> converted into an UPDATE SET FROM WHERE construct. In the original
> query I use the pseudo column ROWID to match a source row with a
> target row.
> 
> In the PostgreSQL version I use the column ctid for this. The above query 
> becomes:
> 
> *UPDATE*test_large d
>    *SET* grp = s.grp
>   *FROM* (*SELECT* ctid, test_large.*
>           *FROM* test_large
>          *WHERE* grp = 1) s
>  *WHERE* d.ctid = s.ctid;

Why don't you join on the primary key column? 
The ctid comparison is typically quite slow.

Thomas

RE: Memory settings

[Daulat Ram]

Hello Hans,

Thanks for your reply. Yes, we are facing performance issue.

Current output of query is:

postgres=# SELECT pg_stat_database.datname,
postgres-#pg_stat_database.blks_read,
postgres-#pg_stat_database.blks_hit,
postgres-#round((pg_stat_database.blks_hit::double precision
postgres(#   / (pg_stat_database.blks_read
postgres(#  + pg_stat_database.blks_hit
postgres(#  +1)::double precision * 100::double 
precision)::numeric, 2) AS cachehitratio
postgres-#FROM pg_stat_database
postgres-#   WHERE pg_stat_database.datname !~ 
'^(template(0|1)|postgres)$'::text
postgres-#   ORDER BY round((pg_stat_database.blks_hit::double precision
postgres(#  / (pg_stat_database.blks_read
postgres(# + pg_stat_database.blks_hit
postgres(# + 1)::double precision * 100::double 
precision)::numeric, 2) DESC;
   datname| blks_read | blks_hit  | cachehitratio
--+---+---+---
kbcc_eng_ret |  1192 |26 | 99.56
nagios   |   178 | 37185 | 99.52
kccm |  1431 |214501 | 99.34
kbbm |   1944006 | 157383222 | 98.78


Thanks,
Daulat

From: Hans Schou mailto:hans.sc...@gmail.com>>
Sent: Sunday, June 30, 2019 11:35 AM
To: Daulat Ram mailto:daulat@exponential.com>>
Cc: 
pgsql-general@lists.postgresql.org
Subject: Re: Memory settings


Try run postgresqltuner.pl as suggested on 
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and also look at 
the other info there.

After running a few days with live data run cache_hit_ratio.sql by Melvin 
Davidson:
SELECT pg_stat_database.datname,
   pg_stat_database.blks_read,
   pg_stat_database.blks_hit,
   round((pg_stat_database.blks_hit::double precision
  / (pg_stat_database.blks_read
 + pg_stat_database.blks_hit
 +1)::double precision * 100::double precision)::numeric, 2) AS 
cachehitratio
   FROM pg_stat_database
  WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
  ORDER BY round((pg_stat_database.blks_hit::double precision
 / (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double precision)::numeric, 
2) DESC;

The real question is: Is your system slow?


On Sun, Jun 30, 2019 at 5:14 AM Daulat Ram 
mailto:daulat@exponential.com>> wrote:
Hi team,

Can you please suggest what will be  the suitable memory settings for 
Postgresql11 if we have 80gb RAM, 16 CPU’s and OS  Linux.

If we set 25 % of total RAM then shared_buffers value will be 20GB. Will it be 
useful or we can set it any random vale like 8g or 12gb.

According to https://pgtune.leopard.in.ua/#/
below are the suggested memory  values for 80gb RAM and 16 CPU.  I assume the 
values preferred for effective_cache_size = 60GB and shared_buffers = 20GB are 
too large.
 max_connections = 500
shared_buffers = 20GB
effective_cache_size = 60GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 300
work_mem = 6553kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
Please give your suggestions.
 Regards,
Daulat

Re: Use ctid in where clause in update from statement

[Dirk Mika]

I know I can join using the pk, but in oracle using the rowid is faster so I 
wanted to know, if this is possible in PostgreSQL as well.

Dirk


--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 01.07.19, 11:36 schrieb "Thomas Kellerer" :

> I come from the Oracle world and am trying to convert some queries to
> PostgreSQL syntax. One of these queries is a MERGE statement, which I
> converted into an UPDATE SET FROM WHERE construct. In the original
> query I use the pseudo column ROWID to match a source row with a
> target row.
>
> In the PostgreSQL version I use the column ctid for this. The above query 
becomes:
>
> *UPDATE*test_large d
>*SET* grp = s.grp
>   *FROM* (*SELECT* ctid, test_large.*
>   *FROM* test_large
>  *WHERE* grp = 1) s
>  *WHERE* d.ctid = s.ctid;

Why don't you join on the primary key column?
The ctid comparison is typically quite slow.

Thomas

Re: Use ctid in where clause in update from statement

[Thomas Kellerer]

Dirk Mika schrieb am 01.07.2019 um 12:02:
> I know I can join using the pk, but in oracle using the rowid is
> faster so I wanted to know, if this is possible in PostgreSQL as
> well.
Well, in Postgres  ctid is not necessarily faster. 

Re: Use ctid in where clause in update from statement

[Thomas Kellerer]

Dirk Mika schrieb am 01.07.2019 um 13:18:
> The problem with the INSERT ON CONFLICT is that an insert is tried here 
> first, which may fire triggers.
> 
> In my case there is a fairly expensive INSERT Trigger ON EACH ROW, which I 
> would like to avoid.

The insert trigger will only be fired if an INSERT actually takes place. 

If INSERT ON CONFLICT results in an UPDATE (or a DO NOTHING), no INSERT trigger 
will be fired.

Re: Use ctid in where clause in update from statement

[Dirk Mika]

I've tried it with the following trigger:

CREATE TRIGGER tr_tl_test1
   BEFORE INSERT
   ON public.test_large
   FOR EACH ROW
EXECUTE PROCEDURE tf_tr_tl_test1 ()

The trigger function does nothing special:

CREATE OR REPLACE FUNCTION public.tf_tr_tl_test1 ()
   RETURNS trigger
   LANGUAGE 'plpgsql'
   VOLATILE
   NOT LEAKPROOF
   SECURITY INVOKER
   PARALLEL UNSAFE
AS
$$
BEGIN
   RAISE NOTICE 'Trigger called with: %', new;
   RETURN new;
END;
$$

If I do a

EXPLAIN ANALYZE
INSERT INTO test_large (id)
 VALUES (2)
ON CONFLICT
   ON CONSTRAINT pk_test_large
   DO NOTHING;

I get the following:

NOTICE:  Trigger called with: (2,,)
QUERY PLAN
---
 Insert on test_large  (cost=0.00..0.01 rows=1 width=40) (actual 
time=0.153..0.153 rows=0 loops=1)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: pk_test_large
   Tuples Inserted: 0
   Conflicting Tuples: 1
   ->  Result  (cost=0.00..0.01 rows=1 width=40) (actual time=0.003..0.004 
rows=1 loops=1)
 Planning Time: 0.142 ms
 Trigger tr_tl_test1: time=0.116 calls=1
 Execution Time: 0.180 ms

As you can see the trigger function is called for the row I try to insert, but 
you can also see that there's no tuple inserted but one conflicting.

Dirk



--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 01.07.19, 13:33 schrieb "Thomas Kellerer" :

Dirk Mika schrieb am 01.07.2019 um 13:18:
> The problem with the INSERT ON CONFLICT is that an insert is tried here 
first, which may fire triggers.
>
> In my case there is a fairly expensive INSERT Trigger ON EACH ROW, which 
I would like to avoid.

The insert trigger will only be fired if an INSERT actually takes place.

If INSERT ON CONFLICT results in an UPDATE (or a DO NOTHING), no INSERT 
trigger will be fired.

Re: Use ctid in where clause in update from statement

[Achilleas Mantzios]

Hello Dirk,

pls don't top post, that's the rule here

On 1/7/19 2:40 μ.μ., Dirk Mika wrote:

I've tried it with the following trigger:

CREATE TRIGGER tr_tl_test1
BEFORE INSERT
ON public.test_large
FOR EACH ROW
EXECUTE PROCEDURE tf_tr_tl_test1 ()

The trigger function does nothing special:

CREATE OR REPLACE FUNCTION public.tf_tr_tl_test1 ()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE
NOT LEAKPROOF
SECURITY INVOKER
PARALLEL UNSAFE
AS
$$
BEGIN
RAISE NOTICE 'Trigger called with: %', new;
RETURN new;
END;
$$

If I do a

EXPLAIN ANALYZE
 INSERT INTO test_large (id)
  VALUES (2)
 ON CONFLICT
ON CONSTRAINT pk_test_large
DO NOTHING;

I get the following:

NOTICE:  Trigger called with: (2,,)
 QUERY PLAN
---
  Insert on test_large  (cost=0.00..0.01 rows=1 width=40) (actual 
time=0.153..0.153 rows=0 loops=1)
Conflict Resolution: NOTHING
Conflict Arbiter Indexes: pk_test_large
Tuples Inserted: 0
Conflicting Tuples: 1
->  Result  (cost=0.00..0.01 rows=1 width=40) (actual time=0.003..0.004 
rows=1 loops=1)
  Planning Time: 0.142 ms
  Trigger tr_tl_test1: time=0.116 calls=1
  Execution Time: 0.180 ms

As you can see the trigger function is called for the row I try to insert, but 
you can also see that there's no tuple inserted but one conflicting.


A before trigger cannot know the actual. So a BEFORE INSERT trigger will behave 
as such even if no INSERT takes place.
If you want to skip your particular "normal" trigger , just run :
set session_replication_role to 'replica';
and run your upsert.



Dirk



--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 01.07.19, 13:33 schrieb "Thomas Kellerer" :

 Dirk Mika schrieb am 01.07.2019 um 13:18:
 > The problem with the INSERT ON CONFLICT is that an insert is tried here 
first, which may fire triggers.
 >
 > In my case there is a fairly expensive INSERT Trigger ON EACH ROW, which 
I would like to avoid.

 The insert trigger will only be fired if an INSERT actually takes place.

 If INSERT ON CONFLICT results in an UPDATE (or a DO NOTHING), no INSERT 
trigger will be fired.








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

Re: Use ctid in where clause in update from statement

[Thomas Kellerer]

> As you can see the trigger function is called for the row I try to
> insert, but you can also see that there's no tuple inserted but one
> conflicting.

Ah, right. 

Thinking about it, it _has_ to call any BEFORE trigger function 
as that might change values of the row to be inserted that 
could possibly change the outcome of the test. 

Re: Use ctid in where clause in update from statement

[Dirk Mika]

Hi

Thinking about it, it _has_ to call any BEFORE trigger function
as that might change values of the row to be inserted that
could possibly change the outcome of the test.

Yeah, that was my thought, too. Unfortunately, the affected trigger changes two 
columns, so I can't change it to an AFTER ROW trigger.

Dirk







--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ

Re: Use ctid in where clause in update from statement

[Tom Lane]

Dirk Mika  writes:
> I know I can join using the pk, but in oracle using the rowid is faster so I 
> wanted to know, if this is possible in PostgreSQL as well.

Existing Postgres releases are not very bright about joins on CTID ---
basically merge join is the only plan type you can get for that.
v12 will improve that somewhat.

regards, tom lane

Statistics tables not being updated anymore

[Ron]

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes, 
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. 
Specifically, all counter fields are 0, and date fields are blank.


The first thing I checked was postgresql.conf (but it hasn't been modified 
since December 2018), and track_activities is turned on. Also, I connect as 
user "postgres", so it's not a privileges problem.


Where else should I look?

track_activities    | on
track_activity_query_size   | 1024
track_commit_timestamp  | off
track_counts    | on
track_functions | none
track_io_timing | off

Thanks

--
Angular momentum makes the world go 'round.

Re: Statistics tables not being updated anymore

[Adrian Klaver]

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes, 
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. 
Specifically, all counter fields are 0, and date fields are blank.


The first thing I checked was postgresql.conf (but it hasn't been 
modified since December 2018), and track_activities is turned on. Also, 
I connect as user "postgres", so it's not a privileges problem.


Where else should I look?


Is it an autovacuum problem?

What happens if you do a manual ANALYZE?

What shows up in the pg_stats view?



track_activities    | on
track_activity_query_size   | 1024
track_commit_timestamp  | off
track_counts    | on
track_functions | none
track_io_timing | off

Thanks

--
Angular momentum makes the world go 'round.



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

Re: Statistics tables not being updated anymore

[Ron]

On 7/1/19 1:07 PM, Adrian Klaver wrote:

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes, 
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. 
Specifically, all counter fields are 0, and date fields are blank.


The first thing I checked was postgresql.conf (but it hasn't been 
modified since December 2018), and track_activities is turned on. Also, I 
connect as user "postgres", so it's not a privileges problem.


Where else should I look?


Is it an autovacuum problem?

What happens if you do a manual ANALYZE?


We run manual ANALYZE jobs every day, and still the columns are blank and zero,



What shows up in the pg_stats view?


Only the pg_catalog tables have values in, for example, elem_count_histogram.





track_activities    | on
track_activity_query_size   | 1024
track_commit_timestamp  | off
track_counts    | on
track_functions | none
track_io_timing | off

Thanks

--
Angular momentum makes the world go 'round.





--
Angular momentum makes the world go 'round.

Re: Statistics tables not being updated anymore

[Tom Lane]

Ron  writes:
> Statistics views like pg_stat_*_tables, pg_stat_*_indexes, 
> pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. 
> Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

It seems like something must be wedged either in the stats collector
process or in backends' communication with that process.  Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.

regards, tom lane

Re: Statistics tables not being updated anymore

[Jerry Sievers]

Ron  writes:

> Hi.
>
> v9.6.9
>
> Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
> pg_statio_*_tables and pg_statio_*_indexes aren't being updated
> anymore. Specifically, all counter fields are 0, and date fields are
> blank.

Perhaps your stats collector is dead, blocked or dropping packets.

Check your server logs for anything related.


>
> The first thing I checked was postgresql.conf (but it hasn't been
> modified since December 2018), and track_activities is turned on.
> Also, I connect as user "postgres", so it's not a privileges problem.
>
> Where else should I look?
>
> track_activities    | on   
> track_activity_query_size   | 1024 
> track_commit_timestamp  | off  
> track_counts    | on   
> track_functions | none 
> track_io_timing | off  
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net

Re: Statistics tables not being updated anymore

[Adrian Klaver]

On 7/1/19 11:24 AM, Ron wrote:

On 7/1/19 1:07 PM, Adrian Klaver wrote:

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes, 
pg_statio_*_tables and pg_statio_*_indexes aren't being updated 
anymore. Specifically, all counter fields are 0, and date fields are 
blank.


The first thing I checked was postgresql.conf (but it hasn't been 
modified since December 2018), and track_activities is turned on. 
Also, I connect as user "postgres", so it's not a privileges problem.


Where else should I look?


Is it an autovacuum problem?

What happens if you do a manual ANALYZE?


We run manual ANALYZE jobs every day, and still the columns are blank 
and zero,




What shows up in the pg_stats view?


Only the pg_catalog tables have values in, for example, 
elem_count_histogram.


Suggestions:

1) For starters I would go back to the source pg_statistic and see if 
the values actually change.


2) Make sure that some code is not issuing a SET that is overriding the 
postgresql.conf settings. Or that someone has not changed an include file.


3) Look at pg_stat_activity to see if there is actually any activity 
recorded.








track_activities    | on
track_activity_query_size   | 1024
track_commit_timestamp  | off
track_counts    | on
track_functions | none
track_io_timing | off

Thanks

--
Angular momentum makes the world go 'round.








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

Argument casting hierarchy?

[Wells Oliver]

Hi guys, hoping you can help clarify what the 'hierarchy' of casts might be
in function arguments.

Meaning I have the following two functions

stats.foo1 (a bigint, b bigint) returns a/b::numeric

stats.foo1 (a real, b real) returns a/b::numeric

It's essentially the same function, but I thought I had to create two to
accept the different types.

However, when I call stats.foo1(3::smallint, 4::smallint) I receive the old:

ERROR:  function stats.foo(smallint, smallint) is not unique

Along w/ the message to explicitly cast.

I am essentially trying to write one function that will cast "down", I
guess, anything smaller than what's passed. I am guessing I only need the
real/real argument signature, but I wanted some clarity from the group.

I appreciate it.

-- 
Wells Oliver
wells.oli...@gmail.com 

Re: Statistics tables not being updated anymore

[Ron]

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron  writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?


No.


It seems like something must be wedged either in the stats collector
process or in backends' communication with that process.  Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.


I'll try and get that approved.

--
Angular momentum makes the world go 'round.

Re: Statistics tables not being updated anymore

[Ron]

On 7/1/19 1:48 PM, Adrian Klaver wrote:

On 7/1/19 11:24 AM, Ron wrote:

On 7/1/19 1:07 PM, Adrian Klaver wrote:

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes, 
pg_statio_*_tables and pg_statio_*_indexes aren't being updated 
anymore. Specifically, all counter fields are 0, and date fields are 
blank.


The first thing I checked was postgresql.conf (but it hasn't been 
modified since December 2018), and track_activities is turned on. Also, 
I connect as user "postgres", so it's not a privileges problem.


Where else should I look?


Is it an autovacuum problem?

What happens if you do a manual ANALYZE?


We run manual ANALYZE jobs every day, and still the columns are blank and 
zero,




What shows up in the pg_stats view?


Only the pg_catalog tables have values in, for example, 
elem_count_histogram.


Suggestions:

1) For starters I would go back to the source pg_statistic and see if the 
values actually change.


I don't see any values in it, either.



2) Make sure that some code is not issuing a SET that is overriding the 
postgresql.conf settings. Or that someone has not changed an include file.


None.



3) Look at pg_stat_activity to see if there is actually any activity 
recorded.


We do see that being updated.

--
Angular momentum makes the world go 'round.

Re: Statistics tables not being updated anymore

[Adrian Klaver]

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron  writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?


No.


Have you gone through the logs looking for errors/warnings about the 
stats collector?





It seems like something must be wedged either in the stats collector
process or in backends' communication with that process.  Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.


I'll try and get that approved.




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

Re: Argument casting hierarchy?

[Adrian Klaver]

On 7/1/19 12:18 PM, Wells Oliver wrote:
Hi guys, hoping you can help clarify what the 'hierarchy' of casts might 
be in function arguments.


Meaning I have the following two functions

stats.foo1 (a bigint, b bigint) returns a/b::numeric

stats.foo1 (a real, b real) returns a/b::numeric

It's essentially the same function, but I thought I had to create two to 
accept the different types.


However, when I call stats.foo1(3::smallint, 4::smallint) I receive the old:

ERROR:  function stats.foo(smallint, smallint) is not unique

Along w/ the message to explicitly cast.

I am essentially trying to write one function that will cast "down", I 
guess, anything smaller than what's passed. I am guessing I only need 
the real/real argument signature, but I wanted some clarity from the group.


From the system catalogs, where float4 = real:

select typname AS cast_target  from pg_cast join pg_type on 
pg_cast.casttarget = pg_type.oid  where castsource = 'float4'::regtype;

 cast_target
-
 int8
 int2
 int4
 float8
 numeric

select typname AS cast_target  from pg_cast join pg_type on 
pg_cast.casttarget = pg_type.oid  where castsource = 'numeric'::regtype;

 cast_target
-
 int8
 int2
 int4
 float4
 float8
 money
 numeric
(7 rows)





I appreciate it.

--
Wells Oliver
wells.oli...@gmail.com 



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

Re: Argument casting hierarchy?

[Wells Oliver]

What is it you're showing me exactly? The valid numeric types accepted (and
cast) by both real and numerics?

On Mon, Jul 1, 2019 at 1:15 PM Adrian Klaver 
wrote:

> On 7/1/19 12:18 PM, Wells Oliver wrote:
> > Hi guys, hoping you can help clarify what the 'hierarchy' of casts might
> > be in function arguments.
> >
> > Meaning I have the following two functions
> >
> > stats.foo1 (a bigint, b bigint) returns a/b::numeric
> >
> > stats.foo1 (a real, b real) returns a/b::numeric
> >
> > It's essentially the same function, but I thought I had to create two to
> > accept the different types.
> >
> > However, when I call stats.foo1(3::smallint, 4::smallint) I receive the
> old:
> >
> > ERROR:  function stats.foo(smallint, smallint) is not unique
> >
> > Along w/ the message to explicitly cast.
> >
> > I am essentially trying to write one function that will cast "down", I
> > guess, anything smaller than what's passed. I am guessing I only need
> > the real/real argument signature, but I wanted some clarity from the
> group.
>
>  From the system catalogs, where float4 = real:
>
> select typname AS cast_target  from pg_cast join pg_type on
> pg_cast.casttarget = pg_type.oid  where castsource = 'float4'::regtype;
>   cast_target
> -
>   int8
>   int2
>   int4
>   float8
>   numeric
>
> select typname AS cast_target  from pg_cast join pg_type on
> pg_cast.casttarget = pg_type.oid  where castsource = 'numeric'::regtype;
>   cast_target
> -
>   int8
>   int2
>   int4
>   float4
>   float8
>   money
>   numeric
> (7 rows)
>
>
>
> >
> > I appreciate it.
> >
> > --
> > Wells Oliver
> > wells.oli...@gmail.com 
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 
Wells Oliver
wells.oli...@gmail.com 

Re: Argument casting hierarchy?

[Adrian Klaver]

On 7/1/19 1:18 PM, Wells Oliver wrote:
What is it you're showing me exactly? The valid numeric types accepted 
(and cast) by both real and numerics?


That there is builtin casting for a source of either float4 or numeric 
to the types listed respectively and that numeric offers more choices. 
Might help you decide on which type you want to use for a single function.




On Mon, Jul 1, 2019 at 1:15 PM Adrian Klaver > wrote:


On 7/1/19 12:18 PM, Wells Oliver wrote:
 > Hi guys, hoping you can help clarify what the 'hierarchy' of
casts might
 > be in function arguments.
 >
 > Meaning I have the following two functions
 >
 > stats.foo1 (a bigint, b bigint) returns a/b::numeric
 >
 > stats.foo1 (a real, b real) returns a/b::numeric
 >
 > It's essentially the same function, but I thought I had to create
two to
 > accept the different types.
 >
 > However, when I call stats.foo1(3::smallint, 4::smallint) I
receive the old:
 >
 > ERROR:  function stats.foo(smallint, smallint) is not unique
 >
 > Along w/ the message to explicitly cast.
 >
 > I am essentially trying to write one function that will cast
"down", I
 > guess, anything smaller than what's passed. I am guessing I only
need
 > the real/real argument signature, but I wanted some clarity from
the group.

  From the system catalogs, where float4 = real:

select typname AS cast_target  from pg_cast join pg_type on
pg_cast.casttarget = pg_type.oid  where castsource = 'float4'::regtype;
   cast_target
-
   int8
   int2
   int4
   float8
   numeric

select typname AS cast_target  from pg_cast join pg_type on
pg_cast.casttarget = pg_type.oid  where castsource = 'numeric'::regtype;
   cast_target
-
   int8
   int2
   int4
   float4
   float8
   money
   numeric
(7 rows)



 >
 > I appreciate it.
 >
 > --
 > Wells Oliver
 > wells.oli...@gmail.com 
>


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Wells Oliver
wells.oli...@gmail.com 



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

Re: Statistics tables not being updated anymore

[Ron]

On 7/1/19 2:43 PM, Adrian Klaver wrote:

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron  writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?


No.


Have you gone through the logs looking for errors/warnings about the stats 
collector?


Yes, but there's nothing.

--
Angular momentum makes the world go 'round.

Re: Statistics tables not being updated anymore

[Adrian Klaver]

On 7/1/19 1:38 PM, Ron wrote:

On 7/1/19 2:43 PM, Adrian Klaver wrote:

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron  writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated 
anymore.

Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?


No.


Have you gone through the logs looking for errors/warnings about the 
stats collector?


Yes, but there's nothing.



And ps ax | grep postgres shows?:

postgres: stats collector process

If so then I guess you are down to the suggestions upstream that 
something is stuck in the stats collectors craw.



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

Re: Statistics tables not being updated anymore

[Ron]

On 7/1/19 5:20 PM, Adrian Klaver wrote:

On 7/1/19 1:38 PM, Ron wrote:

On 7/1/19 2:43 PM, Adrian Klaver wrote:

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron  writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?


No.


Have you gone through the logs looking for errors/warnings about the 
stats collector?


Yes, but there's nothing.



And ps ax | grep postgres shows?:

postgres: stats collector process


Yup, it exists.



If so then I guess you are down to the suggestions upstream that something 
is stuck in the stats collectors craw.




We're going to try and restart it tonight.


--
Angular momentum makes the world go 'round.

Re: Statistics tables not being updated anymore

[Tom Lane]

Ron  writes:
> On 7/1/19 2:43 PM, Adrian Klaver wrote:
>> Have you gone through the logs looking for errors/warnings about the stats 
>> collector?

> Yes, but there's nothing.

One theory as to what broke is that somebody messed with your system's
packet filtering (firewall) rules, and now the kernel is discarding
statistics packets that backends are trying to send to the stats
collector.  If that's the case, then when you restart the postmaster
there will be some bleats in the log about it, because the stats
collector checks for this problem at startup (but never again :-().

This theory doesn't completely explain your problem, because it
only explains why no new stats data is appearing, not why you
can't still see the stats state as it was before data transmission
stopped.

The latter part might be explained if you'd done pg_stat_reset()
in hopes of clearing the problem --- except that I think the
transmission of the reset command is done over the same IP socket
that stats data goes through, so how'd it get through if that's
being blocked?

Anyway, bottom line is to pay close attention to the postmaster
log when you restart.

regards, tom lane

Re: Argument casting hierarchy?

[Andrew Gierth]

> "Wells" == Wells Oliver  writes:

 Wells> Hi guys, hoping you can help clarify what the 'hierarchy' of
 Wells> casts might be in function arguments.

In terms of which casts will be selected in some given context, there
isn't exactly a "hierarchy", but some types are designated as
"preferred" types in their category. Of the builtin types, the preferred
ones are boolean, text, oid, double precision, inet, timestamptz,
interval and varbit. (See pg_type.typispreferred and
pg_type.typcategory.)

If there are several ways to implicitly cast function arguments to match
signatures of known functions, then the result is ambiguous (and hence
an error) _unless_ there is one (and only one) combination of casts to
preferred types in matching categories. So in your example, if you had a
variant of the function (a float8, b float8) then smallint inputs would
call that variant, in preference to the bigint or real ones, because
float8 (aka double precision) is a preferred type in category N (number
types).

You have to be a bit careful with this because it sometimes leads to
unexpected casts or loss of precision. If your function will be casting
the input values to "numeric", for example, then it is better to have
just one function with numeric type args, rather than risk getting casts
like numeric -> float8 -> numeric. Another commonly seen example is the
mis-casting of "date" type to timestamptz (as the preferred type) where
timestamp without tz was intended or semantically required.

-- 
Andrew (irc:RhodiumToad)

restore error

[Prakash Ramakrishnan]

Hi Team,

While restoring the database using psql getting below error any idea about
this?

psql:AICH01PR.sql:641367264: ERROR:  connection for foreign table
"mf_adm_spotservicefiles" cannot be established
DETAIL:  ORA-12170: TNS:Connect timeout occurred


-- 
Thanks,
Prakash.R