Ideas for Test Improvement in PostgreSQL Galy Lee

Ideas for Test Improvement in PostgreSQL Galy Lee [email protected] Contents Introduction Replication Test Framework Kernel Test Framework Concurrency Test Framework Feedback and suggestions

Introduction The existing test framework of PostgreSQL lacks in testing of many features of what it supports. By using the following three enhancements of f ramework the PostgreSQL test can be improved . Replication Test framework Kernel Test framework Concurrency Test framework Replication Test Framework The existing regress framework can be used for testi ng replication also but It is not fully automated. The f ollowing operation should be done manually Need to start both nodes with replication setup. Need to run the hs_primary_setup.sql on primary node. Now needs to run the standby check in standby node. The entire configurations like connection parameter

and database name are hard-coded. Only default co nnection parameter should be used for standby. Only limited number and type of test cases are avail able for replication test. Replication Test Framework The new test framework which is similar in isolation framework in idea can adds the f unctionality of different cluster setups and different synchronization mechanisms such as synchronous, asynchronous and cascadi ng replications can be achieved automatica lly. Replication Test Framework The Replication Test framework idea is tak en from Mysql code base. The Mysql replication framework has all th e advantages of syncing/stopping/resumin

g the logs with master. With this framework, user can control inte rnals of the replication process. Replication Test Framework DB Server Pg Replication Test Framework Meta Framework Init Temp DB Setup Conf Options Scheduler

SUT Server Side Test Framework Schedule files Executor (psql/gsql) SQL Engine Command Processing Meta Command Execution

Storage Vacuum Index WAL Transaction Test Cases Result validation & reporting

DB Server Results Meta Expected Output Note: Yellow color block is proposed change to the existing one. SUT Server Side Test Framework SQL Engine Command Processing

Meta Command Execution Storage Vacuum Index WAL Transaction Replication Test Framework- Component Pg Replication Test Framework

Framework Init Temporary installation , initialization, creating and starting of DB Parsing of test schedule files Running of test cases in sequential or parallel modes Executor

Setting the test environment variables Scheduler Running of single test case (.sql file) using psql/ gsql client tool Result validation & reporting Validates the actual results against the expected

Meta command processing Temp DB Setup Server Side Test Framework Reports the test verdict (pass/fail) Test Resources

Schedule files Files with all the test cases to be executed in both sequential and parallel mode Test cases SQL files having test scenario Results Files (.out) with actual results for every test case Expected output Files (.out) with expected results for every test case Module which understands the meta commands received from the replication test framework.

Meta command execution Module which executes the meta command returns the result to replication test framework. Replication Test Framework List of Meta commands: RESET MASTER Reset master to its original state. CHANGE MASTER TO change the master in subscriber. SYNC_TILL - Wait till gets the logs from master ti ll the specified offset. SYNC_TILL_STOP - Wait till gets the logs from m aster till the specified offset and temporarily stops the re play. SYNC _STOP [] Stops the replay of logs from master.

RESUME_SYNC Resume the replay operation. Replication Test Framework Test file Setup Cluster Test Teardown cluster Replication Test Framework Test specification: Each replication test is defined by a specification file, stored in the sp ecs subdirectory. A test specification consists of three parts, in this o rder: setup cluster { [node,]...} node : node "nodename" config ( [key = value,]....) Through setup cluster command block we can setup a replication to

pology. first node specified in the node list will become the specifica tion of the master node and all other nodes becomes the subscriber in the cluster. For each node we can specify config settings as a (key = value) pair. Replication Test Framework test { commands } Through test block we can connect to particular node, fire queries a nd then compare the data on set of nodes. This is the block where ac tual test on replication is done. Following commands are supported to facilitate replication testing. connect "nodename - switch the current connection to "nodename sql_cmd { sql commands } - Through sql_cmd block we can fire set of s ql commands on current connection. compare result node ([nodename,].... sql_cmd(sql command)) - Throu gh compare result node we can compare data on set of given nodes by firing the given sql_cmd on all nodes and comparing the result set. Replication Test Framework

