International audienceRelational database management systems (RDBMS) come with many features. Tables and columns but also views, triggers or stored procedures. Some database architects (DBAs) feel a lack of tools to help them during the implementation and the evolution of the database. In this paper, we conducted a preliminary survey in the PostgreSQL community to get an idea of the usage they make of some of the PostgreSQL features and to gather feedbacks of the issues they face using these features. The results of this survey suggest that these features are fairly well used by DBAs and that DBAs using them encounter implementation issues

Delplanque, Julien

INRIA a CCSD electronic archive server

HAL Id: hal-02459484https://hal.inria.fr/hal-02459484Submitted on 29 Jan 2020HAL is a multi-disciplinary open accessarchive for the deposit and dissemination of sci-entific research documents, whether they are pub-lished or not. The documents may come fromteaching and research institutions in France orabroad, or from public or private research centers.L’archive ouverte pluridisciplinaire HAL, estdestinée au dépôt et à la diffusion de documentsscientifiques de niveau recherche, publiés ou non,émanant des établissements d’enseignement et derecherche français ou étrangers, des laboratoirespublics ou privés.Software Engineering Issues in RDBMS, a PreliminarySurveyJulien DelplanqueTo cite this version:Julien Delplanque. Software Engineering Issues in RDBMS, a Preliminary Survey. BENEVOL 2017- 16th edition of the BElgian-NEtherlands software eVOLution symposium, Dec 2017, Antwerp, Bel-gium. ￿hal-02459484￿Software Engineering Issues in RDBMS,a Preliminary SurveyJulien DelplanqueUniversité de Lille, CRIStAL, CNRS, UMR 9189, Lille, FranceRMoD Team, Inria Lille Nord Europejulien.delplanque@inria.frAbstract—Relational database management systems (RDBMS)come with many features. Tables and columns but also views,triggers or stored procedures. Some database architects (DBAs)feel a lack of tools to help them during the implementation andthe evolution of the database. In this paper, we conducted apreliminary survey in the PostgreSQL community to get an ideaof the usage they make of some of the PostgreSQL featuresand to gather feedbacks of the issues they face using thesefeatures. The results of this survey suggest that these features arefairly well used by DBAs and that DBAs using them encounterimplementation issues.I. INTRODUCTIONRelational databases [1] have existed for several decades,but the management of their evolution seems to remaincomplex. A database architect contacted our team to reportissues he had while managing his relational databases. Theseissues were initially related to the implementation of views(i.e., named and stored select query). When a view has to bemodified, it has to be deleted and then recreated. The problemis that if this view is used by another view, the dependantview has to be deleted and recreated as well. When you havea lot of views depending on each others, it becomes hard tomodify them. Indeed, the problem can be solved by handbut depending on the number of dependent views it can bevery complex and error prone. By further questioning theDBA, we realised that he was facing other issues related tostored procedures (i.e., set of SQL instructions pre-compiledand available to the database entities and potentially toapplications accessing the RDBMS) or triggers (i.e., a set ofSQL instructions to execute when a specific event occurs inthe database).A study of commercial solutions for RDBMS managementsuggested us that a “database approach” is adopted bythose solutions. That is to say, they provide utilities tocreate/modify/destroy entities in a database and to do adependency analysis based on the meta-data provided by theRDBMS. There are three main problems with this approach.First, the dependencies concerning stored procedures areunknown by these tools because their source code are storedas strings in the RDBMS meta-data. Second, these tools warnthe user that a dependency makes a change impossible but donot propose a solution to allow the change to be integrated.Finally, no visualisation of these dependencies is provided todatabase architects which leads them to build the dependencygraph manually in order to evaluate the impact of the changeto apply.These issues in RDBMS tools make the maintenance ofdatabases (DBs) containing a lot of entities difficult for DBAs.The lack of support for automated or semi-automated changeintegration in the database makes the maintenance even moredifficult. Before addressing these problems, we want to exploreif these issues are faced on the field by other DBAs and if otherissues are met. In this paper, the results of a twofold prelim-inary survey are presented. This survey aims to: 1) providea coarse-grained estimation of these usages by questioningDBAs directly and 2) get feedback of implementation issuesencountered by DBAs in order to be able to analyse theircauses and try to find automatic or semi-automatic solutions.II. PRE-SURVEY IN POSTGRESQL LILLE COMMUNITYIn order to have a first idea on the usage of views, storedprocedures and triggers in companies and a first idea ofproblems DBAs are facing while using these entities, we con-ducted a preliminary survey in a meeting of the PostgreSQLLille community. This section describes the protocol used toconduct the survey and presents our findings.A. Context and ParticipantsPostgreSQL Lille community’s meeting was organized bytwo companies in a bar, which provided a casual atmosphere.The meeting’s schedule consisted in two 45 minutes presen-tations with a pause of 15 minutes between. About 50 peopleparticipated to the meeting from which 13 were asked to an-swer the survey during the pause and after the conference. Nocompensation was provided in exchange to the survey answer.1 person refused to answer the survey claiming that he hadnot enough experience in database administration/architecture.Thus, 12 participants answered the survey during this meeting.B. Survey’s DescriptionThe survey was written in French (French being thenative language of the meeting’s participants). It consisted of16 questions. 12 questions concerning the usage of views,stored procedures and triggers in databases and 4 concerningpersonal information of participants.The 12 questions can be presented as 3 similar groups of4 questions. With X being one of the words “views”,“storedprocedures” or “triggers”, the four questions were:1) What is the proportion of your databases that containX?2) How many X do they hold compared to the number oftables?3) Are your new databases using X? If not, why?4) Do you encounter problems while implementing X? Ifso, what problems?Questions 1 and 2 were multiple choice questions with, aspropositions, 0-20%, 20-40%, 40-60%, 60-80% and 80-100%.Question 3 was a Yes/No question with the possibility toexplain the reason why new databases are not using X.Question 4 was a Yes/No question with the possibility toexplain the problems encountered during the development ofX.The 4 personal questions were the following:1) In which company are you working? Or in which open-source project?2) What is your position?3) How long have you been in this position?4) How many years of experience do you have with Post-greSQL?For each question the participant could answer freely whatresulted in various different answers.C. Results and InterpretationFigure 1 illustrates the results of usage question 1: “Whatis the proportion of your databases that contain X?”. It seemsthat, for each type of entity, most of the interviewees perceivethat only 0-20% of their database(s) use them. 6 participantsuse views in 0-20% of their DBs while the 6 others usethem in intervals included in 20-100%. A similar observationcan be done for stored procedures with 7 participants usingthem in 0-20% of their DBs and 5 in intervals included in20-100%. For triggers, 6 people said that 0-20% of theirDBs use triggers, 5 said 20-40% and 1 said 80-100%. Theseresults suggest that views, stored procedure and triggers arenot always used in database systems. This observation seemsnormal, some applications do not require other features thantables and columns from the DB .The results of the second question, about the proportionof views/stored procedures/triggers compared to the numberof tables in the database, are illustrated in Figure 2. Theinterviewees answers suggest that, generally, the proportionof views, stored procedures, triggers against the number oftables is in the interval 0-20%. For triggers, there are moreanswers (4/12) for the interval of 20-40%. The data collectedsuggest that views, stored procedures and triggers usuallyrepresent a proportion of 0-20% relatively to the number oftables.Figure 3 summarizes the results of questions 3 and 4.As a reminder, question 3 asked participants if they useviews/stored procedures/triggers in their new databases andquestion 4 asked participants if they have problems whileimplementing those entities. In the plot, people using oneof these entities and having problems are illustrated asthe black part of the “yes” bar. Figure 3 suggests that allthese entities are used by a significant part of the survey’sparticipants. Some of those users encounter problems duringthe implementation. Stored procedure is the type of entitythat causes the most problems to its users (3/7 have problemsduring implementation).Questions 3 and 4 let the possibility to the participantsto explain their answer (depending if it was “yes” or “no”).Some people gave a justification to their answer even if it wasnot required and others did not provide a explanations when itwas required. Table I presents the main ideas of participant’sexplanations. These explanations have been summarized tobe able to group those expressing the same idea.For questions about the usage of views/storedprocedures/triggers, the most common answer is thatthe usage of these kinds of entities is not required by theproject(s). The other reasons got the same scores for eachkind of entity excepts for triggers where 2 people reportedperformance issues to justify their answer. Explanationssummaries of answers about implementation issues mostlyhave a single occurrence except for the “programminglanguage complexity” reported twice for stored procedures.1https://www.postgresql.org/docs/current/static/sql-notify.html0-20 %20-40 %40-60 %60-80 %80-100 %024681012# participants(a) Views0-20 %20-40 %40-60 %60-80 %80-100 %024681012# participants(b) Stored procedures0-20 %20-40 %40-60 %60-80 %80-100 %024681012# participants(c) TriggersFig. 1. Interviewees’ perception of the proportion of their databases usingviews/stored procedures/triggers.0-20 %20-40 %40-60 %60-80 %80-100 %024681012# participants(a) Views0-20 %20-40 %40-60 %60-80 %80-100 %024681012# participants(b) Stored procedures0-20 %20-40 %40-60 %60-80 %80-100 %024681012# participants(c) TriggersFig. 2. Interviewees’ perception of the proportion of views/stored proce-dures/triggers in their databases relatively to the number of tables.No Yes024681012# participants(a) ViewsNo Yes024681012# participants(b) Stored procedures/No Yes024681012# participants(c) TriggersFig. 3. Interviewees’ perception of the usage or not of views/stored pro-cedures/triggers in their databases. In the interviewees that use views/storedprocedures/triggers, the black part of the bar represents those having problems.TABLE ISUMMARIES OF PARTICIPANTS’ EXPLANATIONS TO QUESTIONS 3 AND 4FOR VIEWS, STORED PROCEDURES AND TRIGGERS.Answer Justification summary #Views usageNo Not required by the project(s). 2No No advantage compared to a simplerequest.1No Not well managed by ORMs. 1No Work made in application side 1Yes Developers’ lack of awareness. 1Yes To fool the ORM. 1Yes For materialized views. 1Views implementationissuesYes Problem when altering a table used bya view.1Yes Developers’ lack of knowledge. 1Yes Views hide the schema complexity butnot model’s problems.1Stored procedures us-ageNo Not required by the project(s). 3No No advantage compared to simple re-quests.1No Functional aspect not wanted. 1Yes Allows automation of tasks. 1Yes Permissions management. 1Stored procedures im-plementation issuesYes Programming language complexity 2Yes Maintenance complicated. 1Triggers usageNo Not required by the project(s). 3No Performance issues. 2No Maintainability problems. 1No Brings a logic aspect in DB side. 1Yes For partitioning the database. 1Yes With pg notify1 extension. 1Triggers implementa-tion issuesYes Performance issues. 1Figure 4 illustates profiles of participants. Most of themare claiming to be DBAs (6/12), there are also 2 developers, 2CTOs and 2 who could not be grouped (1 geomatics specialistand 1 claiming to be leader of PostgreSQL community). 6participants have 1-5 years of experience with PostgreSQL. 2have 6-10 years of experience and 1 has 11-15 years. Thereare 3 participants who have 0 years of experience. One ofthem just started to use PostgreSQL 1 month ago and the2 others are Oracle users. These Oracle users were able toanswer questions on views, stored procedures and triggersDBADeveloperCTOOthers024681012# participants(a) Participants’ jobs0 years1-5 years6-10 years11-15 years024681012# participants(b) Experience with RDBMSFig. 4. Profiles of the participants.since Oracle RDBMS offers these features too.D. Threats to ValidityThe results of the survey are based on the participants’perception of databases they maintain. Because of that, thereare factors that limit the validity of our observations.First of all, the perception of DBAs on the number ofentities in their databases may be wrong. Indeed, we donot know what is the difference between their perceptionof database sizes and the reality. Nevertheless, answers toquestions concerning the proportion of databases using aparticular entity type and the proportion of a particular entitytype in their database schemas provide the informations thatviews, stored procedures and triggers are used in companies’databases and that this usage can be of different degrees.Second, the proposed percentage intervals provide a poorprecision on the real proportion of entity types. Indeed,we realised that it is particularly awkward for the “0-20%”interval. A lot of people gave this answers and it is notpossible to know if they mean 0% or between 1% and20%. This is an issue of the survey that was not detectedduring its conception because of our expectations on theresults (we thought the intervals selected would be higher).However, answers to question 3 tend to reduce this threatsince the number of people who are not using views/storedprocedures/triggers in their DBs is lower than those providingthe 0-20% interval as answer to question 1.Third, concerning the justifications to questions 3 and 4,we realised that a survey may not be the best way to askDBAs about issues they encounter while implementing views,stored procedures and triggers. Indeed, only a few participantsprovided implementation issues but these issues should, if theyactually occur, be experienced by the other DBAs. We believethat maybe the other DBAs experience these issues but didnot think about it while answering the question. In hindsight,we think that the question can be quite difficult to answeron paper and maybe using a focus group method [4] wouldbe more appropriate to get information from DBAs experience.Finally, the survey was conducted on a small sample ofdatabase users (12 participants). A larger scale survey isrequired to confirm our results.III. RELATED WORKLu et al. [2] did a survey covering various aspects of theusage of SQL in industrial organisations. In particular, theauthors studied the complexity of SQL queries, the usage ofvarious SQL features, the difficulties encountered and theactions taken to solve those difficulties. Statistics about eachaspect are provided.Pönighaus [3] did an empirical study to assess the “codingfrequency” of SQL statements. They analysed the use ofthe four “data manipulation statements” (namely SELECT,INSERT, UPDATE and DELETE) from database requestmodules in 4 proprietary databases using the RDBMSIBM-DB2 V2R2 . To retrieve these statements, they queriedthe DB’s catalog and processed the result in order to do astatistical analysis of statements usages.These two articles provide a work similar to ours but at the“statement level” rather than at the “entity level”.IV. CONCLUSIONIn this paper, we conducted a survey on the PostgreSQLcommunity in order to 1) get an idea of the extent of theirusage of views, stored procedures and triggers and 2) get afeedback of implementation issues they encounter.The results of this survey provide that,• Views, stored procedures and triggers are usually used in0-20% of their databases;• The number views, stored procedures and triggers usu-ally represent 0-20% of the number of tables in theirdatabases;• 7/12 people use stored procedures and 3 of them haveproblems during the implementation;• “Programming language complexity” and “maintenancecomplicated” issues are reported for stored procedures;and• “Performance issues” is reported for triggers.The answers given by participants need to be further anal-ysed but they suggest that there is a need for tools support tohelp DBAs during DB implementation.V. FUTURE WORKFuture work is threefold. First, a review of the literaturehas to be made to determine if the problems reported byDBAs have been solved but are not integrated in commercialsolutions for RDBMS management or if these problems arestill unsolved.Second, it could be interesting to see if the usage resultsare generalisable on a greater sample of participants. Totest this, a larger scale study could be realised (takinginto consideration the issues of the pre-survey). Also, ashighlighted previously, asking participants about issuesencountered during implementation in a survey may not besuitable. Another possibility would be to use the focus groupmethod. By asking these questions to a group of DBAs ableto interact with each others, it may be possible to collectricher answers.Finally, analysing DB schemas directly to extract the num-ber of view(s), stored procedure(s), trigger(s), etc... wouldprovide more solid results by avoiding DBA’s possible miscon-ceptions on DBs. Furthermore, such analysis would allow us tocompare open-source and proprietary DB schemas concerningthe usage of such entities.REFERENCES[1] Edgar F Codd. A relational model of data for large shared data banks.Communications of the ACM, 13(6):377–387, 1970.[2] Hongjun Lu, Hock Chuan Chan, and Kwok Kee Wei. A survey on usageof sql. ACM SIGMOD Record, 22(4):60–65, 1993.[3] Richard Pönighaus. Favourite sql-statements - an empirical analysis ofsql-usage in commercial applications. Information Systems and DataManagement, pages 75–91, 1995.[4] Forrest Shull, Janice Singer, and Dag IK Sjøberg. Guide to advancedempirical software engineering. chapter 4, pages 93–116. Springer, 2008.

Software Engineering Issues in RDBMS, a Preliminary Survey

Abstract

Similar works

Full text

Available Versions

INRIA a CCSD electronic archive server