'PostgreSQL'에 해당되는 글 4건

  1. 2008.07.07 pl/java 로 Trigger 만들기
  2. 2008.07.07 윈도우에서 PostgreSQL 사용하기 (1)
  3. 2008.02.25 contribution library - xml2 사용하기
  4. 2007.10.18 8.2.5 VACUUM
2008.07.07 21:09
PostgreSQL 에서는 Procedural Language 로 Java를 사용하여 function 이나 trigger를 구성할 수 있습니다. 이를 사용하기 위해 윈도우에서는 설치할 때, pl/java 를 선택하여 설치하면 사용할 수 있습니다.

기본적으로 eclipse에서 java code를 작성하기 때문에, 일단,
PostgreSQL설치폴더/share/pljava에서 deploy.jar, examples.jarpljava.jar 등의 jar파일을 이클립스 프로젝트 폴더로 가져와 library path 에 추가합니다. 이 파일들은 http://pgfoundry.org/projects/pljava/ 에서 따로 배포하고 있기도 합니다. 이 pl/java 프로젝트 페이지에서 위 jar파일의 src파일도 다운받을 수 있습니다. 이클립스에서 소스 어탯치해서 보면 pl/java의 사용방법을 금방 익힐 수 있습니다. 기본적인 문서는 PostgreSQL설치폴더/doc/PlJava 폴더에 들어 있습니다.

pl/java 등의 Procedural Langauage를 사용하기 위해서는 대상 데이터베이스에서 사용할 수 있도록 install 해주아야 합니다. pl/java를 install하는 방법은 두 가지가 있습니다.
1. PostgreSQL설치폴더/share/pljava에 들어있는 install.sql을 실행시키는 방법
2. deploy.jar로 배포되어 있는 Deployer 클래스를 사용하는 방법

일단 pl/java를 사용하기 위해서는 OS의 path에 jvm.dll이 들어 있는 경로가 잡혀 있어야 합니다. 다른 Procudural Langauage를 사용할 때도 비슷한 과정이 필요합니다. 이 경로를 잡을 후에는 아마 PostgreSQL을 다시 시작해주어야 합니다.

다음 pgAdminIII에 딸려 있는 Query Tool 등을 이용해서 install.sql을 실행해주면, 'sqlj' schema가 생성된 것을 확인할 수 있습니다. 사실 schema라는 단위가 좀 생소한데, Documentation 5.7 Schemas  에 관련 내용이 잘 설명되어 있습니다.

일단 트리거 함수를 자바 코드로 작성하겠습니다.
사용자 삽입 이미지

INSERT 문이 row-level에 실행되기 전에, 자동으로 실행될 modInsert라는 trigger를 작성해보겠습니다. 대상 테이블에는 creator라는 열이 있다고 가정합니다. PostgreSQL문서의 trigger부분을 읽어보시면 금방 이해가 가시겠지만, INSERT문의 경우, 새로 열이 삽입되므로, old row는 없고, new row 만 있습니다. INSERT 문에 의해 테이블에 새로 입력될 열이 new row입니다. 이 row는 TriggerData라는 클래스의 gerNew() method에 의해 접근할 수 있습니다.
위의 코드에서 td.getNew()를 통해 new row 가 저장되어 있는 ResultSet의 reference를 가져오고 있습니다. td.getArguments() method를 통해서는 대상 테이블의 column의 이름들을 가져올 수 있습니다. str에 creator라는 column에 새로 삽입될 문자열을 가져오고 'modified'을 후미에 붙이도록 수정합니다.
그리고 코드의 서두에서는 Trigger함수를 몇가지 경우에 TriggerException을 발생시켜 trigger가 원하는 경우에는 동작하도록 합니다.

원하는 trigger함수를 작성하고 난 다음에는 jar파일로 만들어야 합니다.
example.jar파일과 그 소스, 그리고 User Guide문서를 보면 자세히 알 수 있습니다.

example.jar에 들어있는 것처럼, MANIFEST.MF파일과 test.drr파일을 작성하여 test.jar파일을 만들었습니다.

다음 과정은 이 트리거 함수를 등록하는 것입니다.
test.drr파일의 내용은 다음과 같습니다.

