Postgresql 42883 error - Исправление ошибок и поиск оптимальных решений проблем

Sure, I’m using Entity Framework v6.1.3. You are right, I missed that and updated Npgsql to v3.2.3 with changing Npgsql.EntityFramework with EntityFramework6.Npgsql (v3.1.1).

Unfortunately this doesn’t fix this error.

This is what I have in log file:

2017-06-06 12:23:46 GMT [9428]: [7-1] user=postgres,db=snopo,app=[unknown],client=::1 ERROR: operator does not exist: bit <> boolean at character 580 
2017-06-06 12:23:46 GMT [9428]: [8-1] user=postgres,db=snopo,app=[unknown],client=::1 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 
2017-06-06 12:23:46 GMT [9428]: [9-1] user=postgres,db=snopo,app=[unknown],client=::1 STATEMENT: SELECT "Project1"."Id", "Project1"."JobType", "Project1"."JobArgs", "Project1"."TryCount", "Project1"."NextTryTime", "Project1"."LastTryTime", "Project1"."IsAbandoned", "Project1"."C1", "Project1"."CreationTime", "Project1"."CreatorUserId" FROM (SELECT "Extent1"."Id", "Extent1"."JobType", "Extent1"."JobArgs", "Extent1"."TryCount", "Extent1"."NextTryTime", "Extent1"."LastTryTime", "Extent1"."IsAbandoned", "Extent1"."CreationTime", "Extent1"."CreatorUserId", CAST ("Extent1"."Priority" AS int2) AS "C1" FROM "dbo"."AbpBackgroundJobs" AS "Extent1" WHERE "Extent1"."IsAbandoned" != TRUE AND "Extent1"."NextTryTime" <= $1) AS "Project1" ORDER BY "Project1"."C1" DESC ,"Project1"."TryCount" ASC ,"Project1"."NextTryTime" ASC LIMIT 1000

I’m not sure if this sql statement is generated by Entity Framework.

And here new stacktrace:

