======================================================================== * README ======================================================================== PostgreSQL Database Management System ===================================== This directory contains the source code distribution of the PostgreSQL database management system. PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions. This distribution also contains C language bindings. PostgreSQL has many language interfaces, many of which are listed here: http://www.postgresql.org/download See the file INSTALL for instructions on how to build and install PostgreSQL. That file also lists supported operating systems and hardware platforms and contains information regarding any other software packages that are required to build or run the PostgreSQL system. Changes between all PostgreSQL releases are recorded in the file HISTORY. Copyright and license information can be found in the file COPYRIGHT. A comprehensive documentation set is included in this distribution; it can be read as described in the installation instructions. The latest version of this software may be obtained at http://www.postgresql.org/download/. For more information look at our web site located at http://www.postgresql.org/. ======================================================================== * src/backend/optimizer/plan/README ======================================================================== $PostgreSQL: pgsql/src/backend/optimizer/plan/README,v 1.3 2008/03/21 13:23:28 momjian Exp $ Subselects ========== Vadim B. Mikheev From owner-pgsql-hackers@hub.org Fri Feb 13 09:01:19 1998 Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) by candle.pha.pa.us (8.8.5/8.8.5) with ESMTP id JAA11576 for ; Fri, 13 Feb 1998 09:01:17 -0500 (EST) Received: from hub.org (hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.3 $) with ESMTP id IAA09761 for ; Fri, 13 Feb 1998 08:41:22 -0500 (EST) Received: from localhost (majordom@localhost) by hub.org (8.8.8/8.7.5) with SMTP id IAA08135; Fri, 13 Feb 1998 08:40:17 -0500 (EST) Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Fri, 13 Feb 1998 08:38:42 -0500 (EST) Received: (from majordom@localhost) by hub.org (8.8.8/8.7.5) id IAA06646 for pgsql-hackers-outgoing; Fri, 13 Feb 1998 08:38:35 -0500 (EST) Received: from dune.krasnet.ru (dune.krasnet.ru [193.125.44.86]) by hub.org (8.8.8/8.7.5) with ESMTP id IAA04568 for ; Fri, 13 Feb 1998 08:37:16 -0500 (EST) Received: from sable.krasnoyarsk.su (dune.krasnet.ru [193.125.44.86]) by dune.krasnet.ru (8.8.7/8.8.7) with ESMTP id UAA13717 for ; Fri, 13 Feb 1998 20:51:03 +0700 (KRS) (envelope-from vadim@sable.krasnoyarsk.su) Message-ID: <34E44FBA.D64E7997@sable.krasnoyarsk.su> Date: Fri, 13 Feb 1998 20:50:50 +0700 From: "Vadim B. Mikheev" Organization: ITTS (Krasnoyarsk) X-Mailer: Mozilla 4.04 [en] (X11; I; FreeBSD 2.2.5-RELEASE i386) MIME-Version: 1.0 To: PostgreSQL Developers List Subject: [HACKERS] Subselects are in CVS... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Sender: owner-pgsql-hackers@hub.org Precedence: bulk Status: OR This is some implementation notes and opened issues... First, implementation uses new type of parameters - PARAM_EXEC - to deal with correlation Vars. When query_planner() is called, it first tries to replace all upper queries Var referenced in current query with Param of this type. Some global variables are used to keep mapping of Vars to Params and Params to Vars. After this, all current query' SubLinks are processed: for each SubLink found in query' qual union_planner() (old planner() function) will be called to plan corresponding subselect (union_planner() calls query_planner() for "simple" query and supports UNIONs). After subselect are planned, optimizer knows about is this correlated, un-correlated or _undirect_ correlated (references some grand-parent Vars but no parent ones: uncorrelated from the parent' point of view) query. For uncorrelated and undirect correlated subqueries of EXPRession or EXISTS type SubLinks will be replaced with "normal" clauses from SubLink->Oper list (I changed this list to be list of EXPR nodes, not just Oper ones). Right sides of these nodes are replaced with PARAM_EXEC parameters. This is second use of new parameter type. At run-time these parameters get value from result of subquery evaluation (i.e. - from target list of subquery). Execution plan of subquery itself becomes init plan of parent query. InitPlan knows what parameters are to get values from subquery' results and will be executed "on-demand" (for query select * from table where x > 0 and y > (select max(a) from table_a) subquery will not be executed at all if there are no tuples with x > 0 _and_ y is not used in index scan). SubLinks for subqueries of all other types are transformed into new type of Expr node - SUBPLAN_EXPR. Expr->args are just correlation variables from _parent_ query. Expr->oper is new SubPlan node. This node is used for InitPlan too. It keeps subquery range table, indices of Params which are to get value from _parent_ query Vars (i.e. - from Expr->args), indices of Params into which subquery' results are to be substituted (this is for InitPlans), SubLink and subquery' execution plan. Plan node was changed to know about dependencies on Params from parent queries and InitPlans, to keep list of changed Params (from the above) and so be re-scanned if this list is not NULL. Also, added list of InitPlans (actually, all of them for current query are in topmost plan node now) and other SubPlans (from plan->qual) - to initialize them and let them know about changed Params (from the list of their "interests"). After all SubLinks are processed, query_planner() calls qual' canonificator and does "normal" work. By using Params optimizer is mostly unchanged. Well, Executor. To get subplans re-evaluated without ExecutorStart() and ExecutorEnd() (without opening and closing relations and indices and without many palloc() and pfree() - this is what SQL-funcs does on each call) ExecReScan() now supports most of Plan types... Explanation of EXPLAIN. vac=> explain select * from tmp where x >= (select max(x2) from test2 where y2 = y and exists (select * from tempx where tx = x)); NOTICE: QUERY PLAN: Seq Scan on tmp (cost=40.03 size=101 width=8) SubPlan ^^^^^^^ subquery is in Seq Scan' qual, its plan is below -> Aggregate (cost=2.05 size=0 width=0) InitPlan ^^^^^^^^ EXISTS subsubquery is InitPlan of subquery -> Seq Scan on tempx (cost=4.33 size=1 width=4) -> Result (cost=2.05 size=0 width=0) ^^^^^^ EXISTS subsubquery was transformed into Param and so we have Result node here -> Index Scan on test2 (cost=2.05 size=1 width=4) Opened issues. 1. No read permissions checking (easy, just not done yet). 2. readfuncs.c can't read subplan-s (easy, not critical, because of we currently nowhere use ascii representation of execution plans). 3. ExecReScan() doesn't support all plan types. At least support for MergeJoin has to be implemented. 4. Memory leaks in ExecReScan(). 5. I need in advice: if subquery introduced with NOT IN doesn't return any tuples then qualification is failed, yes ? 6. Regression tests !!!!!!!!!!!!!!!!!!!! (Could we use data/queries from MySQL' crash.me ? Copyright-ed ? Could they give us rights ?) 7. Performance. - Should be good when subquery is transformed into InitPlan. - Something should be done for uncorrelated subqueries introduced with ANY/ALL - keep thinking. Currently, subplan will be re-scanned for each parent tuple - very slow... Results of some test. TMP is table with x,y (int4-s), x in 0-9, y = 100 - x, 1000 tuples (10 duplicates of each tuple). TEST2 is table with x2, y2 (int4-s), x2 in 1-99, y2 = 100 -x2, 10000 tuples (100 dups). Trying select * from tmp where x >= (select max(x2) from test2 where y2 = y); and begin; select y as ty, max(x2) as mx into table tsub from test2, tmp where y2 = y group by ty; vacuum tsub; select x, y from tmp, tsub where x >= mx and y = ty; drop table tsub; end; Without index on test2(y2): SubSelect -> 320 sec Using temp table -> 32 sec Having index SubSelect -> 17 sec (2M of memory) Using temp table -> 32 sec (12M of memory: -S 8192) Vadim ======================================================================== * src/backend/port/darwin/README ======================================================================== $PostgreSQL: pgsql/src/backend/port/darwin/README,v 1.5 2008/03/21 13:23:28 momjian Exp $ Darwin ====== The file system.c included herein is taken directly from Apple's Darwin open-source CVS archives, and is redistributed under the BSD copyright notice it bears. (According to Apple's CVS logs, their version is identical to the FreeBSD original.) It provides our own implementation of the system(3) function, which ought by all rights to be identical to the one provided in libc on Darwin machines. Nonetheless, this version works, whereas the one that actually ships with Mac OS X 10.1 doesn't. The shipped version appears to disconnect the calling process from any shared memory segments it is attached to. (The symptom seen in PostgreSQL is that a backend attempting to execute CREATE DATABASE core-dumps.) I would love to know why there is a discrepancy between the published source and the actual behavior --- tgl 7-Nov-2001. Appropriate bug reports have been filed with Apple --- see Radar Bug#s 2767956, 2683531, 2805147. One hopes we can retire this kluge in the not too distant future. As of PostgreSQL 7.3 and Mac OS X 10.1, one should expect warnings like these while linking the backend: /usr/bin/ld: warning unused multiple definitions of symbol _system port/SUBSYS.o definition of _system in section (__TEXT,__text) /usr/lib/libm.dylib(system.o) unused definition of _system These are due to overriding system() per the above-described hack. The bug appears to be repaired in OS X 10.2.6 and later (possibly in earlier 10.2.* as well, but no systems handy to check). We #ifdef out the substitute system() definition on 10.3 and later. ======================================================================== * COPYRIGHT ======================================================================== PostgreSQL Database Management System (formerly known as Postgres, then as Postgres95) Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group Portions Copyright (c) 1994, The Regents of the University of California Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. ======================================================================== * src/backend/regex/COPYRIGHT ======================================================================== This regular expression package was originally developed by Henry Spencer. It bears the following copyright notice: ********************************************************************** Copyright (c) 1998, 1999 Henry Spencer. All rights reserved. Development of this software was funded, in part, by Cray Research Inc., UUNET Communications Services Inc., Sun Microsystems Inc., and Scriptics Corporation, none of whom are responsible for the results. The author thanks all of them. Redistribution and use in source and binary forms -- with or without modification -- are permitted for any purpose, provided that redistributions in source form retain this entire copyright notice and indicate the origin and nature of any modifications. I'd appreciate being given credit for this package in the documentation of software which uses it, but that is not a requirement. THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL HENRY SPENCER BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. ********************************************************************** PostgreSQL adopted the code out of Tcl 8.4.1. Portions of regc_locale.c and re_syntax.n were developed by Tcl developers other than Henry; these files bear the Tcl copyright and license notice: ********************************************************************** This software is copyrighted by the Regents of the University of California, Sun Microsystems, Inc., Scriptics Corporation, ActiveState Corporation and other parties. The following terms apply to all files associated with the software unless explicitly disclaimed in individual files. The authors hereby grant permission to use, copy, modify, distribute, and license this software and its documentation for any purpose, provided that existing copyright notices are retained in all copies and that this notice is included verbatim in any distributions. No written agreement, license, or royalty fee is required for any of the authorized uses. Modifications to this software may be copyrighted by their authors and need not follow the licensing terms described here, provided that the new terms are clearly indicated on the first page of each file where they apply. IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OF THIS SOFTWARE, ITS DOCUMENTATION, OR ANY DERIVATIVES THEREOF, EVEN IF THE AUTHORS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NON-INFRINGEMENT. THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, AND THE AUTHORS AND DISTRIBUTORS HAVE NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. GOVERNMENT USE: If you are acquiring this software on behalf of the U.S. government, the Government shall have only "Restricted Rights" in the software and related documentation as defined in the Federal Acquisition Regulations (FARs) in Clause 52.227.19 (c) (2). If you are acquiring the software on behalf of the Department of Defense, the software shall be classified as "Commercial Computer Software" and the Government shall have only "Restricted Rights" as defined in Clause 252.227-7013 (c) (1) of DFARs. Notwithstanding the foregoing, the authors grant the U.S. Government and others acting in its behalf permission to use and distribute the software in accordance with the terms specified in this license. ********************************************************************** Subsequent modifications to the code by the PostgreSQL project follow the same license terms as the rest of PostgreSQL. ======================================================================== * src/tools/copyright ======================================================================== #!/bin/sh # $PostgreSQL: pgsql/src/tools/copyright,v 1.18 2009/01/01 18:31:55 momjian Exp $ echo "Using current year: `date '+%Y'`" rgrep -l 'Copyright.*PostgreSQL Global Development Group' | while read FILE do pipe sed 's/^\(.*Copyright (c) [12][0-9][0-9][0-9]\) \?- \?[12][0-9][0-9][0-9] \?,\?\( PostgreSQL Global Development Group.*\)$/\1-'`date '+%Y'`',\2/' $FILE # handle cases where only one year appears pipe sed 's/^\(.*Copyright (c) [12][0-9][0-9][0-9]\) \?,\?\( PostgreSQL Global Development Group.*\)$/\1-'`date '+%Y'`',\2/' $FILE done echo "Manually update doc/src/sgml/legal.sgml and src/interfaces/libpq/libpq.rc.in too" 1>&2