SQLActions[] = {
    "BEGIN INSTALL
        CREATE FUNCTION modInsert()
          RETURNS trigger
          AS 'ac.uos.dml.blob.trigger'
          LANGUAGE java;
         
        CREATE TRIGGER metadata_modInsert
          BEFORE INSERT ON metadata
          FOR EACH ROW
          EXECUTE PROCEDURE modInsert();
         
    END INSTALL",
   
    "BEGIN REMOVE
        DROP TRIGGER metadata_modInsert;
        DROP FUNCTION modInsert();
    END REMOVE"
}

이 test.drr파일이 들어있는 test.jar파일을 PostgreSQL이 접근할 수 있는 곳에 놓은 다음
sqlj schema의 install_jar function을 이용해 install하게 됩니다.

SELECT sqlj.install_jar('file:///E:/Program Files/PostgreSQL/8.3/share/pljava/test.jar', 'test', true)

install_jar의 세번째 argument가 true이므로, jar파일 내부의 ddr파일에 의해서 설치됩니다.
그리고 set_classpath function을 통해서 해당 스키마가 설치된 jar파일을 사용할 수 있도록 해주어야 합니다.

SELECT sqlj.set_classpath('public', 'test')

이제 trigger를 테스트해볼 수 있습니다.


Posted by 나야
2008.07.07 14:24
리눅스를 깔아서 사용하고 있는 서버가 잘 부팅이 안되는데, 에이에스 기간까지 끝나서 코마 상태에 빠져버렸습니다. 그래서 어쩔 수 없이 windows2003 에 PostgreSQL을 깔게 되었는데 뭔가 이것저것 해야 합니다.

우선 PostgreSQL을 설치할 때, postgres라는 사용자를 만들고, Windows Service로 등록하게 됩니다.
그런데, 이 윈도우 서비스가 '시작'에 실패하게 되면, 그 이유를 알려주지 않습니다. 게다가, 데이타 폴더를 바꾸게 된다면, 레지스트리를 수정해주어야 합니다. 이 위치는  regedit에서 검색해보면 쉽게 찾을 수 있습니다. 어쨌든, 왜 서비스가 시작할 수 없는지 알기 위해서, pg_ctl.exe 를 직접 실행해 보아야 하므로, postgres 사용자로 원격접속을 해야 합니다. postgres는 보안 문제를 이유로 Administrator 아이디로 PostgreSQL 서버를 실행시키는 것을 막고 있습니다.
postgres 로 서버에 로그인하기 위해서, postgres의 소속그룹에 Remote Desktop Users를 추가시켜 주어야 합니다. 이제 postgres 로 로그인하고, 등록되어 있는 PostgreSQL 서비스의 명령문을 cmd에서 직접 실행해 보았습니다.

역시나 실행이 되지 않지만, 오류 번호가 1063 번임을 알 수 있습니다. 에 검색해보니, service 는 service manager에서 실행해야 한다는 군요.

...... 어쨌거나, pg_ctl.exe start -D data 로 실행해보니,

서버를 시작합니다
치명적오류: 알 수 없는 환경 매개변수 이름 : "pljava.classpath"

라는 메시지가..
PostgreSQL 에서 pl/java를 사용하기 위해 관련 라이브러리를 추가해서 설치했는데,
data/postgresql.conf 에 보면,
맨 아래 부분에 CUSTOMIZED OPTIONS 라는 부분이 있는데,
custom_variable_classes = 'pljava'
가 주석처리되어서 생긴 문제였습니다.

어쨌든 결론은, 윈도우에서 PostgreSQL사용하기 문제 없다.....죠.
Posted by 나야
2008.02.25 22:04
xml2 는 PostgreSQL에서 xpath관련 함수를 제공합니다.
버전 8.4에서는 없어지고 Posgres 내부에서 표준 API를 제공한다고 8.3 버전 Document에 나와있습니다.

어쨌거나...

소스 코드를 제가 사용하고 있는 배포판(Redhat AS4와 Fedora8)에 맞도록 컴파일해 사용할 능력(service로 등록한다든지...)은 없기 때문에 주로 배포판에 맞도록 컴파일된 (주로 binary 폴더에 들어있는) 버전으로 Redhat Enterprise AS4 에 PosrgreSQL 을 설치했습니다. 그런데 이 배포판에는 xml2 라이브러리가 포함되어 있지 않았습니다.
궁여지책으로 다음과 같은 방법을 사용하였습니다.

