Database Technologies

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...

Till PostgreSQL 9.0, PostgreSQL doesn't have any feature which can be use to read the external file (a concept of External Table). External Table is a feature of Database using which, any one can read the flat files as if it were in a table in the database. So, I thought give an idea how user can implement this in Database using plperl. Let's see how you can implement it. I have a flat file, which has following data: Filename: test.data Location: /tmp/test.data 1,Check1 2,Check2 3,check3 4,check4 5a,check5 5a,check5 Since, I know this flat file is having two field (numeric and text), so,I can write a plperl function, which can read the file and can get the attribute(column values) of a line on the basis of passed delimiter, with this if there is any bad record, that will go to the bad file for verification if required. Following is a sample plperl function : CREATE OR REPLACE FUNCTION read_external(text,text) returns table(id numeric, col1 text) as $$ ## W...

Following are some of the important Process of PostgreSQL/Postgres Plus Advanced Server. vibhore@ubuntu:~/edb/edb-postgres$ ps -eaf|grep postgre 1002 1080 1 0 06:07 ? 00:00:00 /opt/PostgresPlus/8.4AS/bin/edb-postgres -D /opt/PostgresPlus/8.4AS/data (Mandatory) 1002 1101 1080 0 06:07 ? 00:00:00 postgres: logger process (Mandatory) 1002 1103 1080 0 06:07 ? 00:00:01 postgres: writer process (Mandatory) 1002 1104 1080 0 06:07 ? 00:00:01 postgres: wal writer process (Mandatory) 1002 1105 1080 0 06:07 ? 00:00:00 postgres: autovacuum launcher process (Optional if autovacuum is on) 1002 1106 1080 0 06:07 ? 00:00:00 postgres: stats collector process (Mandatory) Above is an snapshot of the processes which exist in PostgreSQL/Postgres ...

Generally DBA, has to login in system to monitor the activities by querying the pg_stat_activity veiw. However if someone wants to do continous monitor for every 2 seconds. He has to run SQL against pg_stat_activity every 2 seconds. So, For such activity, I have made following shell script which can be use with watch command on linux to monitor the activities. #!/bin/bash ## Prepared by Vibhor Kumar (EDB). Q="select procpid,now() - query_start as long, waiting, current_query from pg_stat_activity where procpid pg_backend_pid() limit 10;" while getopts "b:U:d:p:" opt; do case $opt in b) BIN=$OPTARG;; U) PGUSER=$OPTARG;; d) DB=$OPTARG;; p) port1=$OPTARG;; *) echo "Usage: $0 -b -d -U -mp -sp ";; esac done if [ -z $BIN ];then echo "Usage: $0 -b bin directory -d database name -U user -p port" exit fi if [ -z $PGUSER ];then echo "Usage: $0 -b bin directory -d database name -U user -p port" ...

Some Body has asked over forum to show the Instance Diagram of Postgres Plus Advanced Server as given Diagram of Oracle 9i/10g:  से SomeDiagrams So, I thought to show the Postgres Plus Advanced Server Instance Diagram. This Diagram has been made by one of my Colleague Raghevendra Rao: से SomeDiagrams Detail about the above processes is available on post: PostgreSQL/Postgres Plus advanced Server Process Details

I was going through the one of user posting over EnterpriseDB Forum, on which user has asked if there is any function exists in PostgreSQL/Postgres Plus Advanced Server, which can be use for Finding the number of segments of a table/relation on server. So, I thought to write about it. Currently, PostgreSQL/Postgres Plus Advanced Server doesn't come with any such function. However, if some one wants, he can write a plperl function which can find the number of segments of a table for him, as I have done in following sample code: Perl Program to get the number of segments of a table: CREATE OR REPLACE FUNCTION get_number_of_segments(text,text) returns table(tablename text, segments int) as $$ my $sql = "select 'ls -1 '||case reltablespace when 0 then setting||'/base/'||pg_database.oid||'/'||relfilenode||'.*|grep -v vm|grep -v fsm|wc -l' else (select spclocation||'/'||pg_database.oid||'/'||relfilenode||'.*|grep -v...

While configuring Ubuntu for PostgreSQL, when I was trying to connect Ubuntu server, I got following error message: edbs-MacBook-Pro:~ vibhor$ ssh vibhor@172.16.82.130 ssh: connect to host 172.16.82.130 port 22: Connection refused To resolve this issue, I have found that installation of Ubuntu 10.10 doesn't have openssh-server installed due to which Message was coming, so I have installed openssh-server using following command as given below: root@ubuntu:~# apt-get install openssh-server Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-2.6.35-22 linux-headers-2.6.35-22-generic Use 'apt-get autoremove' to remove them. Suggested packages: rssh molly-guard openssh-blacklist openssh-blacklist-extra The following NEW packages will be installed: openssh-server 0 upgraded, 1 newly installed, 0 to remove and 7 not upgraded. Need to get...