Re: [PROPOSAL] : Disallow use of empty column name in (column_name '') in ALTER or CREATE of foreign table.

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(dot)sharma(at)enterprisedb(dot)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(at)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.
>