cubrid
cubrid copied to clipboard
CUBRID
[CBRD-26427] incorrect result when large record-size in plcsql
http://jira.cubrid.org/browse/CBRD-26427
Purpose
- PL/CSQL로 수행되는 결과 레코드의 크기가 하나의 페이지를 초과하면 그 결과 값이 보장되지 않는 문제 해결
- PL/CSQL 처리시 NULL값인 컬럼이 포함 된 경우 core dump 발생 현상 해결
Implementation
N/A
Remarks
csql>
csql> CREATE OR REPLACE PROCEDURE testx(len integer)
csql> AS
csql> BEGIN
csql> FOR r IN (SELECT rownum || repeat('a', len) as x from table({1,2,3,4,5,6,7,8,9,10,11, 12}) ) LOOP
csql> DBMS_OUTPUT.put_line('x: ' || substring( r.x, 1, 30) );
csql> END LOOP;
csql> END;
Execute OK. (0.025001 sec) Committed. (0.001000 sec)
1 command(s) successfully processed.
csql>
csql> call testx(16284);
=== <Result of CALL Command in Line 2> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 1aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 2aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 3aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 4aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 5aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 6aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 7aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 8aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 9aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 10aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 11aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Execute OK. (0.019000 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql> call testx(16285);
=== <Result of CALL Command in Line 1> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 1aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 2aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 3aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 4aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 5aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 6aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 7aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 8aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 9aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Execute OK. (0.019001 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql> call testx(16286);
=== <Result of CALL Command in Line 1> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Execute OK. (0.019001 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql>
csql>
csql>
csql> CREATE OR REPLACE PROCEDURE testx2(len integer)
csql> AS
csql> BEGIN
csql> FOR r IN (SELECT rownum || repeat('a', 10) as x, repeat('a', len) as y from table({1,2,3,4,5,6,7,8,9,10,11, 12}) ) LOOP
csql> DBMS_OUTPUT.put_line('x: ' || r.x );
csql> END LOOP;
csql> END;
call testx2(162850);
Execute OK. (0.023000 sec) Committed. (0.002001 sec)
1 command(s) successfully processed.
csql>
csql> call testx2(16284);
=== <Result of CALL Command in Line 2> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
Execute OK. (0.009000 sec) Committed. (0.001000 sec)
1 command(s) successfully processed.
csql> call testx2(16285);
=== <Result of CALL Command in Line 1> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
Execute OK. (0.008000 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql> call testx2(162850);
=== <Result of CALL Command in Line 1> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaa
x: 12aaaaaaaaaa
x: 12aaaaaaaaaa
x: 12aaaaaaaaaa
Execute OK. (0.048001 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql>
csql>
csql>
csql> CREATE OR REPLACE PROCEDURE testx3(len integer)
csql> AS
csql> BEGIN
csql> FOR r IN (SELECT rownum || repeat('a', 10) as x, repeat('a', len) as y, repeat('a', len) as z from table({1,2,3,4,5,6,7,8,9,10,11, 12}) ) LOOP
csql> DBMS_OUTPUT.put_line('x: ' || r.x );
csql> END LOOP;
csql> END;
Execute OK. (0.024000 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql>
csql> call testx3(8118);
=== <Result of CALL Command in Line 2> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 1aaaaaaaaaa
x: 2aaaaaaaaaa
x: 3aaaaaaaaaa
x: 4aaaaaaaaaa
x: 5aaaaaaaaaa
x: 6aaaaaaaaaa
x: 7aaaaaaaaaa
x: 8aaaaaaaaaa
x: 9aaaaaaaaaa
x: 10aaaaaaaaaa
x: 11aaaaaaaaaa
x: 12aaaaaaaaaa
Execute OK. (0.007000 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql> call testx3(8119);
=== <Result of CALL Command in Line 1> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
x: �
Execute OK. (0.008001 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql>
csql>
csql>
csql> CREATE OR REPLACE PROCEDURE testx4(len integer)
csql> AS
csql> BEGIN
csql> FOR r IN (select case when rownum % 2 = 0 then rownum || repeat('a', len) else rownum || repeat('a', 30) end as x from table({1,2,3,4,5,6,7,8,9,10,11, 12}) ) LOOP
csql> DBMS_OUTPUT.put_line('x: ' || substring( r.x, 1, 30) );
csql> END LOOP;
csql> END;
Execute OK. (0.023000 sec) Committed. (0.002000 sec)
1 command(s) successfully processed.
csql>
csql> call testx4(16285);
=== <Result of CALL Command in Line 2> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 1aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 2aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 3aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 4aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 5aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 6aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 7aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 8aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 9aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 11aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Execute OK. (0.016001 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql> call testx4(16286);
=== <Result of CALL Command in Line 1> ===
Result
======================
NULL
<DBMS_OUTPUT>
====
x: 1aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 3aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 5aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 7aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 9aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 11aaaaaaaaaaaaaaaaaaaaaaaaaaaa
x: 12aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Execute OK. (0.015000 sec) Committed. (0.000000 sec)
1 command(s) successfully processed.
csql>
csql>
query_cursor::next_row ()함수에서 DB_VALUE로 읽어 들이는 과정의 코드를 간략하게 줄여보면 아래와 같습니다. 이렇게 보면 PEEK방식으로 읽었을 때 그 값은 tuple_record.tpl에 그 원천 데이터가 있는 구조입니다.
qfile_scan_list_next (m_thread, &m_scan_id, &tuple_record, PEEK);
QFILE_TUPLE_VALUE_FLAG flag = (QFILE_TUPLE_VALUE_FLAG) qfile_locate_tuple_value (tuple_record.tpl, i, &ptr, &length);
or_init (&buf, ptr, length);
pr_type->data_readval (&buf, value, domain, -1, false, NULL, 0);
tuple_record.tpl 은 qfile_retrieve_tuple()함수에서 각각의 경우에 따라서 다르게 매칭 되고 있습니다. 이 메모리 영역이 재사용되기 때문에 결과값에 문제가 발생하는 것으로 판단됩니다. 그래서 해결책은 COPY방식으로 읽도록 변경하면 될 것으로 보입니다.
매 레코드를 현재처럼 peek 하되 바로바로 pack 하면 copy 하는 효과가 있어서 메모리를 절약하면서 동일한 효과가 있을 것 같습니다.
매 레코드를 현재처럼 peek 하되 바로바로 pack 하면 copy 하는 효과가 있어서 메모리를 절약하면서 동일한 효과가 있을 것 같습니다.
메모리 사용량 측면에서는 의미 있는 차이가 없을 것으로 예상됩니다. pack 된 데이터를 저장하는 메모리의 크기를 미리 알 수 없어서 일정량 할당해서 사용하다가 다시 늘리는 형식이 되어야 하기 때문입니다. 이런 경우, 메모리 재할당 후 기존 내용 복사의 과정을 거치거나, 나누어서 따로따로 저장하고 연결리스트로 관리하는 경우에는 최종단에서 전체를 하나의 메모리 블록으로 만들어야 하는 문제가 발생합니다. 최적의 방식은 연결리스트가 되더라도 네트웍 전송단에서까지 잘 처리될 수 있도록 해 주면 바람직하겠지만, 전체 컨셉을 조정하는 것이라 작업량이 매우 많아질 것 같습니다.