38401tfc

Estudi i avaluaci de les funcionalitats de PostgreSQL Ivo Plana Vallv Enginyeria en Informtica M. Elena Rodrguez Gonzlez 10/01/2006

Estudi i avaluaci de les funcionalitats de PostgreSQL Ivo Plana Vallv

2

Agraments En aquest mon canviant, de rpids oblits i de feixuga saturaci dinformaci, magradaria seguir oferint els meus agraments...

als meus pares, per les hores que mhan condonat. Per totes aquelles tasques que mhan estalviat amb la illusi de veure com cada estona era dedicada a lesfor dassolir nous horitzons. A la companya destudis que mha fet replantejar cada cosa que ja donava per assolida. Per totes aquelles coses de les que hem tingut que prescindir, i per totes aquelles altres de les que hem gaudit. A tots els amics que han comprs les meves absncies, els meus mals humors, i que han estat capaos daguantar el tema daquests ltims sis anys. A la persona que em va animar a fer una enginyeria. A la persona a la que li dec el present que ja gaudeixo.

magradaria afegir...

els meus agraments al rector Ferrat i a tot lequip docent i administratiu de la UOC, per haver fet possible els estudis virtuals i per aconseguir que pugui dir amb orgull: He estudiat lEnginyeria Informtica a la UOC!


3

Resum

Lobjectiu del projecte s fer un resum de les funcionalitats i la idonetat del sistema de gesti de base de dades PostgreSQL, per a facilitar la possible implantaci en lmbit docent universitari. Tot recollint els coneixements adquirits durant la realitzaci del Treball de Fi de Carrera de lEnginyeria Tcnica Informtica de Sistemes, realitzat a la Universitat oberta de Catalunya a la tardor de 2004, es va plantejar la possibilitat de fer un estudi del PostgreSQL per a verificar que compleix tots aquells punts que requereix un Sistema de Gesti de Base de Dades per a poder considerar-se com a tal, i poder servir aix deina per a desenvolupar en la prctica, els casos terics de les assignatures relacionades amb lrea de les Bases de Dades duna Enginyeria Tcnica Informtica, o una Enginyeria Informtica. Al llarg de la present memria sestudiar la sintaxi bsica de les instruccions elementals, els camins dexecuci i doptimitzaci de les sentncies SQL, les propietats transaccionals i dintegritat concretes del PostgreSQL, fins als detalls de com utilitzar les eines del mateix programari per a fer cpies de seguretat, o per a restaurar el sistema desprs duna fallada. Tot aix, sha complementat amb la verificaci de la compatibilitat del programari en diversos sistemes operatius, amb lelecci i prova en profunditat dun client SQL, i amb la confecci dels manuals ds i installaci de tot aquests materials, en els diferents sistemes operatius contemplats. Al llarg del projecte es comprovar que PostgreSQL pot substituir almenys part del programari utilitzat a lactualitat a lmbit docent, aportant fins i tot valor afegit, tant per caracterstiques, com pel futur que es preveu de cada sistema gestor de bases de dades en el mon empresarial.


4

ndex Agraments ........................................................................................................ 2 Resum ................................................................................................................ 3 1.- Histria......................................................................................................... 6

1.1.- Introducci................................................................................................ 6 1.2.- Ingres i altres ............................................................................................ 6 1.3.- PostgreSQL ............................................................................................... 7

2.- PostgreSQL en el mn acadmic ................................................................. 8 3.- Sentncies de manipulaci........................................................................ 10

3.1.- Create Database .......................................................................................10 3.2.- Create Table.............................................................................................10 3.3.- Insert Into ...............................................................................................12 3.4.- Select From..............................................................................................12 3.5.- Update.....................................................................................................17 3.6.- Delete......................................................................................................17 3.7.- Sintaxi addicional de manipulaci dels resultats ...........................................18

4.- El llenguatge pl/pgSQL.............................................................................. 21

4.1.- Els Procediments. .....................................................................................21 4.2.- Els Cursors. ..............................................................................................25

5.- ndexs. ....................................................................................................... 27

5.1.- B-Tree .....................................................................................................27 5.2.- R-Tree .....................................................................................................27 5.3.- Hash........................................................................................................27

6.- Pla dexecuci de consultes....................................................................... 29

6.1.- Seq Scan: ................................................................................................30 6.2.- Index Scan:..............................................................................................30 6.3.- Sort .........................................................................................................31 6.4.- Unique.....................................................................................................31 6.5.- Limit........................................................................................................31 6.6.- Aggregate ................................................................................................31 6.7.- Append....................................................................................................32 6.8.- Result ......................................................................................................32 6.9.- Nested Loop.............................................................................................32 6.10.- Merge Join .............................................................................................32 6.11.- Hash Join ...............................................................................................32 6.12.- Group ....................................................................................................33 6.13.- Subquery Scan i Subplan .........................................................................33 6.14.- Tid Scan.................................................................................................33 6.15.- Materialize..............................................................................................34 6.16.- Setop Intersect, Setop Intersect All, Setop Except, Setop Except All ............34

7.- Optimitzaci fsica ..................................................................................... 35

7.1.- Consultes preparades................................................................................35 7.2.- Creaci de cluster .....................................................................................36


5

7.3.- Analyze i els plans dexecuci.....................................................................38 7.4.- ndex calculats .........................................................................................40 7.5.- ndex parcials...........................................................................................40

8.- Model de concurrncia............................................................................... 42

8.1.- Funcionament MVCC .................................................................................43 8.2.- Exemples MVCC vs blocat de registres ........................................................43 8.3.- Blocats de taules i registres explcits...........................................................50

9.- Comparatives entre SGBDs........................................................................ 52 10.- Instal.laci PostgreSQL i utilitats............................................................ 55 11.- Compatibilitat amb altre programari ...................................................... 56

11.1.- Software de seguretat .............................................................................57 11.2.- Programari estndard UOC ......................................................................57 11.3.- Altre programari......................................................................................57

12.- Benchmarks; PostgreSQL vs MySQL ....................................................... 58

12.1.- Metodologia............................................................................................58 12.2.- Execuci de les proves ............................................................................60 12.3.- Comparativa de resultats .........................................................................61 12.4.- Comparativa desprs de lANALYZE...........................................................63 12.5.- Plans dexecuci......................................................................................63 12.6.- Conclusions ............................................................................................63 12.7.- Grfica comparativa dels temps dexecuci................................................65

13.- Manteniment de la base de dades........................................................... 67

13.1.- Backup...................................................................................................67 13.2.- Processos peridics .................................................................................70 13.3.- Espai demmagatzemament .....................................................................70

14.- Conclusions i futurs treballs .................................................................... 71 Referncies......................................................................................................... 72 Glossari .............................................................................................................. 73 Annexes Annex I. Exemple ndex calculat complex. ..............................................................75 Annex II. Detalls execuci Benchmarks. .................................................................77 Annex III. Optimitzaci consulta F. ........................................................................91 Annex IV. Dades sinttiques prctica SGBD. ...........................................................92 Annex V. Arbre installaci programari ...................................................................94 Annex VI. Contingut CD programari PostgreSQL .....................................................95 Annex VII. Manual dinstal.laci del programari PostgreSQL (versi Linux) ..................97 Annex VIII. Manual dinstal.laci del programari PostgreSQL (versi Windows 9x/ME).104 Annex IX. Manual dinstal.laci del programari PostgreSQL (versi Windows 2000/XP).110 Annex X. Guia dinstallaci del programari SQuirreL (versi Windows 2000/XP)..........118 Annex XI. Guia dinstallaci del programari SQuirreL (versi Linux) .........................129 Annex XII. Manual funcionament PostgreSQL CDLive! ...........................................140


6

1.- Histria

1.1.- Introducci PostgreSQL agafa el seu nom i les seves caracterstiques bsiques en ser recollit el projecte Postgres95 per la Comunitat del Programari Lliure. Levoluci daquest, amb els afegits del llenguatge de consulta SQL, fa que assoleixi el seu nom definitiu. El concepte de Base de Dades (BD) relacional s relativament nou. El desenvolupament de tota la gamma de programari daquesta branca de la informtica ha evolucionat bsicament des de dos inicis clarament diferenciats; per un costat va comenar IBM amb el seu System R, i per laltra el Ingres de la Universitat de Berkeley a Califrnia. Ms tard va aparixer Oracle, el System R va evolucionar fins a lactual DB2, i parallelament va sorgir tot una nova srie de fabricants de Sistemes de Gesti de Bases de Dades (SGBD), descendents directes de la primignia Ingres.

1.2.- Ingres i altres A lany 1973 Michael Stonebraker i Eugene Wong van comenar a pensar en la necessitat dun SGBD de caracterstiques avanades. Gran part de la idea inicial va provenir dels papers que IBM va publicar sobre el projecte que estava desenvolupant, el System R. Amb un sistema de rotaci de programadors, i amb alguns ajuts dinstitucions publiques (DARPA, ARO, NSF, NESL), es va portar a terme tot el desenvolupament del Ingres. El maquinari sobre el que funcionava el Ingres era fora modest, ja que sexecutava sobre ordinadors PDP11. El diferent enfocament en quant a segment de mercat amb IBM (el System R noms funcionava en grans mainframes), va fer que Ingres assolis gran popularitat, i conseqentment quota de mercat. El fet de que tingus un tipus de llicncia de distribuci BSD, va fer addicionalment que molts altres desenvolupadors agafessin aquest sistema com a base per a fer nous sistemes comercials. Aix, en pocs anys va aparixer Informix, Sybase, Non Stop SQL i altres, essent tots evolucions dIngres. Informix va ser el segon SGBD en volum de mercat (per darrera de levolucionada bases de dades de IBM ara ja DB2), cosa que va fer que en passar aquesta companyia per uns incidents econmics derivats de problemes en la seva gesti (1997), IBM aprofites per a adquirir-la. El propi xit del projecte Ingres, va ofegar als desenvolupadors daquest programari en un cicle continu de manteniment. Com que aquesta no era la finalitat acadmica del projecte, des de Berkeley es va decidir donar aquest per acabat. Al 1994 Computer Associates (CA) va comprar a una de les companyia que comercialitzaven el Ingres (ASK Corporation), una de les evolucions ms avanades del moment. Donat que la seva posterior comercialitzaci no va tenir lxit esperat per CA, a lany 2004 es van tornar a alliberar el codi del projecte amb llicncia CATOSL (una llicncia similar a la BSD), aconseguint aix que la comunitat del programari lliure es fes crrec de les evolucions segents.


