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 different files 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 example where 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 example as example, which everyone will can adapt for his own needs.