(2011/11/07 20:26), Shigeru Hanada wrote:
> (2011/10/20 18:56), Etsuro Fujita wrote:
>> I revised the patch according to Hanada-san's comments. Attached is the
>> updated version of the patch.
>>
>> Changes:
>>
>>     * pull up of logging "analyzing foo.bar"
>>     * new vac_update_relstats always called
>>     * tab-completion in psql
>>     * add "foreign tables are not analyzed automatically..." to 23.1.3
>> Updating Planner Statistics
>>     * some other modifications
> 
> Submission review
> =================
> 
> - Patch can be applied, and all regression tests passed. :)

Thank you for your testing.  I updated the patch according to your
comments.  Attached is the updated version of the patch.

> - file_fdw_do_analyze_rel is almost copy of do_analyze_rel.  IIUC,
> difference against do_analyze_rel are:
>      * don't logging analyze target
>      * don't switch userid to the owner of target table
>      * don't measure elapsed time for autoanalyze deamon
>      * don't handle index
>      * some comments are removed.
>      * sample rows are acquired by file_fdw's routine
> 
> I don't see any problem here, but would you confirm that all of them are
> intentional?

Yes.  But in the updated version, I've refactored analyze.c a little bit
to allow FDW authors to simply call do_analyze_rel().

> - In your design, each FDW have to copy most of do_analyze_rel to their
> own source.  It means that FDW authors must know much details of ANALYZE
> to implement ANALYZE handler.  Actually, your patch exports some static
> functions from analyze.c.  Have you considered hooking
> acquire_sample_rows()?  Such handler should be more simple, and
> FDW-specific.  As you say, such design requires FDWs to skip some
> records, but it would be difficult for some FDW (e.g. twitter_fdw) which
> can't pick sample data up easily.  IMHO such problem *must* be solved by
> FDW itself.

The updated version enables FDW authors to just write their own
acquire_sample_rows().  On the other hand, by retaining to hook
AnalyzeForeignTable routine at analyze_rel(), higher level than
acquire_sample_rows() as before, it allows FDW authors to write
AnalyzeForeignTable routine for foreign tables on a remote server to ask
the server for its current stats instead, as pointed out earlier by Tom
Lane.

Best regards,
Etsuro Fujita
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
***************
*** 16,31 ****
  #include <unistd.h>
  
  #include "access/reloptions.h"
  #include "catalog/pg_foreign_table.h"
  #include "commands/copy.h"
  #include "commands/defrem.h"
  #include "commands/explain.h"
  #include "foreign/fdwapi.h"
  #include "foreign/foreign.h"
  #include "miscadmin.h"
  #include "nodes/makefuncs.h"
  #include "optimizer/cost.h"
! #include "utils/rel.h"
  #include "utils/syscache.h"
  
  PG_MODULE_MAGIC;
--- 16,36 ----
  #include <unistd.h>
  
  #include "access/reloptions.h"
+ #include "access/transam.h"
  #include "catalog/pg_foreign_table.h"
  #include "commands/copy.h"
  #include "commands/defrem.h"
  #include "commands/explain.h"
+ #include "commands/vacuum.h"
  #include "foreign/fdwapi.h"
  #include "foreign/foreign.h"
  #include "miscadmin.h"
  #include "nodes/makefuncs.h"
  #include "optimizer/cost.h"
! #include "parser/parse_relation.h"
! #include "pgstat.h"
! #include "utils/attoptcache.h"
! #include "utils/memutils.h"
  #include "utils/syscache.h"
  
  PG_MODULE_MAGIC;
***************
*** 101,106 **** static void fileBeginForeignScan(ForeignScanState *node, int 
eflags);
--- 106,112 ----
  static TupleTableSlot *fileIterateForeignScan(ForeignScanState *node);
  static void fileReScanForeignScan(ForeignScanState *node);
  static void fileEndForeignScan(ForeignScanState *node);
+ static void fileAnalyzeForeignTable(Relation onerel, VacuumStmt *vacstmt, int 
elevel);
  
  /*
   * Helper functions
***************
*** 112,118 **** static List *get_file_fdw_attribute_options(Oid relid);
  static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
                           const char *filename,
                           Cost *startup_cost, Cost *total_cost);
! 
  
  /*
   * Foreign-data wrapper handler function: return a struct with pointers
--- 118,127 ----
  static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
                           const char *filename,
                           Cost *startup_cost, Cost *total_cost);
! static int    acquire_sample_rows(Relation onerel,
!                          HeapTuple *rows, int targrows,
!                          double *totalrows, double *totaldeadrows,
!                          BlockNumber *totalpages, int elevel);
  
  /*
   * Foreign-data wrapper handler function: return a struct with pointers
***************
*** 129,134 **** file_fdw_handler(PG_FUNCTION_ARGS)
--- 138,144 ----
        fdwroutine->IterateForeignScan = fileIterateForeignScan;
        fdwroutine->ReScanForeignScan = fileReScanForeignScan;
        fdwroutine->EndForeignScan = fileEndForeignScan;
+       fdwroutine->AnalyzeForeignTable = fileAnalyzeForeignTable;
  
        PG_RETURN_POINTER(fdwroutine);
  }
***************
*** 575,580 **** fileReScanForeignScan(ForeignScanState *node)
--- 585,602 ----
  }
  
  /*
+  * fileAnalyzeForeignTable
+  *            Analyze table
+  */
+ static void
+ fileAnalyzeForeignTable(Relation onerel, VacuumStmt *vacstmt, int elevel)
+ {
+       do_analyze_rel(onerel, vacstmt,
+                                  elevel, false,
+                                  acquire_sample_rows);
+ }
+ 
+ /*
   * Estimate costs of scanning a foreign table.
   */
  static void
