Re: Cluster OID Limit

[SERHAD ERDEM]

Hi ,
its  about  xid.
u may use the following sqls  for check.


---Transaction ID Exhaustion Analysis  --

SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database where datallowconn = true
ORDER BY 2 DESC;



WITH max_age AS (
SELECT 20 as max_old_xid
, setting AS autovacuum_freeze_max_age
FROM pg_catalog.pg_settings
WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
SELECT datname
, m.max_old_xid::int
, m.autovacuum_freeze_max_age::int
, age(d.datfrozenxid) AS oldest_current_xid
FROM pg_catalog.pg_database d
JOIN max_age m ON (true)
WHERE d.datallowconn )
SELECT max(oldest_current_xid) AS oldest_current_xid
, max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS 
percent_towards_wraparound
, max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS 
percent_towards_emergency_autovac
FROM per_database_stats;





SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 100;

From: Adrian Klaver 
Sent: Thursday, June 9, 2022 3:02 PM
To: Lucas ; pgsql-general@lists.postgresql.org 

Subject: Re: Cluster OID Limit

On 6/9/22 02:10, Lucas wrote:
> Hello,
>
> In the company I work for, some clusters reached the OID limit (2^32)
> and we had to reinstall the cluster.

Was this really about OIDs or XID wraparound?:

https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

>
> I was wondering if there is any discussion on:
> * "compress" the OID space
> * "warp around" the OID space
> * segment a OID range for temporary tables with "wrap around"
>
> --
> Lucas


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

Re: SQL SERVER migration to PostgreSql

[SERHAD ERDEM]

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR)

RETURNS TABLE (

film_title VARCHAR,

film_release_year INT

)

AS $$

BEGIN

RETURN QUERY SELECT

title,

cast( release_year as integer)

FROM

film

WHERE

title ILIKE p_pattern ;

END; $$



LANGUAGE 'plpgsql';





2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT)

RETURNS TABLE (

film_title VARCHAR,

film_release_year INT

) AS $$

DECLARE

var_r record;

BEGIN

FOR var_r IN(SELECT

title,

release_year

FROM film

WHERE title ILIKE p_pattern AND

release_year = p_year)

LOOP

film_title := upper(var_r.title) ;

film_release_year := var_r.release_year;

RETURN NEXT;

END LOOP;

END; $$

LANGUAGE 'plpgsql';


From: İlyas Derse 
Sent: Thursday, November 7, 2019 1:28 PM
To: pgsql-general@lists.postgresql.org 
Subject: SQL SERVER migration to PostgreSql

I'm trying to migration to PostgreSql from SQL Server. I have Stored Procedures 
what have output parameters and returning tables.But you know what, we can not 
returning tables in stored procedures in PostgreSql and we can not use output 
parameters in functions in PostgreSql.

So i did not find to solves this problem. Anybody have an idea ?

Re: No primary key table

[SERHAD ERDEM]

Hi,
if you have not seen any benefit , of course you can remove identity column 
from a DWH table , there is a sequence and a trigger  for identity serial 
column.
ID columns are being generaly used for base tables which are under end-user 
operations.



From: Ertan Küçükoglu 
Sent: Friday, September 13, 2019 3:34 PM
To: pgsql-general@lists.postgresql.org 
Subject: No primary key table

Hello,

We are using PostgreSQL 10.0 on a Windows  VM.

There is one database in that server.
There are several tables that will be used for data warehouse purposes.

There are daily inserts and relatively heavy bulk (whole month data at once) 
reads at end of months. Reads will be from several hundred clients and will be 
over internet and no local network connection.

Daily data saving application check for duplicate entries of a single record 
using an sql before each insert. That is only select statement during in month 
days.

End of the month queries will be selecting bulk data from previous month 
records and will filter on GUID field and a varchar(25) field.

There is one primary key of a bigint identity column on each table. Primary key 
won't be used for any purpose for any queries.

We wonder if it maybe a suggested to remove primary key index and column. There 
is no unique key index on these tables otherwise.

Thanks & regards,
Ertan Küçükoğlu

Sent from my iPhone