Postgresql – postgres npgsql DDL statement success or failure

npgsqlpostgresql

I am using npgsql with a postgres DB, writing a DB updater that will need to execute a series of (usually) DDL statements like "ALTER TABLE ADD COLUMN blah…" and the like. The simple way to do this is with Command.ExecuteNonQuery, however I need to know if each statement succeeded or not. ExecuteNonQuery returns "the number of rows effected" but it appears that this is always 0 for DDL regardless of success or failure, since the content of the table is not altered, only the structure.

What is the simplest way of determining the success or failure of arbitrary DDL (and update) commands read in from a text file? Can/Should/Must I wrap each statement in a procedure with an exception handler? Is there a simpler, more straightforward way?

One issue I see with wrapping in a procedure is that the procedure itself must then be part of the DB, and the update process is no longer as independent. I would like to avoid this. Of course, my updater could create the procedure if needed, and remove it after, but this seems cumbersome. Am I missing something obvious?

________Edit for clarification:

@Laurenz Albe is of course completely correct, if there is a syntax error, key duplication, missing column referenced, etc., an exception will be thrown. This probably covers nearly all cases. However, I have encountered issues where the SQL is valid but may or may not actually do anything useful – for example, if there is a WHERE clause, I may be expecting some (particular) number of 'rows effected'. The absence of an exception merely tells me that the statement was valid. When can or cannot I rely on the return value of ExecuteNonQuery, or is there a simple alternative?

Best Answer

If a statement fails, you'll get an exception.

There is no way to miss that unless you catch and ignore exceptions, which is never ok.

Related Solutions

Postgresql – Single data type for imprecise date values, as allowed by ISO 8601

No, the interval type supports reduced precision but none of the other date/time types do.

Postgres allows you to roll your own with create type but unfortunately wont allow contraints to be added to the type which limits it's usefulness in this scenario. The best I can come up with requires you to repeat check constraints on every field where the fuzzy type is used:

create type preciseness as enum('day', 'month', 'year');
create type fuzzytimestamptz as (ts timestamptz, p preciseness);
create table t( id serial primary key,
                fuzzy fuzzytimestamptz
                    check( (fuzzy).ts is not null 
                           or ((fuzzy).ts is null and (fuzzy).p is not null) ),
                    check((fuzzy).ts=date_trunc('year', (fuzzy).ts) or (fuzzy).p<'year'),
                    check((fuzzy).ts=date_trunc('month', (fuzzy).ts) or (fuzzy).p<'month'),
                    check((fuzzy).ts=date_trunc('day', (fuzzy).ts) or (fuzzy).p<'day') );

insert into t(fuzzy) values (row(date_trunc('year', current_timestamp), 'year'));
insert into t(fuzzy) values (row(date_trunc('month', current_timestamp), 'month'));
insert into t(fuzzy) values (row(date_trunc('day', current_timestamp), 'day'));

select * from t;

 id |              fuzzy
----+----------------------------------
  1 | ("2011-01-01 00:00:00+00",year)
  2 | ("2011-09-01 00:00:00+01",month)
  3 | ("2011-09-23 00:00:00+01",day)

--edit - an example equality operator:

create function fuzzytimestamptz_equality(fuzzytimestamptz, fuzzytimestamptz)
                returns boolean language plpgsql immutable as $$
begin
  return ($1.ts, $1.ts+coalesce('1 '||$1.p, '0')::interval)
         overlaps ($2.ts, $2.ts+coalesce('1 '||$2.p, '0')::interval);
end;$$;
--
create operator = ( procedure=fuzzytimestamptz_equality, 
                    leftarg=fuzzytimestamptz, 
                    rightarg=fuzzytimestamptz );

sample query:

select *, fuzzy=row(statement_timestamp(), null)::fuzzytimestamptz as equals_now,
          fuzzy=row(statement_timestamp()+'1 day'::interval, null)::fuzzytimestamptz as equals_tomorrow,
          fuzzy=row(date_trunc('month', statement_timestamp()), 'month')::fuzzytimestamptz as equals_fuzzymonth,
          fuzzy=row(date_trunc('month', statement_timestamp()+'1 month'::interval), 'month')::fuzzytimestamptz as equals_fuzzynextmonth
from t;
 id |               fuzzy                | equals_now | equals_tomorrow | equals_fuzzymonth | equals_fuzzynextmonth
----+------------------------------------+------------+-----------------+-------------------+-----------------------
  1 | ("2011-01-01 00:00:00+00",year)    | t          | t               | t                 | t
  2 | ("2011-09-01 00:00:00+01",month)   | t          | t               | t                 | f
  3 | ("2011-09-24 00:00:00+01",day)     | t          | f               | t                 | f
  4 | ("2011-09-24 11:45:23.810589+01",) | f          | f               | t                 | f

Postgresql – Trying to connect to PostgreSQL with Entity Framework via Npgsql and C#

I recommend not using Entity Framework with anything else other than SQLSERVER. And then LINQ to SQL may be better. lightspeed was recommended to me as a better product. I personally have not used it.

Related Question