1. 25 Feb, 2009 1 commit
    • Tom Lane's avatar
      Get rid of the rather fuzzily defined FlattenedSubLink node type in favor of · e549722a
      Tom Lane authored
      making pull_up_sublinks() construct a full-blown JoinExpr tree representation
      of IN/EXISTS SubLinks that it is able to convert to semi or anti joins.
      This makes pull_up_sublinks() a shade more complex, but the gain in semantic
      clarity is worth it.  I still have more to do in this area to address the
      previously-discussed problems, but this commit in itself fixes at least one
      bug in HEAD, as shown by added regression test case.
      e549722a
  2. 24 Feb, 2009 1 commit
  3. 06 Feb, 2009 1 commit
    • Tom Lane's avatar
      Fix cost_mergejoin's failure to adjust for rescanning of non-unique merge join · c473d923
      Tom Lane authored
      keys when considering a semi or anti join.  This requires estimating the
      selectivity of the merge qual as though it were a regular inner join condition.
      To allow caching both that and the real outer-join-aware selectivity, split
      RestrictInfo.this_selec into two fields.
      
      This fixes one of the problems reported by Kevin Grittner.
      c473d923
  4. 02 Feb, 2009 1 commit
  5. 22 Jan, 2009 1 commit
  6. 16 Jan, 2009 1 commit
  7. 01 Jan, 2009 1 commit
  8. 31 Dec, 2008 1 commit
    • Tom Lane's avatar
      Add some basic support for window frame clauses to the window-functions · 8e8854da
      Tom Lane authored
      patch.  This includes the ability to force the frame to cover the whole
      partition, and the ability to make the frame end exactly on the current row
      rather than its last ORDER BY peer.  Supporting any more of the full SQL
      frame-clause syntax will require nontrivial hacking on the window aggregate
      code, so it'll have to wait for 8.5 or beyond.
      8e8854da
  9. 28 Dec, 2008 1 commit
  10. 19 Dec, 2008 1 commit
    • Peter Eisentraut's avatar
      SQL/MED catalog manipulation facilities · cae565e5
      Peter Eisentraut authored
      This doesn't do any remote or external things yet, but it gives modules
      like plproxy and dblink a standardized and future-proof system for
      managing their connection information.
      
      Martin Pihlak and Peter Eisentraut
      cae565e5
  11. 04 Dec, 2008 2 commits
  12. 24 Nov, 2008 1 commit
  13. 15 Nov, 2008 1 commit
    • Tom Lane's avatar
      Make SELECT FOR UPDATE/SHARE work on inheritance trees, by having the plan · 0656ed3d
      Tom Lane authored
      return the tableoid as well as the ctid for any FOR UPDATE targets that
      have child tables.  All child tables are listed in the ExecRowMark list,
      but the executor just skips the ones that didn't produce the current row.
      
      Curiously, this longstanding restriction doesn't seem to have been documented
      anywhere; so no doc changes.
      0656ed3d
  14. 11 Nov, 2008 1 commit
    • Tom Lane's avatar
      Get rid of adjust_appendrel_attr_needed(), which has been broken ever since · 04366799
      Tom Lane authored
      we extended the appendrel mechanism to support UNION ALL optimization.  The
      reason nobody noticed was that we are not actually using attr_needed data for
      appendrel children; hence it seems more reasonable to rip it out than fix it.
      Back-patch to 8.2 because an Assert failure is possible in corner cases.
      Per examination of an example from Jim Nasby.
      
      In HEAD, also get rid of AppendRelInfo.col_mappings, which is quite inadequate
      to represent UNION ALL situations; depend entirely on translated_vars instead.
      04366799
  15. 31 Oct, 2008 1 commit
  16. 21 Oct, 2008 1 commit
    • Tom Lane's avatar
      Add a concept of "placeholder" variables to the planner. These are variables · e6ae3b5d
      Tom Lane authored
      that represent some expression that we desire to compute below the top level
      of the plan, and then let that value "bubble up" as though it were a plain
      Var (ie, a column value).
      
      The immediate application is to allow sub-selects to be flattened even when
      they are below an outer join and have non-nullable output expressions.
      Formerly we couldn't flatten because such an expression wouldn't properly
      go to NULL when evaluated above the outer join.  Now, we wrap it in a
      PlaceHolderVar and arrange for the actual evaluation to occur below the outer
      join.  When the resulting Var bubbles up through the join, it will be set to
      NULL if necessary, yielding the correct results.  This fixes a planner
      limitation that's existed since 7.1.
      
      In future we might want to use this mechanism to re-introduce some form of
      Hellerstein's "expensive functions" optimization, ie place the evaluation of
      an expensive function at the most suitable point in the plan tree.
      e6ae3b5d
  17. 07 Oct, 2008 1 commit
  18. 06 Oct, 2008 1 commit
    • Tom Lane's avatar
      When expanding a whole-row Var into a RowExpr during ResolveNew(), attach · bf461538
      Tom Lane authored
      the column alias names of the RTE referenced by the Var to the RowExpr.
      This is needed to allow ruleutils.c to correctly deparse FieldSelect nodes
      referencing such a construct.  Per my recent bug report.
      
      Adding a field to RowExpr forces initdb (because of stored rules changes)
      so this solution is not back-patchable; which is unfortunate because 8.2
      and 8.3 have this issue.  But it only affects EXPLAIN for some pretty odd
      corner cases, so we can probably live without a solution for the back
      branches.
      bf461538
  19. 04 Oct, 2008 1 commit
    • Tom Lane's avatar
      Implement SQL-standard WITH clauses, including WITH RECURSIVE. · 44d5be0e
      Tom Lane authored
      There are some unimplemented aspects: recursive queries must use UNION ALL
      (should allow UNION too), and we don't have SEARCH or CYCLE clauses.
      These might or might not get done for 8.4, but even without them it's a
      pretty useful feature.
      
      There are also a couple of small loose ends and definitional quibbles,
      which I'll send a memo about to pgsql-hackers shortly.  But let's land
      the patch now so we can get on with other development.
      
      Yoshiyuki Asaba, with lots of help from Tatsuo Ishii and Tom Lane
      44d5be0e
  20. 09 Sep, 2008 1 commit
    • Tom Lane's avatar
      Improve the plan cache invalidation mechanism to make it invalidate plans · ee33b95d
      Tom Lane authored
      when user-defined functions used in a plan are modified.  Also invalidate
      plans when schemas, operators, or operator classes are modified; but for these
      cases we just invalidate everything rather than tracking exact dependencies,
      since these types of objects seldom change in a production database.
      
      Tom Lane; loosely based on a patch by Martin Pihlak.
      ee33b95d
  21. 01 Sep, 2008 1 commit
  22. 30 Aug, 2008 1 commit
  23. 28 Aug, 2008 1 commit
    • Tom Lane's avatar
      Extend the parser location infrastructure to include a location field in · a2794623
      Tom Lane authored
      most node types used in expression trees (both before and after parse
      analysis).  This allows us to place an error cursor in many situations
      where we formerly could not, because the information wasn't available
      beyond the very first level of parse analysis.  There's a fair amount
      of work still to be done to persuade individual ereport() calls to actually
      include an error location, but this gets the initdb-forcing part of the
      work out of the way; and the situation is already markedly better than
      before for complaints about unimplementable implicit casts, such as
      CASE and UNION constructs with incompatible alternative data types.
      Per my proposal of a few days ago.
      a2794623
  24. 22 Aug, 2008 1 commit
    • Tom Lane's avatar
      Arrange to convert EXISTS subqueries that are equivalent to hashable IN · bd3dadda
      Tom Lane authored
      subqueries into the same thing you'd have gotten from IN (except always with
      unknownEqFalse = true, so as to get the proper semantics for an EXISTS).
      I believe this fixes the last case within CVS HEAD in which an EXISTS could
      give worse performance than an equivalent IN subquery.
      
      The tricky part of this is that if the upper query probes the EXISTS for only
      a few rows, the hashing implementation can actually be worse than the default,
      and therefore we need to make a cost-based decision about which way to use.
      But at the time when the planner generates plans for subqueries, it doesn't
      really know how many times the subquery will be executed.  The least invasive
      solution seems to be to generate both plans and postpone the choice until
      execution.  Therefore, in a query that has been optimized this way, EXPLAIN
      will show two subplans for the EXISTS, of which only one will actually get
      executed.
      
      There is a lot more that could be done based on this infrastructure: in
      particular it's interesting to consider switching to the hash plan if we start
      out using the non-hashed plan but find a lot more upper rows going by than we
      expected.  I have therefore left some minor inefficiencies in place, such as
      initializing both subplans even though we will currently only use one.
      bd3dadda
  25. 14 Aug, 2008 1 commit
    • Tom Lane's avatar
      Implement SEMI and ANTI joins in the planner and executor. (Semijoins replace · e006a24a
      Tom Lane authored
      the old JOIN_IN code, but antijoins are new functionality.)  Teach the planner
      to convert appropriate EXISTS and NOT EXISTS subqueries into semi and anti
      joins respectively.  Also, LEFT JOINs with suitable upper-level IS NULL
      filters are recognized as being anti joins.  Unify the InClauseInfo and
      OuterJoinInfo infrastructure into "SpecialJoinInfo".  With that change,
      it becomes possible to associate a SpecialJoinInfo with every join attempt,
      which permits some cleanup of join selectivity estimation.  That needs to be
      taken much further than this patch does, but the next step is to change the
      API for oprjoin selectivity functions, which seems like material for a
      separate patch.  So for the moment the output size estimates for semi and
      especially anti joins are quite bogus.
      e006a24a
  26. 07 Aug, 2008 3 commits
    • Tom Lane's avatar
      Improve INTERSECT/EXCEPT hashing by realizing that we don't need to make any · af95d7aa
      Tom Lane authored
      hashtable entries for tuples that are found only in the second input: they
      can never contribute to the output.  Furthermore, this implies that the
      planner should endeavor to put first the smaller (in number of groups) input
      relation for an INTERSECT.  Implement that, and upgrade prepunion's estimation
      of the number of rows returned by setops so that there's some amount of sanity
      in the estimate of which one is smaller.
      af95d7aa
    • Tom Lane's avatar
      Support hashing for duplicate-elimination in INTERSECT and EXCEPT queries. · 368df304
      Tom Lane authored
      This completes my project of improving usage of hashing for duplicate
      elimination (aggregate functions with DISTINCT remain undone, but that's
      for some other day).
      
      As with the previous patches, this means we can INTERSECT/EXCEPT on datatypes
      that can hash but not sort, and it means that INTERSECT/EXCEPT without ORDER
      BY are no longer certain to produce sorted output.
      368df304
    • Tom Lane's avatar
      Teach the system how to use hashing for UNION. (INTERSECT/EXCEPT will follow, · 2d1d96b1
      Tom Lane authored
      but seem like a separate patch since most of the remaining work is on the
      executor side.)  I took the opportunity to push selection of the grouping
      operators for set operations into the parser where it belongs.  Otherwise this
      is just a small exercise in making prepunion.c consider both alternatives.
      
      As with the recent DISTINCT patch, this means we can UNION on datatypes that
      can hash but not sort, and it means that UNION without ORDER BY is no longer
      certain to produce sorted output.
      2d1d96b1
  27. 02 Aug, 2008 1 commit
    • Tom Lane's avatar
      Rearrange the querytree representation of ORDER BY/GROUP BY/DISTINCT items · 95113047
      Tom Lane authored
      as per my recent proposal:
      
      1. Fold SortClause and GroupClause into a single node type SortGroupClause.
      We were already relying on them to be struct-equivalent, so using two node
      tags wasn't accomplishing much except to get in the way of comparing items
      with equal().
      
      2. Add an "eqop" field to SortGroupClause to carry the associated equality
      operator.  This is cheap for the parser to get at the same time it's looking
      up the sort operator, and storing it eliminates the need for repeated
      not-so-cheap lookups during planning.  In future this will also let us
      represent GROUP/DISTINCT operations on datatypes that have hash opclasses
      but no btree opclasses (ie, they have equality but no natural sort order).
      The previous representation simply didn't work for that, since its only
      indicator of comparison semantics was a sort operator.
      
      3. Add a hasDistinctOn boolean to struct Query to explicitly record whether
      the distinctClause came from DISTINCT or DISTINCT ON.  This allows removing
      some complicated and not 100% bulletproof code that attempted to figure
      that out from the distinctClause alone.
      
      This patch doesn't in itself create any new capability, but it's necessary
      infrastructure for future attempts to use hash-based grouping for DISTINCT
      and UNION/INTERSECT/EXCEPT.
      95113047
  28. 16 Jul, 2008 1 commit
    • Tom Lane's avatar
      Support "variadic" functions, which can accept a variable number of arguments · d89737d3
      Tom Lane authored
      so long as all the trailing arguments are of the same (non-array) type.
      The function receives them as a single array argument (which is why they
      have to all be the same type).
      
      It might be useful to extend this facility to aggregates, but this patch
      doesn't do that.
      
      This patch imposes a noticeable slowdown on function lookup --- a follow-on
      patch will fix that by adding a redundant column to pg_proc.
      
      Pavel Stehule
      d89737d3
  29. 16 May, 2008 1 commit
    • Tom Lane's avatar
      Add a RESTART (without parameter) option to ALTER SEQUENCE, allowing a · 10a3471b
      Tom Lane authored
      sequence to be reset to its original starting value.  This requires adding the
      original start value to the set of parameters (columns) of a sequence object,
      which is a user-visible change with potential compatibility implications;
      it also forces initdb.
      
      Also add hopefully-SQL-compatible RESTART/CONTINUE IDENTITY options to
      TRUNCATE TABLE.  RESTART IDENTITY executes ALTER SEQUENCE RESTART for all
      sequences "owned by" any of the truncated relations.  CONTINUE IDENTITY is
      a no-op option.
      
      Zoltan Boszormenyi
      10a3471b
  30. 29 Apr, 2008 1 commit
  31. 14 Apr, 2008 1 commit
    • Tom Lane's avatar
      Push index operator lossiness determination down to GIST/GIN opclass · 9b5c8d45
      Tom Lane authored
      "consistent" functions, and remove pg_amop.opreqcheck, as per recent
      discussion.  The main immediate benefit of this is that we no longer need
      8.3's ugly hack of requiring @@@ rather than @@ to test weight-using tsquery
      searches on GIN indexes.  In future it should be possible to optimize some
      other queries better than is done now, by detecting at runtime whether the
      index match is exact or not.
      
      Tom Lane, after an idea of Heikki's, and with some help from Teodor.
      9b5c8d45
  32. 13 Apr, 2008 1 commit
    • Tom Lane's avatar
      Since createplan.c no longer cares whether index operators are lossy, it has · 226837e5
      Tom Lane authored
      no particular need to do get_op_opfamily_properties() while building an
      indexscan plan.  Postpone that lookup until executor start.  This simplifies
      createplan.c a lot more than it complicates nodeIndexscan.c, and makes things
      more uniform since we already had to do it that way for RowCompare
      expressions.  Should be a bit faster too, at least for plans that aren't
      re-used many times, since we avoid palloc'ing and perhaps copying the
      intermediate list data structure.
      226837e5
  33. 21 Mar, 2008 1 commit
  34. 20 Mar, 2008 1 commit
    • Tom Lane's avatar
      Arrange for an explicit cast applied to an ARRAY[] constructor to be applied · 6b0706ac
      Tom Lane authored
      directly to all the member expressions, instead of the previous implementation
      where the ARRAY[] constructor would infer a common element type and then we'd
      coerce the finished array after the fact.  This has a number of benefits,
      one being that we can allow an empty ARRAY[] construct so long as its
      element type is specified by such a cast.
      
      Brendan Jurd, minor fixes by me.
      6b0706ac
  35. 07 Feb, 2008 1 commit
  36. 01 Jan, 2008 1 commit
  37. 01 Dec, 2007 1 commit
    • Tom Lane's avatar
      Code review for LIKE ... INCLUDING INDEXES patch. Fix failure to propagate · 265f904d
      Tom Lane authored
      constraint status of copied indexes (bug #3774), as well as various other
      small bugs such as failure to pstrdup when needed.  Allow INCLUDING INDEXES
      indexes to be merged with identical declared indexes (perhaps not real useful,
      but the code is there and having it not apply to LIKE indexes seems pretty
      unorthogonal).  Avoid useless work in generateClonedIndexStmt().  Undo some
      poorly chosen API changes, and put a couple of routines in modules that seem
      to be better places for them.
      265f904d