7

1.3.- PostgreSQL Al donar Stonebraker per acabada letapa de comercialitzaci del Ingres (des de la companyia Relational Technologies, Inc), torna al 1985 a Berkeley per a desenvolupar un nou concepte de SGBDs. La problemtica de transformar el disseny conceptual original dun nou desenvolupament informtic, al seu equivalent SQL relacional, resultava cada vegada ms evident en aquelles poques. Sesperava poder dissenyar un SGBD amb tipus nadius, amb unes caracterstiques mnimes dherncia, i fer fins i tot que ell mateix fes el control de les restriccions imposades amb les relacions. Des dun principi sevita utilitzar com a base del nou desenvolupament el codi font de Ingres, ja que si b la forma del nou projecte era similar a lanterior, el fons tenia un enfocament completament diferent. Els conceptes de funcionament van comenar a veure la llum lany 1986. Al 1988 el primer prototipus ja era funcional. La primera versi era publicada al 1989. Versi darrera versi es van anar millorant i afegint funcionalitats, fins arribar a la versi 4 (Postgres95). A ligual que va passar amb lIngres, lxit del projecte va fer que les peticions de noves funcionalitats i de manteniment sobrepasss les possibilitats de lequip de desenvolupament, pel que es va lliurar aquesta ltima versi, i es va decidir donar el projecte per finalitzat. Malgrat que Stonebraker va crear lempresa Illustra Information per a comercialitzar Postgres, tot el projecte va ser alliberat sota llicncia BSD, per a que pogus servir de base per a futurs desenvolupaments. En aquest punt, dos estudiants graduats de la mateixa Berkeley (Andrew Yu i Jolly Chen), van reescriure part del Postgres, per a que en lloc dutilitzar el llenguatge de consulta original QUEL, acceptes el ja gaireb estndard SQL. Donades les importncies dels canvis fets, i que aquest ja era mantingut per la comunitat del programari lliure, es va decidir tamb fer evolucionar el nom fins a lactual conegut: PostgreSQL.


8

2.- PostgreSQL en el mn acadmic

Tal com sha justificat a la planificaci del present Projecte de Fi de Carrera, PostgreSQL a part de ser Programari Lliure (amb llicncia ds BSD), s estrictament ACID, s multi-Sistema Operatiu, s multi-maquinari, t diverses comunitats que levolucionen i li donen suport, i hi han diverses empreses que ofereixen manteniment i serveis. s un dels desenvolupaments de Programari Lliure dignes dadmiraci, tant per la seva histria inicial, com per levoluci que va tenir desprs de que es finalitzs el seu desenvolupament a Berkeley. A lhaver estat PostgreSQL iniciat en un entorn universitari precisament amb lobjectiu de demostrar els conceptes terics que sesperaven dels SGBD futurs, aquest implementa totes les funcionalitats habituals, i fins i tot, ha estat pioner en la utilitzaci de determinats components, ja que al no haver-se desenvolupat segons criteris comercials, es va poder prioritzar el model teric vers un rpid retorn de la inversi. El seu rendiment estable i continuat (demostrat a lapartat de Benchmarks), lalta concurrncia de processos que permet (grcies al MVCC), la seva versatilitat, lexistncia dels procediments emmagatzemats i dels disparadors, lexistncia del connectors JDBC/ODBC, fa que la seva utilitzaci hagi sobrepassat lmbit teric i que hagin empreses que la utilitzen com a component principal dels seus sistemes (NASA en el projecte SEQUOIA, BASF en el back-office del seu sistema de comandes de fertilitzants, etc). Donat el constant creixement de la popularitat daquest programari i al fet de que companyies de la categoria dIBM, Novell, o Fujitsu estan apostant pel seu s, es creu que en un futur seguir les tendncies dxit que ja sestan veient en algun altre programari concret.

El servidor web Apache s un bon exemple de lxit del programari lliure.

Actualment, ms del 70% dels servidors dinternet actius estan funcionant amb aquest programa!


9

En un mn en que les fusions de grans companyies ja s habitual, els avantatges de disposar del codi font del programari que sutilitza s clara, ja que sevita dependre de cap fabricant per a tenir la continutat garantida dun producte estratgic per la subsistncia de lempresa davui en dia. En el cas dutilitzar programari lliure, malgrat desaparegus lequip principal de desenvolupament del programa, la inversi feta fins al moment estaria assegurada, ja que lSGBD (per exemple) seguiria tenint un manteniment i evoluci garantit. Tamb sha de considerar que no sest subjecte al fabricant per a disposar dels mduls per a un idioma en concret, o de certes caracterstiques que siguin molt especifiques, ja que al ser el codi font pblic, es pot desenvolupar o fer-ho desenvolupar lliurement. El fet de dintroduir a lmbit empresarial programari lliure, fa que el teixit empresarial de la zona/pas millori, ja que a banda devitar-se lexportaci de divises, es creen provedors locals de tecnologia i manteniment. Si les expectatives devoluci exposades sn encertades, el mercat laboral dels propers anys necessitar gran quantitat de tcnics especialistes en Programari Lliure. Dentre les diferents especialitzacions que caldrien en lmbit dels SGBD destacarem la dadministrador de lSGBD (o DBA per les seves sigles en angls), la del Dissenyador Analista amb coneixements del programari concret, o la del programador en les seves diferents especialitzacions i variants prpies. El fet de que PostgreSQL incorpori moltes caracterstiques avanades s molt positiu per lmbit educatiu, ja que utilitzant aquest programari com a eina daprenentatge sassoliran uns coneixements que dutilitzar altres SGBD no es podrien consolidar. Aix, si una persona que hagi utilitzat aquest programari hagus dutilitzar un altre SGBD (Programari Lliure o comercial), no tindria mancances de coneixements, ja que nicament shauria dadaptar a les limitacions daquest altre sistema, o a les petites diferncies de sintaxi existents.


10

3.- Sentncies de manipulaci En aquest apartat no es pretn repetir el hi ha en els manuals de PostgreSQL, nicament es pretn fer un resum rpid de les sentncies ms habituals, amb les opcions que sutilitzen de forma ms usual. Per ordre ds en comenar a utilitzar PostgreSQL.

3.1.- Create Database Sutilitza per a crear la Base de Dades (BD). En fer la connexi amb lSGBD shaur de seleccionar la BD que sutilitza, i a partir daqu, quan es crea una taula, o es faci una consulta, es far sobre les dades que hi ha en aquesta. Una taula pot existir amb el mateix nom a diferents BD, ja que estan fsicament separades. Sintaxi bsica:

CREATE DATABASE nomBaseDades [ WITH LOCATION = 'cami_dacces' ]

Exemple:

CREATE DATABASE ProvesUOC;

3.2.- Create Table A linterior de la base de dades crearem taules per a emmagatzemar dades segons les seves caracterstiques. Els atributs de cada columna de cada taula definiran el tipus de dades que podran emmagatzemar. Es podran afegir clusules addicionals a la sintaxi de la creaci de la taula, per a especificar restriccions que shauran de complir en inserir, esborrar i modificar les dades. Sintaxi bsica:

CREATE TABLE NomTaula (nomColumna1 tipusColumna1, nomColumna2 tipusColumna2,.... );

On els tipus bsics que es poden utilitzar a tipusColumna poden ser: char(longitud) Cadena de carcters de longitud fixa. varchar(longitud) Cadena de carcters de longitud variable. Bool Pot contenir els valors booleans true/false. Date Per emmagatzemar la data (sense lhora). Timestamp Per emmagatzemar la data, hora i segons, amb

zona horria inclosa. Float4 Per emmagatzemar nmeros en coma flotant. Float8 Per emmagatzemar nmeros en coma flotant

(doble precisi)


11

Int2 Per emmagatzemar nmeros enters (32.768 a 32.767).

Int4 Per emmagatzemar nmeros enters (2.147.483.648 a 2147483647)

Els tipus addicionals que afegeix el propi PostgreSQL (entre daltres) poden ser: Money Per emmagatzemar quantitats monetries (2

decimals). Box(x1,y1, x2,y2) Per a emmagatzemar rectangles. Circle (centre, radi) Per a emmagatzemar cercles. Line (x1,y1, x2,y2) Per a emmagatzemar lnies. Point (x,y) Per a definir un punt a lespai. Poligon Per a emmagatzemar les coordenades de

polgons. Inet Per a emmagatzemar adreces IP. Sha de destacar que al tenir PostgreSQL com a caracterstica base la possibilitat de definir nous tipus, la llista es pot ampliar tant com convingui. En el moment de la creaci de la taula tamb es poden assignar caracterstiques (restriccions) que les dades han de complir. Si podem traspassar la feina de fer aquestes comprovacions bsiques a la mateix base de dades, ser impossible que per una errada de programaci o per una errada de manipulaci de dades ad-hoc, transgredim aquestes directrius, tot evitant inconsistncies de dades o violacions de les regles de negoci. Exemple:

CREATE TABLE Clients ( CIF CHAR(10) PRIMARY KEY, nomComercial VARCHAR(40), deute INT8, codiPostal CHAR(4)

CONSTRAINT credit CHECK (Deute < 1000));

Creem una taula amb les columnes necessries per a contenir les dades bsiques dels clients, i indiquem que el CIF ser la clau principal que sutilitzar per a localitzar a aquests (al indicar que s clau principal es crea automticament un ndex per accelerar laccs, i la restricci de que ha de ser unique), i que el possible deute dun Client ha de ser sempre inferior a 1000. Amb aquesta ltima restricci, assegurarem que en intentar fer una operaci que excedeixi aquesta xifra en sumar una quantitat, falli, i retorni aix lerror conforme est a mxim de crdit.


12

3.3.- Insert Into Amb aquesta clusula es poden inserir dades a les taules. s important comprovar el resultat de loperaci, ja que la base de dades pot retornar un codi derror, si sincompleix alguna de les restriccions indicades durant la creaci de la taula. Sintaxi:

INSERT INTO nomTaula (nomCamp1, nomCamp2, ...) VALUES (valorsPelCamp1, valorsPelCamp2, ....)

Sha danotar que els noms dels camps sn opcionals, si sinsereixen tants valors com camps t la taula (i en el mateix ordre). En cas el de no inserir dades a tots els camps, caldr especificar aquests, per identificar on shan de collocar les dades. Exemple:

INSERT INTO Clients VALUES ('G43014969', 'Associaci BlauCel', 25, '4301');

Es possible fer una inserci de dades en una taula, amb les dades recuperades directament duna consulta. Exemple:

INSERT INTO taulaProva SELECT * FROM unaAltraTaula;

(si no coincideixen el nombre de columnes, caldr especificar les columnes on han danar a parar les dades en la taula de destinaci).

3.4.- Select From Aquesta s la instrucci bsica de consulta de dades. Sutilitza tant per a recuperar informaci duna nica taula, com per a retornar dades relacionades de vries taules, tant generant aquesta sortida amb lobjectiu de ser visualitzat per pantalla, com per a formar part de lentrada de dades dun altre procs o consulta. Sintaxi:

SELECT nomCamp1, nomCamp2, .... FROM nomTaula WHERE nomCampX = literal;

Exemple:

SELECT * FROM Clients WHERE CIF = 'G43014969';


13

A lexemple bsic sobserva com es fa una consulta que retornar tots els camps, de tots els registres que compleixin la condici dequivalncia del DNI. En el cas de substituir lasterisc per uns noms de camps concrets de la taula, noms es retornarien aquests. Exemples de consultes complexes Una vegada vist lexemple bsic, sha de destacar que normalment es combinen diverses taules, per a tornar un resultat compost amb les dades daquestes. Els exemples que es mostren a continuaci, es fan considerant lestructura de taules segents:

CREATE TABLE persona (nom varchar(20) PRIMARY KEY, cognoms VARCHAR(40), edat INT, dni CHAR(10));

CREATE TABLE vehicle (marca VARCHAR(10) PRIMARY KEY, model VARCHAR(15), anyfabricacio INT, propietari CHAR(10));

Les dades que contindran aquestes taules per a fer les proves seran:

INSERT INTO persona VALUES ('Josep', 'Hernandez Sole', 25, '39123123T');

INSERT INTO persona VALUES ('Joana', 'Sic Sol', 37, '39321321F');

INSERT INTO persona VALUES ('David', 'Sanahuja Sevilla', 35, '37123321A');

INSERT INTO persona VALUES ('Jordi', 'Lluc Catal', 27, '39696969Z');

INSERT INTO vehicle VALUES ('Seat', 'Leon 1.9TDI', 2002, '39123123T');

INSERT INTO vehicle VALUES ('Suzuki', 'GSX 750', 1997, '39123123T');

INSERT INTO vehicle VALUES ('Opel', 'Vectra DTI S', 2001, '39321321F');

INSERT INTO vehicle VALUES ('Audi', 'S3 Sport', 2004, '37123321A');

INSERT INTO vehicle VALUES ('Fiat', '500', 1972, '7123123');

EXEMPLE 1 (consultes amb subconsultes):

SELECT persona.nom, persona.cognoms,

(SELECT distinct vehicle.propietari FROM vehicle WHERE persona.dni = vehicle.propietari)


14

AS Identificador FROM persona;

Sobserva que es fa una subconsulta que utilitza el resultat duna fila duna columna, per fer una nova cerca per a cada resultat. La subconsulta es realitza un cop recuperat el resultat de la taula principal, tot just abans de retornar la informaci. En aquest cas, s obligatori que la subconsulta noms retorni un resultat per a cada registre de la consulta principal. Tamb sobserva la utilitzaci de la clusula as, tot just desprs de la subconsulta. Amb aquesta clusula donarem nom a una columna que s generada a partir duna consulta, o dun clcul. Pel conjunt de dades de prova, el resultat retornat s:

Josep Hernandez Sole 39123123T Joana Sic Sol 39321321F David Sanahuja Sevilla 37123321A Jordi Lluc Catal

Sha de fer notar que en no tenir en Jordi Lluc cap cotxe, falla la subconsulta, obtenint un null en la posici on hauria danar el DNI del propietari del vehicle. EXEMPLE 2 (consultes amb subconsultes):

SELECT persona.nom, persona.cognoms FROM persona WHERE persona.dni IN (SELECT distinct vehicle.propietari

FROM vehicle);

En aquest exemple sobserva que la SELECT principal est restringida pels resultats que es troben a la SELECT que hi ha desprs del WHERE, o sigui, de totes les possibles persones, noms es retornen els DNI daquells que tenen algun vehicle. Resultat de la consulta:

Josep Hernandez Sole 39123123T Joana Sic Sol 39321321F David Sanahuja Sevilla 37123321A

Com que en Jordi Lluc no t cap vehicle (no apareix a lltima subconsulta), no sens retornar. EXEMPLE 3 (consulta amb combinaci de taules):

SELECT persona.nom, persona.cognoms, vehicle.marca FROM persona, vehicle


15

WHERE persona.dni = vehicle.propietari;

En aquest cas, combinem les taules persona i vehicle, per a relacionar cada un daquests amb el seu propietari, per tant, la cardinalitat ser dun o varis de la segona taula, per cadascun de la primera, si existeix a la de vehicles. Resultat de la consulta:

David Sanahuja Sevilla Audi Josep Hernandez Sole Suzuki Josep Hernandez Sole Seat Joana Sic Sol Opel

Sobserva que no apareix la persona que no t vehicle, i que la que en t dos, apareix per a cadascun daquests. EXEMPLE 4 (consulta amb combinaci de taules):

La mateixa consulta de lexemple 3, per amb la clusula JOIN, segons sintaxi estndard SQL92.

SELECT persona.nom, persona.cognoms, vehicle.marca FROM persona JOIN vehicle ON persona.dni = vehicle.propietari;

(retorna el mateix resultat que lexemple 3). EXEMPLE 5 (consulta amb combinaci de taules):

SELECT persona.nom, persona.cognoms, vehicle.marca FROM persona RIGHT JOIN vehicle ON persona.dni = vehicle.propietari;

Aquest cas es diferncia dels anterior per la clusula RIGHT del JOIN. Com sobserva en el resultat, la combinaci de taules es fa per vehicle, apareixent tots els vehicles, per no totes les persones. Juntament amb cada vehicle, apareix el seu propietari (o null si no en t). Resultat de la consulta:

David Sanahuja Sevilla Audi Josep Hernandez Sole Suzuki Josep Hernandez Sole Seat Joana Sic Sol Opel Fiat

EXEMPLE 6 (consulta amb combinaci de taules):


16

SELECT persona.nom, persona.cognoms, vehicle.marca FROM persona LEFT JOIN vehicle ON persona.dni = vehicle.propietari;

s el cas contrari a lexemple 5. Aqu sutilitza la clusula LEFT en el JOIN. Aix fa que apareguin tots els propietaris, i si en tenen, la marca del vehicle corresponent. Resultat de la consulta:

David Sanahuja Sevilla Audi Josep Hernandez Sole Suzuki Josep Hernandez Sole Seat Joana Sic Sol Opel Jordi Lluc Catal

EXEMPLE 7 (consulta amb combinaci de taules):

SELECT persona.nom, persona.cognoms, vehicle.marca FROM persona FULL JOIN vehicle ON persona.dni = vehicle.propietari;

Utilitzant la clusula FULL en el JOIN sens retorna tant els registres de la taula persones, com els de la taula vehicles. Si en fer-se la combinaci, no hi ha corresponena, ens apareixeran valors null all on faltin les dades. Resultat de la consulta:

David Sanahuja Sevilla Audi Josep Hernandez Sole Suzuki Josep Hernandez Sole Seat Joana Sic Sol Opel Jordi Lluc Catal Fiat

EXEMPLE 8 (consulta amb uni de dades):

De vegades pot ser convenient que els resultats de dues consultes suneixin per ser retornades com si fossin un nic resultat. Podrem voler aconseguir (amb les dades de prova) un llistat de noms i acrnims en una nica columna.

SELECT persona.nom FROM persona UNION

SELECT vehicle.marca FROM vehicle;


17

Resultat de la consulta:

Audi David Fiat Joana Jordi Josep Opel Seat Suzuki

3.5.- Update Sutilitza la sentncia UPDATE per a actualitzar les dades de les columnes dun o varis registres. Sintaxi:

UPDATE nomTaula SET nomColumna = valor WHERE condici;

Exemple:

UPDATE vehicle SET propietari = 37123321A WHERE propietari = 7123123 AND model = 500;

A lexemple estem substituint el DNI del propietari del vehicle, per un dels existents a la taula persona (tal com si fos erroni el valor del camp a vehicle). s important observar que es poden introduir subconsultes en la sentencia. En lexemple segent s mostra com recuperar el valor que sactualitzar, amb una nova consulta.

UPDATE vehicle SET propietari = (SELECT DISTINCT dni

FROM persona WHERE cognoms ILIKE '%lluc catal%' AND edat BETWEEN 20 AND 30)

WHERE marca = 'Fiat';

En aquest cas, es vol canviar el DNI del propietari de lnic vehicle que hi ha de la marca Fiat, pel duna persona que sabem que es diu Lluc Catal (sha utilitzat el ILIKE per ometre la distinci entre majscules i minscules), i que t entre 20 i 30 anys.

3.6.- Delete Sutilitza la sentencia DELETE per a esborrar un o varis registres duna taula. Sintaxi:


18

DELETE FROM nomTaula WHERE condicio;

Exemple:

DELETE FROM vehicle WHERE propietari = 37123321A;

Sha de fer notar que dutilitzar aquesta sentencia sense la condici, sesborraria tot el contingut de la taula ! Dhaver-se desborrar completament una taula amb molts registres, es pot utilitzar la instrucci TRUNCATE (que s especifica del PostgreSQL), ja que est optimitzada especialment per aquests casos. Exemple:

TRUNCATE vehicle;

3.7.- Sintaxi addicional de manipulaci dels resultats Desprs de la condici limitadora WHERE de les SELECT, es pot afegir les clusules: ORDER BY Sutilitzar el ORDER BY nomCamp1, nomCamp2, etc per a que sens mostri els resultats de la SELECT ordenats segons el camp, o camps especificats. GROUP BY Dutilitzar la sentencia dagrupaci GROUP BY nomCamp1, nomCamp2, etc, sagruparan les files que tinguin el nomCamp1 (i segents) igual, per a ser mostrades noms com una de sola. s imprescindible que noms apareguin com a camps a mostrar, els que hi han en aquesta clusula, ja que no s pot mostrar camps agrupats amb atributs diferents. LIKE En el cas de voler fer una cerca pel contingut dun camp, per no estar segur de que el literal de cerca sigui el contingut total daquest, es pot utilitzar el operador LIKE per a cercar els registres que en el camp especificat continguin la cadena literal. El signe tant per cent s el comod. En aquest cas, sespecificaria que es cerqus una paraula concreta, malgrat tingus altres carcters al davant i al darrera. Aix si fssim (segons les dades dexemple utilitzades a lapartat de les SELECT):

SELECT * FROM persona WHERE dni LIKE '%123%';

Sens retornaria com a resultat de la consulta:

Josep Hernandez Sole 25 39123123T David Sanahuja Sevilla 35 37123321A


19

En el cas de voler que la cerca no tingui en compte les majscules / minscules, es pot utilitzar el ILIKE (instrucci prpia del PostgreSQL):

SELECT * FROM vehicle WHERE propietari ILIKE '%123t%';

En el resultat sobserva que shan retornar dos registres, malgrat la lletra del seu NIF estigui en majscules. Resultat de la consulta:

Seat Leon 1.9 TDI 2002 39123123T Suzuki GSX 750 1997 39123123T

Expressions Regulars En el PostgreSQL es poden utilitzar expressions regulars en les condicions dacotament (no s una caracterstica estndard SQL). Aquests tipus dexpressions sn prou potents (i complexes) com per a requerir tot un ampli manual ds. Lexemple senzill i illustratiu consistir en fer una cerca dels usuaris que tinguin com a segon carcter del seu nom la lletra o:

SELECT * FROM persona WHERE nom ~'^.o'


Josep Hernandez Sole 25 39123123T Joana Sic Sol 37 39321321F Jordi Lluc Catal 27 39696969Z

EXPRESSIONS REGULARS Es construeixen seguint el segent patr: Inici: ~ indicaci dexpressi regular ~* indicaci dexpressi regular no sensible a les majscules/minscules !~ indicaci de negaci dexpressi regular !~* indicaci de negaci dexpressi regular no sensible a majscules/minscules Condicions: ^ inici $ final . qualsevol carcter [ccc] conjunt de carcters [^ccc] diferent al conjunt de carcters [a-z] rang de carcters [^a-z] diferent al rang de carcters ? zero o un carcter * zero o varis carcters + un o varis carcters | operador OR


20

SIMILAR TO Loperador SIMILAR TO avalua una expressi a cert o fals, depenent de si la cadena avaluada compleix amb el patr indicat. El funcionament de loperador s similar al LIKE, per en aquest cas el patr de cerca s interpretat com una expressi regular. Exemple 1:

SELECT * FROM persona WHERE dni SIMILAR TO '%(3_1+)%'


Josep Hernandez Sole 25 39123123T Joana Sic Sol 37 39321321F David Sanahuja Sevilla 35 37123321A

Dinteressar nicament els registres que comencin per 3 i que tinguin en la tercera posici el 1, shauria deliminar el primer smbol % a lexpressi de cerca.


21

4.- El llenguatge pl/pgSQL En el PostgreSQL es poden utilitzar diversos llenguatges per a escriure les funcions que sexecutaran com a procediments emmagatzemats. Entre ells hi ha el java, el C i el pl/pgSQL. En el cas dutilitzar el C, podrien sorgir problemes de portabilitat de canviar el maquinari que executa lSGBD (si es canvia de 32 a 64 bits, shauria de revisar tot el tractament dels numrics). En el cas delegir el java com a llenguatge procedimental per a executar les rutines a la base de dades, tindrem un ampli suport, per probablement el servidor patiria de problemes de rendiment. Lopci recomanada s la dutilitzar el llenguatge nadiu de lSGBD, el pl/pgSQL. El pl/pgSQL no s ms que lampliaci del conjunt dinstruccions de lSQL habitual, per a poder controlar el flux dels programes, poder utilitzar estructures de dades, variables, i a la fi, aconseguir totes aquelles altres funcionalitats prpies dels llenguatges tradicionals. Aquest apartat es presenta com la continuaci de la lectura de la guia de pg/plSQL inclosa en el manual del PostgreSQL, donat que sha considerat que els exemples sn molt bsics, i els addicionals trobats a internet no aportaven ms informaci que la que apareixia a la web oficial de PostgreSQL, o al manual oficial (habitualment amb lleugers retocs). En alguns casos es plantegen les funcionalitats de forma comparativa amb les delInformix-4GL.

4.1.- Els Procediments. Els procediments emmagatzemats sn anomenats realment funcions en el PostgreSQL. Al crear-los, aix shaur despecificar a la sintaxis. Aquests poden tenir tant parmetres dentrada com de sortida. Els parmetres poden ser de qualsevol tipus perms, i poden ser un o varis. El cos del codi de linterior del procediment pot anar entre comentes senzilles (entre les clusules AS i LANGUAGE), o entre els smbols $$. En el primer cas, cada vegada que aparegui una cometa a linterior daquest codi, shaur de doblar. Exemple:

CREATE OR REPLACE FUNCTION afegeixAmic (nomEntrada VARCHAR(20)) RETURNS VARCHAR(32) AS ' BEGIN RETURN ''Hola amic ''||$1; END; ' LANGUAGE 'plpgsql'; Sobserva que all on shan inserit cometes (lnia del RETURN), shan doblat. Tamb sobserva que per a fer la concatenaci de dues cadenes de text, shan dutilitzar les barres verticals. Per a executar un procediment sutilitza el select. En el cas de lexemple segent passem un valor al procediment, i esperem que es retorni un resultat:


22

SELECT * FROM afegeixAmic('Pere'); Dhaver de recollir el valor en una variable (des del interior dun procediment): temps := (SELECT FROM TempsTrajecte(horaentrada, horasortida)); En lloc de fer lassignaci es podria utilitzar la clusula into per a recollir el resultat. En el cas en que no es vulgui recollir el resultat que retorna la select shaur dutilitzar el PERFORM. Exemple: PERFORM ActualitzaTemperatures(Tarragona, 34); (en el cas de que per la lgica de negoci, lactualitzaci de la temperatura sigui un procs complex que sha dexecutar des dun procediment). De forma similar a altes llenguatges, el signe igual es reserva per a les comparacions, mentre que per a les assignacions sha dutilitzar els dos punt igual. Exemple:

CREATE FUNCTION .... DECLARE var1 INT; BEGIN var1 := 99; IF (var1 = varEntrada) THEN ... ...

A diferencia daltres SGBD a PostgreSQL no sinclou codi en els disparadors. Aquests han de cridar immediatament a un procediment emmagatzemat. Aquest fet es deu a que al poder utilitzar diferents llenguatges per a escriure la codificaci dels procediments, es fa una certa normalitzaci en determinar que a partir del disparador, el codi que sexecuta ja pot ser en el llenguatge que sindica en el mateix procediment (Informix si permet incloure les rutines SQL en els mateixos disparadors). En cridar-se un procediment emmagatzemat des dun disparador, els valors OLD i NEW passen per defecte en indicar-se a la capalera del procediment el tipus dentrada de dades (no cal, com en lInformix, passar els valors que necessitem al procediment, explcitament des del trigger). Exemple (considerant que el procediment s cridat des dun disparador):


23

CREATE OR REPLACE FUNCTION comprovarSou () RETURNS TRIGGER AS ' BEGIN IF (OLD.Sou > NEW.Sou) THEN RAISE EXCEPTION ''ERROR - El sou nicament es

pot incrementar!''; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; Pel mateix funcionament del pas de parmetres entre els disparadors i els procediments (sha dindicar explcitament que el tipus dentrada dun procediment s trigger), no s possible que un mateix procediment pugui ser cridat des dun disparador i des dun altra procediment. De ser necessari aix, shaur descriure dos procediments diferents. En el que s cridat des dun altra procediment, haur dindicar els valors dentrada que espera. Per a millorar la fiabilitat del codi, i suportar millor els possibles canvis de tipus, o de llargada dels camps de les taules, es pot especificar amb la clusula %TYPE que una variable determinada sigui del tipus dun camp determinat. Tamb sha dobservar que els valors que es reben dentrada als procediments, tindran com a nom el nombre dordre en que shan rebut (segons la capalera), amb un signe dlar anteposat. Exemple: CREATE FUNCTION p_trajecte (trajecte.data_e%TYPE, trajecte.data_s%TYPE) RETURNS INT AS ' BEGIN IF ($1 = $2) THEN ..... .......

.......

END; ' LANGUAGE 'plpgsql'; Sobserva que es reben dos parmetres (desprs sutilitzen amb el nom de $1 i de $2). Aquests seran del mateix tipus que el que t els camps data_e i data_s de la taula trajecte (res hagus impedit posar directament a la capalera (date, date) en lloc dutilitzar el %type). El tipus de dades ms complex que pot retornar una funci, s el de varies files de tipus registre (varies columnes, no coincidents en tipus amb els duna taula). En aquest cas shaur de declarar que retornarem un SETOF RECORD. En cridar-se una funci que retorna un SETOF RECORD shaur despecificar la seva estructura, o sigui, els camps i els tipus daquests amb la clusula AS. El noms dels camps que sespecifiquin, seran els dels camps que sens retornaran.


24

Exemple complert:

CREATE TABLE ciutats (ciutat VARCHAR(20), prov VARCHAR(20), temperatura INT);

INSERT INTO ciutats VALUES ('Tarragona', 'Tarragona', 15); INSERT INTO ciutats VALUES ('Valls', 'Tarragona', 17); INSERT INTO ciutats VALUES ('Reus', 'Tarragona', 20); INSERT INTO ciutats VALUES ('Barcelona', 'Barcelona', 30); INSERT INTO ciutats VALUES ('Mollet', 'Barcelona', 31);

CREATE OR REPLACE FUNCTION tempCiutatsProv(ciutats.prov%TYPE) RETURNS SETOF RECORD AS ' DECLARE actual RECORD; BEGIN FOR actual IN SELECT ciutat, temperatura FROM ciutats WHERE prov = $1 LOOP RETURN NEXT actual; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; Sobserva que a linterior del procediment sexecuta el codi que hi ha entre el LOOP i el END LOOP (aquest exemple s bsic) per a cada registre que compleix el criteri de la select. En fer la consulta shaur despecificar quina s lestructura del RECORD que es retornar (amb la clusula AS): SELECT * FROM tempCiutatsProv('Tarragona')

AS (ciutat VARCHAR(20), graus INT); El resultat retornat ser:

ciutat graus Reus 20 Tarragona 15 Valls 17

En el cas de que els valors a retornar coincideixin amb els dels camps duna taula, es pot indicar que els valors de sortida del procediment sn: SETOF nomTaula. Modificant lexemple anterior:


25

CREATE OR REPLACE FUNCTION tempCiutatsProv2(ciutats.prov%TYPE) RETURNS SETOF ciutats AS ' DECLARE actual ciutats%ROWTYPE; BEGIN FOR actual IN SELECT ciutat, prov, temperatura FROM ciutats WHERE prov = $1 LOOP RETURN NEXT actual; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; En fer la consulta: SELECT * FROM tempCiutatsProv2('Barcelona'); Es retornarien els segents resultats:

ciutat prov temperatura Barcelona Barcelona 30 Mollet Barcelona 31

4.2.- Els Cursors. Es pot aprofitar el fet que un procediment que retorna varis registres pot ser considerat com una taula, per a simplificar el codi dels programes. Es poden utilitzar els valors resultants com una taula al crear cursors i fer altres operacions. En lexemple segent crearem un cursor en un procediment. Dhaver-se dexecutar aquest des dun altra rutina pl/pgSQL es podria implementar lobertura daquest des de la mateixa, per tal com es fa, aquest pot ser cridat fins i tot des dun programa extern (en java mitjanant JDBC, per exemple) o des duna altra procediment fet en un altra llenguatge:

CREATE OR REPLACE FUNCTION ex_cursor (refcursor, ciutats.provincia%TYPE) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT * FROM tempCiutatsProv2($2) AS (ciutat VARCHAR(20), graus INT); RETURN $1; END; ' LANGUAGE plpgsql;


26

Tal com sens indiqui que sha creat correctament, podem obrir el cursor:

BEGIN; SELECT ex_cursor('cursortest', 'Tarragona'); La primera vegada sens retornar un valor conforme sha obert correctament. En demanar els valors dun en un amb les instruccions (lhaurem dexecutar diverses vegades); FETCH NEXT IN cursortest; sens retornar una ciutat i una temperatura cada vegada. Acabarem tancant el cursor amb la instrucci: COMMIT;


27

5.- ndexs. Els ndexs permeten optimitzar laccs a les dades duna taula, per una o vries de les columnes que t. Aquests consisteixen bsicament en una estructura de dades separada de la taula que indexa, fcilment recorrible (amb forma darbre habitualment), on cercar un valor sol costar log(n), i que relaciona el valor concret que se cerca, amb la seva posici dintre de lestructura on semmagatzemen les dades, per a poder accedir a aquesta directament. Amb la installaci per defecte del PostgreSQL, sincorporen tres tipus diferents dndex; els B-Tree, els R-Tree i els Hash. Habitualment, i si no sindica el contrari, PostgreSQL utilitzar el index B-Tree. No obstant sha de considerar que cada ndex t unes peculiaritats que el fan ms ptim en uns casos que en daltres, pel que sha de revisar els criteris dutilitzaci, per a assegurar ls adequat en cada cas. Tamb s important considerar que no tots els tipus dndex tenen ni el mateix rendiment, ni el mateix cost dexecuci, ni les mateixes possibilitats de concurrncia. Aquest ltim punt s especialment crtic en entorns en que hi ha un alt nombre dinsercions i modificacions, ja que tal com es veur, la inserci de noves branques o fulles per a mantenir larbre on semmagatzema lndex ben equilibrat, pot fer que de no tenir-se en compte, altres processos en curs puguin fallar per trobar-se registres blocats (en els accessos a les estructures de dades que contenen els ndexs encara no sutilitza el tipus daccs Multi Version Concurrent Control).

5.1.- B-Tree s un ndex dalta concurrncia, basat en els de Lehman Yao. Aquest tipus dndex s totalment dinmic, ja que no requereix ni de manteniments ni doptimitzacions peridiques. PostgreSQL utilitza aquest tipus dndex per defecte, ja que en casos genrics s el que pot donar uns millors temps daccs i a que s lnic que t suport per creaci de ndexs per ms duna columna (en un PostgreSQL installat de forma estndard, es poden utilitzar ndexs com a mxim de 16 columnes). Es pot utilitzar en fer cerques amb condicions de menor, major o dequivalncia a uns valors determinats.

5.2.- R-Tree Aquests ndex sn especialment eficients quan sutilitzen per a cercar formes geomtriques o punts a lespai. Estan basats en els treball de divisions quadrtiques den Atonin Buttman. Al igual que el B-Tree, aquests tipus dndex no requereixen ni de manteniment, ni doptimitzacions peridiques.

5.3.- Hash Aquests ndexs sn una implementaci de lalgorisme lineals Hash den Litwin, i no necessiten ni doptimitzacions ni de manteniments peridics (veure ltim punt daquest apartat).


28

Aquest ndex noms es pot utilitzar quan es cerca un valor concret, ja que saplica una funci sobre el valor del camp a buscar, per a trobar la seva posici en lestructura de dades on semmagatzema. s important que la funci hash faci que la distribuci de valors sigui uniforme i nica, ja que si a un resultat corresponen varis valors, shauran de fer recorreguts de cost lineal. La implementaci que es fa dels ndexs linear-hash al PostgreSQL (al menys fins a la versi 8.0) fa que aquests tinguin un rendiment baix (pitjor en accs a les dades que els b-tree), que siguin lents de creaci i regeneraci, i que pel sistema utilitzat en lenregistrament dels canvis, en cas de fallada del sistema puguin quedar inconsistents (havent-se de regenerar). Per tots els motius anteriors, la comunitat de desenvolupadors recomana noms utilitzar aquest tipus dndex per a fins acadmics (textual), i argumenta que no shan posat en estat deprecated, donat que en qualsevol moment, qualsevol desenvolupador pot recollir el testimoni per a millorar el seu funcionament.


29

6.- Pla dexecuci de consultes Una consulta pot tardar ms o menys temps en executar-se, depenent del disseny de les taules, les seves interrelacions, la quantitat de dades, de la planificaci en lordre dexecuci de les operacions que cal fer per a extreure les dades concretes de les taules on semmagatzemen, i daltres parmetres addicionals.

Per a verificar que una consulta sexecuta de forma ptima s necessari comprovar els passos que indica el planificador que es faran per a resoldre la tasca. Fent un EXPLAIN ANALYZE consulta, obtindrem una explicaci esquemtica, amb costos, de la consulta concreta. La interpretaci daquesta execuci sha de fer de les fulles (part inferior) a larrel, ja que les operacions dels nivells superior, utilitzen els resultats obtinguts pels processaments de les parts inferiors. El pla dexecuci retornat, no t perqu haver estat lnic que sha considerat, ja que una consulta pot tenir ms dun forma vlida dexecutar-se. En aquests casos, el planificador tria la de menor cost (segons les estimacions). En determinats casos, especialment si la consulta a analitzar inclou ms de N taules relacionades, lestimaci de quina s la millor possibilitat dexecuci pot tenir un cost dexecuci tant alt, que s preferible utilitzar el mtode Genetic Query Optimitzer (GEQO), per a obtenir el pla dexecuci millor estimat. Amb aquest sistema (basat en algorismes gentics), no sassegura que sutilitzar el millor mtode de tots, per si que el que sutilitzar ser una bona aproximaci a aquest, ja que durant lestimaci de les possibilitats, es fa evolucionar les alternatives a considerar, obtenint millors resultats que amb un anlisi aleatori. Dhaver-se dexecutar mltiples vegades una mateixa consulta, es pot obviar el temps que utilitza el planificador per a obtenir la millor forma dexecuci, dutilitzar-se una consulta preparada (segons es detalla a lapartat doptimitzacions - captol 6).

El client genera una consulta SQL i: - LSGBD la recull. - Comprova si sintcticament s

correcte. - La transforma en un arbre dexecuci. - Aplica la reescriptura si s necessari. - Planifica les possibles formes

dexecuci, i tria la de menor cost. - Executa cadascun dels nodes de larbre

en que sha descompost la petici SQL.- Es transmet el resultat al client.


30

s important adonar-sen que les estimacions de costs de les consultes que fa lSGBD parteixen de les estadstiques que t sobre el contingut de les taules (nombre de registres, quantitat de pgines ocupades, etc), i que aquestes estadstiques nicament sactualitzen en executar-se la instrucci analyze. (instrucci prpia del PostgreSQL, equivalent a update statistics de lSQL92). Cal remarcar que la base de dades no actualitza aquestes estadstiques automticament, pel que s imprescindible, per tal de mantenir un bon rendiment, lexecuci de les mateixes de forma peridica. En cas contrari, lincrement del nombre de registres duna taula (per exemple), podria fer que el planificador de lSGBD decids fer un recorregut (de cost lineal) sobre aquesta, en lloc de fer-ho utilitzant un ndex disponible. Els 19 operadors que poden aparixer durant el processament duna consulta, sn:

6.1.- Seq Scan: s el operador ms bsic. Consisteix en fer una lectura seqencial dels registres duna taula. Aix per a cada fila llegida savaluar la condici de cerca, tot afegint aquesta al conjunt de sortida, si la condici es compleix. Si lexpressi de cerca que sutilitza, s dequivalncia sobre un registre que no pot tenir repeticions (UNIQUE), es pot fer acabar el recorregut desprs de trobar la primera correspondncia. Loperaci s de cost lineal, i ser la utilitzada de no existir cap ndex a la taula que pugui ser utilitzat per a accedir segons la consulta feta, o en el cas de que sestimi que t menys cost que les altres opcions. En taules amb pocs registres pot ser ms ptim aquest accs, que un dalternatiu per ndex, ja que tota la informaci estar ubicada en un sol bloc, i es realitzar en una nica E/S de disc. Sha de considerar que en utilitzar maquinari modern, el cost de llargues lectures seqencials pot ser ms baix que el de mltiples lectures distribudes per tot el disc (pel temps emprat pels desplaaments dels capals daquest), pel que shauria de considerar forar aquest mtode en taules de no molts registres (fins a un centenar), sobretot si tenen mida petita. En el cas que la consulta no impliqui altres operacions que facin manipular altres taules, o facin una ordenaci dels resultats posteriorment (per ex.), la primera fila ser tornada dimmediat (mentre la resta de la consulta sacaba dexecutar), pel que en molts casos (en utilitzar-se en un cursor), dhaver-se de tractar tota una taula, ser lopci ms rpida.

6.2.- Index Scan: Aquest tipus daccs sutilitza en el cas dhaver daccedir a un conjunt de registres, i poder utilitzar un ndex per a accedir al primer dells. Si la consulta especifica un rang de valors, el primer accs t cost log(n) (si ndex tipus B-Tree per ex.), i la resta lineal entre la selecci. A diferncia de Seq Scan, que retorna els valors segons lordre en que es troben a la taula, Index Scan en utilitzar un ndex, retorna aquests ordenats pel mateix.


31

Aquesta tcnica noms sutilitzar si el tipus dndex utilitzat s B-Tree, R-Tree o GiST (Generalized Search Tree), per no HASH, ja que amb aquest ltim noms s poden localitzar registres individuals, per no es possible fer un recorregut, ja que donat el propi concepte daquest ndex, els registres no tenen vincles entre ells.

6.3.- Sort El operant sort sutilitza (tal com el seu nom indica), per a ordenar un conjunt de registres, per la columna o columnes indicades. Hi han diferents mtodes dordenaci, i tant es pot fer loperaci en la memria principal com sobre disc (la decisi de fer-ho utilitzant un o laltre suport es pren automticament segons el volum de dades). s important tenir en compte que el conjunt resultant de loperaci sort noms es retorna quan sha executat tot lalgorisme dordenaci, pel que shauria dintentar ajornar lesmentada operaci (en el procs de planificaci de la consulta) fins a tenir un conjunt de dades mnim. La operaci dordenaci no noms sutilitza quan sespecifica que es vol el resultat ordenat, ja que per a realitzar altres operacions internes (tal com interseccions i unions), tamb cal que sexecuti.

6.4.- Unique Amb aquest operador seliminen valors duplicats dun conjunt de dades. Es pot utilitzar, per exemple, en el cas de voler retornar un nic registre de cada categoria, malgrat apareguin diverses dades de cadascuna daquesta. Aix, sempre sexecutar en especificar la clusula DISTINCT en una consulta, o en eliminar duplicats per a fer una UNION. El cost ser lineal, pel conjunt de registres implicats en loperaci.

6.5.- Limit Aquest s loperador que sutilitza per a obtenir un conjunt de resultats finit, segons el valor especificat. Aix, sutilitzar aquesta funci quan vulguem que noms sens retorni els primers n registres duna consulta. La poda que fa aquest operador sexecuta en una de les ltimes etapes de la consulta, ja que de demanar els primers n registres duna operaci ordenada, en el cas de fer-ho en una primera etapa, no obtindrem els primers resultats del producte ordenat, tal com sesperava. En el cas en el que lordre no sigui important, es retornaran els primers resultats, sense que sigui necessari lexecuci de tota la consulta. El cost dexecuci que afegeix aquesta clusula a una consulta s negligible, ja que aquesta sha dexecutar normalment, per passar a eliminar desprs els resultats sobrers en fer la sortida.

6.6.- Aggregate Loperador aggregate sutilitza en el cas de que la consulta tingui operadors que retornin resultats calculats, a partir dels valors de les columnes.


32

Aix, en el cas de que shagi de calcular la mitjana aritmtica dun conjunt de valors, desprs de fer lagrupaci daquests segons els criteris especificats, sexecutar el aggregate per a dur loperaci dobtenci de la mitjana.

6.7.- Append Aquest operador fa una agregaci dels resultats de dues o ms consultes, per a que el resultat daquestes apareguin com un dnic. El cost total de loperaci ser el de la suma de les suboperacions que sexecuten i normalment sutilitzar en realitzar una UNION entre els resultats de vries consultes. s imprescindible que el resultat que retornen les consultes siguin del mateix tipus, i tinguin el mateix nombre de columnes.

6.8.- Result Aquest tipus doperaci sanotar com a execuci, quan la consulta no tingui que retornar dades de cap taula, per estar-se executant una acci que retorna una dada de lentorn o generada (la data, per exemple), o en el cas en que a la clusula WHERE sutilitzi com a condici boolena, per a retornar, o no, un resultat.

6.9.- Nested Loop s loperaci que sutilitza en combinar dues taules, partint duna delles. A partir de la recuperaci de cada registre de la taula principal, se cerca (mitjanant un ndex, si existeix), el registre corresponent (segons la condici establerta), a laltra taula. La cardinalitat dependr del nombre de registres que existeixin a la segona taula, per a cadascun dels que apareixen a la primera, per en cap cas, apareixeran en el resultat els registres que apareguin a la segona, sense correspondncia amb la primera. Aix, aquesta operaci sutilitzar en el cas dels LEFT JOIN, per no en el cas dels RIGHT JOIN o dels FULL JOIN.

6.10.- Merge Join Sutilitza quan sha de fer una JOIN de dues o ms taules. El funcionament consisteix en recuperar tots els registres implicats en la condici, segons les claus especificades, per passar a fer loperaci dextracci combinada dels registres daquesta relaci, malgrat algun dells noms existeixi en una de les taules. La operaci de Merge Join normalment es realitzar desprs de que shagi fet un Index Scan o un Seq Scan de les dues taules i un Sort dels resultats previs a la consulta que retornar una cardinalitat en la que existiran els registres coincidents, i els no coincidents, tant de la primera com de la segona taula.

6.11.- Hash Join Aquest operador sutilitza en fer una INNER JOIN, un LEFT OUTER JOIN o un UNION, i no requereix que les taules estiguin ordenades, ni que tinguin cap ndex creat amb anterioritat. En utilitzar-se el Hash Join, en primer lloc es crea una taula temporal,


33

creant un ndex hash per la columna que sutilitza per fer la combinaci amb laltra taula. Un cop fet aix, es pot fer una lectura dels registres de la segona taula, per accedir directament pel ndex hash creat a la primera, per trobar els registres corresponents.

6.12.- Group El operador dagrupaci sutilitza exclusivament quan sespecifica la clusula GROUP BY a la condici de la consulta. Per a poder portar-se a terme, cal que els registres que intervenen en aquesta operaci estiguin ordenats (pot fer augmentar considerablement el temps de procs).

6.13.- Subquery Scan i Subplan Aquestes dues operacions es poden obviar al veure-les en larbre dexecuci duna consulta, ja que no afegeixen cost a la mateixa, i sn de funcionament intern. La de Subquery Scan sutilitza per a obtenir els resultats de dues consultes que retornen el resultat conjuntament, donat un UNION, mentre que el Subplan sutilitza en el cas de haver dexecutar una subconsulta.

6.14.- Tid Scan s una operaci que no sol ser utilitzada, ja que sha de forar una consulta especfica per a que aparegui. El Tid (tuple idenficator), s un nombre que lSGBD emmagatzema en cada registre. Consta del numero de bloc on est emmagatzemat el registre, i la posici del registre concret en aquest. En aquesta operaci daccs, de cost 1, sutilitza lidentificador nic ctid de cada registre, per a recuperar de forma rpida i unvoca un valor. Es poden aconseguir optimitzacions en laccs a les dades de guardar el valor ctid, en el cas dhaver daccedir a un valor dun registre per a operar amb aquest, i posteriorment haver-lo dactualitzar en el registre original. Ser molt ms rpid accedir pel ctid, que pel ndex! Exemple de cas ds del Tid Scan:

select * from trajecte where ctid = (select ctid from trajecte where cotxe = 'B-0000-TF' and horaentrada = '21/08/2003 03:34:59');

Al fer lexplain plan sens indicar:


34

Tid Scan on trajecte (cost=3.89..7.91 rows=1 width=77) Filter: (ctid = $0) InitPlan

-> Index Scan using idx_cotxe on trajecte (cost=0.00..3.89 rows=1 width=6) Index Cond: ((cotxe)::text = 'B-0000-TF'::text) Filter: ((horaentrada)::text = '21/08/2003 03:34:59'::text) La representaci grfica de laccs mitjanant el ctid s ladjunta:

6.15.- Materialize Tal com el seu nom indica, aquesta operaci materialitza un conjunt de dades en una taula temporal. Aix es duu a terme quan loptimitzador calcula que aquest procs ser ms rpid que no pas haver de fer repetides consultes dalt cost al llarg duna operaci (en molts casos en fer Merge Joins).

6.16.- Setop Intersect, Setop Intersect All, Setop Except, Setop Except All Sn operacions que noms sexecutaran en els casos dutilitzar la instrucci Intersect o Except en les seves diverses variants. Loperaci sexecutaria en ltim terme, ja que primer sexecutarien els Seq Scan o accs per ndex necessaris per a resoldre les consultes independents, per passar desprs a fer-se lappend, tot acabant (si no hi ha ordenacions o altres operacions de format de sortida) amb el Setop corresponent.


35

7.- Optimitzaci fsica