***************
*** 584,590 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
  {
        struct stat stat_buf;
        BlockNumber pages;
!       int                     tuple_width;
        double          ntuples;
        double          nrows;
        Cost            run_cost = 0;
--- 606,613 ----
  {
        struct stat stat_buf;
        BlockNumber pages;
!       BlockNumber     relpages;
!       double          reltuples;
        double          ntuples;
        double          nrows;
        Cost            run_cost = 0;
***************
*** 604,619 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
        if (pages < 1)
                pages = 1;
  
!       /*
!        * Estimate the number of tuples in the file.  We back into this 
estimate
!        * using the planner's idea of the relation width; which is bogus if not
!        * all columns are being read, not to mention that the text 
representation
!        * of a row probably isn't the same size as its internal representation.
!        * FIXME later.
!        */
!       tuple_width = MAXALIGN(baserel->width) + 
MAXALIGN(sizeof(HeapTupleHeaderData));
  
!       ntuples = clamp_row_est((double) stat_buf.st_size / (double) 
tuple_width);
  
        /*
         * Now estimate the number of rows returned by the scan after applying 
the
--- 627,658 ----
        if (pages < 1)
                pages = 1;
  
!       relpages = baserel->pages;
!       reltuples = baserel->tuples;
! 
!       if (relpages > 0)
!       {
!               double          density;
  
!               density = reltuples / (double) relpages;
! 
!               ntuples = clamp_row_est(density * (double) pages);
!       }
!       else
!       {
!               int                     tuple_width;
! 
!               /*
!                * Estimate the number of tuples in the file.  We back into 
this estimate
!                * using the planner's idea of the relation width; which is 
bogus if not
!                * all columns are being read, not to mention that the text 
representation
!                * of a row probably isn't the same size as its internal 
representation.
!                * FIXME later.
!                */
!               tuple_width = MAXALIGN(baserel->width) + 
MAXALIGN(sizeof(HeapTupleHeaderData));
! 
!               ntuples = clamp_row_est((double) stat_buf.st_size / (double) 
tuple_width);
!       }
  
        /*
         * Now estimate the number of rows returned by the scan after applying 
the
***************
*** 645,647 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
--- 684,888 ----
        run_cost += cpu_per_tuple * ntuples;
        *total_cost = *startup_cost + run_cost;
  }
+ 
+ /*
+  * acquire_sample_rows -- acquire a random sample of rows from the table
+  *
+  * Selected rows are returned in the caller-allocated array rows[], which
+  * must have at least targrows entries.
+  * The actual number of rows selected is returned as the function result.
+  * We also count the number of rows in the table, and return it into 
*totalrows.
+  *
+  * The returned list of tuples is in order by physical position in the table.
+  * (We will rely on this later to derive correlation estimates.)
+  */
+ static int
+ acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
+                                       double *totalrows, double 
*totaldeadrows,
+                                       BlockNumber *totalpages, int elevel)
+ {
+       int                     numrows = 0;
+       double          samplerows = 0;  /* total # rows collected */
+       double          rowstoskip = -1; /* -1 means not set yet */
+       double          rstate;
+       HeapTuple       tuple;
+       TupleDesc       tupDesc;
+       TupleConstr *constr;
+       int                     natts;
+       int                     attrChk;
+       Datum      *values;
+       bool       *nulls;
+       bool            found;
+       bool            sample_it = false;
+       BlockNumber     blknum;
+       OffsetNumber offnum;
+       char       *filename;
+       struct stat     stat_buf;
+       List       *options;
+       CopyState       cstate;
+       ErrorContextCallback errcontext;
+ 
+       Assert(onerel);
+       Assert(targrows > 0);
+ 
+       tupDesc = RelationGetDescr(onerel);
+       constr = tupDesc->constr;
+       natts = tupDesc->natts;
+       values = (Datum *) palloc(tupDesc->natts * sizeof(Datum));
+       nulls = (bool *) palloc(tupDesc->natts * sizeof(bool));
+ 
+       /* Fetch options of foreign table */
+       fileGetOptions(RelationGetRelid(onerel), &filename, &options);
+ 
+       /*
+        * Get size of the file.
+        */
+       if (stat(filename, &stat_buf) < 0)
+               ereport(ERROR,
+                               (errcode_for_file_access(),
+                                errmsg("could not stat file \"%s\": %m",
+                                               filename)));
+ 
+       /*
+        * Convert size to pages for use in I/O cost estimate.
+        */
+       *totalpages = (stat_buf.st_size + (BLCKSZ - 1)) / BLCKSZ;
+       if (*totalpages < 1)
+               *totalpages = 1;
+ 
+       /*
+        * Create CopyState from FDW options.  We always acquire all columns, so
+        * as to match the expected ScanTupleSlot signature.
+        */
+       cstate = BeginCopyFrom(onerel, filename, NIL, options);
+ 
+       /* Prepare for sampling rows */
+       rstate = init_selection_state(targrows);
+ 
+       /* Set up callback to identify error line number. */
+       errcontext.callback = CopyFromErrorCallback;
+       errcontext.arg = (void *) cstate;
+       errcontext.previous = error_context_stack;
+       error_context_stack = &errcontext;
+ 
+       for (;;)
+       {
+               sample_it = true;
+ 
+               /*
+                * Check for user-requested abort.
+                */
+               CHECK_FOR_INTERRUPTS();
+ 
+               found = NextCopyFrom(cstate, NULL, values, nulls, NULL);
+ 
+               if (!found)
+                       break;
+ 
+               tuple = heap_form_tuple(tupDesc, values, nulls);
+ 
+               if (constr && constr->has_not_null)
+               {
+                       for (attrChk = 1; attrChk <= natts; attrChk++)
+                       {
+                               if (onerel->rd_att->attrs[attrChk - 
1]->attnotnull &&
+                                       heap_attisnull(tuple, attrChk))
+                               {
+                                       sample_it = false;
+                                       break;
+                               }
+                       }
+               }
+ 
+               if (!sample_it)
+               {
+                       heap_freetuple(tuple);
+                       continue;
+               }
+ 
+               /*
+                * The first targrows sample rows are simply copied into the
+                * reservoir. Then we start replacing tuples in the sample
+                * until we reach the end of the relation.      This algorithm 
is
+                * from Jeff Vitter's paper (see full citation below). It
+                * works by repeatedly computing the number of tuples to skip
+                * before selecting a tuple, which replaces a randomly chosen
+                * element of the reservoir (current set of tuples).  At all
+                * times the reservoir is a true random sample of the tuples
+                * we've passed over so far, so when we fall off the end of
+                * the relation we're done.
+                */
+               if (numrows < targrows)
+               {
+                       blknum = (BlockNumber) samplerows / MaxOffsetNumber;
+                       offnum = (OffsetNumber) samplerows % MaxOffsetNumber + 
1;
+                       ItemPointerSet(&tuple->t_self, blknum, offnum);
+                       rows[numrows++] = heap_copytuple(tuple);
+               }
+               else
+               {
+                       /*
+                        * t in Vitter's paper is the number of records already
+                        * processed.  If we need to compute a new S value, we
+                        * must use the not-yet-incremented value of samplerows 
as
+                        * t.
+                        */
+                       if (rowstoskip < 0)
+                               rowstoskip = get_next_S(samplerows, targrows, 
&rstate);
+ 
+                       if (rowstoskip <= 0)
+                       {
+                               /*
+                                * Found a suitable tuple, so save it, 
replacing one
+                                * old tuple at random
+                                */
+                               int k = (int) (targrows * random_fract());
+ 
+                               Assert(k >= 0 && k < targrows);
+                               heap_freetuple(rows[k]);
+ 
+                               blknum = (BlockNumber) samplerows / 
MaxOffsetNumber;
+                               offnum = (OffsetNumber) samplerows % 
MaxOffsetNumber + 1;
+                               ItemPointerSet(&tuple->t_self, blknum, offnum);
+                               rows[k] = heap_copytuple(tuple);
+                       }
+ 
+                       rowstoskip -= 1;
+               }
+ 
+               samplerows += 1;
+               heap_freetuple(tuple);
+       }
+ 
+       /* Remove error callback. */
+       error_context_stack = errcontext.previous;
+ 
+       /*
+        * If we didn't find as many tuples as we wanted then we're done. No 
sort
+        * is needed, since they're already in order.
+        *
+        * Otherwise we need to sort the collected tuples by position
+        * (itempointer). It's not worth worrying about corner cases where the
+        * tuples are already sorted.
+        */
+       if (numrows == targrows)
+               qsort((void *) rows, numrows, sizeof(HeapTuple), compare_rows);
+ 
+       *totalrows = samplerows;
+       *totaldeadrows = 0;
+ 
+       EndCopyFrom(cstate);
+ 
+       pfree(values);
+       pfree(nulls);
+ 
+       /*
+        * Emit some interesting relation info
+        */
+       ereport(elevel,
+                       (errmsg("\"%s\": scanned, "
+                                       "%d rows in sample, %d total rows",
+                                       RelationGetRelationName(onerel), 
numrows, (int) *totalrows)));
+ 
+       return numrows;
+ }
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 111,116 **** EXECUTE st(100);
--- 111,121 ----
  EXECUTE st(100);
  DEALLOCATE st;
  
