/// <summary> /// Collects the tables to cleanup /// </summary> /// <returns></returns> public static List <TTLTable> GetTTLTables() { List <TTLTable> tables = new List <TTLTable>(); using (SqlConnection sqlConn = Server.GetSqlConnection()) { NpgsqlConnection conn = sqlConn.GetConnection(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "Select database_name, schema_name, table_name, col_name, expiry_in_sec from pg_ttl_tables;"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { TTLTable tbl = new TTLTable(); tbl._databaseName = reader.GetString(0); tbl._schemaName = reader.GetString(1); tbl._tableName = reader.GetString(2); tbl._columnName = reader.GetString(3); tbl._expiryTime = reader.GetInt32(4); tables.Add(tbl); } } } } return(tables); }
public void RunSweeper() { List <TTLTable> tables = TTLTable.GetTTLTables(); _traceWriter.Info(string.Format("Processing {0} tables on the server", tables.Count)); foreach (TTLTable tbl in tables) { try { using (SqlConnection conn = new SqlConnection()) { NpgsqlCommand cmd = conn.GetConnection().CreateCommand(); cmd.CommandText = string.Format(_queryFormat, tbl.SchemaName, tbl.TableName, tbl.ColumnName, tbl.ExpiryTimeInSeconds); int delRows = cmd.ExecuteNonQuery(); _traceWriter.Info(string.Format("Delete {0} rows from {1}.{2}", delRows, tbl.SchemaName, tbl.TableName)); } } catch (Exception ex) { _traceWriter.Error(ex.ToString()); } } }