create table (if not exist) and insert data without exception handling way

Question

I am using Npgsql version 4.1.3.1 as a .NET data provider for PostgreSQL. Here is my use case

• For a database I have to create table's dynamically and table name is Guid which I passing to one method.
• If particular table is exist then using table specific connection string (each table has own credential), I need to insert data into table.
• If table not exist then using admin connection string (has ONLY permission to create a table), I need to create the table and then insert the record in respective table.

Currently I am using C# try/catch, within try I am trying to insert the record in table and if table is NOT exist, then in catch I am trying to create the table and try re-insert again.

private async Task<bool> Insert(Guid tableId, string name)
    {
        //here connection string is specific to table, which
        //don't have permission to create any table
        using (var conn = await GetTableConnectionString(tableId))
        {
            //open site specific connection
            await conn.OpenAsync();

            try
            {
                //insert data in table
                await using (var cmd = new NpgsqlCommand($@"Insert into Tbl-{tableId:N} (
                                                                id,
                                                                name) Values (@id, @name)", conn))
                {

                    cmd.Parameters.AddWithValue("id", Guid.NewGuid());
                    cmd.Parameters.AddWithValue("name", name);
                    await cmd.ExecuteNonQueryAsync();
                }

                //close site specific connection
                await conn.CloseAsync();
            }
            catch (Npgsql.PostgresException ex)
            {
                //close site specific connection
                await conn.CloseAsync();

                if (ex.SqlState == "42P01") // table does not exist
                {
                    //here admin connection string have permission to create a table
                    using (var adminConn = await GetAdminConnectionString())
                    {
                        try
                        {
                            //open admin specific connection
                            await adminConn.OpenAsync();

                            //create a table
                            using (var cmd = new NpgsqlCommand($@"CREATE Table Site_{tableId:N} (
                                                            id UUID PRIMARY KEY,
                                                            name VARCHAR(128);", adminConn))
                            {
                                cmd.ExecuteNonQuery();

                            }

                            //close admin specific connection
                            await adminConn.CloseAsync();

                            //insert data to table again
                            return await Insert(tableId, name);
                        }
                        catch (Exception exception)
                        {
                            //close admin specific connection
                            await adminConn.CloseAsync();
                            throw;
                        }
                    }
                }
            }
        }

        return true;
    }

Question is,

Is this approach correct? without exception handing (if (ex.SqlState == "42P01") // table does not exist) do we have any better way or ant library out of the box helps here? My connection string is also different for table and admin.

Answer 1

Given your specific design, trying and catching 42P01 seems reasonable. An alternate approach would be to first check if the table exists (by querying information_schema.tables or pg_class), but that adds an additional roundtrip and is potentially vulnerable to race conditions.

However, consider reviewing your design of dynamically created tables. If the tables have identical schema and the goal here is to have authorization per-table, then PostgreSQL has row-level security which may allow a much better design.