limit과 offset은 보통 쿼리의 pagination을 개발할 때 주로 사용됩니다.

-- 처음 10개의 Row를 반환
SELECT * FROM test LIMIT 10;
-- 위 SQL과 아래의 SQL은 같은 결과
SELECT * FROM test LIMIT 10 OFFSET 0;
 
-- 11번째 부터 10개의 Row를 반환.
SELECT * FROM test LIMIT 10 OFFSET 10;


PL/pgSQL 함수는 블럭 구조로 되어있으며 아래와 같은 문법을 따르고 있습니다.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements;
 ...
END [ label ];


각 블록에는 declaration과 body라는 두 개의 섹션이 있습니다. body 섹션은 필수적인 반면, declaration 섹션은 선택 사항입니다. 블록은 END 키워드 다음에 세미콜론 (;)으로 끝납니다.

블록에는 시작과 끝 부분에 추가적인 label이 있을 수 있습니다. 이 label의 처음과 끝은 동일해야합니다. 이 블록 label은 EXIT 문에서 블록을 사용하거나 블록에서 선언 된 변수의 이름을 정규화하려는 경우에 사용됩니다.

declaration 섹션은 body 섹션에서 사용되는 모든 변수를 선언하는 곳입니다. declaration 섹션의 각 명령문은 세미콜론 (;)으로 끝납니다.

body 섹션은 블록의 logic을 두는 곳입니다. 유효한 모든 명령문을 포함합니다. ㅠㅐ요 섹션의 각 문은 세미콜론 (;)으로 끝납니다.

예제

DO 문은 블록에 속하지 않습니다. DO문은 익명 블록을 실행하는 데 사용됩니다. PostgreSQL에서는 버전 9.0부터 DO 문을 도입했습니다.

DO $$ 
<<first_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
END first_block $$;
 
 
 
NOTICE:  The current value of counter is 1

위 예시는 declaration 섹션에서 count라는 변수를 선언하고 값을 0으로 설정했습니다. body 섹션에서 count를 1로 늘리고 RAISE NOTICE 문을 사용하여 값을 출력했습니다. first_block이라는 label은 데모 목적으로만 사용됩니다. 이 예제에서는 아무것도 수행하지 않습니다.

Subblock

블록을 다른 블록의 body 안에 넣을 수 있습니다. 다른 블록 안에 중첩된이 블록을 하위 블록이라고합니다. 서브 블록을 포함하는 블록을 외부 블록이라고합니다.

큰 블록을 더 작은 논리적 서브 블록으로 나눌 수 있도록 명령문을 그룹화하기 위해 종종 서브 블록을 사용합니다. 하위 블록의 변수는 바깥 규칙에있는 변수와 마찬가지로 이름을 가질 수 있습니다.

외부 블록의 이름과 동일한 이름을 가진 하위 블록 내에서 변수를 정의하면 외부 블록의 변수가 하위 블록에 숨겨집니다. 외부 블록의 변수에 액세스하려면 블록 레이블을 사용하여 해당 이름을 한정합니다.

DO $$ 
<<outer_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
 
   DECLARE 
       counter integer := 0;
   BEGIN 
       counter := counter + 10;
       RAISE NOTICE 'The current value of counter in the subblock is %', counter;
       RAISE NOTICE 'The current value of counter in the outer block is %', outer_block.counter;
   END;
 
   RAISE NOTICE 'The current value of counter in the outer block is %', counter;
   
END outer_block $$;
 
 
NOTICE:  The current value of counter is 1
NOTICE:  The current value of counter in the subblock is 10
NOTICE:  The current value of counter in the outer block is 1
NOTICE:  The current value of counter in the outer block is 1

이 예에서는 먼저 outer_block에 counter라는 변수를 선언했습니다. 그런 다음 하위 블록에서 동일한 이름의 변수도 선언했습니다. 그런 다음 하위 블록에 들어가기 전에 counter 값은 1입니다. 하위 블록에서 counter 값을 10으로 늘린 다음이를 인쇄합니다. 변경 사항은 하위 블록의 counter 변수에만 영향을줍니다. 그런 다음 outer_block.counter 이름을 한정하기 위해 블록 label을 사용하여 외부 블록의 counter 변수를 참조했습니다. 마지막으로 바깥 쪽 블록에 값 counter 변수를 출력합니다. 값은 그대로 유지됩니다. 이 튜토리얼에서는 PL/pgSQL의 블록 구조와 DO 문을 사용하여 블록을 실행하는 방법에 대해 배웠다.


set search_path to 변경할 스키마;
 
-- 예시
abcd.temp1
public.temp2 
과 같이 abcd 스키마에는 temp1 테이블, public 스키마에는 temp2 테이블이 존재한다고 가정
 
select * from temp1;
ERROR:  relation "temp1" does not exist
 
set search_path to abcd;  -- abcd스키마로 변경
select * from temp1; -- 성공


해당 오류는 트랜잭션 수행 중간에 쿼리가 오류를 생성되고 롤백을 실행하지 않고 다른 쿼리를 실행하려고 할 때 PostgreSQL이 발생시키는 에러입니다. 이 문제를 해결하려면 잘못된 쿼리가 실행되는 코드의 위치를 파악해야합니다. postgresql 서버에서 log_statement와 log_min_error_statement 옵션을 사용하면 도움이 됩니다. 또는 에러가 발생되는 해당 쿼리 부분의 데이터들을 전부 truncate 하면 해결되기도 합니다. 

문제점

트랜잭션 블록에서 쿼리 실행
열려있는 트랜잭션 블록에 잘못된 구문 또는 잘못된 쿼리가 실행되어 오류가 발생
오류로 인해 트랜잭션 블록이 유효하지 않아져 다음 트랜잭션부터 오류가 발생


이러한 문제가 발생되면 트랜잭션을 롤백하고 전체 데이터를 초기화 시키는 것 밖에 없습니다.

예시

gpadmin=# BEGIN;
BEGIN
gpadmin=# insert into Cant_Ignore values(1);
INSERT 0 1
gpadmin=# insert into Cant_Ignore values(2);
INSERT 0 1
gpadmin=# select * from Cant_Ignore;
id
----
  1
  2
(2 rows)

gpadmin=# insert into Cant_Ignore values(ERROR);
ERROR:  column "error" does not exist
LINE 1: insert into Cant_Ignore values(ERROR);
                                       ^
gpadmin=#
gpadmin=# insert into Cant_Ignore values(3);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
gpadmin=# COMMIT;
ROLLBACK
gpadmin=#
gpadmin=# select * from Cant_Ignore;
id
----
(0 rows)


log_statement와 log_min_error_statement 세팅법


1. postgresql 서버의 postgresql.conf 접속

$ cd /etc/postgresql/9.5/main
$ vi postgresql.conf
 
...
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'    #주석해제          # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on    #주석해제       # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log'       #주석해제         # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #주석해제 # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600      #주석해제              # creation mode for log files,
 
...
log_min_error_statement = error # 주석해제 # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic (effectively off)
 
log_statement = 'mod'      #주석 해제 후 none에서 mod로 변경             # none, ddl, mod, all

위와같이 postgresql.conf 파일을 수정한 다음 postgres 서버를 재실행합니다.

각 속성에 대해서는 https://www.postgresql.org/docs/current/static/runtime-config-logging.htm에서 확인할 수 있습니다.

다음 에러나는 (스크립트 또는 코드)를 실행하면 /etc/var/lib/postgres/9.5/main/pg_log 폴더에 로그가 쌓입니다. 해당 폴더로 접근한 다음 로그파일을 열어 어디서 에러가 났는지 확인한 다음, 수정하면 됩니다.


위의 에러가 났을 때, 전체 트랜잭션 롤백이 아닌 에러난 부분을 제외한 나머지를 commit하는 방법:


https://discuss.pivotal.io/hc/en-us/articles/205111468-HowTo-Overcome-Error-current-transaction-is-aborted-commands-ignored-until-end-of-transaction-block-

