SQL statement in an error report for deferred constraint violation.
Hi, While PQresultErrorField() from libpq allows to get context in which an error occurred for immediate constraints, and thus an SQL statement which caused the constraint violation, I cannot see any way to find out which SQL statement caused an error in case of deferred constraints, in particular deferred foreign key constraints. Is there any way to check which SQL statement or at least which row violated a constraint when it's deferred? If not does anyone know why there is such restriction? Konrad signature.asc Description: OpenPGP digital signature
Recreating functions after starting the database server.
Hi, I'm considering changing my database schema upgrade routines to recreate functions and all objects depending on them (e.g. triggers, views) just after the database server is started. It would make the routines much simpler since they wouldn't have to depend on the history of the schema. Does anyone has any experience with recreating all functions and triggers to upgrade a database schema assuming that during the upgrade there are no client connections to the database? Does anyone see any problems with such approach in terms of consistency and performance? I'm not familiar with PostgreSQL internals and I'm not sure how it would affect the planner when using various function volatile categories. Konrad signature.asc Description: OpenPGP digital signature
Re: Recreating functions after starting the database server.
On 01/31/2018 14:03, Achilleas Mantzios wrote: > On 31/01/2018 14:45, Konrad Witaszczyk wrote: >> Hi, >> >> I'm considering changing my database schema upgrade routines to recreate >> functions and all objects depending on them (e.g. triggers, views) just after >> the database server is started. It would make the routines much simpler since >> they wouldn't have to depend on the history of the schema. >> >> Does anyone has any experience with recreating all functions and triggers to >> upgrade a database schema assuming that during the upgrade there are no >> client >> connections to the database? >> >> Does anyone see any problems with such approach in terms of consistency and >> performance? I'm not familiar with PostgreSQL internals and I'm not sure how >> it >> would affect the planner when using various function volatile categories. > Do you have indexes that use those functions? There are no indexes which use functions. I can see it would be a problem in the other case since indexes would have to be rebuilt. Thanks for pointing it out. > It would help to just give an example of your situation and what you are > trying > to solve. In my case the upgrade routines run migration scripts which modify a database schema. Besides that I have a bunch of SQL files with the schema that can be used to initialize a new database. > Why recreate triggers, indexes and functions after server startup instead of > leaving them? > Why create needless traffic? I would like to eliminate the migration scripts to have all definitions in one file which would be easier to maintain. I'm mainly interested in functions and hence objects depending on them. However I wouldn't like to recreate indexes as it's not needed. >> >> >> Konrad signature.asc Description: OpenPGP digital signature
Re: Recreating functions after starting the database server.
On 01/31/2018 14:32, Achilleas Mantzios wrote: > On 31/01/2018 15:22, Konrad Witaszczyk wrote: >> On 01/31/2018 14:03, Achilleas Mantzios wrote: >>> On 31/01/2018 14:45, Konrad Witaszczyk wrote: >>>> Hi, >>>> >>>> I'm considering changing my database schema upgrade routines to recreate >>>> functions and all objects depending on them (e.g. triggers, views) just >>>> after >>>> the database server is started. It would make the routines much simpler >>>> since >>>> they wouldn't have to depend on the history of the schema. >>>> >>>> Does anyone has any experience with recreating all functions and triggers >>>> to >>>> upgrade a database schema assuming that during the upgrade there are no >>>> client >>>> connections to the database? >>>> >>>> Does anyone see any problems with such approach in terms of consistency and >>>> performance? I'm not familiar with PostgreSQL internals and I'm not sure >>>> how it >>>> would affect the planner when using various function volatile categories. >>> Do you have indexes that use those functions? >> There are no indexes which use functions. I can see it would be a problem in >> the >> other case since indexes would have to be rebuilt. Thanks for pointing it >> out. >> >>> It would help to just give an example of your situation and what you are >>> trying >>> to solve. >> In my case the upgrade routines run migration scripts which modify a database >> schema. Besides that I have a bunch of SQL files with the schema that can be >> used to initialize a new database. >> >>> Why recreate triggers, indexes and functions after server startup instead of >>> leaving them? >>> Why create needless traffic? >> I would like to eliminate the migration scripts to have all definitions in >> one >> file which would be easier to maintain. I'm mainly interested in functions >> and >> hence objects depending on them. However I wouldn't like to recreate indexes >> as >> it's not needed. > If it is only functions which retain their signature and only change the code, > there is no need to touch their related objects. You can CREATE OR REPLACE > those functions and change nothing else. I know that it works as long as a function doesn't change its return type. I'd like to cover this case as well not to worry about it in the future. Hence I'm looking for a general method with the above assumptions. signature.asc Description: OpenPGP digital signature
