Обсуждение: File_FDW with example

(apologies for prior incomplete post.  Webmail spazzed on me).

Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql csv
logs. I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

(2011/06/17 10:21), Joshua Berkus wrote:
> (apologies for prior incomplete post.  Webmail spazzed on me).
>
> Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql csv
logs. I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 

Such example would be useful for administrators who wants to manage
servers via SQL. :-)

I examined the example, and found some points which should be corrected:

- Some lines are over 80 columns.
- CREATE EXTENSION file_fdw also creates default FOREIGN DATA WRAPPER
file_fdw, so no need to create FOREIGN DATA WRAPPER explicitly.

BTW, filename option can be changed via SQL:

ALTER FOREIGN TABLE pglog OPTIONS ( SET filename '/path/to/new/file' );

I think it's worth to mention the way to switch to new log file after
log rotation.

Regards,
--
Shigeru Hanada

(2011/06/17 20:13), Shigeru Hanada wrote:
> I examined the example, and found some points which should be corrected:
>
> - Some lines are over 80 columns.
> - CREATE EXTENSION file_fdw also creates default FOREIGN DATA WRAPPER
> file_fdw, so no need to create FOREIGN DATA WRAPPER explicitly.

Oops, I forgot an important one.

- </para> needs to be added after </example>

Regards,
--
Shigeru Hanada

Excerpts from Joshua Berkus's message of jue jun 16 21:21:36 -0400 2011:
> (apologies for prior incomplete post.  Webmail spazzed on me).
>
> Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql csv
logs. I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 

Interesting ...

The final query is a bit unwieldy.  I wonder if this would work

CREATE TABLE generic_pglog (
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text
);

CREATE FOREIGN TABLE pglog (LIKE generic_pglog) SERVER pglog
OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );

Note that you have a "Not" instead of "now" in the last paragraph.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Jun 17, 2011 at 03:21, Joshua Berkus <josh@agliodbs.com> wrote:
> (apologies for prior incomplete post.  Webmail spazzed on me).
>
> Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql csv
logs. I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 

Hey, did you steal this straight from my blog? ;)

No, clearly not, because really.. Because the commands you've
suggested don't work, do they?
STATEMENT:  CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
ERROR:  foreign-data wrapper "file_fdw" already exists


because CREATE EXTENSION creates that one for you...


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

> No, clearly not, because really.. Because the commands you've
> suggested don't work, do they?
> STATEMENT:  CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
> ERROR:  foreign-data wrapper "file_fdw" already exists
>
>
> because CREATE EXTENSION creates that one for you...

Ah, I think I combined code from non-create extension version with the
create extension version.  Need to fix that, with both examples.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Joshua Berkus wrote:
> (apologies for prior incomplete post.  Webmail spazzed on me).
>
> Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql csv
logs. I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 

I have merged your file_fdw example into our docs --- patch attached.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
new file mode 100644
index dd712e9..900b055
*** a/doc/src/sgml/file-fdw.sgml
--- b/doc/src/sgml/file-fdw.sgml
***************
*** 158,161 ****
--- 158,233 ----
    specified, the file size (in bytes) is shown as well.
   </para>

+  <example>
+  <title id="csvlog-fdw">Create a Foreign Table for PostgreSQL CSV Logs</title>
+
+   <para>
+    One of the obvious uses for the <literal>file_fdw</> is to make
+    the PostgreSQL activity log available as a table for querying.  To
+    do this, first you must be logging to a CSV file, which here we
+    will call <literal>pglog.csv</>.  First, install <literal>file_fdw</>
+    as an extension:
+   </para>
+
+ <programlisting>
+ CREATE EXTENSION file_fdw;
+ </programlisting>
+
+   <para>
+    Next, create the foreign data wrapper:
+
+ <programlisting>
+ CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
+ </programlisting>
+   </para>
+
+   <para>
+    Then create a foreign data server:
+
+ <programlisting>
+ CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
+ </programlisting>
+   </para>
+
+   <para>
+    Now you are ready to create the foreign data table.  Using the
+    <command>CREATE FOREIGN TABLE</> command, you will need to define
+    the columns for the table, the CSV filename, and its format:
+
+ <programlisting>
+ CREATE FOREIGN TABLE pglog (
+   log_time timestamp(3) with time zone,
+   user_name text,
+   database_name text,
+   process_id integer,
+   connection_from text,
+   session_id text,
+   session_line_num bigint,
+   command_tag text,
+   session_start_time timestamp with time zone,
+   virtual_transaction_id text,
+   transaction_id bigint,
+   error_severity text,
+   sql_state_code text,
+   message text,
+   detail text,
+   hint text,
+   internal_query text,
+   internal_query_pos integer,
+   context text,
+   query text,
+   query_pos integer,
+   location text,
+   application_name text
+ ) SERVER pglog
+ OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );
+ </programlisting>
+   </para>
+
+   <para>
+    That's it — now you can query your log directly. In production, of course,
+    you would need to define some way to adjust to log rotation.
+   </para>
+  </example>
+
  </sect1>

bruce wrote:
> Joshua Berkus wrote:
> > (apologies for prior incomplete post.  Webmail spazzed on me).
> >
> > Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql
csvlogs.  I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 
>
> I have merged your file_fdw example into our docs --- patch attached.

Applied.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +