Subject: File_FDW with example

From: Joshua Berkus <[email-masked]>
Date: Thu, 16 Jun 2011 20:21:36 -0500 (CDT)

(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

---

From: Shigeru Hanada <[email-masked]>
Date: Fri, 17 Jun 2011 20:13:27 +0900

> 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 d…

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

---

From: Shigeru Hanada <[email-masked]>
Date: Fri, 17 Jun 2011 20:24:25 +0900

> 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

---

From: Alvaro Herrera <[email-masked]>
Date: Fri, 17 Jun 2011 10:00:14 -0400

> 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 d…

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 <[email-masked]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: Magnus Hagander <[email-masked]>
Date: Fri, 17 Jun 2011 16:51:51 +0200

> 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 d…

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/

---

From: Josh Berkus <[email-masked]>
Date: Mon, 27 Jun 2011 13:49:24 -0700

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 29 Nov 2011 19:28:51 -0500 (EST)

> 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 d…

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

-- 
  Bruce Momjian  <[email-masked]>        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 &mdash; 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>