show cluster status -- display current cluster topology and each nodes xlog positions. sync nodes([nodename,]...) -- wait until given nodes are in sync with current master of the cl uster. teardown cluster {} Through teardown cluster block we can cleanup the test setup. Usage example: setup cluster{ node master config (host_addr = localhost, port=5555, data_dir = data ) Replication Test Framework node standby config (host_addr = localhost, port=5556, data_dir = sdata ) node standby2 config (host_addr = localhost, port=5557, data_dir = sdata2) } test {

connect master; sql_cmd ( "create table t1(c1 int); insert into t1 values(10); select * from t1;" ) ; sql_cmd ( "create table t2(c1 int); insert into t2 values(99); select * from t2;" ) ; sync nodes (standby,standby2); connect standby; Replication Test Framework sql_cmd ( "select * from t1;" ) ; sql_cmd ( "select * fro m t2;" ) ; connect master; sql_cmd ( "select * from t1;" ) ; sql_cmd ( "select * fro m t2;" ) ; show cluster status; compare result nodes(master, standby) sql_cmd("select * from t1 order by c1"); }

teardown cluster{ connect master; sql_cmd ( "drop table t1;" ) ; sql_cmd ( "drop table t2;" ) ; } Replication Test Framework - Summary With this framework the defects which are

difficult to reproduce can be automated with the deeper controls in the replication. Needs to enhance more controlling in the server by adding more meta commands. Kernel Test Framework There is a regression suite is available for te sting the PostgreSQL. Currently the regressi on test is carried out by executing SQL quer ies. SQL based tests cannot ensure data stored i n the storage engine, it only result oriented. It is very difficult generate scenarios for inte rnal design testing of modules. Kernel Test Framework The new framework is an enhancement on existi

ng regress framework by adding some specific C-functions to every module to get the details of internal design. Those functions will be used to carry out the test using the exposed functions fr om SQL statements. With this approach any functionality which is di fficult to hit from outer test can be achieved easi ly and also it can be automated for future valida tions. Kernel Test Framework Testing storage functionalities through SQL functions (extensions) with Pg Regress framework Pg Regress Test Framework DB Server Framework Init Temp DB Setup

Schedule files Scheduler SQL Engine Executor (psql/gsql) Test Cases Result validation & reporting Expected Output

Server Side Test Framework Extensions (DB specific functions in C) Results SUT Storage Vacuum Index WAL Transaction

Note: Yellow color block is proposed change to the existing one. Kernel Test Framework Cont.. Pg Regress Test Framework Framework Init Temp DB Setup

Parsing of test schedule files Running of test cases in sequential or parallel modes Executor Temporary installation , initialization, creating and starting of DB Scheduler

Setting the test environment variables Running of single test case (.sql file) using psql/ gsql client tool Result validation & reporting Validates the actual results against the expected Reports the test verdict (pass/fail) Test Resources

Schedule files Files with all the test cases to be executed in both sequential and parallel mode Test cases SQL files having test scenario Results Files (.out) with actual results for every test case Expected output Files (.out) with expected results for every test case Server Side Test Framework Extensions

To test kernel functionality with Necessary precondition & validation functions and Finer control of execution Kernel Test Framework The following three modules design is exposed by w riting new c-functions and those are automated suc cessfully. Btree bt_page_get_record_size - returns information on the siz e of the total records, the number of live items and the n umber of dead items, in a B-Tree index page. bt_get_page_count_of_specified_type_from_index - ret urns the count of the pages of the specified type, belongi ng to the specified index relation.

Kernel Test Framework bt_metapage_info - returns information about a B-tree i ndex's meta page. bt_page_statistics - returns summary information about single page of B-tree index. bt_page_contents - returns detailed information about all of the items on a B-tree index page Transaction: tam_change_tbl_perm - Modifies the permission of the disk file for the given relation name, so that we can perf orm failure testing of the transaction operation when it don't have table file permission during DDL operation lik e Drop table. Kernel Test Framework

tam_check_XLog_file_exists_from_XLogRecPtr - Retur ns the Xlog File name corresponding to the XLogRecPt r, which is passed to this function as input parameter of type: 'text *'. This function will then check whether that file is present in the pg_xlog folder within the Dat a directory. If present, the value of 'file_validity' field c an be observed as "Valid" and "Invalid" otherwise. get_transaction_status - Returns status of the given tr ansaction. trans_get_current_wal_xid - Returns the last written WAL transaction id. trans_validate_commit_wal - Returns true if the last w al written was a commit transaction or false. Kernel Test Framework trans_validate_wal_record - Returns true if the last written WAL record is valid or false. get_current_transaction_level - Returns the nesting level of current transaction.

