Re: Create a Foreign Table for PostgreSQL CSV Logs

On Fri, Aug 21, 2020 at 08:41:54PM -0700, David G. Johnston wrote:
> On Fri, Aug 21, 2020 at 2:58 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Good idea.  People have been confused about this before.  Attached is a
> patch.
>
>
> + It is also possible to access the file as a foreign data wrapper
> +        using <xref linkend="file-fdw"/>.
>
> Seems more accurate to say "It is also possible to access the file as a foreign
> table, using the supplied <xref linkend="file-fdw"/> module."
>
> The file_fdw -> config change looks good.

OK, updated patch attached.

> A bit off-topic, but since this is being touched anyway - the listing of fields
> in the paragraph is not particularly readable (but maybe we want to keep it for
> accessibility reasons?) while the CREATE TABLE statement is very readable and
> more accurate, though it could be better.  Adding CHECK constraints and --
> comments to the CREATE TABLE command would be a welcome addition.  In
> particular I noticed:
>
> paragraph: client host:port number
> example: connection_from text,
>
> could become:
>
> connection_from text check(connection_from ~ '^[^:]+:[0-9]+$) -- the host and
> port of the client, colon-separated
>
> I've been mentally playing around with the idea of having the Config section
> with the CREATE TABLE somehow describe both the plain table and foreign table
> variants directly and removing the example from the file_fdw section and
> instead leaving the cross-references in place from file_fdw to config to see
> the example and from config to file_fdw to get clarity on the options and the
> SERVER syntax.  As they are being written for copy-and-paste though, and it's
> not like we are going to change the format, having the table definition
> duplicated isn't a terrible option.  But consolidation is something to
> consider.
>
> I may pick this up in the future unless someone thinks it wouldn't be a good
> idea.  I would be removing the paragraph of field names and make the table
> specification authoritative.

I am a little worried about adding this since the data is generated in
an automated way, and might change, or some config value might change
its format. I think the example is to show how to load, and adding extra
constraints would just detract from the illustration, I think.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee