Oops... I forgot to attach the patch. Thanks to Amul Sul for pointing that out. :)
On Fri, Aug 16, 2024 at 2:37 PM Nishant Sharma < nishant.sha...@enterprisedb.com> wrote: > Hi, > > > > -------------------------------------------------------------------------------------------------------------- > Actual column names used while creation of foreign table are not allowed > to be an > empty string, but when we use column_name as an empty string in OPTIONS > during > CREATE or ALTER of foreign tables, it is allowed. > > *EXAMPLES:-* > 1) CREATE FOREIGN TABLE test_fdw(*"" *VARCHAR(15), id VARCHAR(5)) SERVER > localhost_fdw OPTIONS (schema_name 'public', table_name 'test'); > ERROR: zero-length delimited identifier at or near """" > LINE 1: CREATE FOREIGN TABLE test_fdw("" VARCHAR(15), id VARCHAR(5))... > > 2) CREATE FOREIGN TABLE test_fdw(name VARCHAR(15) *OPTIONS* *(column_name > '')*, id VARCHAR(5)) SERVER localhost_fdw OPTIONS (schema_name 'public', > table_name 'test'); > CREATE FOREIGN TABLE > > postgres@43832=#\d test_fdw > Foreign table "public.test_fdw" > Column | Type | Collation | Nullable | Default | FDW > options > > --------+-----------------------+-----------+----------+---------+------------------ > name | character varying(15) | | | | > *(column_name > '')* > id | character varying(5) | | | | > Server: localhost_fdw > FDW options: (schema_name 'public', table_name 'test') > > > -------------------------------------------------------------------------------------------------------------- > > Due to the above, when we try to simply select a remote table, the remote > query uses > the empty column name from the FDW column option and the select fails. > > *EXAMPLES:-* > 1) select * from test_fdw; > ERROR: zero-length delimited identifier at or near """" > CONTEXT: remote SQL command: SELECT "", id FROM public.test > > 2) explain verbose select * from test_fdw; > QUERY PLAN > -------------------------------------------------------------------------- > Foreign Scan on public.test_fdw (cost=100.00..297.66 rows=853 width=72) > Output: name, id > Remote SQL: SELECT "", id FROM public.test > (3 rows) > > > -------------------------------------------------------------------------------------------------------------- > > We can fix this issue either during fetching of FDW column option names > while > building remote query or we do not allow at CREATE or ALTER of foreign > tables itself. > We think it would be better to disallow adding the column_name option as > empty in > CREATE or ALTER itself as we do not allow empty actual column names for a > foreign > table. Unless I missed to understand the purpose of allowing column_name > as empty. > > *THE PROPOSED SOLUTION OUTPUT:-* > 1) CREATE FOREIGN TABLE test_fdw(name VARCHAR(15) OPTIONS *(column_name > '')*, id VARCHAR(5)) SERVER localhost_fdw OPTIONS (schema_name 'public', > table_name 'test'); > ERROR: column generic option name cannot be empty > > 2) CREATE FOREIGN TABLE test_fdw(name VARCHAR(15), id VARCHAR(5)) SERVER > localhost_fdw OPTIONS (schema_name 'public', table_name 'test'); > CREATE FOREIGN TABLE > > ALTER FOREIGN TABLE test_fdw ALTER COLUMN id OPTIONS *(column_name '')*; > ERROR: column generic option name cannot be empty > > > -------------------------------------------------------------------------------------------------------------- > > PFA, the fix and test cases patches attached. I ran "make check world" and > do > not see any failure related to patches. But, I do see an existing failure > t/001_pgbench_with_server.pl > > > Regards, > Nishant. > > P.S > Thanks to Jeevan Chalke and Suraj Kharage for their inputs for the > proposal. >
v1-0001-Disallow-empty-Foreign-Table-column-option-name-i.patch
Description: Binary data
v1-0002-Test-Cases-Changes.patch
Description: Binary data