7.1.- Consultes preparades Per a executar una consulta repetitiva de la forma ms eficient possible, hi ha lopci devitar la fase danlisi i doptimitzaci de la consulta per part del planificador. Aix noms ser dutilitat si la mida de les taules, les relacions entre aquestes i els ndex (entre daltres) es mantenen iguals entre les diferents execucions, ja que de canviar aquests parmetres de forma substancial, la planificaci inicial de la consulta podria ja no ser ptima. Per fer una consulta preparada, caldr emmagatzemar-la amb un nom. Desprs noms caldr fer la crida corresponent per aconseguir la seva execuci immediata. Exemple: Tenim una taula clients, i volem tenir una consulta preparada per que ens retorni els registres que tinguin un nom determinat. Crearem la consulta preparada:

PREPARE CPrep_Clients (varchar(20)) AS SELECT * FROM client WHERE nom = $1;

I desprs la podrem executar:

EXECUTE CPrep_Clients('Francio');

Al fer lEXPLAIN ANALYZE de lexecuci daquesta consulta preparada, o de la sentencia equivalent:

SELECT * FROM client WHERE nom = 'Francio';

comprovarem que indica que mentre la primera triga 1482.000 ms, la segona triga 1533.000 ms. Restant un temps de laltra, obtenim que sestalvien els 51ms que triga la fase danlisis en decidir com sha dexecutar!


36

7.2.- Creaci de cluster En utilitzar un ndex per fer laccs a una taula, ens trobarem que aquest est ordenat segons el camp pel que sha creat, per que en fer laccs als registres de la taula corresponent, aquests no estaran ubicats consecutivament en les pgines de la BD, pel que shauran de fer mltiples accessos al disc.

Sobserva que les dades estan emmagatzemades en 5 pgines fsiques de la BD, i que si noms es pot tenir una pagina a la memria cau simultniament, per a recuperar-ho tot, caldr fer laccs a un total donze pagines!


37

En lexemple del grfic superior sobserva que per a recuperar seqencialment les dades de la taula (pel ordre del camp ndex cotxe), sha daccedir a diferents pgines aleatriament. Funcionant aix lSGBD ha de renovar constantment les dades de la cache (hi ha pocs encerts), incrementant-se per tant, el nombre daccessos fsics al sistema demmagatzemament. De tenir les dades de la taula ordenades pel mateix camp pel que sha creat lndex, en fer una lectura seqencial, noms shauria de carregar una vegada cada pgina de dades des de la BD, optimitzant les lectures, tot aconseguint uns temps dexecuci menors. PostgreSQL implementa la creaci de CLUSTERs, per a aconseguir precisament que lordre de les dades emmagatzemades, coincideixin amb les dun ndex determinat. Dutilitzar taula trajecte (utilitzades a lapartat de benchmarks), primer caldr crear un ndex per la columna per la que es voldr crear el cluster:

CREATE INDEX idx_cotxe_trajecte ON trajecte(cotxe);

Desprs crearem el cluster:

CLUSTER idx_cotxe_trajecte ON trajecte;

En tornar a executar la consulta del primer exemple, ens trobarem amb un accs similar a:

Es important anotar que lordenaci de les dades noms es fa durant el procs de creaci del Cluster, pel que en afegir o modificar files, sanir perdent eficcia en els accessos, tornant en el pitjor dels casos a la situaci inicial.

En aquest exemple seguim tenint un total de 5 pgines fsiques amb dades, per per a recuperar-les totes noms caldr fer 5 accessos al sistema demmagatzemament!


38

Per a regenerar lordenaci que es fa durant el procs de creaci del Cluster, es pot fer:

CLUSTER nom_taula;

De voler-se incloure aquesta funci de regeneraci dels ndexs, en un procs automtic (en hores de baixa activitat), tamb es pot indicar que es regenerin tots els Cluster existents, executant senzillament:

CLUSTER;

Lefecte dutilitzar la clusteritzaci, s similar a fer la seqncia segent:

SELECT * INTO TABLE nova_taula FROM taula_vella ORDER BY cotxe;

Per passar desprs a esborrar taula_vella, i renombrar nova_taula amb el nom antic. En el cas dutilitzar la instrucci especfica, assegurarem si en un futur es t en compte lexistncia del cluster en executar-se el pla de consulta, lanalitzador tri lalternativa ms ptima.

7.3.- Analyze i els plans dexecuci Sutilitza Analyze (o Analyse) per a fer que lSGBD recopili informaci de les taules, ocupacions, fragmentaci, existncia i tipus dndex (entre daltres), per a realitzar les estadstiques que permetran desprs al planificador triar el millor mtode dexecuci de les consultes. En lapartat de benchmarks es fa referncia a les diferncies en el temps dexecuci duna mateixa consulta (veure tamb Annex III), de fer-se abans o desprs dexecutar el Analyze. Els diferents camins dexecuci es poden observar en el grfic A i B (pgina segent). Els dos plans dexecuci corresponen a la planificaci de la consulta: SELECT client.DNI, teletac.Contracte, associar.Teletac, cotxe.Matricula, trajecte.PeatgeEntrada, trajecte.HoraEntrada FROM client, teletac, associar, trajecte, cotxe WHERE client.DNI = teletac.Client AND teletac.Contracte = associar.Teletac AND associar.Cotxe = cotxe.Matricula AND cotxe.Matricula = trajecte.Cotxe AND client.Nom = 'Maria' ORDER BY matricula;

Estu

di i

aval

uaci

de

les

func

iona

litat

s de

Pos

tgre

SQL

Iv

o Pl

ana

Vallv

39

A

B


40

El primer pla dexecuci s el que lSGBD ha fet no tenint actualitzades les estadstiques de la base de dades. En utilitzar lanalyze i tornar a repetir la consulta, el planificador ha triat una altre cam en el procs de planificaci de lexecuci de la mateixa. El temps dexecuci, segons lentorn descrit en lapartat de Benchmarks, passa dels 23,7 segons inicials als 2,4 segons, degut bsicament a que en el cas B no sexecuta el pas dordenaci dels registres de la taula trajecte (de 459.267 registres!). Amb aquest exemple es constata que shauria de planificar lexecuci de la instrucci Analyze peridicament. El fer-ho en moments de poca activitat de lSGBD pot ser important, ja que depenent de la mida de les taules i daltres factors, el procs pot ser costs en temps i en utilitzaci de maquinari. El detall textual dels plans dexecuci de la consulta de prova es poden consultar a lannex II. A lannex III es troba el pla dexecuci ptim, un cop fetes les possibles millores a la BD.

7.4.- ndex calculats En els casos en que shagi daccedir freqentment a una taula utilitzant una expressi, pot ser ptim utilitzar un ndex calculat. De fer-ho aix, en fer-se insercions o modificacions a un dels camps implicats en el ndex calculat, el valor daquest sactualitzar, i podr ser utilitzat posteriorment a les consultes per accedir rpidament als registres dinters. Exemple:

CREATE INDEX idx_nom ON persona (UPPER(nom));

A partir daquest moment, en fer una cerca en majscules pel camp nom de persona, sutilitzar aquest ndex (en el cas que el nombre de registres de la taula sigui prou gran, com per a ser ptim). De consultar el contingut duna cpia de seguretat feta amb el pg_dump (punt 12 de la memria), sobservar que les dades generades durant la creaci de lndex no apareixen. A la cpia noms constar la instrucci de creaci de lndex, per a que tal com es recuperi aquesta instrucci, les dades associades a aquest es torni a generar (la copia de seguretat podria ocupar menys espai que locupat pels fitxers de dades i ndexs de la BD!). A lannex I sha preparat un exemple complert dndex calculat, basat en un possible cas real.

7.5.- ndex parcials Els ndex parcials sn aquells que es creen per a ser utilitzats noms per accedir a un determinat conjunt de dades duna taula. Per a ser utilitzats pel planificador daccs, el predicat que sutilitza en la consulta ha de coincidir amb lutilitzat per a crear lesmentat ndex.


41

Exemple: En el cas dhaver daccedir a un volum important dinformaci, segons un camp on consta lany, es podria crear un ndex parcial, amb la segent instrucci:

CREATE INDEX idx_trajecte_any2003 ON trajecte (horaentrada) WHERE SUBSTRING(horaentrada FROM 7 FOR 4) = '2003';

En fer lexplain de la segent consulta:

SELECT * FROM trajecte WHERE SUBSTRING(horaentrada FROM 7 FOR 4) = '2003';

comprovarem que sens indicar que sutilitza lndex per a fer laccs a les dades:

Index Scan using idx_trajecte_any2003 on trajecte (cost=0.00..8234.22 rows=2297 width=76) Filter: ("substring"((horaentrada)::text,7,4)= '2003'::text)


42

8.- Model de concurrncia Les sigles MVCC sn una constant en buscar qualsevol tipus dinformaci relacionada amb el bloquejos de taules i registres, i la simultanetat daccessos en el PostgreSQL. El Multi-Version Concurrency Control s el mecanisme que implementa aquest SGBD per augmentar el rendiment de les consultes i escriptures, tot evitant haver dutilitzar els mecanismes de bloqueig de registre i/o de taules. Lobjectiu s que les consultes i les escriptures a la BD no interfereixin entre si. Tal com es diu en el propi manual ds, la finalitat s que els escriptors mai hagin desperar als lectors, i que els lectors mai hagin desperar als escriptors. Les bases de dades que no utilitzen aquest mecanisme de concurrncia, asseguren que les dades tinguin una integritat ACID mitjanant els mecanismes tradicionals de bloquejos de registres;

- En fer una lectura, es comprova que el registre no sestigui modificant, o no estigui blocat per una altra transacci. Si s aix, saturaria fins que queds alliberat.

- En fer una escriptura, una modificaci, o un esborrat, els registre es marquen com a blocats per a que cap altra procs el pugui utilitzar mentre es porta a terme una de les tasques enumerades.

Procedint daquesta manera la BD assegura la consistncia esmentada, ja que es compleixen les condicions ACID en les transaccions:

- Atomicitat: Cal que totes les operacions duna transacci sexecutin completament, o en cas contrari, que no sexecuti cap en absolut.

- Consistncia: Cal que les transaccions no violin cap de les regles dintegritat relacional establertes a la base de dades.