postgres 데이터베이스에 postgres 유저 접속

$ psql -U postgres -d postgres
postgres 사용자의 암호:

psql에 지정한 비밀번호로 접속

$ PGPASSWORD=password psql -U postgres -d postgres
psql (9.5.4)
도움말을 보려면 "help"를 입력하십시오.
postgres=#

psql에서 query 실행

$ PGPASSWORD=password psql -U postgres -d postgres -c 'select * from test'

psql에서 .sql 파일에 저장되어 있는 쿼리 실행

$ PGPASSWORD=password psql -U postgres -d postgres -f test.sql 
 
$ PGPASSWORD=password psql -U postgres -d postgres < test.sql

원격 서버의 postgres 데이터베이스의 postgres 유저 접속 

$ psql -h 100.100.100.10 -U postgres -d postgres


postgresql의 function을 설명하기에 앞서 먼저 저장 프로시저를 설명하겠습니다. 타 데이터베이스에서 부르는 저장 프로시저는 PostgreSQL의 function (이하,. 함수)와 같은 개념이기 때문입니다.

저장 프로시저란?

저장 프로시저란 SQL 로 만든 함수입니다. 이 함수에 여러 SQL문을 작성해 사용합니다.

사용 이유

단위 하나로, 작업을 만들어 저장 할 수 있기 때문입니다. 모든 이유는 오로지 이 이유 때문입니다. 이 이유 때문에 얻게 되는 건 언어에서 함수를 사용 할 때 얻게 되는 이점과 같습니다. 예를 들어 작업이 단위로 구분되어 있어 디버깅이 쉬워지고 관리 또한 편해지고 반복작업을 없앨 수 있습니다.

여기에 SQL 만이 얻는 장점이 하나 추가 할 수 있는데, 바로 보안입니다. 특정 유저에게 UPDATE 나 DELETE 를 막고, 함수만 사용 할 수 있게 막음으로써, 기존 데이터 회손을 최대한 막을 수 있습니다.

PostgreSQL의 Function

위에서 말했던 것과 같이 타 데이터베이스에서 부르는 저장 프로시저가 postgresql에서는 function이라 칭합니다. PL/pgSQL은 기본적인 SQL만으로 프로시저를 만들지 않고 PL/pgSQL같은 절차지향식 언어를 지원합니다. (Oracle에서는 PL/SQL, SQL Server에서는 TSQL, Postgresql에서는 PL/pgSQL 로 부릅니다. 추가적으로 PL/pgSQL은 PL/SQL과 유사합니다.) 생성 문법은 Oracle과 같이 CREATE OR REPLACE 를 지원하며 변수선언도 유사하고, SQL Server와 같이 function body에 DDL(Date Define Language)을 사용해도 Compile이 Invalid 되지 않습니다.

장점

  • 응용 프로그램과 데이터베이스 서버 간의 왕복 횟수를 줄입니다. 모든 SQL 문은 PostgreSQL 데이터베이스 서버에 저장된 함수로 래핑되므로 응용 프로그램은 여러 SQL 문을 보내는 대신 결과를 얻기 위해 함수 호출을 실행하고 각 호출 사이에 결과를 기다려야합니다. 
  • 사용자 정의 함수가 PostgreSQL 데이터베이스 서버에 사전 컴파일되고 저장되기 때문에 응용 프로그램 성능을 향상시킵니다. 
  • 많은 응용 프로그램에서 재사용 할 수 있어야합니다. 함수를 개발하면 모든 응용 프로그램에서 재사용 할 수 있습니다.

단점

  • 소프트웨어 개발이 느려지므로 많은 개발자가 소유하지 않은 특수 기술이 필요합니다.
  • 버전 관리가 어렵고 디버그하기가 어렵습니다. 
  • MySQL이나 Microsoft SQL Server와 같은 다른 데이터베이스 관리 시스템으로 이식 할 수 없습니다.

+ Random Posts