| PostgreSQL | |
|---|---|
| Developed by | PostgreSQL Global Development Group |
| Latest release | 8. A software developer is a person or organization concerned with facets of the software development process wider than design and coding a somewhat broader scope of A software release is the distribution whether public or private of an initial or new and upgraded version of a Computer software product 3. 1 / March 18, 2008[1] |
| Written in | C |
| OS | Cross-platform |
| Genre | ORDBMS |
| License | BSD license |
| Website | http://www.postgresql.org/ |
PostgreSQL is an object-relational database management system (ORDBMS). Events 37 - The Roman Senate annuls Tiberius ' will and proclaims Caligula emperor 2008 ( MMVIII) is the current year in accordance with the Gregorian calendar, a Leap year that started on Tuesday of the Common A programming language is an Artificial language that can be used to write programs which control the behavior of a machine particularly a Computer. tags please moot on the talk page first! --> In Computing, C is a general-purpose cross-platform block structured An operating system (commonly abbreviated OS and O/S) is the software component of a Computer system that is responsible for the management and coordination In computing cross-platform (also known as multi-platform) is a term used to refer to Computer software or computing methods and concepts that are implemented Computer software can be organized into categories based on common function type or field of use An object-relational database ( ORD) or object-relational database management system ( ORDBMS) is a Database management system (DBMS similar A software license (or software licence in commonwealth usage is a Legal instrument governing the usage or redistribution of copyright protected software BSD licenses represent a family of Permissive free software licences. A website (alternatively web site or Web site, a back-construction from the Proper noun World Wide Web) is a collection of Web pages An object-relational database ( ORD) or object-relational database management system ( ORDBMS) is a Database management system (DBMS similar It is released under a BSD-style license and is thus free software. BSD licenses represent a family of Permissive free software licences. Free software or software libre is Software that can be used studied and modified without restriction and which can be copied and redistributed in modified or unmodified As with many other open-source programs, PostgreSQL is not controlled by any single company, but relies on a global community of developers and companies to develop it.
Contents |
The mixed-capitalization of the PostgreSQL name can confuse some people on first viewing. The several pronunciations of 'SQL' can lead to this confusion. PostgreSQL's developers pronounce it /poːst ɡɹɛs kjuː ɛl/; (Audio sample, 5. 6k MP3). MPEG-1 Audio Layer 3, more commonly referred to as MP3, is a Digital audio encoding format using a form of Lossy data compression It is also common to hear it abbreviated as simply "postgres", which was its original name. Because of ubiquitous support for the SQL Standard amongst all relational databases, the community considered changing the name back to Postgres. SQL2003 is the fifth revision of the SQL Database Query language. However, the PostgreSQL Core Team announced in 2007 that the product would continue to be named PostgreSQL[2]. The name refers to the project's origins as a "post-Ingres" database, the original authors having also developed the Ingres database.
PostgreSQL evolved from the Ingres project at University of California, Berkeley. The University of California Berkeley (also referred to as Cal, Berkeley and UC Berkeley) is a major research university located in Berkeley In 1982, the project leader, Michael Stonebraker, left Berkeley to commercialize Ingres. Michael Stonebraker is a Computer scientist specializing in database research and development He returned to Berkeley in 1985 and started a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. The new project, Postgres, aimed to add the fewest features needed to completely support types. These features included the ability to define types and to fully describe relationships – something used widely before but maintained entirely by the user. In Postgres, the database "understood" relationships, and could retrieve information in related tables in a natural way using rules. Postgres used many ideas of Ingres but not its code.
Starting in 1986, the team published a number of papers describing the basis of the system, and by 1988 had a prototype version. The team released version 1 to a small number of users in June 1989, then version 2 with a re-written rules system in June 1990. Version 3, released in 1991, again re-wrote the rules system, and added support for multiple storage managers and an improved query engine. By 1993 the great number of users began to overwhelm the project with requests for support and features. After releasing version 4 — primarily a cleanup — the project ended.
But open-source developers could obtain copies and develop the system further, because Berkeley had released Postgres under the BSD license. Open source is a development methodology which offers practical accessibility to a product's source (goods and knowledge BSD licenses represent a family of Permissive free software licences. In 1994, Berkeley graduate students Andrew Yu and Jolly Chen replaced the Ingres-based QUEL query language interpreter with one for the SQL query language, creating Postgres95. QUEL is a Relational database access language similar in most ways to SQL. The code was released on the web.
In July 1996, Marc Fournier at Hub. Org Networking Services provided the first non-university development server for the open source development effort. Along with Bruce Momjian and Vadim B. Mikheev, work began to stabilize the code inherited from Berkeley. The first open source version was released on August 1, 1996.
In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. The first PostgreSQL release formed version 6. 0 in January 1997. Since then, the software was maintained by a group of database developers and volunteers around the world, coordinating via the Internet. The Internet is a global system of interconnected Computer networks
Although the license allowed for the commercialization of Postgres, the code did not develop commercially at first — somewhat surprisingly considering the advantages Postgres offered. The main offshoot originated when Paula Hawthorn (an original Ingres team member who moved from Ingres) and Michael Stonebraker formed Illustra Information Technologies to commercialize Postgres. Illustra was a commercialized version of the Postgres object-relational database management system ( DBMS) sold by Illustra Information Technologies a company formed
In 2000, former Red Hat investors created the company Great Bridge to commercialize PostgreSQL and compete against commercial database vendors. In Computing, Red Hat Inc ( is a company dedicated to Free and open source software, and a major Linux distribution vendor Great Bridge sponsored several PostgreSQL developers and donated many resources back to the community,[3] but by late 2001 closed due to tough competition from companies like Red Hat and to poor market conditions. [4]
In 2001, Command Prompt, Inc. released Mammoth PostgreSQL, the oldest surviving commercial PostgreSQL distribution. It continues to actively support the PostgreSQL community through developer sponsorships and projects including PL/Perl, PL/php, and hosting of community projects such as the PostgreSQL Build Farm.
In January 2005, PostgreSQL received backing by database vendor Pervasive Software, known for its Btrieve product which was ubiquitous on the Novell NetWare platform. Pervasive Software develops and distributes data infrastructure Software In Computing, Btrieve is a Transactional database product based on Indexed Sequential Access Method (ISAM NetWare is a Network operating system developed by Novell Inc Pervasive announced commercial support and community participation and achieved some success. But in July 2006, it left the PostgreSQL support market. [5]
In mid-2005 two other companies announced plans to commercialize PostgreSQL with focus on separate niche markets. EnterpriseDB added functionality to allow applications written to work with Oracle to be more readily run with PostgreSQL. EnterpriseDB is a privately-held company that develops and supports Postgres Plus Advanced Server a proprietary relational database management system ( Oracle Database (commonly referred to as Oracle RDBMS or simply Oracle) is a Relational database management system (RDBMS produced and marketed by Greenplum contributed enhancements directed at data warehouse and business intelligence applications, including the BizGres project. A data warehouse is a Repository of an organization's electronically stored data Business intelligence ( BI) refers to technologies applications and practices for the collection integration analysis and presentation of business Information and
In October 2005, John Loiacono, executive vice president of software at Sun Microsystems, commented: "We're not going to OEM Microsoft but we are looking at PostgreSQL right now,"[6] although no specifics were released at that time. __FORCETOC__ For the Vice President of the United States, their roles and other information see Vice President of the United States. Sun Microsystems Inc ( is a multinational vendor of Computers computer components Computer software, and Information technology services An original equipment manufacturer, or OEM is typically a company that uses a component made by a second company in its own product or sells the product of the second company By November 2005, Sun had announced support for PostgreSQL. [7] As of June 2006, Sun Solaris 10 6/06 ships PostgreSQL. June 2006 was a month with thirty days The following events also occurred during the month
In August, 2007, EnterpriseDB announced[8] the Postgres Resource Center [1] and EnterpriseDB Postgres, designed to be a fully configured distribution of PostgreSQL including many contrib modules and add-on components.
The PostgreSQL project continues to make yearly major releases and minor "bugfix" releases, all available under the BSD license, based on contributions from both commercial vendors, support companies, and open source programmers at large.
Functions allow blocks of code to be executed by the server. Although these blocks can be written in SQL, the lack of basic programming operations, such as branching and looping, has driven the adoption of other languages inside of functions. In Computer science control flow (or alternatively flow of control refers to the order in which the individual statements, instructions or Function Iteration means the act of repeating Mathematics Iteration in mathematics may refer to the process of iterating a function, or to the techniques used Some of the languages can even execute inside of triggers. Functions in PostgreSQL can be written in the following languages:
PostgreSQL supports row-returning functions, where the output of the function is a set of values which can be treated much like a table within queries.
Functions can be defined to execute with the privileges of either the caller or the user who defined the function. Functions are sometimes referred to as stored procedures, although there is a slight technical distinction between the two. A stored procedure is a Subroutine available to applications accessing a relational database system.
User-defined index methods can be created, or the built-in B-tree, hash table and GiST indices can be used. A database index is a Data structure that improves the speed of operations on a database table. In Computer science, a B-tree is a Tree data structure that keeps data sorted and allows searches insertions and deletions in logarithmic amortized In Computer science, a hash table, or a hash map, is a Data structure that associates keys with values. In computing GiST or Generalized Search Tree is a Data structure and API that can be used to build a variety of disk-based search trees. Indexes in PostgreSQL also support the following features:
ORDER BY field DESC. WHERE clause to the end of the CREATE INDEX statement. A partial index is a database index which has some condition applied to it such that it only includes a portion of the rows in the table. This allows a smaller index to be created. Triggers are events triggered by the action of SQL query. For example, an INSERT query might activate a trigger that checked if the values of the query were valid. Most triggers are only activated by either INSERT or UPDATE queries.
Triggers are fully supported and can be attached to tables but not to views. A database trigger is Procedural code that is automatically executed in response to certain events on a particular table in a Database. Views can have rules, though. Multiple triggers are fired in alphabetical order. In addition to calling functions written in the native PL/PgSQL, triggers can also invoke functions written in other languages like PL/Perl.
PostgreSQL manages concurrency through a system known as Multi-Version Concurrency Control (MVCC), which gives each user a "snapshot" of the database, allowing changes to be made without being visible to other users until a transaction is committed. In Computer science, especially in the fields of Computer programming (see also Concurrent programming, Parallel programming) Operating systems Multiversion concurrency control (abbreviated MCC or MVCC) in the Database field of Computer science, is a Concurrency control This largely eliminates the need for read locks, and ensures the database maintains the ACID principles in an efficient manner. In Computer science, ACID ( Atomicity Consistency Isolation Durability) is a set of properties that guarantee that Database transactions are
Rules allow the "query tree" of an incoming query to be rewritten. One common usage is to implement updatable views.
A wide variety of native data types are supported, including:
In addition, users can create their own data types which can usually be made fully indexable via PostgreSQL's GiST infrastructure. In computing GiST or Generalized Search Tree is a Data structure and API that can be used to build a variety of disk-based search trees.
Examples of these are the Geographic information system (GIS) data types from the PostGIS project for PostgreSQL. PostGIS (/post'-jis/ is an open source Geographic information system software program that adds support for geographic objects to the PostgreSQL object-relational
New types of almost all objects inside the database can be created, including:
Tables can be set to inherit their characteristics from a "parent" table. In Computer programming, operator overloading (less commonly known as operator Ad-hoc polymorphism) is a specific case of polymorphism in In Computer science, an aggregate function is a function that computes a single result value from a collection of input values such as a Set, a bag In Data management and Database analysis a data domain refers to all the unique values which a Data element may contain Data is shared between "parent" and "child(ren)" tables. Tuples inserted or deleted in the "child" table will respectively be inserted or deleted in the "parent" table. Also adding a column in the parent table will cause that column to appear in the child table as well. This feature is not fully supported yet—in particular, table constraints are not currently inheritable. This means that attempting to insert the id of a row from a child table into a table that has a foreign key constraint referencing a parent table will fail because Postgres doesn't recognize that the id from the child table is also a valid id in the parent table.
Inheritance provides a way to map the features of generalization hierarchies depicted in Entity Relationship Diagrams (ERD) directly into the PostgreSQL database.
The primary front-end for PostgreSQL is the psql command-line program, which can be used to enter SQL queries directly, or execute them from a file. Front-end and back-end are generalized terms that refer to the initial and the end stages of a process In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks; for example tab completion of object names and SQL syntax.
pgAdmin is a graphical front-end administration tool for PostgreSQL, which is supported on most popular computer platforms. The program is available in more than a dozen languages, and is free software released under the Artistic License. Free software or software libre is Software that can be used studied and modified without restriction and which can be copied and redistributed in modified or unmodified Artistic license (also known as dramatic license, poetic license, narrative license, licentia poetica, or simply license) is a colloquial The first prototype, named pgManager, was written for PostgreSQL 6. 3. 2 from 1998. The stable release (named pgAdmin II) was first released on 16 January 2002. Events 27 BC - The title Augustus is bestowed upon Gaius Julius Caesar Octavian by the Roman Senate. See also 2002 (disambiguation Year 2002 ( MMII) was a Common year starting on Tuesday of the Gregorian calendar. The current version is pgAdmin III, which is released under the Artistic License. Artistic license (also known as dramatic license, poetic license, narrative license, licentia poetica, or simply license) is a colloquial
phpPgAdmin is a web-based administration tool for PostgreSQL written in PHP and based on the popular phpMyAdmin interface originally written for MySQL administration. phpPgAdmin is a Web application, written in PHP, for managing PostgreSQL databases PHP is a computer Scripting language. Originally designed for producing Dynamic web pages it has evolved to include a Command line interface capability phpMyAdmin is an open source tool written in PHP intended to handle the administration of MySQL over the Internet. MySQL is a Relational database management system (RDBMS which has more than 11 million installations [9]
Many informal performance studies of PostgreSQL have been done[10] but the first industry-standard and peer-validated benchmark was completed in June 2007 using the Sun Java Systems Application Server 9. 0 Platform Edition, UltraSPARC T1 based Sun Fire server and Postgres 8. Sun Microsystems ' UltraSPARC T1 Microprocessor, known until its 14 November 2005 announcement by its development Codename " The Sun Fire server brand is a series of server computers introduced by Sun Microsystems in 2001 2[11]. This result of 778. 14 SPECjAppServer2004 JOPS@Standard compares favourably with the 874 JOPS@Standard with Oracle 10 on an Itanium based HP-UX [10]
In August 2007, Sun submitted an improved benchmark score of 813. Itanium is the brand name for 64-bit Intel Microprocessors that implement the Intel Itanium architecture (formerly called IA-64) HP-UX (Hewlett Packard UniX is Hewlett-Packard 's proprietary implementation of the Unix Operating system, based on System V (initially 73 SPECjAppServer2004 JOPS@Standard. With the system under test at a reduced price, the price/performance improved from $US 84. System Under Test (shortened as SUT) refers to a System that is being tested for correct operation 98/JOPS to $US 70. 57/JOPS. [12]
As of 2008 PostgreSQL has received the following awards[21]:
Performance tuning documentation
The developers of PostgreSQL try to keep the system itself down to a set of "core" features, rather than encouraging extensions to be rolled into the main system. Here are places where "secondary" projects are managed: