Re: Deleting, indexes and transactions - Mailing list pgsql-general

Hello Adrian,

Thanks for replying!

I guess in this case, referential action, from your quote, on deleting As is check that there are no Bs referencing to-be-deleted A row. But since all Bs are deleted (not committed yet though) prior to deleting As, I don't understand why is this check taking that long time. Doesn't this transaction, that both deleting Bs and As belong to, have enough "knowledge" if not to skip this check then to at least have it executed faster? It seems, in case without index, that postgres is executing this referential integrity check sequentially over B data as if they were not deleted, it just skips raising error because it ultimately after long time finds B records are about to be deleted. It would be faster if postgres had a structure/info on transaction level which would allow it to execute following (sequential) queries/checks only over rows which haven't been marked for deletion - I guess that would add complexity. With index I guess postgres does same logic just uses index to lookup Bs referencing to-be-deleted A much faster, and then determines Bs have been marked for deletion and doesn't raise error. I wonder how other RDBMS behave in this case.

Anyway, regarding your second question, cascade delete hasn't been applied or tried yet. Case I've initially explained is one subcase of actual case that needs to be supported which is to sync As with an external source, which unfortunatelly doesn't provide info whether Bs have been changed or not for given A. So, there are two subcases, one where almost all data is dropped (As and Bs) and replaced with new, while in other subcase just some As data gets added while some As are deleted. In either case, we need to drop all Bs and add them because of lack of information of changes in Bs. Will check and see how that performs for both scenarios.

Kind regards,
Stevo.