DevAdmin

Hi As a part of ORACLE to PostgreSQL Migration, I come across to implement a trigger on Oracle, which sync it's data to PostgreSQL. I have tried with a simple table as below, which is hopefully helpful to others. Find this link to configure the heterogeneous dblink to postgres. I believe, the below approach works effectively with the Primary Key tables of Oracle Database. If we don't have primary key in a table, then the UPDATE,DELETE statements going to fire multiple times in Postgres, which leads performance issues. ORACLE CREATE TABLE test(t INT PRIMARY KEY); CREATE OR REPLACE TRIGGER testref AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; C number; N number; BEGIN c:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@pglink; IF INSERTING THEN DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'INSERT INTO test VALUES('||:NEW.t||');'); n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c); ELSIF DELETING THEN DBMS_HS_PASSTHROU...

I am sure that, most of you aware of this. But, for me it's the first time, I accomplished it. As one of my assigned tasks to build a 32-bit instance of postgresql on 64-bit machine, I have followed the below approach. I hope, it will be helpful to others as well, if you got any problems. As an initial step on this task, I have tried to build a sample "c" program using "gcc -m32". Once, I resolved this, I moved to compile the PostgreSQL 9.0. [root@localhost Desktop]# gcc -m32 -o test test.c In file included from /usr/include/features.h:385,                  from /usr/include/stdio.h:28,                  from test.c:1: /usr/include/gnu/stubs.h:7:27: error: gnu/stubs-32.h: No such file or directory To resolve the above issue, I have installed the 32-bit glibc-devel package through yum. yum -y install glibc-devel.i686 glibc-devel Again, I have tried to run the same command. [root@localhost...

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL. We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo. Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..? As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.} Please follow the below steps to set up the 1 - 1 multi master replication. 1. Follow the below steps to get all the pre-requisites for the Bucardo. yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here . tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For t...

Recently i faced a problem with some catalog views, which do not give you the complete information as a normal user. For example, take pg_stat_activity, pg_stat_replication, pg_settings, e.t.c. If we run the above catalog views as non super user, you don't get the result what we get as a superuser. This is really a good security between super user and normal user. What we need to do, if we want to collect these metrics as normal user. I think the possible solution is "Write a wrapper function with security definer as like below" and grant/revoke the required privileges to the user/public. CREATE OR REPLACE FUNCTION pg_stat_activity( RETURNS SETOF pg_catalog.pg_stat_activity AS $$ BEGIN RETURN QUERY(SELECT * FROM pg_catalog.pg_stat_activity); END $$ LANGUAGE PLPGSQL SECURITY DEFINER; REVOKE ALL ON FUNCTION pg_stat_activity() FROM public; CREATE VIEW pg_stat_activity AS SELECT * FROM pg_stat_activity(); REVOKE ALL ON pg_stat_activity FROM public; ...

I hope this gives you a bit FUN with pg SQL. select * from (select array_to_string(array_agg(CASE WHEN (power((xx.x-25),2)/130+power((yy.y-25),2)/130)=1 THEN '$' WHEN (sqrt(power(xx.x-20,2)+power(yy.y-20,2)))<2 THEN '#' WHEN (sqrt(power(xx.x-20,2)+power(yy.y-30,2)))<2 THEN '#' WHEN (sqrt(power(xx.x-29,2)+power(yy.y-25,2)))<4 THEN '#' WHEN (power((xx.x-10),2)/40+power((yy.y-10),2)/40)=1 THEN '$' WHEN (power((xx.x-10),2)/40+power((yy.y-40),2)/40=1) THEN '$' ELSE ' ' END),' ') as cartoon from (select generate_series(1,40) as x) as xx,(select generate_series(1,50) as y) as yy group by xx.x order by xx.x) as co_ord; Oracle Mode CREATE OR REPLACE TYPE series AS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(n INT, m INT) RETURN series PIPELINED IS BEGIN FOR i IN n..m LOOP PIPE ROW (i); END LOOP; RETURN; END; SELECT REPLACE(WM_CONCAT( CASE WHEN (power((xx.COLUMN_VALUE-25),2)/...

Hi, Today, i have faced an interesting problem like below. I want to get MIN(UNNEST(ARRAY[0, 1, 2, ....]))  as non-zero small element. In this case, it's 1. Below is my problem description. postgres=# SELECT SUM(val), MAX(val), MIN(val) FROM ( SELECT UNNEST(ARRAY[1, 2, 3]) val UNION ALL --Appending some dummy rows, for getting what i would like to expect. SELECT UNNEST(ARRAY[0, 0, 0]) val ) AS FOO;  sum | max | min -----+-----+-----    6 |  3  | 0 (1 row) As you see, i can able to identify the sum, max without any problem. But when it comes to "min", i am getting the value as 0. But, I want the minimum as 1 as per my requirement. I can able to get the min, max, sum from the first array it self. But, my implementation doesn't allow this. :( I have tried it in so many ways, and finally found the following solution. I believe, there will be some better ways also, but just wanted to keep a note on this. postgres=# SELECT SUM(val), MAX(...

Hi Isn't the nice way to represent the Oracle Architecture. Soon i will be posting the PostgreSQL architecture as well. Dinesh Kumar