- Isolaci: Cal que mentre sexecuta una transacci, altres processos no puguin obtenir una visi parcial de les diferents modificacions que es poden fer dels registres.

- Durabilitat: Les dades han de perdurar, fins i tot en cas de fallada de maquinari o de programari.

Les bases de dades que no implementen el MVCC tenen fortes penalitzacions en el rendiment en executar moltes transaccions concurrentment, ja que moltes de les que hi haur a la cua de pendents dexecuci, hauran desperar per a processar-se a que salliberi alguna fila blocada per un altra procs (o pot haver-se de repetir, en fer lexecuci i no poder recuperar una fila determinada per aquest mateix blocatge). Aquest mtode tradicional, tamb sanomena dexecuci serialitzada, ja que cada transacci ha desperar (en part) a que acabi lanterior. s evident que amb aquest mtode, lampliabilitat futura de la base de dades es pot veure compromesa, ja que malgrat saugmentin els recursos del maquinari (processadors, memria, etc) el rendiment no augmentar en proporci a aquesta major potncia. Els SGBD que sha pogut verificar que utilitzen el mecanisme MVCC per a millorar la concurrncia sn: Oracle, Interbase i PostgreSQL. Totes les altres (incloses DB2 i MySQL), utilitzen la serialitzaci i bloquejos.


43

8.1.- Funcionament MVCC Conceptualment sembla complicat que la BD pugui tenir constncia per a cada transacci, de quin s lestat de tots els registres que puguin intervenir en la mateixa. La soluci que sutilitza, a ms de ser elegant, s rpida, segura i senzilla: El sistema MVCC utilitza una marca de temps en cada transacci de modificaci de files, per que quedi en el registre implicat la marca identificativa de la versi del registre. En fer una de les esmentades operacions de modificaci, la versi del registre que sha modificat no sesborra. A partir del moment de lesmentada modificaci, existiran dos (o ms) registres a la base de dades. Cadascun tindr una marca de temps que correspondr a la transacci que lha modificat. Funcionant daquesta manera, a la BD hi hauran vries versions dun mateix registre, cadascun amb la marca de temps del moment del seu canvi. s important notar que en fer la modificaci dun registre, lespai que ocupa aquest a la BD queda doblat. Noms en fer una neteja especifica (amb la instrucci VACUUM), salliberar lespai ocupat per les versions velles dels registres. Com que PostgreSQL crea una nova cpia del registres que sest modificat, els altres usuaris poden estar utilitzant els valors originals, mentre no es consolidin (commit) les noves dades. Les consultes simultnies de tipus READ COMMITTED veuran dimmediat les noves dades, mentre que les que es facin en mode SERIALIZABLE, seguiran executant-se correctament, tot utilitzant les dades de la versi vella del registre. En el cas de desfer la transacci (rollback), les consultes en curs no es veuran afectades. Lavantatge daquest funcionament consisteix en el seguiment que es fa de les versions de les files, evitant els conseqents blocats a nivell de taula o de fila (malgrat aix, aquest tipus de blocats concrets existeixen, i es poden utilitzar de necessitar-se). El mateix sistema que identifica les versions dels registres sutilitza en el mdul de PostgreSQL time travel. Aquest permet fer consultes tot indicant el moment determinat en el temps en que es volen obtenir els resultats. Com que en aquest cas es mantenen totes les versions dels registres modificats, lSGBD tindr en compte el time-stamp daquests, per resoldre loperaci segons les dades existents en moment demanat donat. Una altra avantatja del MVCC s que es poden fer cpies de seguretat en calent, ja que en comenar la cpia, tindrem un ID de transacci, pel que noms caldr emmagatzemar els registres amb ID inferior, per a tenir una versi de la BD consistent salvaguardada.

8.2.- Exemples MVCC vs blocat de registres Per a comprovar la consistncia ACID i els avantatges del MVCC, sexecutaran sobre la mateixa BD, un conjunt de tres proves, tot intentant reproduir la simultanetat daccessos a lSGBD per part de varis clients. Aquestes proves actualitzaran i consultaran un conjunt redut de registres, tot buscant la coincidncia en els accessos, i aquells casos que es consideren crtics. Per a comparar les diferncies del MVCC vers un model de concurrncia per serialitzaci, es faran les mateixes proves amb PostgreSQL 8.03 i Informix 7.33.


44

Prova A Amb aquesta prova sintentar verificar el correcte allament entre dues transaccions. Sinserir un registres des duna delles, i aquest no ha daparixer en laltra, almenys fins que no es tanqui la transacci des de la que sha afegit el registre. PostgreSQL 8.03

Sobserva que:

- Primer es crea la taula que sutilitzar per a fer les proves (lnies 1 a 5). - Sengega la primera transacci des de la cnsola 2. - La inserci es fa des de la cnsola 1 (lnia 11). - Les consultes que es duen a terme des de la cnsola 2 no retornen la dada

inserida des de la cnsola 1. - Malgrat es fa el commit a la cnsola 1 (lnia 15), el nou registres no apareix a la

cnsola 2 (lnia 17). - Noms en tancar la transacci de la cnsola 2, i obrir una de nova, es retorna

el nou registre. Amb aquesta prova sobserva que les operacions lectores no han desperar les escriptores, i que al llarg de totes les transaccions, aquestes treballen amb les dades que havien en el moment de comenar la mateixa.


45

Informix 7.33

Sobserva que: - En primer lloc es crea la taula que sutilitzar en les tres proves, amb el mode

de blocat per registre (la BD sobre la que es crea la taula s de tipus ANSI). - Sobra en primer lloc una transacci des de la cnsola 2. - Des de la cnsola 1 sinsereix un registre (lnia 23). - Des de la cnsola 2 es fa una consulta a la taula, i es queda en mode despera

fins que no es fa el commit a la cnsola 1. - Des del mateix moment en que sha comitat a la cnsola 1, es pot tornar a fer

una consulta des de la transacci de la cnsola 2, i malgrat aquesta hagi comenat en primer lloc, ja apareix el registre inserit des de la cnsola 1.

Diferncies: Lallament entre els dos SGBD s completament diferent, ja que mentre amb PostgreSQL la transacci utilitzar les dades que hi havien en el moment en que aquesta ha comenat, en Informix sutilitzaran totes aquelles dades que estiguin comitades. En cap cas lSGBD PostgreSQL ha tingut que introduir estats despera per a poder executar les transaccions correctament.


46

Prova B La prova B s similar a la A, per canviat lordre en que sinicien les transaccions de la cnsola 1 i 2. Sutilitza la taula creada a la prova A, inicialment buida. PostgreSQL 8.03

Sobserva que:

- La primera transacci sinicia des de la cnsola 1. - La inserci de la lnia 5 es fa una vegada iniciada la transacci de la cnsola 2. - Malgrat sha comitat a la cnsola 1, a les select de les lnies 7 i 11 no es

retorna cap registre. - Noms un cop sha tancat la transacci en curs, i sha iniciat una de nova,

apareix el nou registre.


47

Informix 7.33

Sobserva que:

- La primera transacci sinicia a la cnsola 1, en mode serialitzable, i amb espera en cas de blocat de registre.

- La inserci es fa des de la cnsola 1, i un cop fet el commit en aquesta, ja es pot utilitzar el nou registre des de la cnsola 2 (tal com surt de lestat despera ja el retorna lnia 20).

Diferncies:

- Al igual que a la primera prova, sobserva que mentre PostgreSQL treballa amb les dades que existeixen quan sinicia la transacci, Informix utilitza les que hi han en el moment comitades.

- En el PostgreSQL, els processos lectors no han desperar als escriptors (no sentra en cap estat despera), tal com sindica en les especificacions del MVCC, pel que la concurrncia ser ms elevada, havent menys interferncies entre processos.


48

Prova C A la darrera prova sintentar comprovar com es comporta el MVCC en els casos de coincidncia en lactualitzaci de registres. Sha de tenir en compta que sutilitza la taula creada a la prova A, i que inicialment existeix el registre que sobserva al final de la prova B. PostgreSQL 8.03

Sobserva que:

- La primera transacci sinicia a la cnsola 1. - Es fa una inserci des de la cnsola 1, desprs de que sinici la transacci de la

cnsola 2. - Des de la cnsola 2 sactualitza el contingut del registre que existeix inicialment

a la taula. - Des de la cnsola 1 es recupera el contingut de la taula, des del seu punt de

vista, i es comprova que existeix el registre inicial, i el que sacaba dinserir. - Des de la cnsola 2 tamb es fa una consulta del contingut de la taula, i

sobserva que el registre sha actualitzat correctament. - Sintenta actualitzar el registre que existia inicialment des de la cnsola 1, i

sentra en estat despera, donat que es detecta que ha estat actualitzat desprs diniciar la transacci.

- En fer el commit de la cnsola 2, la primera surt de lestat despera, i en fer el commit, en lloc de comitar, fa un rollback per avortar tot el procs (eliminant-se la dada que shavia inserit).

- El procs que ha aconseguit actualitzar el registre, ha estat el que primer ho ha intentat, malgrat la transacci no hagu comenat en primer lloc.


49

Informix 7.33

Sobserva que:

- La primera transacci sinicia des de la cnsola 1. - Malgrat que en crear la taula sha especificat lock mode row, en fer la inserci

de la lnia 13, lactualitzaci de la lnia 15 entra en estat despera. - Noms en fer el commit a la cnsola 1, loperaci que ha quedat latent

continua. - Al fer la consulta final, sobserva que sha porta a terme la inserci i

lactualitzaci (lnia 34 a 36). Diferncies: Donades les diferncies en el blocat de registres, el resultat final no s el mateix. Mentre que al PostgreSQL avorta (correctament) tota una transacci, per haver-se trobat un registre que sutilitza modificat (des del moment en que sha comenat la transacci), Informix no ho fa, ja que bloca la taula en el moment de fer la inserci a la lnia 13, i no permet que es faci cap operaci amb aquesta fins que es fa el commit (lnia 22). En aquest cas, lordre de les operacions si s important, i shauria de fer blocats explcits, de voler un comportament determinat.

Estudi i avaluaci de les funcionalitats de PostgreS

38401tfc

Documents

Transcript of 38401tfc