[PostgresException (0x80004005): 42883: operator does not exist: bit = boolean]
   Npgsql.<DoReadMessage>d__147.MoveNext() +188
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +65
   Npgsql.<ReadMessage>d__146.MoveNext() +468
   Npgsql.<ReadMessage>d__146.MoveNext() +774
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +65
   Npgsql.<ReadExpecting>d__153`1.MoveNext() +404
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +65
   Npgsql.<NextResult>d__32.MoveNext() +1822
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Npgsql.<NextResultAsync>d__31.MoveNext() +653
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Npgsql.<Execute>d__71.MoveNext() +1190
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +65
   Npgsql.<ExecuteDbDataReader>d__92.MoveNext() +361
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +65
   Npgsql.<ExecuteDbDataReaderAsync>d__90.MoveNext() +426
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Core.EntityClient.Internal.<ExecuteStoreCommandsAsync>d__c.MoveNext() +246

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
   System.Data.Entity.Core.EntityClient.Internal.<ExecuteStoreCommandsAsync>d__c.MoveNext() +331
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Core.Objects.Internal.<ExecuteAsync>d__0`1.MoveNext() +1449
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Utilities.CultureAwaiter`1.GetResult() +59
   System.Data.Entity.Core.Objects.<ExecuteInTransactionAsync>d__3d`1.MoveNext() +852
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Utilities.CultureAwaiter`1.GetResult() +59
   System.Data.Entity.Core.Objects.<GetResultsAsync>d__e.MoveNext() +627
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Utilities.CultureAwaiter`1.GetResult() +59
   System.Data.Entity.Internal.<FirstMoveNextAsync>d__0.MoveNext() +256
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   System.Data.Entity.Infrastructure.<ForEachAsync>d__5`1.MoveNext() +356
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.EntityFramework.Repositories.<GetAllListAsync>d__8.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbp.EntityFrameworkEntityFrameworkRepositoriesEfRepositoryBaseOfTEntityAndTPrimaryKey.cs:0
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Threading.<AwaitTaskWithPostActionAndFinallyAndGetResult>d__5`1.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpThreadingInternalAsyncHelper.cs:120
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Localization.<GetLanguagesFromDatabaseAsync>d__17.MoveNext() in D:HalilGitHubmodule-zerosrcAbp.ZeroLocalizationApplicationLanguageManager.cs:203
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Threading.<AwaitTaskWithPostActionAndFinallyAndGetResult>d__5`1.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpThreadingInternalAsyncHelper.cs:120
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Runtime.Caching.<<GetAsync>b__0>d.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpRuntimeCachingCacheExtensions.cs:0
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Runtime.Caching.<GetAsync>d__15.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpRuntimeCachingCacheBase.cs:69
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Runtime.Caching.<GetAsync>d__5`2.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpRuntimeCachingCacheExtensions.cs:38
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Localization.<GetLanguageDictionary>d__15.MoveNext() in D:HalilGitHubmodule-zerosrcAbp.ZeroLocalizationApplicationLanguageManager.cs:177
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Localization.<GetLanguagesAsync>d__8.MoveNext() in D:HalilGitHubmodule-zerosrcAbp.ZeroLocalizationApplicationLanguageManager.cs:60
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Nito.AsyncEx.Synchronous.TaskExtensions.WaitAndUnwrapException(Task`1 task) +55
   Nito.AsyncEx.<>c__DisplayClass7`1.<Run>b__5(Task`1 t) +52
   System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() +77
   System.Threading.Tasks.Task.Execute() +51
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Nito.AsyncEx.Synchronous.TaskExtensions.WaitAndUnwrapException(Task`1 task) +55
   Nito.AsyncEx.AsyncContext.Run(Func`1 action) +301
   Abp.Localization.ApplicationLanguageProvider.GetLanguages() in D:HalilGitHubmodule-zerosrcAbp.ZeroLocalizationApplicationLanguageProvider.cs:35
   Abp.Localization.LanguageManager.GetLanguages() in D:HalilGitHubaspnetboilerplatesrcAbpLocalizationLanguageManager.cs:21
   Abp.Localization.MultiTenantLocalizationDictionaryProvider.GetDictionaries() in D:HalilGitHubmodule-zerosrcAbp.ZeroLocalizationMultiTenantLocalizationDictionaryProvider.cs:56
   Abp.Localization.MultiTenantLocalizationDictionaryProvider.get_Dictionaries() in D:HalilGitHubmodule-zerosrcAbp.ZeroLocalizationMultiTenantLocalizationDictionaryProvider.cs:25
   Abp.Localization.Dictionaries.DictionaryBasedLocalizationSource.GetStringOrNull(String name, CultureInfo culture, Boolean tryDefaults) in D:HalilGitHubaspnetboilerplatesrcAbpLocalizationDictionariesDictionaryBasedLocalizationSource.cs:84
   Abp.Localization.Dictionaries.DictionaryBasedLocalizationSource.GetString(String name) in D:HalilGitHubaspnetboilerplatesrcAbpLocalizationDictionariesDictionaryBasedLocalizationSource.cs:61
   Abp.Authorization.<AuthorizeAsync>d__19.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpAuthorizationAuthorizationHelper.cs:42
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Authorization.<CheckPermissions>d__22.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpAuthorizationAuthorizationHelper.cs:100
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Abp.Authorization.<AuthorizeAsync>d__20.MoveNext() in D:HalilGitHubaspnetboilerplatesrcAbpAuthorizationAuthorizationHelper.cs:67
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Nito.AsyncEx.<>c__DisplayClass3.<Run>b__1(Task t) +56
   System.Threading.Tasks.ContinuationTaskFromTask.InnerInvoke() +88
   System.Threading.Tasks.Task.Execute() +51
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Nito.AsyncEx.AsyncContext.Run(Func`1 action) +199
   Abp.Authorization.AuthorizationHelperExtensions.Authorize(IAuthorizationHelper authorizationHelper, MethodInfo methodInfo) in D:HalilGitHubaspnetboilerplatesrcAbpAuthorizationAuthorizationHelperExtensions.cs:27
   Abp.Web.Mvc.Authorization.AbpMvcAuthorizeFilter.OnAuthorization(AuthorizationContext filterContext) in D:HalilGitHubaspnetboilerplatesrcAbp.Web.MvcWebMvcAuthorizationAbpMvcAuthorizeFilter.cs:42
   System.Web.Mvc.ControllerActionInvoker.InvokeAuthorizationFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor) +97
   System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__19(AsyncCallback asyncCallback, Object asyncState) +743
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallBeginDelegate(AsyncCallback callback, Object callbackState) +14
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +128
   System.Web.Mvc.Async.AsyncControllerActionInvoker.BeginInvokeAction(ControllerContext controllerContext, String actionName, AsyncCallback callback, Object state) +343
   System.Web.Mvc.Controller.<BeginExecuteCore>b__1c(AsyncCallback asyncCallback, Object asyncState, ExecuteCoreState innerState) +25
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallBeginDelegate(AsyncCallback callback, Object callbackState) +30
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +128
   System.Web.Mvc.Controller.BeginExecuteCore(AsyncCallback callback, Object state) +465
   System.Web.Mvc.Controller.<BeginExecute>b__14(AsyncCallback asyncCallback, Object callbackState, Controller controller) +18
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallBeginDelegate(AsyncCallback callback, Object callbackState) +20
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +128
   System.Web.Mvc.Controller.BeginExecute(RequestContext requestContext, AsyncCallback callback, Object state) +374
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.BeginExecute(RequestContext requestContext, AsyncCallback callback, Object state) +16
   System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__4(AsyncCallback asyncCallback, Object asyncState, ProcessRequestState innerState) +52
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallBeginDelegate(AsyncCallback callback, Object callbackState) +30
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +128
   System.Web.Mvc.MvcHandler.BeginProcessRequest(HttpContextBase httpContext, AsyncCallback callback, Object state) +384
   System.Web.Mvc.MvcHandler.BeginProcessRequest(HttpContext httpContext, AsyncCallback callback, Object state) +48
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData) +16
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +103
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Источник