read_xlog_records - Returns the set of WAL records if the in put txid is NULL. Returns the set of WAL records inserted by the transaction specified in argument of txid. read_xlog_snapshot - Sets the last inserted WAL record posi tion as record pointer for start reading the WAL records. Buffer pool: pg_buffercache_pages - returns a set of records, where eac h record contains the details of a single shared buffer block. Kernel Test Framework pg_buffer_validity - returns a set of records, w here each record describes the validity of a sh ared buffer block. pg_buffer_get_size_details - returns a record, with the details of the size of the shared buffer .

pg_buffer_get_size_details_of_temp_buf - ret urns a record, with the details of the size of th e local(temp) buffer. Kernel Test Framework - Examples Kernel Test Framework - Examples Kernel Test Framework - Examples Kernel Test Framework - Summary By exposing internal design through C-functions i t is easy to write an automatable test case to test the design. With this approach it is very easy to fi nd the defect if any change causing the test failur e. Needs to add more such C-functions in the code f or validating all modules design.

Concurrency Test Framework In PostgreSQL test framework there exist a framework called Isolation used to test the concurrent sessions u nlike to the normal regress framework. The existing isolation test framework carried out the t est when the one session waits on a lock and proceeds with another session to execute the operations. The existing isolation framework can work only when t he sessions are waiting on a lock only and also it does nt detect dead locks which can occur between the ses sions. Concurrency Test Framework By using the new framework which is enhanced on existing isolation framework, user can create concurrency between sessions at any point in th e code by specifying the sync points in the code. The defects which requires a special synchroniz ation mechanisms for reproduction can be auto

mated using the new proposed concurrency fra mework later for avoiding any change causing t he defect to reopen. Concurrency Test Framework step Executio n Concurrency Test Framework The concurrency framework idea is taken from Mysql co de base and enhanced it according to PostgreSQL. In Mysql only one session can raise the signal only once , the same is extended in PostgreSQL to support multipl e signals. In Mysql one session can wait only on one signal, the sa me is extended in PostgreSQL to support multiple signal s. With the above two changes from Mysql the concurrenc y scenario can be generated between many sessions ver y easily.

Concurrency Test Framework Pg Isolation Test Framework DB Server Framework Init Temp DB Setup Schedule files Scheduler Executor (psql/gsql) Test Cases

Debug_Sync Expected Output SQL Engine Command Processing Storage Vacuum Index WAL Debug_Sync

Singnal Handler Result validation & reporting SUT Server Side Test Framework Results Transaction Note: Yellow color block is proposed change to the existing one.

Concurrency Test Framework - Component The following two components needs to be added to the server for adding concurrency test framework. Debug Sync (Command Handler): This component of de bug sync will be used to receive command from clients and process them to store the new sync point and corre sponding action. Debug Sync (Signal Handler): This component will be us ed to execute the sync point i.e. to send or wait for the signal and then take the necessary action. Concurrency Test Framework Syntax: SET SYNC_POINT {RESET | { {CLEAR | {SIGNAL [RAISE ] | WAIT_FOR [, ] [TIMEOUT ] [EXECUTE ]}}}}

RESET: Resets the Debug sync shared memory. This clears any existing signals i n the shared memory. CLEAR: Deletes the sync point which is already added to the corresponding ses sion. If the sync point doesnt exist it reports the error. SIGNAL: whenever the specified sync point is hit in the code flow, then SIGNA L which is specified will be raised. Concurrency Test Framework WAIT_FOR: whenever the specified sync point is hit in the code flow, it waits for the corresponding signal which is specified during sync point creation. O nce the corresponding signal receives it stops waiting and continue further opera tions. RAISE: This option is associated with SIGNAL option as how many times the corresponding signal can raise, whenever the specified sync point hits in the code flow.

TIMEOUT: This option is associated with WAIT_FOR option as how much time max the session can wait for the signal to receive whenever the specified sy nc point hits in the code flow. Concurrency Test Framework EXECUTE: How many times the sync point can be active before it gets disa bled. By default the value is 1. If user wants the sync point to be active for many numbers of times, user can use this option. The isolation framework logic needs to be extended as the main session can detect if any session is waiting on a sync point as w ell as the existing functionality of lock also. Concurrency Test Framework How to Use? Add the debug points in module(Source code), based o n whichever part needs to be tested.

DEBUG_SYNC_POINT("SP_B4_COMMIT"); Ex:-1. In exec_simple_query() the followng sync point is add ed. if(!(IsA(parsetree, DebugSyncStmt))) DEBUG_SYNC_POINT("SP_B4_COMMIT"); Ex:-2. In LockRelationOid() the following sync point is added. Concurrency Test Framework How to Use? SetLocktagRelationOid(&tag, relid); DEBUG_SYNC_POINT("sp_b4_rel_lock_4_upd"); res = LockAcquire(&tag, lockmode, false, false); Add the debug sync command to define a particular sy nc point along with the action in the session. SET SYNC_POINT SP_B4_COMMIT WAIT_FOR EXEC_SELECT; Note: By using this approach, some of the fixed complex defects also can be automated.

