File_FDW with example

(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. +