+ -- statistics collection tests
+ ANALYZE agg_csv;
+ SELECT relpages, reltuples FROM pg_class WHERE relname = 'agg_csv';
+ SELECT * FROM pg_stats WHERE tablename = 'agg_csv';
+ 
  -- tableoid
  SELECT tableoid::regclass, b FROM agg_csv;
  
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
***************
*** 174,179 **** EXECUTE st(100);
--- 174,194 ----
  (1 row)
  
  DEALLOCATE st;
+ -- statistics collection tests
+ ANALYZE agg_csv;
+ SELECT relpages, reltuples FROM pg_class WHERE relname = 'agg_csv';
+  relpages | reltuples 
+ ----------+-----------
+         1 |         3
+ (1 row)
+ 
+ SELECT * FROM pg_stats WHERE tablename = 'agg_csv';
+  schemaname | tablename | attname | inherited | null_frac | avg_width | 
n_distinct | most_common_vals | most_common_freqs |    histogram_bounds     | 
correlation 
+ 
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+-------------------------+-------------
+  public     | agg_csv   | a       | f         |         0 |         2 |       
  -1 |                  |                   | {0,42,100}              |        
-0.5
+  public     | agg_csv   | b       | f         |         0 |         4 |       
  -1 |                  |                   | {0.09561,99.097,324.78} |         
0.5
+ (2 rows)
+ 
  -- tableoid
  SELECT tableoid::regclass, b FROM agg_csv;
   tableoid |    b    
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 228,233 **** EndForeignScan (ForeignScanState *node);
--- 228,246 ----
      </para>
  
      <para>
+ <programlisting>
+ void
+ AnalyzeForeignTable (Relation onerel,
+                      VacuumStmt *vacstmt, 
+                      int elevel);
+ </programlisting>
+ 
+      Collect statistics on a foreign table and store the results in the
+      pg_class and pg_statistics system catalogs.
+      This is called when <command>ANALYZE</> command is run.
+     </para>
+ 
+     <para>
       The <structname>FdwRoutine</> and <structname>FdwPlan</> struct types
       are declared in <filename>src/include/foreign/fdwapi.h</>, which see
       for additional details.
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
***************
*** 279,284 ****
--- 279,288 ----
      <command>ANALYZE</> strictly as a function of the number of rows
      inserted or updated; it has no knowledge of whether that will lead
      to meaningful statistical changes.
+     Note that the autovacuum daemon does not issue <command>ANALYZE</>
+     commands on foreign tables.  It is recommended to run manually-managed
+     <command>ANALYZE</> commands as needed, which typically are executed
+     according to a schedule by cron or Task Scheduler scripts.
     </para>
  
     <para>
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
***************
*** 36,41 **** ALTER FOREIGN TABLE <replaceable 
class="PARAMETER">name</replaceable>
--- 36,44 ----
      DROP [ COLUMN ] [ IF EXISTS ] <replaceable 
