topic Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du in Administration and Deployment

sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob dumps

paterd2 — Wed, 07 May 2025 08:32:05 GMT

Dear community experts.

I have an issue with inodes in our backups , especially postgres .
wc -l gives me 642258
642258 .........../20250507-010211F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc

The issue is that it looks that there are orphaned largeobjects.

I suspect the export and import of reports.

Daily we export and import our reports.

We delete the old ones, but it looks like this does not correctly delete the large objects.

We use 'sas-viya transfer export ' and 'sas-viya transfer delete'.

sh-4.4$ vacuumlo -n SharedServices

Connected to database "SharedServices"

Test run: no large objects will be removed!

Checking content_data_oid_no in files.file_content

Checking content_bin in transfer.large_object

Checking content_bin in transfer.map_object

Checking object_content_txt in transfer.package_object

Checking object_summary_txt in transfer.package_object

Checking error_txt in transfer.transfer_export_job

Checking request_txt in transfer.transfer_export_job

Checking error_txt in transfer.transfer_export_task

Checking error_txt in transfer.transfer_import_job

Checking request_txt in transfer.transfer_import_job

Checking error_txt in transfer.transfer_import_task

Checking model_xml_doc_oid in workflow.wf_definition_model

Checking message_json_doc_oid in workflow.wf_history_event

Would remove 447798 large objects from database "SharedServices".

Do we have to run : vacuumlo SharedServices on a regular schedule ?
Is this normal behavior of 'sas-viya transfer' ?

Regards

Dik.

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

JackMcGuire — Wed, 07 May 2025 09:52:04 GMT

Hi Dik,

The transfer packages are stored in the SharedServices PostgreSQL database and can only be deleted using the SAS CLI, rather than through any of the GUIs. There is a great post from AllenCunningham which provides much more detail here: Managing SAS Viya Transfer Packages.

TDLR;

'Transfer List' will show you all packages:

./sas-viya transfer list

'Transfer Delete' will then delete a specific package:

./sas-viya transfer delete --id <package_id>

To delete multiple packages you can use Python Tools for SAS Viya, listtransferpackages.py allows you to create a CSV file with filters which can then be used as input into deletetransferpackages.py to delete multiple packages.

Thanks,

Jack

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

gwootton — Wed, 07 May 2025 13:52:15 GMT

The transfer service will call lo_unlink to the postgres database when you delete a package to delete the associated large object. If this call returns an error from postgres I would expect you to see an error in the transfer service log. The vacuumlo utility is the appropriate solution for removing those large objects that have been orphaned, so I would agree this is something that should be done periodically along with your other Postgres routine maintenance tasks like vacuuming and reindexing.

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

paterd2 — Thu, 08 May 2025 05:56:22 GMT

Greg , we will schedule this. I will also take a look at the log.

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

paterd2 — Thu, 08 May 2025 05:58:40 GMT

Jack, thank you for this advice. We do use these transfer cli commands.
I will take a closer look at the Python Tools for SAS Viya.
Regards,
Dik

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

paterd2 — Fri, 09 May 2025 07:42:42 GMT

Greg , there are many ERRORS in the transfer log.....
Here is an example.

{"version":1,"timeStamp":"2025-04-23T19:28:56.65+02:00","level":"error","source":"sas-transfer","message":"[TRANSFER_PROMOTION_EXCEPTION] Exception occurred while importing a package.\norg
.springframework.orm.jpa.JpaSystemException: Unable to access lob stream; nested exception is org.hibernate.HibernateException: Unable to access lob stream\nCaused by: org.hibernate.Hibern
ateException: Unable to access lob stream\nCaused by: org.postgresql.util.PSQLException: ERROR: large object 30416 does not exist","properties":{"logger":"com.sas.transfer.domain.Promotion
Manager","thread":"transfer-service-request-pool-10-thread-1"},"messageKey":"com.sas.transfer.logging.LogMessages.TRANSFER_PROMOTION_EXCEPTION"}

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

gwootton — Fri, 09 May 2025 15:14:03 GMT

This error is an import failure. When importing you first upload a package to the transfer service which would store the package as a large object, then import on that package ID. This error message is saying the package ID we are trying to import is associated with a large object that isn't in the database, so this is sort of the opposite of your initial concern, where we have a reference to the large object in the transfer schema table, but the large object itself isn't present.

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

paterd2 — Thu, 13 Nov 2025 09:09:39 GMT

I have some interesting information on this subject.

Daily we export the SAS reports.

We keep this for 30 days.

Side effect is that this is stored in the postgres database.

about 80% of the pg_largeobjects is from the sas - package exports.

Giving the following effect.

psql # select count from pg_largeobject
where loid in ( select object_content_txt from transfer.package_object ) or loid in ( select object_summary_txt from transfer.package_object ) ;
count
----------
15337937
(1 row)

SharedServices=# select count from pg_largeobject;
count
----------
17444165
(1 row)

The blobs.toc file containts over 500.000 records, we keep 15 days.

$ wc -l viya/data/*/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
597579 viya/data/20251022-200214F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
634932 viya/data/20251026-010016F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
562805 viya/data/20251027-200210F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
577580 viya/data/20251028-200018F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
592384 viya/data/20251029-200214F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
607219 viya/data/20251030-200210F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
621721 viya/data/20251031-200207F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
620364 viya/data/20251102-010217F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
566437 viya/data/20251103-200213F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
578777 viya/data/20251104-200208F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
583160 viya/data/20251105-200210F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
597774 viya/data/20251106-200209F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
612399 viya/data/20251107-200208F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
401607 viya/data/20251109-010213F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
550359 viya/data/20251110-200210F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
564687 viya/data/20251111-200209F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
579554 viya/data/20251112-200213F/__default__/postgres/SharedServices_pg_dump.dmp/blobs.toc
9849338 total

This is a large amount of inodes ...

We are now considering another approach.

Btw : we run vacuum_lo on a regular base.

regards,

Dik

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

gwootton — Tue, 18 Nov 2025 16:47:32 GMT

vacuumlo would remove large objects that are orphaned, (i.e. no table references the loid). Your query returns objects whose loid is in the table, so all the packages are still in the transfer service. Are you deleting the packages after downloading them using the command Jack mentioned? Did you go back and delete old packages?

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

paterd2 — Wed, 19 Nov 2025 07:39:08 GMT

Greg,

thanks for your answer.

We are now using the pyviyatools on test and will implement this in our production environments.

Selective backup and restore of SAS Viya Content

I will let you know the results after we deleted all the transfer packages.

Regards,

Dik

Re: sas-viya transfer delete leaves orphaned large objects behind resulting in many postgres blob du

paterd2 — Fri, 23 Jan 2026 06:41:20 GMT

The transfer to disk instead of the database with the pyviyatools helped a lot, the backup time is now 'normal' about 1 hour instead of 5 hours. The inode table is not full anymore.