소스코드를 다운 받아서 /usr/local 에 저장합니다.
tar xvfz postgresql-8.3.0.tar.gz
해서 압축을 풀고,
압축 풀린 파일들이 있는 /usr/local/postgresql-8.3.0/에 가서
./configure
./make
./make install을 해주면
/usr/local/pgsql에 설치가 됩니다.

다음은 xml2라이브러리 설치입니다.
/usr/local/postgresql-8,3,0/contrib/xml2
에 가서
make install
하면
/usr/local/pgsql/lib 에 pgxml.so 파일이
/usr/local/pgsql/share/contrib/ 에 pgxml.sql 과 unistall_pgxml.sql파일이 생성됩니다.

여기까지 하고,  psql -d 데이타베이스이름 -f pgxml.sql 하면 /usr/local/pgsql에 설치된 Posgres의 해당 데이터베이스에서 함수를 사용할 수 있습니다.

하지만 Redhat AS4에서 컴파일된 Binary에서 사용하는 것이 목적이기 때문에
라이브러리 파일인 pgxml.so파일은 /usr/lib로
sql 스크립트 파일인 pgxml.sql과 unistall_pgxml.sql 은
/usr/share/pgsql/contrib로 복사해줍니다. 그리고
pgxml.sql의 내용을 수정해 주어야 이 함수들을 설치할 수 있습니다.

이 파일을 열어서 &libdir/pgxml을 /usr/lib/pgxml로 바꾸어주면, 라이브러리를 찾아서 함수들을 사용할 수 있도록 해줍니다.

pgAdmin III 에서 확인해 보시면 해당 함수가 설치되어 있는 것을 확인하실 수 있습니다.

앗싸.





Posted by 나야
2007.10.18 22:56

JDBC로 LargeObject를 업로드하는 코딩을 테스트하는데 500MB짜리 파일을 여러 개 올리고 지우는 테스트를 반복하다 보니, 어느새 /data의 크기가 80기가를 넘어가고 있더군요.
그래서 JDBC의 LargeObject를 지우는 API를 이용해서 파일을 지웠으나 여전히 용량을 그대로.

VACUUM 이라는 기능이 있더군요. 이 기능과 관련하여 구글을 검색하다 보니 MySQL과 PostgreSQL을 비교해놓은 MySQL vs. PostgreSQL 라는 글이 있군요. 저 글이 2002년 6월에 쓰여 졌으니, 현재는 각 데이터베이스에서 어떤 기능들이 더 구현되었는지 확인해야 겠지만, 저때만 해도 PostgreSQL에는 구현되어 있으나, MySQL에는 구현되지 않은 기능들도 많이 있군요.(그러나 MySQL은 빠릅니다)
어쨌거나 MySQL은 데이터베이스를 VACUUM을 이용해 청소할 필요가 없고, PostgreSQL은 VACUUM을 실행해 주어야 한다는 군요.

일단 PostgreSQL 8.2.5 Documentation의 VACCUM 항목을 보면

-이름
VACUUM 쓰레기 수집기 / 선택적으로 데이터베이스 분석하기

-개요
VACUUM [FULL] [FREEZE] [VERBOSE] [ table]
VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE [ table [ (column [, ...] ) ] ]

-설명
VACUUM 은 삭제된 tuple들이 차지하고 있는 공간을 반환합니다. 보통의 PostgreSQL 작업에서 삭제되거나 update에 의해 쓸모 없어진 tuple들은 테이블에서 물리적으로는 삭제되지 않고 VACUUM이 실행될 때까지 남아있습니다. 그러므로 VACUUM은 주기적으로 실행되어야 하며, 자주 업데이트 되는 테이블들의 경우는 더 그렇습니다.

매개변수 없이 VACUUM을 실행하면, 현재 데이터베이스의 모든 테이블에 대해서 실행합니다. 그리고 table을 매개변수로 지정하면 해당 테이블에 대해서만 실행됩니다.

