Hi hackers,
The new statistics import feature in postgres_fdw (commit 28972b6fc3d)
builds a remote query to fetch pg_stats rows, filtering by column name
with:
AND attname = ANY('{col1, col2}'::text[])
The column names are formatted with quote_identifier(), which only
escapes double quotes. But since the list is embedded inside a
single-quoted string literal, any single quote in a column name
breaks the literal and produces a syntax error on the remote server.
Reproduction:
CREATE TABLE t ("col'quote" int, c2 int);
INSERT INTO t SELECT g, g FROM generate_series(1,100) g;
ANALYZE t;
CREATE FOREIGN TABLE ft ("col'quote" int, c2 int)
SERVER loopback OPTIONS (table_name 't', restore_stats 'true');
ANALYZE ft;
-- ERROR: syntax error at or near "quote"
-- CONTEXT: remote SQL command: ... attname = ANY('{... "col'quote"}'::text[])
The attached patch switches to an ARRAY[] constructor with each
element escaped by deparseStringLiteral(), matching how schemaname
and tablename are already handled in the same function.
Thoughts?
It should also address the issue that was raised in [1].
[1]
PostgreSQL: Fix array-element quoting in postgres_fdw import statisticsRegards,
Ayush
