Get Number of Segments in PostgreSQL 9.0 (PL/Perl)

In my Blog on get_number_of_segments, I had used the simple query to find the number of segments.
Since, from PostgreSQL 9.0, system admin function pg_relation_filepath can be use to find the location of relfilenode, therefore I thought to reduce the code of get_number_function and use the pg_relation_filepath function.
Following is a modified plperl function get_number_of_segments(text) using system admin function pg_relation_filepath(relation reglcass):
CREATE OR REPLACE FUNCTION get_number_of_segments(text) returns  table(tablename text, segments int)     
as     
$$    
 my $sql = spi_prepare("select 'ls -1 '||pg_relation_filepath(\$1)||'.*' as cmd",'TEXT');
 my $q = spi_query_prepared($sql,$_[0]);     
 my $rv = spi_fetchrow($q);   
  my $cmd = $rv->{rows}[0];    
  my $command = $cmd -> {location};    
  open(CMD, "$command |");    
    $count = ;    
  close(CMD);    
    @count=split(/[\n\r]+/,$count);    
    
  return_next {'tablename' => $_[0] , 'segments' => $count[0]+1};  
  spi_freeplan($sql);  
  return undef;    
$$ language plperlu;

Some Examples:
psql -c " select * from get_number_of_segments('public.test');"
  tablename  | segments 
-------------+----------
 public.test |        1
(1 row)

psql -c " select * from get_number_of_segments('public.testa');"
ERROR:  relation "public.testa" does not exist at line 4.
CONTEXT:  PL/Perl function "get_number_of_segments"

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Database Link to Oracle Database on Linux

I have seen question, like "How to make Database Link from PostgreSQL to Oracle?", always floats in PostgreSQL Community Forum. So, I thought to do some research on it and write a Blog. Cybertec (One of The PostgreSQL Database Company) has released a PostgreSQL Module ODBC Link, using which user can make Database link to any other database, including Oracle, MS SQL Server, PostgreSQL etc, which have ODBC compliant Data source. Lets see how you can make ODBC Connection from PostgreSQL to any ODBC compliant data source and fetch data. Installation of this module is very simple. Following are the steps which user can follow: 1. Install the unixODBC driver on your linux machine. user can use following useful link for Downloading and Installing the unixODBC Driver: http://www.unixodbc.org/ 2. Download ODBC-Link from following location: http://www.cybertec.at/download/odbc_link/ODBC-Link-1.0.4.tar.gz 3. Untar the downloaded file as given below: tar -zxvf ODBC-Link-1.0.4....
Read more

New Features in Slony 2.1

Slony 2.1 has been released on 19th Oct. 2011. So, I thought to look at some important improvement done in this new release, which can make users life easier. Before discussing about the changes, lets setup slony replication. Following Codes can be use for setting up slony replication. #### Preable Scripts: cluster name=slonytest; NODE 1 ADMIN CONNINFO = 'dbname=postgres host=localhost user=postgres port=5432 password=postgres'; NODE 2 ADMIN CONNINFO = 'dbname=repdb host=localhost user=postgres port=5432 password=postgres'; Adding Node script: ### create_nodes.slonik include ; init cluster (id=1, comment='slonytest node 1'); store node (id=2, comment='slonytest subscriber node 2', event node=1); Storing Path: ### store_paths.slonik include ; STORE PATH (SERVER=1, CLIENT=2, CONNINFO='dbname=postgres host=localhost user=postgres port=5432 password=Empid#042'); STORE PATH (SERVER=2, CLIENT=1, CONNINFO='dbname=repdb host=localhost us...
Read more

Creating user probe and alert in PEM 2.1

This is also one type of thing in which people are very interested. How to create probes other than PEM inbuilt probe? And how to create alert based on probe? Well answer is simple. Understand what probe is in PEM and then understand PEM Data detail. Probe in PEM requires following: 1. SQL Code which can be use to gathering data by pemagent. 2. Table in pemdata schema, which will be use for storing Current status/data of SQL. 3. History table (specially in pemhistory schema) where all history data will reside. Suppose user wants to monitor the Slony Replication using PEM (since, PEM doesn't have slony replication moniotring), so user can do following: 1. SQL Code which can be use for slony replication monitoring. We know that slony replication monitoring can be done using view sl_status of slony. So, user can create a SQL Code as given below for replication. select '_test_slony' as schema_name, st_origin,st_received, st_lag_num_events , st_lag_time from _test_slony...
Read more