Reading from a database partition

I have the following code snippet that creates and writes to a Postgres Partition:

package main
import (
"fmt"
"log"
"time"
"github.com/go-pg/pg/v10"
"github.com/go-pg/pg/v10/orm"
)
type Log struct {
tableName struct{} `pg:"logs,partition_by:RANGE(log_time)"`
Id        int       `pg:"id,pk"`
LogString string    `pg:"log_string"`
LogTime   time.Time `pg:"log_time,pk"`
}
func main() {
db := pg.Connect(&pg.Options{
Addr:     "localhost:5432",
User:     "postgres",
Password: "postgres",
Database: "db_name",
})
defer db.Close()
/*
The code below generates the following MySQL query
CREATE TABLE IF NOT EXISTS "logs" (
"id" bigserial,
"log_string" text,
"log_time" timestamptz,
PRIMARY KEY ("id", "log_time")
) PARTITION BY RANGE(log_time)
*/
err := db.Model(&Log{}).CreateTable(&orm.CreateTableOptions{
IfNotExists: true,
})
if err != nil {
log.Fatal(err)
}
logStartTime := time.Date(2020, 1, 1, 0, 0, 0, 0, time.UTC)
logEndTime := logStartTime.AddDate(0, 0, 3)
for logStartTime.Unix() <= logEndTime.Unix() {
logData := &Log{
LogString: fmt.Sprintf("Log at %s", logStartTime.String()),
LogTime:   logStartTime,
}
// before insert: always try to create partition
err = createNewPartition(db, logStartTime)
if err != nil {
log.Fatal(err)
}
_, err = db.Model(logData).Insert()
if err != nil {
log.Fatal(err)
}
logStartTime = logStartTime.AddDate(0, 0, 1)
}
}
// createNewPartition will create new partition of logs table if not exist.
// Partition created by in one month range start from first date of the month to last date of the month.
func createNewPartition(db *pg.DB, currentTime time.Time) error {
firstOfMonth := time.Date(currentTime.Year(), currentTime.Month(), 1, 0, 0, 0, 0, time.UTC)
firstOfNextMonth := firstOfMonth.AddDate(0, 1, 0)
year := firstOfMonth.Format("2006")
month := firstOfMonth.Format("01")
sql := fmt.Sprintf(
`CREATE TABLE IF NOT EXISTS logs_y%s_m%s PARTITION OF logs FOR VALUES FROM ('%s') TO ('%s');`,
year, month,
firstOfMonth.Format(time.RFC3339Nano),
firstOfNextMonth.Format(time.RFC3339Nano),
)
log.Println("Creating partition ", sql)
_, err := db.Exec(sql)
return err
}
func readFromPartition(db *pg.DB) error {
return nil
}

However, I want to to implement the method readFromPartition... what would look like? Can I read from each partition? Given a data log, I want to query in the right partition.

To read from the correct partition, you'll need to determine which partition the data resides in based on the LogTime. Then, you can construct a query that specifies the appropriate partition using the pg.Query and pg.Model functions. Here's an implementation of the readFromPartition function that retrieves log entries based on a given logTime:

func readFromPartition(db *pg.DB, logTime time.Time) ([]Log, error) {
firstOfMonth := time.Date(logTime.Year(), logTime.Month(), 1, 0, 0, 0, 0, time.UTC)
year := firstOfMonth.Format("2006")
month := firstOfMonth.Format("01")
partitionName := fmt.Sprintf("logs_y%s_m%s", year, month)
var logs []Log
err := db.Model(&logs).TableExpr(partitionName).Where("log_time >= ? AND log_time < ?", logTime, logTime.Add(24*time.Hour)).Select()
if err != nil {
return nil, err
}
return logs, nil
}

Here's how to use the readFromPartition function to read logs:

func main() {
// ... (existing code)
logStartTime := time.Date(2020, 1, 1, 0, 0, 0, 0, time.UTC)
logEndTime := logStartTime.AddDate(0, 0, 3)
for logStartTime.Unix() <= logEndTime.Unix() {
// ... (existing code)
logStartTime = logStartTime.AddDate(0, 0, 1)
}
// Read logs for a specific date
logs, err := readFromPartition(db, time.Date(2020, 1, 1, 0, 0, 0, 0, time.UTC))
if err != nil {
log.Fatal(err)
}
for _, logData := range logs {
fmt.Println(logData.LogString, logData.LogTime)
}
}

This code will read logs from the partition that corresponds to the specified logTime. If there are any logs in the partition for the given date, it will print them.