class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ 
SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable>
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { 
SET | DROP } NOT NULL
+     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET 
STATISTICS <replaceable class="PARAMETER">integer</replaceable>
+     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET 
( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable 
class="PARAMETER">value</replaceable> [, ... ] )
+     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> 
RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> 
OPTIONS ( [ ADD | SET | DROP ] <replaceable 
class="PARAMETER">option</replaceable> ['<replaceable 
class="PARAMETER">value</replaceable>'] [, ... ])
      OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
      OPTIONS ( [ ADD | SET | DROP ] <replaceable 
class="PARAMETER">option</replaceable> ['<replaceable 
class="PARAMETER">value</replaceable>'] [, ... ])
***************
*** 94,99 **** ALTER FOREIGN TABLE <replaceable 
class="PARAMETER">name</replaceable>
--- 97,146 ----
     </varlistentry>
  
     <varlistentry>
+     <term><literal>SET STATISTICS</literal></term>
+     <listitem>
+      <para>
+       This form
+       sets the per-column statistics-gathering target for subsequent
+       <xref linkend="sql-analyze"> operations.
+       The target can be set in the range 0 to 10000; alternatively, set it
+       to -1 to revert to using the system default statistics
+       target (<xref linkend="guc-default-statistics-target">).
+      </para>
+     </listitem>
+    </varlistentry>
+ 
+    <varlistentry>
+     <term><literal>SET ( <replaceable 
class="PARAMETER">attribute_option</replaceable> = <replaceable 
class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
+     <term><literal>RESET ( <replaceable 
class="PARAMETER">attribute_option</replaceable> [, ... ] )</literal></term>
+     <listitem>
+      <para>
+       This form
+       sets or resets a per-attribute option.  Currently, the only defined
+       per-attribute option is <literal>n_distinct</>, which overrides
+       the number-of-distinct-values estimates made by subsequent
+       <xref linkend="sql-analyze"> operations. 
+       When set to a positive value, <command>ANALYZE</> will assume that
+       the column contains exactly the specified number of distinct nonnull
+       values.
+       When set to a negative value, which must be greater than or equal
+       to -1, <command>ANALYZE</> will assume that the number of distinct
+       nonnull values in the column is linear in the size of the foreign
+       table; the exact count is to be computed by multiplying the estimated
+       foreign table size by the absolute value of the given number.
+       For example,
+       a value of -1 implies that all values in the column are distinct,
+       while a value of -0.5 implies that each value appears twice on the
+       average.
+       This can be useful when the size of the foreign table changes over
+       time, since the multiplication by the number of rows in the foreign
+       table is not performed until query planning time.  Specify a value
+       of 0 to revert to estimating the number of distinct values normally.
+      </para>
+     </listitem>
+    </varlistentry>
+ 
+    <varlistentry>
      <term><literal>OWNER</literal></term>
      <listitem>
       <para>
*** a/doc/src/sgml/ref/analyze.sgml
--- b/doc/src/sgml/ref/analyze.sgml
***************
*** 39,47 **** ANALYZE [ VERBOSE ] [ <replaceable 
class="PARAMETER">table</replaceable> [ ( <re
  
    <para>
     With no parameter, <command>ANALYZE</command> examines every table in the
!    current database.  With a parameter, <command>ANALYZE</command> examines
!    only that table.  It is further possible to give a list of column names,
!    in which case only the statistics for those columns are collected.
    </para>
   </refsect1>
  
--- 39,49 ----
  
    <para>
     With no parameter, <command>ANALYZE</command> examines every table in the
!    current database except for foreign tables.  With a parameter, <command>
!    ANALYZE</command> examines only that table.  For a foreign table, it is
!    necessary to spcify the name of that table.  It is further possible to 
!    give a list of column names, in which case only the statistics for those
!    columns are collected.
    </para>
   </refsect1>
  
***************
*** 63,69 **** ANALYZE [ VERBOSE ] [ <replaceable 
class="PARAMETER">table</replaceable> [ ( <re
      <listitem>
       <para>
        The name (possibly schema-qualified) of a specific table to
!       analyze. Defaults to all tables in the current database.
       </para>
      </listitem>
     </varlistentry>
--- 65,72 ----
      <listitem>
       <para>
        The name (possibly schema-qualified) of a specific table to
!       analyze. Defaults to all tables in the current database except
!       for foreign tables.
       </para>
      </listitem>
     </varlistentry>
***************
*** 137,143 **** ANALYZE [ VERBOSE ] [ <replaceable 
class="PARAMETER">table</replaceable> [ ( <re
     In rare situations, this non-determinism will cause the planner's
     choices of query plans to change after <command>ANALYZE</command> is run.
     To avoid this, raise the amount of statistics collected by
!    <command>ANALYZE</command>, as described below.
    </para>
  
    <para>
--- 140,148 ----
     In rare situations, this non-determinism will cause the planner's
     choices of query plans to change after <command>ANALYZE</command> is run.
     To avoid this, raise the amount of statistics collected by
!    <command>ANALYZE</command>, as described below.  Note that the time
!    needed to analyze on foreign tables depends on the implementation of
!    the foreign data wrapper via which such tables are attached.
    </para>
  
    <para>
*** a/src/backend/commands/analyze.c
--- b/src/backend/commands/analyze.c
***************
*** 23,28 ****
--- 23,29 ----
  #include "access/xact.h"
  #include "catalog/index.h"
  #include "catalog/indexing.h"
+ #include "catalog/pg_class.h"
  #include "catalog/pg_collation.h"
  #include "catalog/pg_inherits_fn.h"
  #include "catalog/pg_namespace.h"
***************
*** 30,35 ****
--- 31,38 ----
  #include "commands/tablecmds.h"
  #include "commands/vacuum.h"
  #include "executor/executor.h"
+ #include "foreign/foreign.h"
+ #include "foreign/fdwapi.h"
  #include "miscadmin.h"
  #include "nodes/nodeFuncs.h"
  #include "parser/parse_oper.h"
***************
*** 78,91 **** typedef struct AnlIndexData
  int                   default_statistics_target = 100;
  
  /* A few variables that don't seem worth passing around as parameters */
- static int    elevel = -1;
- 
  static MemoryContext anl_context = NULL;
  
  static BufferAccessStrategy vac_strategy;
  
  
- static void do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh);
  static void BlockSampler_Init(BlockSampler bs, BlockNumber nblocks,
                                  int samplesize);
  static bool BlockSampler_HasMore(BlockSampler bs);
--- 81,91 ----
***************
*** 96,110 **** static void compute_index_stats(Relation onerel, double 
totalrows,
                                        MemoryContext col_context);
  static VacAttrStats *examine_attribute(Relation onerel, int attnum,
                                  Node *index_expr);
! static int acquire_sample_rows(Relation onerel, HeapTuple *rows,
!                                       int targrows, double *totalrows, double 
*totaldeadrows);
! static double random_fract(void);
! static double init_selection_state(int n);
! static double get_next_S(double t, int n, double *stateptr);
! static int    compare_rows(const void *a, const void *b);
  static int acquire_inherited_sample_rows(Relation onerel,
                                                          HeapTuple *rows, int 
targrows,
!                                                         double *totalrows, 
double *totaldeadrows);
  static void update_attstats(Oid relid, bool inh,
                                int natts, VacAttrStats **vacattrstats);
  static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
--- 96,109 ----
                                        MemoryContext col_context);
  static VacAttrStats *examine_attribute(Relation onerel, int attnum,
                                  Node *index_expr);
! static int acquire_sample_rows(Relation onerel,
!                                                          HeapTuple *rows, int 
targrows,
!                                                          double *totalrows, 
double *totaldeadrows,
!                                                          BlockNumber 
*totalpages, int elevel);
  static int acquire_inherited_sample_rows(Relation onerel,
                                                          HeapTuple *rows, int 
targrows,
!                                                         double *totalrows, 
double *totaldeadrows,
!                                                         BlockNumber 
*totalpages, int elevel);
  static void update_attstats(Oid relid, bool inh,
                                int natts, VacAttrStats **vacattrstats);
  static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
***************
*** 119,125 **** static bool std_typanalyze(VacAttrStats *stats);
--- 118,126 ----
  void
  analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy)
  {
+       int                     elevel;
        Relation        onerel;
+       FdwRoutine *fdwroutine;
  
        /* Set up static variables */
        if (vacstmt->options & VACOPT_VERBOSE)
***************
*** 184,193 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, 
BufferAccessStrategy bstrategy)
        }
  
        /*
!        * Check that it's a plain table; we used to do this in get_rel_oids() 
but
         * seems safer to check after we've locked the relation.
         */
!       if (onerel->rd_rel->relkind != RELKIND_RELATION)
        {
                /* No need for a WARNING if we already complained during VACUUM 
*/
                if (!(vacstmt->options & VACOPT_VACUUM))
--- 185,195 ----
        }
  
        /*
!        * Check that it's a plain table or foreign table; we used to do this 
in get_rel_oids() but
         * seems safer to check after we've locked the relation.
         */
!       if (onerel->rd_rel->relkind != RELKIND_RELATION &&
!               onerel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
        {
                /* No need for a WARNING if we already complained during VACUUM 
*/
                if (!(vacstmt->options & VACOPT_VACUUM))
***************
*** 212,217 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, 
BufferAccessStrategy bstrategy)
--- 214,221 ----
  
        /*
         * We can ANALYZE any table except pg_statistic. See update_attstats
+        * We can ANALYZE foreign tables if the underlying foreign-data wrappers
+        * have their AnalyzeForeignTable callback routines.
         */
        if (RelationGetRelid(onerel) == StatisticRelationId)
        {
***************
*** 219,224 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, 
BufferAccessStrategy bstrategy)
--- 223,242 ----
                return;
        }
  
+       if (onerel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+       {
+               fdwroutine = GetFdwRoutineByRelId(RelationGetRelid(onerel));
+ 
+               if (fdwroutine->AnalyzeForeignTable == NULL)
+               {
+                       ereport(WARNING,
+                                       (errmsg("skipping \"%s\" --- underlying 
foreign-data wrapper cannot analyze it",
+                                                       
RelationGetRelationName(onerel))));
+                       relation_close(onerel, ShareUpdateExclusiveLock);
+                       return;
+               }
+       }
+ 
        /*
         * OK, let's do it.  First let other backends know I'm in ANALYZE.
         */
***************
*** 226,241 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, 
BufferAccessStrategy bstrategy)
        MyProc->vacuumFlags |= PROC_IN_ANALYZE;
        LWLockRelease(ProcArrayLock);
  
!       /*
!        * Do the normal non-recursive ANALYZE.
!        */
!       do_analyze_rel(onerel, vacstmt, false);
  
!       /*
!        * If there are child tables, do recursive ANALYZE.
!        */
!       if (onerel->rd_rel->relhassubclass)
!               do_analyze_rel(onerel, vacstmt, true);
  
        /*
         * Close source relation now, but keep lock so that no one deletes it
--- 244,280 ----
        MyProc->vacuumFlags |= PROC_IN_ANALYZE;
        LWLockRelease(ProcArrayLock);
  
!       if (onerel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
!       {
!               ereport(elevel,
!                               (errmsg("analyzing \"%s.%s\"",
!                                               
get_namespace_name(RelationGetNamespace(onerel)),
!                                               
RelationGetRelationName(onerel))));
!               fdwroutine->AnalyzeForeignTable(onerel, vacstmt, elevel);
!       }
!       else
!       {
!               /*
!                * Do the normal non-recursive ANALYZE.
!                */
!               ereport(elevel,
!                               (errmsg("analyzing \"%s.%s\"",
!                                               
get_namespace_name(RelationGetNamespace(onerel)),
!                                               
RelationGetRelationName(onerel))));
!               do_analyze_rel(onerel, vacstmt, elevel, false, 
acquire_sample_rows);
  
!               /*
!                * If there are child tables, do recursive ANALYZE.
!                */
!               if (onerel->rd_rel->relhassubclass)
!               {
!                       ereport(elevel,
!                                       (errmsg("analyzing \"%s.%s\" 
inheritance tree",
!                                                       
get_namespace_name(RelationGetNamespace(onerel)),
!                                                       
RelationGetRelationName(onerel))));
!                       do_analyze_rel(onerel, vacstmt, elevel, true, 
acquire_inherited_sample_rows);
!               }
!       }
  
        /*
         * Close source relation now, but keep lock so that no one deletes it
***************
*** 257,264 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, 
BufferAccessStrategy bstrategy)
  /*
   *    do_analyze_rel() -- analyze one relation, recursively or not
   */
! static void
! do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
  {
        int                     attr_cnt,
                                tcnt,
--- 296,304 ----
  /*
   *    do_analyze_rel() -- analyze one relation, recursively or not
   */
! void
! do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, int elevel,
!                          bool inh, int (*sample_row_acquirer) ())
  {
        int                     attr_cnt,
                                tcnt,
***************
*** 273,278 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
--- 313,319 ----
                                numrows;
        double          totalrows,
                                totaldeadrows;
+       BlockNumber totalpages;
        HeapTuple  *rows;
        PGRUsage        ru0;
        TimestampTz starttime = 0;
***************
*** 281,297 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
        int                     save_sec_context;
        int                     save_nestlevel;
  
-       if (inh)
-               ereport(elevel,
-                               (errmsg("analyzing \"%s.%s\" inheritance tree",
-                                               
get_namespace_name(RelationGetNamespace(onerel)),
-                                               
RelationGetRelationName(onerel))));
-       else
-               ereport(elevel,
-                               (errmsg("analyzing \"%s.%s\"",
-                                               
get_namespace_name(RelationGetNamespace(onerel)),
-                                               
RelationGetRelationName(onerel))));
- 
        /*
         * Set up a working context so that we can easily free whatever junk 
gets
         * created.
--- 322,327 ----
***************
*** 449,459 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
         */
        rows = (HeapTuple *) palloc(targrows * sizeof(HeapTuple));
        if (inh)
!               numrows = acquire_inherited_sample_rows(onerel, rows, targrows,
!                                                                               
                &totalrows, &totaldeadrows);
        else
!               numrows = acquire_sample_rows(onerel, rows, targrows,
!                                                                         
&totalrows, &totaldeadrows);
  
        /*
         * Compute the statistics.      Temporary results during the 
calculations for
--- 479,491 ----
         */
        rows = (HeapTuple *) palloc(targrows * sizeof(HeapTuple));
        if (inh)
!               numrows = sample_row_acquirer(onerel, rows, targrows,
!                                                                         
&totalrows, &totaldeadrows,
!                                                                         NULL, 
elevel);
        else
!               numrows = sample_row_acquirer(onerel, rows, targrows,
!                                                                         
&totalrows, &totaldeadrows,
!                                                                         
&totalpages, elevel);
  
        /*
         * Compute the statistics.      Temporary results during the 
calculations for
***************
*** 534,540 **** do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, bool inh)
         */
        if (!inh)
                vac_update_relstats(onerel,
!                                                       
RelationGetNumberOfBlocks(onerel),
                                                        totalrows,
                                                        
visibilitymap_count(onerel),
                                                        hasindex,
--- 566,572 ----
         */
        if (!inh)
                vac_update_relstats(onerel,
!                                                       totalpages,
                                                        totalrows,
                                                        
visibilitymap_count(onerel),
                                                        hasindex,
***************
*** 1017,1023 **** BlockSampler_Next(BlockSampler bs)
   */
  static int
  acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
!                                       double *totalrows, double 
*totaldeadrows)
  {
        int                     numrows = 0;    /* # rows now in reservoir */
        double          samplerows = 0; /* total # rows collected */
--- 1049,1056 ----
   */
  static int
  acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
!                                       double *totalrows, double 
*totaldeadrows,
!                                       BlockNumber *totalpages, int elevel)
  {
        int                     numrows = 0;    /* # rows now in reservoir */
        double          samplerows = 0; /* total # rows collected */
***************
*** 1032,1037 **** acquire_sample_rows(Relation onerel, HeapTuple *rows, int 
targrows,
--- 1065,1072 ----
        Assert(targrows > 0);
  
        totalblocks = RelationGetNumberOfBlocks(onerel);
+       if (totalpages)
+         *totalpages = totalblocks;
  
        /* Need a cutoff xmin for HeapTupleSatisfiesVacuum */
        OldestXmin = GetOldestXmin(onerel->rd_rel->relisshared, true);
***************
*** 1254,1260 **** acquire_sample_rows(Relation onerel, HeapTuple *rows, int 
targrows,
  }
  
  /* Select a random value R uniformly distributed in (0 - 1) */
! static double
  random_fract(void)
  {
        return ((double) random() + 1) / ((double) MAX_RANDOM_VALUE + 2);
--- 1289,1295 ----
  }
  
  /* Select a random value R uniformly distributed in (0 - 1) */
! double
  random_fract(void)
  {
        return ((double) random() + 1) / ((double) MAX_RANDOM_VALUE + 2);
***************
*** 1274,1287 **** random_fract(void)
   * determines the number of records to skip before the next record is
   * processed.
   */
! static double
  init_selection_state(int n)
  {
        /* Initial value of W (for use when Algorithm Z is first applied) */
        return exp(-log(random_fract()) / n);
  }
  
! static double
  get_next_S(double t, int n, double *stateptr)
  {
        double          S;
--- 1309,1322 ----
   * determines the number of records to skip before the next record is
   * processed.
   */
! double
  init_selection_state(int n)
  {
        /* Initial value of W (for use when Algorithm Z is first applied) */
        return exp(-log(random_fract()) / n);
  }
  
! double
  get_next_S(double t, int n, double *stateptr)
  {
        double          S;
***************
*** 1366,1372 **** get_next_S(double t, int n, double *stateptr)
  /*
   * qsort comparator for sorting rows[] array
   */
! static int
  compare_rows(const void *a, const void *b)
  {
        HeapTuple       ha = *(const HeapTuple *) a;
--- 1401,1407 ----
  /*
   * qsort comparator for sorting rows[] array
   */
! int
  compare_rows(const void *a, const void *b)
  {
        HeapTuple       ha = *(const HeapTuple *) a;
***************
*** 1397,1403 **** compare_rows(const void *a, const void *b)
   */
  static int
  acquire_inherited_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
!                                                         double *totalrows, 
double *totaldeadrows)
  {
        List       *tableOIDs;
        Relation   *rels;
--- 1432,1439 ----
   */
  static int
  acquire_inherited_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
!                                                         double *totalrows, 
double *totaldeadrows,
!                                                         BlockNumber 
*totalpages, int elevel)
  {
        List       *tableOIDs;
        Relation   *rels;
***************
*** 1460,1465 **** acquire_inherited_sample_rows(Relation onerel, HeapTuple 
*rows, int targrows,
--- 1496,1503 ----
                totalblocks += relblocks[nrels];
                nrels++;
        }
+       if (totalpages)
+         *totalpages = totalblocks;
  
        /*
         * Now sample rows from each relation, proportionally to its fraction of
***************
*** 1493,1499 **** acquire_inherited_sample_rows(Relation onerel, HeapTuple 
*rows, int targrows,
                                                                                
                rows + numrows,
                                                                                
                childtargrows,
                                                                                
                &trows,
!                                                                               
                &tdrows);
  
                                /* We may need to convert from child's rowtype 
to parent's */
                                if (childrows > 0 &&
--- 1531,1539 ----
                                                                                
                rows + numrows,
                                                                                
                childtargrows,
                                                                                
                &trows,
!                                                                               
                &tdrows,
!                                                                               
                NULL,
!                                                                               
                elevel);
  
                                /* We may need to convert from child's rowtype 
to parent's */
                                if (childrows > 0 &&
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 311,316 **** static void ATPrepSetStatistics(Relation rel, const char 
*colName,
--- 311,318 ----
                                        Node *newValue, LOCKMODE lockmode);
  static void ATExecSetStatistics(Relation rel, const char *colName,
                                        Node *newValue, LOCKMODE lockmode);
+ static void ATPrepSetOptions(Relation rel, const char *colName,
+                                Node *options, LOCKMODE lockmode);
  static void ATExecSetOptions(Relation rel, const char *colName,
                                 Node *options, bool isReset, LOCKMODE 
lockmode);
  static void ATExecSetStorage(Relation rel, const char *colName,
***************
*** 2887,2892 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
--- 2889,2895 ----
                case AT_SetOptions:             /* ALTER COLUMN SET ( options ) 
*/
                case AT_ResetOptions:   /* ALTER COLUMN RESET ( options ) */
                        ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX);
+                       ATPrepSetOptions(rel, cmd->name, cmd->def, lockmode);
                        /* This command never recurses */
                        pass = AT_PASS_MISC;
                        break;
***************
*** 4822,4831 **** ATPrepSetStatistics(Relation rel, const char *colName, Node 
*newValue, LOCKMODE
         * allowSystemTableMods to be turned on.
         */
        if (rel->rd_rel->relkind != RELKIND_RELATION &&
!               rel->rd_rel->relkind != RELKIND_INDEX)
                ereport(ERROR,
                                (errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                                errmsg("\"%s\" is not a table or index",
                                                RelationGetRelationName(rel))));
  
        /* Permissions checks */
--- 4825,4835 ----
         * allowSystemTableMods to be turned on.
         */
        if (rel->rd_rel->relkind != RELKIND_RELATION &&
!               rel->rd_rel->relkind != RELKIND_INDEX &&
!               rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
                ereport(ERROR,
                                (errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                                errmsg("\"%s\" is not a table, index or 
foreign table",
                                                RelationGetRelationName(rel))));
  
        /* Permissions checks */
***************
*** 4894,4899 **** ATExecSetStatistics(Relation rel, const char *colName, Node 
*newValue, LOCKMODE
--- 4898,4923 ----
  }
  
  static void
+ ATPrepSetOptions(Relation rel, const char *colName, Node *options,
+                                LOCKMODE lockmode)
+ {
+       if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+       {
+               ListCell   *cell;
+ 
+               foreach(cell, (List *) options)
+               {
+                       DefElem    *def = (DefElem *) lfirst(cell);
+ 
+                       if (pg_strncasecmp(def->defname, 
"n_distinct_inherited", strlen("n_distinct_inherited")) == 0)
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                                                errmsg("cannot support option 
\"n_distinct_inherited\" for foreign tables")));
+               }
+       }
+ }
+ 
+ static void
  ATExecSetOptions(Relation rel, const char *colName, Node *options,
                                 bool isReset, LOCKMODE lockmode)
  {
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 399,404 **** static const SchemaQuery Query_for_list_of_tsvf = {
--- 399,419 ----
        NULL
  };
  
+ static const SchemaQuery Query_for_list_of_tf = {
+       /* catname */
+       "pg_catalog.pg_class c",
+       /* selcondition */
+       "c.relkind IN ('r', 'f')",
+       /* viscondition */
+       "pg_catalog.pg_table_is_visible(c.oid)",
+       /* namespace */
+       "c.relnamespace",
+       /* result */
+       "pg_catalog.quote_ident(c.relname)",
+       /* qualresult */
+       NULL
+ };
+ 
  static const SchemaQuery Query_for_list_of_views = {
        /* catname */
        "pg_catalog.pg_class c",
***************
*** 2769,2775 **** psql_completion(char *text, int start, int end)
  /* ANALYZE */
        /* If the previous word is ANALYZE, produce list of tables */
        else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0)
!               COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
  
  /* WHERE */
        /* Simple case of the word before the where being the table name */
--- 2784,2790 ----
  /* ANALYZE */
        /* If the previous word is ANALYZE, produce list of tables */
        else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0)
!               COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tf, NULL);
  
  /* WHERE */
        /* Simple case of the word before the where being the table name */
*** a/src/include/commands/vacuum.h
--- b/src/include/commands/vacuum.h
***************
*** 167,171 **** extern void lazy_vacuum_rel(Relation onerel, VacuumStmt 
*vacstmt,
--- 167,178 ----
  /* in commands/analyze.c */
  extern void analyze_rel(Oid relid, VacuumStmt *vacstmt,
                        BufferAccessStrategy bstrategy);
+ extern void do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, int elevel, 
+                                                  bool inh, int 
(*sample_row_acquirer) ());
+ extern double random_fract(void);
+ extern double init_selection_state(int n);
+ extern double get_next_S(double t, int n, double *stateptr);
+ extern int    compare_rows(const void *a, const void *b);
+ 
  
  #endif   /* VACUUM_H */
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
***************
*** 12,19 ****
--- 12,21 ----
  #ifndef FDWAPI_H
  #define FDWAPI_H
  
+ #include "foreign/foreign.h"
  #include "nodes/execnodes.h"
  #include "nodes/relation.h"
+ #include "utils/rel.h"
  
  /* To avoid including explain.h here, reference ExplainState thus: */
  struct ExplainState;
***************
*** 68,73 **** typedef void (*ReScanForeignScan_function) (ForeignScanState 
*node);
--- 70,78 ----
  
  typedef void (*EndForeignScan_function) (ForeignScanState *node);
  
+ typedef void (*AnalyzeForeignTable_function) (Relation relation,
+                                                                               
          VacuumStmt *vacstmt,
+                                                                               
          int elevel);
  
  /*
   * FdwRoutine is the struct returned by a foreign-data wrapper's handler
***************
*** 88,93 **** typedef struct FdwRoutine
--- 93,99 ----
        IterateForeignScan_function IterateForeignScan;
        ReScanForeignScan_function ReScanForeignScan;
        EndForeignScan_function EndForeignScan;
+       AnalyzeForeignTable_function AnalyzeForeignTable;
  } FdwRoutine;
  
  
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to