1. 14 7月, 1999 1 次提交
  2. 12 7月, 1999 1 次提交
  3. 21 6月, 1999 1 次提交
    • T
      Replace rewriter's checkQueryHasAggs and checkQueryHasSubLink · 1f2c6f4f
      Tom Lane 提交于
      with expression_tree_walker-based code.  The former failed to cope with
      expressions containing SubLinks, and the latter returned TRUE for both
      SubLinks and Aggrefs (cut-and-paste bug?).  There is a lot more scope for
      using expression_tree_walker in this module, but I'll restrain myself
      until the 6.6 split occurs from touching not-demonstrably-broken code.
      1f2c6f4f
  4. 26 5月, 1999 2 次提交
  5. 25 5月, 1999 1 次提交
    • J
      Bugfix - Range table entries that are unused after rewriting should · b122e16a
      Jan Wieck 提交于
      not be marked inFromCl any longer. Otherwise the planner gets confused
      and joins over them where in fact it does not have to.
      
      Adjust hasSubLinks now with a recursive lookup - could be wrong in
      multi action rules because parse state isn't reset correctly and all
      actions in the rule are marked hasSubLinks if one of them has.
      
      Jan
      b122e16a
  6. 18 5月, 1999 2 次提交
  7. 13 5月, 1999 2 次提交
    • T
      Rip out QueryTreeList structure, root and branch. Querytree · 507a0a2a
      Tom Lane 提交于
      lists are now plain old garden-variety Lists, allocated with palloc,
      rather than specialized expansible-array data allocated with malloc.
      This substantially simplifies their handling and eliminates several
      sources of memory leakage.
      Several basic types of erroneous queries (syntax error, attempt to
      insert a duplicate key into a unique index) now demonstrably leak
      zero bytes per query.
      507a0a2a
    • J
      Fixed wrong hasAggs when aggregate columns of view aren't · b7a86e40
      Jan Wieck 提交于
      selected.
      
      Disabled ability of defining DISTINCT or ORDER BY on views.
      
      Jan
      b7a86e40
  8. 12 5月, 1999 1 次提交
  9. 10 5月, 1999 1 次提交
  10. 22 2月, 1999 1 次提交
  11. 21 2月, 1999 1 次提交
    • M
      · 8c3e8a8a
      Marc G. Fournier 提交于
      From: Tatsuo Ishii <t-ishii@sra.co.jp>
      
      Ok. I made patches replacing all of "#if FALSE" or "#if 0" to "#ifdef
      NOT_USED" for current. I have tested these patches in that the
      postgres binaries are identical.
      8c3e8a8a
  12. 14 2月, 1999 1 次提交
  13. 04 2月, 1999 1 次提交
  14. 02 2月, 1999 1 次提交
  15. 26 1月, 1999 1 次提交
  16. 25 1月, 1999 1 次提交
  17. 24 1月, 1999 1 次提交
  18. 22 1月, 1999 1 次提交
  19. 18 1月, 1999 1 次提交
    • B
      Hi! · bd8ffc6f
      Bruce Momjian 提交于
      INTERSECT and EXCEPT is available for postgresql-v6.4!
      
      The patch against v6.4 is included at the end of the current text
      (in uuencoded form!)
      
      I also included the text of my Master's Thesis. (a postscript
      version). I hope that you find something of it useful and would be
      happy if parts of it find their way into the PostgreSQL documentation
      project (If so, tell me, then I send the sources of the document!)
      
      The contents of the document are:
        -) The first chapter might be of less interest as it gives only an
           overview on SQL.
      
        -) The second chapter gives a description on much of PostgreSQL's
           features (like user defined types etc. and how to use these features)
      
        -) The third chapter starts with an overview of PostgreSQL's internal
           structure with focus on the stages a query has to pass (i.e. parser,
           planner/optimizer, executor). Then a detailed description of the
           implementation of the Having clause and the Intersect/Except logic is
           given.
      
      Originally I worked on v6.3.2 but never found time enough to prepare
      and post a patch. Now I applied the changes to v6.4 to get Intersect
      and Except working with the new version. Chapter 3 of my documentation
      deals with the changes against v6.3.2, so keep that in mind when
      comparing the parts of the code printed there with the patched sources
      of v6.4.
      
      Here are some remarks on the patch. There are some things that have
      still to be done but at the moment I don't have time to do them
      myself. (I'm doing my military service at the moment) Sorry for that
      :-(
      
      -) I used a rewrite technique for the implementation of the Except/Intersect
         logic which rewrites the query to a semantically equivalent query before
         it is handed to the rewrite system (for views, rules etc.), planner,
         executor etc.
      
      -) In v6.3.2 the types of the attributes of two select statements
         connected by the UNION keyword had to match 100%. In v6.4 the types
         only need to be familiar (i.e. int and float can be mixed). Since this
         feature did not exist when I worked on Intersect/Except it
         does not work correctly for Except/Intersect queries WHEN USED IN
         COMBINATION WITH UNIONS! (i.e. sometimes the wrong type is used for the
         resulting table. This is because until now the types of the attributes of
         the first select statement have been used for the resulting table.
         When Intersects and/or Excepts are used in combination with Unions it
         might happen, that the first select statement of the original query
         appears at another position in the query which will be executed. The reason
         for this is the technique used for the implementation of
         Except/Intersect which does a query rewrite!)
         NOTE: It is NOT broken for pure UNION queries and pure INTERSECT/EXCEPT
               queries!!!
      
      -) I had to add the field intersect_clause to some data structures
         but did not find time to implement printfuncs for the new field.
         This does NOT break the debug modes but when an Except/Intersect
         is used the query debug output will be the already rewritten query.
      
      -) Massive changes to the grammar rules for SELECT and INSERT statements
         have been necessary (see comments in gram.y and documentation for
         deatails) in order to be able to use mixed queries like
         (SELECT ... UNION (SELECT ... EXCEPT SELECT)) INTERSECT SELECT...;
      
      -) When using UNION/EXCEPT/INTERSECT you will get:
         NOTICE: equal: "Don't know if nodes of type xxx are equal".
         I did not have  time to add comparsion support for all the needed nodes,
         but the default behaviour of the function equal met my requirements.
         I did not dare to supress this message!
      
         That's the reason why the regression test for union will fail: These
         messages are also included in the union.out file!
      
      -) Somebody of you changed the union_planner() function for v6.4
         (I copied the targetlist to new_tlist and that was removed and
         replaced by a cleanup of the original targetlist). These chnages
         violated some having queries executed against views so I changed
         it back again. I did not have time to examine the differences between the
         two versions but now it works :-)
         If you want to find out, try the file queries/view_having.sql on
         both versions and compare the results . Two queries won't produce a
         correct result with your version.
      
      regards
      
          Stefan
      bd8ffc6f
  20. 14 12月, 1998 1 次提交
  21. 04 12月, 1998 1 次提交
  22. 22 10月, 1998 1 次提交
    • B
      The patch does 2 things: · 524f4b2d
      Bruce Momjian 提交于
              Fixes  a  bug  in  the rule system that caused a crashing
              backend when a join-view with calculated column  is  used
              in subselect.
      
              Modifies  EXPLAIN to explain rewritten queries instead of
              the plain SeqScan on a view. Rules can produce very  deep
      MORE
      
      Jan.
      524f4b2d
  23. 21 10月, 1998 1 次提交
  24. 03 10月, 1998 2 次提交
    • B
      · e5a8da02
      Bruce Momjian 提交于
          Please apply the patch at the end. Disables use of system
          columns of views at all (not only oid, cmin etc. too).
          pgsql=> select cmin from pg_rules;
          ERROR:  system column cmin not available - pg_rules is a view
          pgsql=> select * from pg_rules where pg_rules.oid = pg_class.oid;
          ERROR:  system column oid not available - pg_rules is a view
          pgsql=>
      
      Jan
      e5a8da02
    • B
      Here's a combination of all the patches I'm currently waiting · f93b6974
      Bruce Momjian 提交于
          for against a just updated CVS tree. It contains
      
              Partial new rewrite system that handles subselects,  view
              aggregate  columns, insert into select from view, updates
              with set col = view-value and select rules restriction to
              view definition.
      
              Updates  for  rule/view  backparsing utility functions to
              handle subselects correct.
      
      
              New system views pg_tables and pg_indexes (where you  can
              see the complete index definition in the latter one).
      
              Enabling array references on query parameters.
      
              Bugfix for functional index.
      
              Little changes to system views pg_rules and pg_views.
      
      
          The rule system isn't a release-stopper any longer.
      
          But  another  stopper  is  that  I  don't  know if the latest
          changes to PL/pgSQL (not already in CVS) made it  compile  on
          AIX. Still wait for some response from Dave.
      
      Jan
      f93b6974
  25. 01 9月, 1998 1 次提交
  26. 24 8月, 1998 1 次提交
    • B
      This is the final state of the rule system for 6.4 after the · 15cb32d9
      Bruce Momjian 提交于
          patch is applied:
      
      	Rewrite rules on relation level work fine now.
      
      	Event qualifications on insert/update/delete  rules  work
      	fine now.
      
      	I  added  the  new  keyword  OLD to reference the CURRENT
      	tuple. CURRENT will be removed in 6.5.
      
      	Update rules can  reference  NEW  and  OLD  in  the  rule
      	qualification and the actions.
      
      	Insert/update/delete rules on views can be established to
      	let them behave like real tables.
      
      	For  insert/update/delete  rules  multiple  actions   are
      	supported  now.   The  actions  can also be surrounded by
      	parantheses to make psql  happy.   Multiple  actions  are
      	required if update to a view requires updates to multiple
      	tables.
      
      	Regular users  are  permitted  to  create/drop  rules  on
      	tables     they     have     RULE     permissions     for
      	(DefineQueryRewrite() is  now  able  to  get  around  the
      	access  restrictions  on  pg_rewrite).  This enables view
      	creation for regular users too. This  required  an  extra
      	boolean  parameter  to  pg_parse_and_plan() that tells to
      	set skipAcl on all rangetable entries  of  the  resulting
      	queries.       There      is      a      new     function
      	pg_exec_query_acl_override()  that  could  be   used   by
      	backend utilities to use this facility.
      
      	All rule actions (not only views) inherit the permissions
      	of the event relations  owner.  Sample:  User  A  creates
      	tables    T1    and    T2,   creates   rules   that   log
      	INSERT/UPDATE/DELETE on T1 in T2 (like in the  regression
      	tests  for rules I created) and grants ALL but RULE on T1
      	to user B.  User B  can  now  fully  access  T1  and  the
      	logging  happens  in  T2.  But user B cannot access T2 at
      	all, only the rule actions can. And due to  missing  RULE
      	permissions on T1, user B cannot disable logging.
      
      	Rules  on  the  attribute  level are disabled (they don't
      	work properly and since regular users are  now  permitted
      	to create rules I decided to disable them).
      
      	Rules  on  select  must have exactly one action that is a
      	select (so select rules must be a view definition).
      
      	UPDATE NEW/OLD rules  are  disabled  (still  broken,  but
      	triggers can do it).
      
      	There are two new system views (pg_rule and pg_view) that
      	show the definition of the rules or views so the db admin
      	can  see  what  the  users do. They use two new functions
      	pg_get_ruledef() and pg_get_viewdef() that are  builtins.
      
      	The functions pg_get_ruledef() and pg_get_viewdef() could
      	be used to implement rule and view support in pg_dump.
      
      	PostgreSQL is now the only database system I  know,  that
      	has rewrite rules on the query level. All others (where I
      	found a  rule  statement  at  all)  use  stored  database
      	procedures  or  the  like  (triggers as we call them) for
      	active rules (as some call them).
      
          Future of the rule system:
      
      	The now disabled parts  of  the  rule  system  (attribute
      	level,  multiple  actions on select and update new stuff)
      	require a complete new rewrite handler from scratch.  The
      	old one is too badly wired up.
      
      	After  6.4  I'll  start to work on a new rewrite handler,
      	that fully supports the attribute level  rules,  multiple
      	actions on select and update new.  This will be available
      	for 6.5 so we get full rewrite rule capabilities.
      
      Jan
      15cb32d9
  27. 19 8月, 1998 1 次提交
    • B
      heap_fetch requires buffer pointer, must be released; heap_getnext · 79715390
      Bruce Momjian 提交于
      no longer returns buffer pointer, can be gotten from scan;
      	descriptor; bootstrap can create multi-key indexes;
      pg_procname index now is multi-key index; oidint2, oidint4, oidname
      are gone (must be removed from regression tests); use System Cache
      rather than sequential scan in many places; heap_modifytuple no
      longer takes buffer parameter; remove unused buffer parameter in
      a few other functions; oid8 is not index-able; remove some use of
      single-character variable names; cleanup Buffer variables usage
      and scan descriptor looping; cleaned up allocation and freeing of
      tuples; 18k lines of diff;
      79715390
  28. 18 8月, 1998 1 次提交
    • M
      · 338c54cb
      Marc G. Fournier 提交于
      From: Jan Wieck <jwieck@debis.com>
      
      Hi,
      
          as  proposed here comes the first patch for the query rewrite
          system.
      
        <for details, see archive dated Mon, 17 Aug 1998>
      338c54cb
  29. 19 7月, 1998 1 次提交
    • B
      1) Queries using the having clause on base tables should work well · 460b20a4
      Bruce Momjian 提交于
         now. Here some tested features, (examples included in the patch):
      
      1.1) Subselects in the having clause 1.2) Double nested subselects
      1.3) Subselects used in the where clause and in the having clause
           simultaneously 1.4) Union Selects using having 1.5) Indexes
      on the base relations are used correctly 1.6) Unallowed Queries
      are prevented (e.g. qualifications in the
           having clause that belong to the where clause) 1.7) Insert
      into as select
      
      2) Queries using the having clause on view relations also work
         but there are some restrictions:
      
      2.1) Create View as Select ... Having ...; using base tables in
      the select 2.1.1) The Query rewrite system:
      
      2.1.2) Why are only simple queries allowed against a view from 2.1)
      ? 2.2) Select ... from testview1, testview2, ... having...; 3) Bug
      in ExecMergeJoin ??
      
      
      Regards Stefan
      460b20a4
  30. 16 6月, 1998 1 次提交
  31. 31 3月, 1998 1 次提交
    • B
      I started adding the Having Clause and it works quite fine for · c579ce0f
      Bruce Momjian 提交于
      sequential scans! (I think it will also work with hash, index, etc
      but I did not check it out! I made some High level changes which
      should work for all access methods, but maybe I'm wrong. Please
      let me know.)
      
      Now it is possible to make queries like:
      
      select s.sname, max(p.pid), min(p.pid) from part p, supplier s
      where s.sid=p.sid group by s.sname having max(pid)=6 and min(pid)=1
      or avg(pid)=4;
      
      Having does not work yet for queries that contain a subselect
      statement in the Having clause, I'll try to fix this in the next
      days.
      
      If there are some bugs, please let me know, I'll start to read the
      mailinglists now!
      
      Now here is the patch against the original 6.3 version (no snapshot!!):
      
      Stefan
      c579ce0f
  32. 26 2月, 1998 1 次提交
  33. 25 2月, 1998 1 次提交
    • M
      From: Jan Wieck <jwieck@debis.com> · 780068f8
      Marc G. Fournier 提交于
          seems  that  my last post didn't make it through. That's good
          since  the  diff  itself  didn't  covered  the  renaming   of
          pg_user.h to pg_shadow.h and it's new content.
      
          Here  it's  again.  The  complete regression test passwd with
          only some  float  diffs.  createuser  and  destroyuser  work.
          pg_shadow cannot be read by ordinary user.
      780068f8
  34. 21 2月, 1998 1 次提交
    • M
      First step done, · 7b30490b
      Marc G. Fournier 提交于
          below  is  the patch to have views to override the permission
          checks for the accessed tables. Now we can do the following:
      
          CREATE VIEW db_user AS SELECT
               usename,
               usesysid,
               usecreatedb,
               usetrace,
               usecatupd,
               '**********'::text as passwd,
               valuntil
              FROM pg_user;
      
          REVOKE ALL ON pg_user FROM public;
          REVOKE ALL ON db_user FROM public;
          GRANT SELECT ON db_user TO public;
      7b30490b
  35. 21 1月, 1998 1 次提交
  36. 09 1月, 1998 1 次提交