Skip to main content

Posts

Showing posts from June, 2012

unnest in postgresql

Hi ,

We do have unnest operation in PostgreSQL for fetching the records from any arrary.

Step 1 ====== Create a sample UDT as below. create type t_type as (a varchar);
Step 2 ====== Create a sample procedure as below. create or replace function fn_test()
return t_type[]
is
v t_type[];
begin
raise notice '%',array(select ename from emp);
select array(select row(ename) from emp)::varchar[] into v;
return v;
end;
Step 3 ======
edb=# select unnest(fn_Test());
NOTICE:  {SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER}
  unnest  
----------
 (SMITH)
 (ALLEN)
 (WARD)

--Dinesh

JDBC PostgreSQL Example

Hi ,

Here are the steps, how to configure the JDBC with PostgreSQL/PostgresPlus.

JDBC PostgreSQL
===============
Step 1
======
Download the postgreSQL jdbc diver.
Step 2
======
cp the file location to /opt/PostgreSQL/9.1/lib
Step 3
======
Create the following JavaTest.java @/opt/PostgreSQL/9.1
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JavaTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException{
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres", "postgres", "postgres");
PreparedStatement ps = con.prepareStatement("SELECT RELNAME,RELFILENODE FROM PG_CATALOG.PG_CLASS");
//String oldestDate = "20120101";
//ps.setString(1, oldestDate);
System.out.println("Statement output " + ps.toString());
ResultSet rs = ps.ex…

Fatal Python error: Py_Initialize: Unable to get the locale encoding

Hi ,

If you are getting the above ERROR message while configuring PL/PYTHON module, then please use the below steps to configure in PostgreSQL. We need to use PYTHON 3.2 Version due to some bugs in earlier version.

Step 1
=====
Download Link: http://www.activestate.com/activepython/downloads/thank-you?dl=http://downloads.activestate.com/ActivePython/releases/3.2.2.3/ActivePython-3.2.2.3-linux-x86.tar.gz"

Step 2
=====

-bash-4.1$ export PYTHONPATH=/opt/ActivePython-3.2/bin:$PATH
-bash-4.1$ export PYTHONHOME=/opt/ActivePython-3.2/
-bash-4.1$ export LD_LIBRARY_PATH=/opt/ActivePython-3.2/lib/

Step 3
=====

-bash-4.1$ ./pg_ctl -D ../data/ stop -mf
waiting for server to shut down.... done
server stopped

-bash-4.1$ ./pg_ctl -D ../data/ start
server starting

Step 4
=====

-bash-4.1$ ./psql -p 5434 postgres

Password:
psql.bin (9.1.4)
Type "help" for help.
postgres=# CREATE LANGUAGE plpython3u;
CREATE LANGUAGE
postgres=#

Step 5
=====

postgres=#     CREATE OR REPLACE FUNCTION pymax …

/*NO LOAD BALANCE*/

Hi ,
This is the pgpool Prefix option for skipping any statement to go to slave side. Let's consider an example. If we have "Pgpool" Replication and "No Load Balance" Mode. 
In this case, we all know that all the SELECT operations goes to Primary Node Only. Ok, then what's case if we use "DECLARE CURSOR -> OPEN -> FETCH -> CLOSE" ? This is also a behvaiour of SELECT. But pgpool will replicate these kind of cases to Slave Node. To avoid this case, we need to use the above /*NO LOAD BALANCE*/ as a pgpool prefix. 
Ex:- postgres=#/*NO LOAD BALANCE*/ DECLARE --> OPEN --> FETCH-->CLOSE;
It won't cause to replicate (DECLARE/OPEN/FETCH/CLOSE) to slave. 
What's the case if i use /*NO LOAD BALANCE*/ CREATE TABLE TEST(T INT)?; Well, Your guess is correct. Pgpool won't replicate this statement as well. Because, When Pgpool sees that prefix, it just ignore the following SQL statement to Slave.  So, be careful about this parameter…

FATAL slon_node_health_check() returned false - fatal health problem!

Hi ,


If you are getting the above error in Slony(It's new feature in Slony 2.1 which checks the slony catalogs), then try to execute the below query.

select tab_id, tab_relname, tab_nspname,tab_set from  "_Slonyschema".sl_table t where not exists (select 1 from pg_catalog.pg_class c, pg_catalog.pg_namespace n where c.oid = t.tab_reloid and c.relname = t.tab_relname and c.relnamespace = n.oid and n.nspname = t.tab_nspname)
If gives any entries then use the following function to fix the Slony catalogs.
Select _Slonyschema.updatereloid(<SETID>,<NODEID>);
(OR)


Use REPAIR CONFIG SLONIK

REPAIR CONFIG ( SET ID = 1, EVENT NODE = 2 );--Dinesh

PL/Java in Postgres

Hi ,
Here is my first PL/Java which i have been compiled the documented PL/Java Example.
Step 1 ======
bash-4.1$ vi HelloWorld.java -bash-4.1$ pwd /opt/PostgresPlus/9.1AS -bash-4.1$ more HelloWorld.java       package com.mycompany.helloworld;
     public class HelloWorld      { public static String helloWorld()          {                 return "Hello World";          }      }
Step 2 ====== -bash-4.1$ javac HelloWorld.java  -bash-4.1$ mkdir -p com/mycompany/helloworld/ -bash-4.1$ cp HelloWorld.class com/mycompany/helloworld/ -bash-4.1$ jar cf helloworld.jar com/mycompany/helloworld/HelloWorld.class

Step 3 ===== edb=# SELECT sqlj.install_jar('file:///opt/PostgresPlus/9.1AS/helloworld.jar','helloworld', true);   edb=# SELECT sqlj.set_classpath('public', 'helloworld'); edb=# SELECT * FROM helloworld();  helloworld
-------------
 Hello World


--Dinesh
Will post more java stuff soon ...