Error Code	Condition Name
Class 00 — Successful Completion
`00000`	`successful_completion`
Class 01 — Warning
`01000`	`warning`
`0100C`	`dynamic_result_sets_returned`
`01008`	`implicit_zero_bit_padding`
`01003`	`null_value_eliminated_in_set_function`
`01007`	`privilege_not_granted`
`01006`	`privilege_not_revoked`
`01004`	`string_data_right_truncation`
`01P01`	`deprecated_feature`
Class 02 — No Data (this is also a warning class per the SQL standard)
`02000`	`no_data`
`02001`	`no_additional_dynamic_result_sets_returned`
Class 03 — SQL Statement Not Yet Complete
`03000`	`sql_statement_not_yet_complete`
Class 08 — Connection Exception
`08000`	`connection_exception`
`08003`	`connection_does_not_exist`
`08006`	`connection_failure`
`08001`	`sqlclient_unable_to_establish_sqlconnection`
`08004`	`sqlserver_rejected_establishment_of_sqlconnection`
`08007`	`transaction_resolution_unknown`
`08P01`	`protocol_violation`
Class 09 — Triggered Action Exception
`09000`	`triggered_action_exception`
Class 0A — Feature Not Supported
`0A000`	`feature_not_supported`
Class 0B — Invalid Transaction Initiation
`0B000`	`invalid_transaction_initiation`
Class 0F — Locator Exception
`0F000`	`locator_exception`
`0F001`	`invalid_locator_specification`
Class 0L — Invalid Grantor
`0L000`	`invalid_grantor`
`0LP01`	`invalid_grant_operation`
Class 0P — Invalid Role Specification
`0P000`	`invalid_role_specification`
Class 0Z — Diagnostics Exception
`0Z000`	`diagnostics_exception`
`0Z002`	`stacked_diagnostics_accessed_without_active_handler`
Class 20 — Case Not Found
`20000`	`case_not_found`
Class 21 — Cardinality Violation
`21000`	`cardinality_violation`
Class 22 — Data Exception
`22000`	`data_exception`
`2202E`	`array_subscript_error`
`22021`	`character_not_in_repertoire`
`22008`	`datetime_field_overflow`
`22012`	`division_by_zero`
`22005`	`error_in_assignment`
`2200B`	`escape_character_conflict`
`22022`	`indicator_overflow`
`22015`	`interval_field_overflow`
`2201E`	`invalid_argument_for_logarithm`
`22014`	`invalid_argument_for_ntile_function`
`22016`	`invalid_argument_for_nth_value_function`
`2201F`	`invalid_argument_for_power_function`
`2201G`	`invalid_argument_for_width_bucket_function`
`22018`	`invalid_character_value_for_cast`
`22007`	`invalid_datetime_format`
`22019`	`invalid_escape_character`
`2200D`	`invalid_escape_octet`
`22025`	`invalid_escape_sequence`
`22P06`	`nonstandard_use_of_escape_character`
`22010`	`invalid_indicator_parameter_value`
`22023`	`invalid_parameter_value`
`22013`	`invalid_preceding_or_following_size`
`2201B`	`invalid_regular_expression`
`2201W`	`invalid_row_count_in_limit_clause`
`2201X`	`invalid_row_count_in_result_offset_clause`
`2202H`	`invalid_tablesample_argument`
`2202G`	`invalid_tablesample_repeat`
`22009`	`invalid_time_zone_displacement_value`
`2200C`	`invalid_use_of_escape_character`
`2200G`	`most_specific_type_mismatch`
`22004`	`null_value_not_allowed`
`22002`	`null_value_no_indicator_parameter`
`22003`	`numeric_value_out_of_range`
`2200H`	`sequence_generator_limit_exceeded`
`22026`	`string_data_length_mismatch`
`22001`	`string_data_right_truncation`
`22011`	`substring_error`
`22027`	`trim_error`
`22024`	`unterminated_c_string`
`2200F`	`zero_length_character_string`
`22P01`	`floating_point_exception`
`22P02`	`invalid_text_representation`
`22P03`	`invalid_binary_representation`
`22P04`	`bad_copy_file_format`
`22P05`	`untranslatable_character`
`2200L`	`not_an_xml_document`
`2200M`	`invalid_xml_document`
`2200N`	`invalid_xml_content`
`2200S`	`invalid_xml_comment`
`2200T`	`invalid_xml_processing_instruction`
`22030`	`duplicate_json_object_key_value`
`22031`	`invalid_argument_for_sql_json_datetime_function`
`22032`	`invalid_json_text`
`22033`	`invalid_sql_json_subscript`
`22034`	`more_than_one_sql_json_item`
`22035`	`no_sql_json_item`
`22036`	`non_numeric_sql_json_item`
`22037`	`non_unique_keys_in_a_json_object`
`22038`	`singleton_sql_json_item_required`
`22039`	`sql_json_array_not_found`
`2203A`	`sql_json_member_not_found`
`2203B`	`sql_json_number_not_found`
`2203C`	`sql_json_object_not_found`
`2203D`	`too_many_json_array_elements`
`2203E`	`too_many_json_object_members`
`2203F`	`sql_json_scalar_required`
Class 23 — Integrity Constraint Violation
`23000`	`integrity_constraint_violation`
`23001`	`restrict_violation`
`23502`	`not_null_violation`
`23503`	`foreign_key_violation`
`23505`	`unique_violation`
`23514`	`check_violation`
`23P01`	`exclusion_violation`
Class 24 — Invalid Cursor State
`24000`	`invalid_cursor_state`
Class 25 — Invalid Transaction State
`25000`	`invalid_transaction_state`
`25001`	`active_sql_transaction`
`25002`	`branch_transaction_already_active`
`25008`	`held_cursor_requires_same_isolation_level`
`25003`	`inappropriate_access_mode_for_branch_transaction`
`25004`	`inappropriate_isolation_level_for_branch_transaction`
`25005`	`no_active_sql_transaction_for_branch_transaction`
`25006`	`read_only_sql_transaction`
`25007`	`schema_and_data_statement_mixing_not_supported`
`25P01`	`no_active_sql_transaction`
`25P02`	`in_failed_sql_transaction`
`25P03`	`idle_in_transaction_session_timeout`
Class 26 — Invalid SQL Statement Name
`26000`	`invalid_sql_statement_name`
Class 27 — Triggered Data Change Violation
`27000`	`triggered_data_change_violation`
Class 28 — Invalid Authorization Specification
`28000`	`invalid_authorization_specification`
`28P01`	`invalid_password`
Class 2B — Dependent Privilege Descriptors Still Exist
`2B000`	`dependent_privilege_descriptors_still_exist`
`2BP01`	`dependent_objects_still_exist`
Class 2D — Invalid Transaction Termination
`2D000`	`invalid_transaction_termination`
Class 2F — SQL Routine Exception
`2F000`	`sql_routine_exception`
`2F005`	`function_executed_no_return_statement`
`2F002`	`modifying_sql_data_not_permitted`
`2F003`	`prohibited_sql_statement_attempted`
`2F004`	`reading_sql_data_not_permitted`
Class 34 — Invalid Cursor Name
`34000`	`invalid_cursor_name`
Class 38 — External Routine Exception
`38000`	`external_routine_exception`
`38001`	`containing_sql_not_permitted`
`38002`	`modifying_sql_data_not_permitted`
`38003`	`prohibited_sql_statement_attempted`
`38004`	`reading_sql_data_not_permitted`
Class 39 — External Routine Invocation Exception
`39000`	`external_routine_invocation_exception`
`39001`	`invalid_sqlstate_returned`
`39004`	`null_value_not_allowed`
`39P01`	`trigger_protocol_violated`
`39P02`	`srf_protocol_violated`
`39P03`	`event_trigger_protocol_violated`
Class 3B — Savepoint Exception
`3B000`	`savepoint_exception`
`3B001`	`invalid_savepoint_specification`
Class 3D — Invalid Catalog Name
`3D000`	`invalid_catalog_name`
Class 3F — Invalid Schema Name
`3F000`	`invalid_schema_name`
Class 40 — Transaction Rollback
`40000`	`transaction_rollback`
`40002`	`transaction_integrity_constraint_violation`
`40001`	`serialization_failure`
`40003`	`statement_completion_unknown`
`40P01`	`deadlock_detected`
Class 42 — Syntax Error or Access Rule Violation
`42000`	`syntax_error_or_access_rule_violation`
`42601`	`syntax_error`
`42501`	`insufficient_privilege`
`42846`	`cannot_coerce`
`42803`	`grouping_error`
`42P20`	`windowing_error`
`42P19`	`invalid_recursion`
`42830`	`invalid_foreign_key`
`42602`	`invalid_name`
`42622`	`name_too_long`
`42939`	`reserved_name`
`42804`	`datatype_mismatch`
`42P18`	`indeterminate_datatype`
`42P21`	`collation_mismatch`
`42P22`	`indeterminate_collation`
`42809`	`wrong_object_type`
`428C9`	`generated_always`
`42703`	`undefined_column`
`42883`	`undefined_function`
`42P01`	`undefined_table`
`42P02`	`undefined_parameter`
`42704`	`undefined_object`
`42701`	`duplicate_column`
`42P03`	`duplicate_cursor`
`42P04`	`duplicate_database`
`42723`	`duplicate_function`
`42P05`	`duplicate_prepared_statement`
`42P06`	`duplicate_schema`
`42P07`	`duplicate_table`
`42712`	`duplicate_alias`
`42710`	`duplicate_object`
`42702`	`ambiguous_column`
`42725`	`ambiguous_function`
`42P08`	`ambiguous_parameter`
`42P09`	`ambiguous_alias`
`42P10`	`invalid_column_reference`
`42611`	`invalid_column_definition`
`42P11`	`invalid_cursor_definition`
`42P12`	`invalid_database_definition`
`42P13`	`invalid_function_definition`
`42P14`	`invalid_prepared_statement_definition`
`42P15`	`invalid_schema_definition`
`42P16`	`invalid_table_definition`
`42P17`	`invalid_object_definition`
Class 44 — WITH CHECK OPTION Violation
`44000`	`with_check_option_violation`
Class 53 — Insufficient Resources
`53000`	`insufficient_resources`
`53100`	`disk_full`
`53200`	`out_of_memory`
`53300`	`too_many_connections`
`53400`	`configuration_limit_exceeded`
Class 54 — Program Limit Exceeded
`54000`	`program_limit_exceeded`
`54001`	`statement_too_complex`
`54011`	`too_many_columns`
`54023`	`too_many_arguments`
Class 55 — Object Not In Prerequisite State
`55000`	`object_not_in_prerequisite_state`
`55006`	`object_in_use`
`55P02`	`cant_change_runtime_param`
`55P03`	`lock_not_available`
`55P04`	`unsafe_new_enum_value_usage`
Class 57 — Operator Intervention
`57000`	`operator_intervention`
`57014`	`query_canceled`
`57P01`	`admin_shutdown`
`57P02`	`crash_shutdown`
`57P03`	`cannot_connect_now`
`57P04`	`database_dropped`
`57P05`	`idle_session_timeout`
Class 58 — System Error (errors external to PostgreSQL itself)
`58000`	`system_error`
`58030`	`io_error`
`58P01`	`undefined_file`
`58P02`	`duplicate_file`
Class 72 — Snapshot Failure
`72000`	`snapshot_too_old`
Class F0 — Configuration File Error
`F0000`	`config_file_error`
`F0001`	`lock_file_exists`
Class HV — Foreign Data Wrapper Error (SQL/MED)
`HV000`	`fdw_error`
`HV005`	`fdw_column_name_not_found`
`HV002`	`fdw_dynamic_parameter_value_needed`
`HV010`	`fdw_function_sequence_error`
`HV021`	`fdw_inconsistent_descriptor_information`
`HV024`	`fdw_invalid_attribute_value`
`HV007`	`fdw_invalid_column_name`
`HV008`	`fdw_invalid_column_number`
`HV004`	`fdw_invalid_data_type`
`HV006`	`fdw_invalid_data_type_descriptors`
`HV091`	`fdw_invalid_descriptor_field_identifier`
`HV00B`	`fdw_invalid_handle`
`HV00C`	`fdw_invalid_option_index`
`HV00D`	`fdw_invalid_option_name`
`HV090`	`fdw_invalid_string_length_or_buffer_length`
`HV00A`	`fdw_invalid_string_format`
`HV009`	`fdw_invalid_use_of_null_pointer`
`HV014`	`fdw_too_many_handles`
`HV001`	`fdw_out_of_memory`
`HV00P`	`fdw_no_schemas`
`HV00J`	`fdw_option_name_not_found`
`HV00K`	`fdw_reply_handle`
`HV00Q`	`fdw_schema_not_found`
`HV00R`	`fdw_table_not_found`
`HV00L`	`fdw_unable_to_create_execution`
`HV00M`	`fdw_unable_to_create_reply`
`HV00N`	`fdw_unable_to_establish_connection`
Class P0 — PL/pgSQL Error
`P0000`	`plpgsql_error`
`P0001`	`raise_exception`
`P0002`	`no_data_found`
`P0003`	`too_many_rows`
`P0004`	`assert_failure`
Class XX — Internal Error
`XX000`	`internal_error`
`XX001`	`data_corrupted`
`XX002`	`index_corrupted`

Источник

3 min read.

Have you run into this puzzling error? It comes in a couple of flavors, but it boils down to you failing miserably when you run the create_hypertable() function on your PostgreSQL TimescaleDB database server! Annoyingly, it might mean that the whole function is missing (extension is not installed or loaded) or that your parameters are not properly parsed (an issue with your command).

I know, that it sucks. I have been there. That’s why I wrote this article!

Problem

You’re trying to transform your PostgreSQL database/schema, possibly already using TimescaleDB, into a Hypertable. However, that fails and throws an error like this:

Npgsql: 42883: function create_hypertable(…) does not exist.

You might get the parameters populated like this:

Npgsql: 42883: function create_hypertable('TableOne','created') does not exist.

Or they might be unknown like this:

Npgsql: 42883: function create_hypertable(unknown, unknown) does not exist.

Reason

You’ll either be missing the extension from the server, it might not be loaded properly, it might be missing from your database, or your command might be using bad or corrupted parameters.

Below, I’ll go through the solutions to these issues!

Solution

The simple tutorial below explains step-by-step what to do (or verify) to get this working!

Time needed: 20 minutes.

How to configure your PostgreSQL/TimescaleDB to support Hypertables?

Add Hypertables extension to your database server

Navigate to your PostgreSQL server, select “Server parameters” and then find “shared_preload_libraries” – it should be a dropdown, from which you can enable TIMESCALEDB.

Then navigate back to Overview of your PostgreSQL server and select Restart.
Add Hypertables extension to your database (schema)

Connect to your PostgreSQL database with psql (or any other such tool), and run the following SQL:
c "YourSchema" CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

I guess technically speaking you could also insert this into an EF Core Migration!

In case you’re wondering how to connect to your Azure PostgreSQL Server with psql, you can do this with Cloud Shell – and it’s really easy! See here:
How to configure Azure PostgreSQL to allow Cloud Shell connections?
(OPTIONAL) Add an empty EF Core Migration
Do this only if you’re using Entity Framework in the first place. 😅 Just run Add-Migration
Change a table into a hypertable with a function

create_hypertable() is a function that has a couple of alternative signatures – the one I’ll be using takes the (1) table name first and (2) the column name of the column containing time values (as well as the primary column to partition by).

You will need to call this function, and instead of actually transforming or changing a table into a hypertable, it creates a new hypertable based on your old table – and deletes the old one. This is irreversible!

Additionally, it is a SELECT <function_name> -kinda command. We can live with that – it’ll look somewhat like the below:
SELECT create_hypertable(‘”TableOne”‘, ‘Created’)

Wait, WAIT – what’s with the weird quotes?

Yep, that’s what it takes – the column name needs to be in single quotes, but the table name needs to be first in double quotes (if not in lowercase), then wrapped in single quotes.
(OPTIONAL) Run create_hypertable in your EF Core Migration

There are a couple of exact ways that you can write this command, but at the end of the day, you’ll probably be using migrationBuilder.Sql to run your SQL commands (in case you’re using EF, that is).

At the end of the day, your migration and command(s) might look somewhat like this:
protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.Sql(@"SELECT create_hypertable('""TableOne""', 'Created')"); migrationBuilder.Sql(@"SELECT create_hypertable('""TableTwo""', 'Created')"); }

Wait, wait, WAIT! This is even weirder now, with different quotes and literal string??

Yeah, it is pretty weird, sure.

The column name needs to be in single quotes, but the table name needs to be first in double-quotes (if not in lowercase), then in single quotes, and finally the double-quotes need to be escaped (for string literal).

I couldn’t get the normal escape (non-literal string with ”) to work, so this was my workaround (using @ to make the string literal)… 😉

And that’s it! Let me know if it works for you as well in the comments -section below! ☺

References

https://docs.timescale.com/timescaledb/latest/how-to-guides/hypertables/
Power IoT and time-series workloads with TimescaleDB for Azure Database for PostgreSQL
https://techinplanet.com/how-do-i-create-a-hypertable-with-timescaledb-from-a-table-with-joint-primary-key/
https://github.com/timescale/timescaledb/issues/747

Author
Recent Posts

Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That’s definitely enough funny buzzwords!), who works as Solutions Architect for Precio Fishbone, building delightful Digital Workplaces.

He’s been a developer from 2004 (starting with PHP and Java), and he’s been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He’s also Microsoft MVP for Office Development.

This is his personal professional (e.g. professional, but definitely personal) blog.

Источник

Epoch time stored with bigint format in PostgreSQL. Most of problem when converting this epoch format to date or timestamp is because we deal with time being stored in number format and integer column type.

When I’m trying to convert epoch time that stored in bigint format, I found several way ready on Stackoverflow but didn’t works. Several script like below is not working for the latest PostgreSQL 13 version.

select *, to_timestamp(time in milli sec / 1000) from mytable

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

SELECT DATE(builds.build_created/ 1000) FROM builds;

You may trying with several approach like to_timestamp, to_date and receive several error results like :

1. Timezone not found
2. Need to cast
3. Or Operator does not exists

Some errors details eg:

SQL Error [42883]: ERROR: operator does not exist: character varying * interval
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 64

SQL Error [42883]: ERROR: operator does not exist: character varying / integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 33

How to find the solution? What we need apparently just combining Timestamp with epoch, cast field to int to be save and multiple it with INTERVAL.

Here is the query:

SELECT TIMESTAMP 'epoch' + (<table>.field::int) * INTERVAL '1 second' as started_on from <table>;

To make group by by weekly from epoch time

SELECT COUNT(*), another_column, date_trunc('week', TIMESTAMP 'epoch' + (epoch_column::int) * INTERVAL '1 second') AS weekly from builds group by weekly, another_column order by weekly desc;

Hope this can help you.

Tags

Convert epoch time to timestamp in Postgresql, Convert unix epoch time to date postgresql, Convert Unix timestamp to timestamp without time zone

Источник