VACUUM ANALYZE 는 먼저 VACUUM하고, 그 테이블을 ANALYZE합니다. ANALYZE는 해당 영문글 참조.

FULL 옵션 없이 VACUUM을 실행하면 단순히 점유되고 있던 공간은 반환하고, 다시 사용할 수 있도록 합니다. 이 작업은 테이블을 읽고 쓰는 일반적인 작업과 동시에 진행할 수 있으며, 독점적인 잠금은 포함되지 않습니다. VACUUM FULL는 각 테이블이 더 작은 수의 디스크 블럭을 유지하도록 하기 위해 tupte를 옮기는 등의 더 많은 작업을 하게 됩니다. 당연히 더 느리게 실행되며, 실행되는 동안 table에 독점적인 잠금을 겁니다.

-매개 변수
FULL
더 많은 공간을 확보하려면 FULL을 사용하시오. 그러나 더 오래 걸리고, 독점적인 잠금을 테이블에 걸게 됩니다.

FREEZE
freezing, age 등의 개념이 뭔지 잘 모르기 때문에 패스 패스 ㅜ.ㅜ

VERBOSE
각 테이블에 대한 상세한 vacuum 동작의 보고를 해 줍니다.

table
vacuum을 실행할 테이블을 지정하게 되고, 기본적으로 현재 데이터베이스의 모든 테이블이 기본값입니다.

column
analyze할 열을 지정합니다. 기본값은 모든 열입니다.

-출력
VERBOSE가 매개변수를 지정되면, VACUUM은 실행에 따른 메시지들을 출력합니다.

- 주의 사항
VACUUM은 트랜잭션 구간내에서 실행될 수 없습니다.

실제 상황에서는 거의 매일 밤 VACUUM을 실행하는 것을 추천합니다. 많은 수의 열을 지우고 더하고 난 뒤에는 해당 테이블에 대하여 VACUUM ANALYZE를 실행하는 것이 좋습니다. 이것은 모든 최근 변환에 대한 결과를 system catalog에 업데이트해서 PostgreSQL query planner 가 planning queries를 할 수 있도록 합니다.( PostgresQL query planner는 또 뭐죠ㅜㅜ )

FULL 매개변수는 일상적으로 사용되지는 않지만  특별한 상황에서는 유용할 수 있습니다. 그 한 예는 어떤 테이블의 거의 모든 열을 삭제하고, 그 테이블이 더 작은 디스크 공간을 점유하도록 축소시키기 위하기를 원할 때 입니다. 보통 VACUUM FULL이 점유한 공간을 더 축소시켜줍니다. FULL 옵션은 인덱스들을 축소시켜주지는 않습니다. 주기적으로 REINDEX를 실행시켜 주는 것이 좋습니다. 사실, 모든 인덱스들을 삭제하고, VACUUM FULL을 실행한 다음, 인덱스를 다시 만드는 것이 빠를 때가 있습니다.

VACUUM은 실질적인 I/O 트래픽을 증가시키고, 실행되고 있는 다는 세션들의 성능을 저하시킬 수 있습니다. 그러므로 비용 기반의 vaccum 지연 특징들을 사용하는 것이 좋습니다. Section 17.4.4 를 보라네요.

그리고 autovacuum을  사용할 수 있습니다. 더 자세한 정보는 Section 22.1을 보라네요.

네~ 그렇습니다. VACUUM을 실행해야 하는 군요.
자 이제 본론으로 들어가서
pgAdmin III을 실행시키고 데이터베이스에 접속하면, 데이터베이스 항목에 만들어져 있는 데이터베이스들이 보이고, 각 데이터베이스 밑에 Catalog와 Schemas항목이 있습니다.
PostgreSQL에 LargeObject를 업로드 하게 되면, pg_largeobject라는 테이블에 bytea 데이터로 들어가게 됩니다. LargeObject를 업로드 하시고, 이 테이블을 확인하시면 업로드 할 때 받은 oid로 데이터들이 들어가 있는 것을 확인할 수 있습니다.
VACUUM을 이 테이블에 대하여 실행해야 물리적으로 LargeObject를 지울 수 있습니다.
Posted by 나야