Re: Create a Foreign Table for PostgreSQL CSV Logs

> 24 авг. 2020 г., в 18:07, Bruce Momjian <bruce@momjian.us> написал(а):
>
> On Mon, Aug 24, 2020 at 07:40:49AM -0700, David G. Johnston wrote:
>> On Mon, Aug 24, 2020 at 6:17 AM Олег Самойлов <splarv@ya.ru> wrote:
>>
>>    There must not be constraints at all. Constraints are needed to check
>>    incoming data to the table. But here table is read-only for database!
>>
>>
>> Please don't top-post.
>>
>>
>>
>>    So all your constraints is totally useless.
>>>> could become:
>>>>
>>>> connection_from text check(connection_from ~ '^[^:]+:[0-9]+$) -- the
>>    host and
>>>> port of the client, colon-separated
>>
>>
>> I'll agree that the benefit for adding the constraints to a foreign table are
>> less than for a normal table but it is still not zero.  Constraints are also a
>> form of documentation.  And also can be used (at least non-null ones) during
>> optimization.
>
> I feel constraints are going to lose focus of what we are trying to
> show.  Do the constraints actually do anything on a foreign table?

I'll add: "on foreign table based on read only text file without indexes, etc". :) But in this case I indeed added some
CHECK()constraints for old PostgreSQL and change them for the partition syntax sugar on new PostgreSQL to point on
differentfiles according to PostgreSQL default log config, where logs of each day of a week is kept in the different
files.For example: 

ALTER SYSTEM SET log_destination=csvlog;
SELECT pg_reload_conf();
CREATE EXTENSION file_fdw;
CREATE SERVER file_fdw FOREIGN DATA WRAPPER file_fdw;
BEGIN;
CREATE SCHEMA pglog;
CREATE TABLE pglog.pglog (
    log_time timestamp(3),
    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
) PARTITION BY LIST (date_part('isodow', log_time));
CREATE FOREIGN TABLE pglog.Mon
    PARTITION OF pglog.pglog FOR VALUES IN (1)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Mon.csv', format 'csv' );
CREATE FOREIGN TABLE pglog.Tue
    PARTITION OF pglog.pglog FOR VALUES IN (2)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Tue.csv', format 'csv' );
CREATE FOREIGN TABLE pglog.Wed
    PARTITION OF pglog.pglog FOR VALUES IN (3)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Wed.csv', format 'csv' );
CREATE FOREIGN TABLE pglog.Thu
    PARTITION OF pglog.pglog FOR VALUES IN (4)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Thu.csv', format 'csv' );
CREATE FOREIGN TABLE pglog.Fri
    PARTITION OF pglog.pglog FOR VALUES IN (5)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Fri.csv', format 'csv' );
CREATE FOREIGN TABLE pglog.Sat
    PARTITION OF pglog.pglog FOR VALUES IN (6)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Sat.csv', format 'csv' );
CREATE FOREIGN TABLE pglog.Sun
    PARTITION OF pglog.pglog FOR VALUES IN (7)
    SERVER file_fdw
    OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Sun.csv', format 'csv' );
COMMIT;

But there is another point of view. The same table schema used now not only for file_fdw access to the log, but also in
examplewhere this is an ordinary table. In this case may be some other optimisation: indexes, etc. 

But do we really need in the simple example such detailed and specific code? May be better give as simple as possible
exampleas example, which everyone will can adapt for his own needs.