This page has not been translated into your language. Request translation

SYS_LOAD_DETAIL

PDFRSS

Returns information to track or troubleshoot a data load.

This view records the progress of each data file as it is loaded into a database table.

This view is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.

Table columns

Column name	Data type	Description
user_id	integer	ID of the user who generated the entry.
query_id	integer	Query ID.
file_name	character(256)	File name to be loaded.
bytes_scanned	integer	The number of bytes scanned from the file in Amazon S3.
lines_scanned	integer	Number of lines scanned from the load file. This number may not match the number of rows that are actually loaded. For example, the load may scan but tolerate a number of bad records, based on the MAXERROR option in the COPY command.
record_time	timestamp	Time that this entry was last updated.
splits_scanned	Number of splits of this file.	Number of splits of this file.
start_time	timestamp	Time that this file processing started.
end_time	timestamp	Time that this file processing finished.

Sample queries

The following example returns details for the last COPY operation.

select query_id, trim(file_name) as file, record_time
from sys_load_detail
where query_id = pg_last_copy_id();

 query_id |               file               |          record_time        
----------+----------------------------------+----------------------------
 28554    | s3://dw-tickit/category_pipe.txt | 2013-11-01 17:14:52.648486 
(1 row)

The following query contains entries for a fresh load of the tables in the TICKIT database:

select query_id, trim(file_name), record_time
from sys_load_detail
where file_name like '%tickit%' order by query_id;

 query_id |           btrim          |          record_time          
----------+--------------------------+----------------------------
 22475    | tickit/allusers_pipe.txt | 2013-02-08 20:58:23.274186 
 22478    | tickit/venue_pipe.txt    | 2013-02-08 20:58:25.070604  
 22480    | tickit/category_pipe.txt | 2013-02-08 20:58:27.333472 
 22482    | tickit/date2008_pipe.txt | 2013-02-08 20:58:28.608305  
 22485    | tickit/allevents_pipe.txt| 2013-02-08 20:58:29.99489    
 22487    | tickit/listings_pipe.txt | 2013-02-08 20:58:37.632939 
 22593    | tickit/allusers_pipe.txt | 2013-02-08 21:04:08.400491  
 22596    | tickit/venue_pipe.txt    | 2013-02-08 21:04:10.056055  
 22598    | tickit/category_pipe.txt | 2013-02-08 21:04:11.465049  
 22600    | tickit/date2008_pipe.txt | 2013-02-08 21:04:12.461502  
 22603    | tickit/allevents_pipe.txt| 2013-02-08 21:04:14.785124  
 22605    | tickit/listings_pipe.txt | 2013-02-08 21:04:20.170594  

(12 rows)

The fact that a record is written to the log file for this system view does not mean that the load committed successfully as part of its containing transaction. To verify load commits, query the STL_UTILITYTEXT view and look for the COMMIT record that corresponds with a COPY transaction. For example, this query joins SYS_LOAD_DETAIL and STL_QUERY based on a subquery against STL_UTILITYTEXT:

select l.query_id,rtrim(l.file_name),q.xid
from sys_load_detail l, stl_query q
where l.query_id=q.query
and exists
(select xid from stl_utilitytext where xid=q.xid and rtrim("text")='COMMIT');

 query_id |           rtrim           |  xid
----------+---------------------------+-------
 22600    | tickit/date2008_pipe.txt  | 68311
 22480    | tickit/category_pipe.txt  | 68066
  7508    | allusers_pipe.txt         | 23365
  7552    | category_pipe.txt         | 23415
  7576    | allevents_pipe.txt        | 23429
  7516    | venue_pipe.txt            | 23390
  7604    | listings_pipe.txt         | 23445
 22596    | tickit/venue_pipe.txt     | 68309
 22605    | tickit/listings_pipe.txt  | 68316
 22593    | tickit/allusers_pipe.txt  | 68305
 22485    | tickit/allevents_pipe.txt | 68071
  7561    | allevents_pipe.txt        | 23429
  7541    | category_pipe.txt         | 23415
  7558    | date2008_pipe.txt         | 23428
 22478    | tickit/venue_pipe.txt     | 68065
   526    | date2008_pipe.txt         |  2572
  7466    | allusers_pipe.txt         | 23365
 22482    | tickit/date2008_pipe.txt  | 68067
 22598    | tickit/category_pipe.txt  | 68310
 22603    | tickit/allevents_pipe.txt | 68315
 22475    | tickit/allusers_pipe.txt  | 68061
   547    | date2008_pipe.txt         |  2572
 22487    | tickit/listings_pipe.txt  | 68072
  7531    | venue_pipe.txt            | 23390
  7583    | listings_pipe.txt         | 23445
(25 rows)