Concurrency Test Framework Real Example # Alter table set schema in parallel with drop schema. # # Alter table set schema the lock on the schema is not taken prior to d efect # fix, during that time if the schema gets dropped the table cann ot be # operated later. setup{ CREATE TABLE test(f INT); CREATE SCHEMA sch1; } teardown{ DROP TABLE test; SET SYNC_POINT RESET; } Concurrency Test Framework

session "s1 step "rx1" { SET SYNC_POINT sp_after_get_name_space_id WAIT_FOR drop_schema; } step "rx2" { ALTER TABLE test SET SCHEMA sch1; } session "s2 step "wx1" { SET SYNC_POINT sp_after_drop_schema SIGNAL drop_schema; } step "wx2" { DROP SCHEMA sch1; } step "wx3" { INSERT INTO test VALUES (1); } permutation "rx1" "rx2" "wx1" "wx2" "wx3" Concurrency Test Framework -Summary With this framework the defects which are difficult to reproduce can be automated an d get the results. Need to enhance the framework further to get the exact error messages during the te st execution.

Feedback and Suggestions ? [email protected] Page 44

Recently Viewed Presentations

  • 8-01-2013 TODAYS JOURNEY Car Rental Industry Whats New

    8-01-2013 TODAYS JOURNEY Car Rental Industry Whats New

    Consolidation continues - Hertz/DTG; ABG acquires Zip & Payless. Car residual values down w/ car sales being down - impacts on RACs. Q1 2013 - Good Growth (+8%) led by commercial segment. Q2 2013 business growth slowed, commercial trending to...
  • owenhsdunbar.weebly.com

    owenhsdunbar.weebly.com

    By the 1700s slavery in the North was dying out. And an increasing number of Northerners began to voice their religious and political opposition of slavery. By 1804 almost all of the Northern states and voluntarily abolished slavery. Cotton King:...
  • 1. dia - MFTTT

    1. dia - MFTTT

    VÁZRAJZ Engedéllyel nem rendelkező épület NE szerepeljen rajta - csak a műszaki leírásban * MFTTT Fővárosi és Pest megyei Földmérő Nap Budapest 2016. április 20. VÁZLAT Engedéllyel nem rendelkező épület feltüntetendő - műszaki leírásban szerepeljen Hatálytalan - 46/2010. FVM r....
  • Mission Statement and Philosophy Statement

    Mission Statement and Philosophy Statement

    Mission Statement and Philosophy Statement Step 2 ... Research completed by the National Association for Physical Education (NASPE) and the Center for Disease Control and Prevention (CDC) reveal that there is a direct relationship between academic achievement and fitness. Movement,...
  • Rubric Development PowerPoint Presentation

    Rubric Development PowerPoint Presentation

    The SOLO (short for Structure of the Learning Outcome) taxonomy has five levels, which we've labeled 0 through 4. The lowest level, prestructural consists of only information that is irrelevant to the question. The next highest level , unistructural, consists...
  • BIOLOGY - WordPress.com

    BIOLOGY - WordPress.com

    b) the material higher on the electrostatic series gains protons from material lower on the . c) the material higher on the electrostatic series lose electrons to the material lower on the list. d) the material lower on the electrostatic...
  • GFO Radar Altimeter Performance (Preliminary) July 2, 1999

    GFO Radar Altimeter Performance (Preliminary) July 2, 1999

    GFO RADAR ALTIMETER PERFORMANCE July 2000 George S. Hayne/David W. Hancock III NASA GSFC/Wallops Flight Facility, Code 972 Contributors: Dennis W. Lockwood, Raytheon ITSS Ronald L. Brooks, Raytheon ITSS Ngan Tran, Raytheon ITSS Anita Brenner, Raytheon ITSS Richard Sailor, TASC
  • BPS Early Childhood Programs - System Accreditation

    BPS Early Childhood Programs - System Accreditation

    Prekindergarten Programs. Title l Step FOURward VPK: Maintains a class size of 20. Be four by September 1st and live in the attendance area of the designated schools.. Head Start: Includes 3 and 4 year olds students, Head Start 3...