Advanced filtering in the log explorer

[TheOtherBrian1]

Querying the Logs

For greater control over the Logs, consider using the Query Explorer.

Basic Queries

select
  cast(timestamp as datetime) as timestamp,
  event_message, 
  metadata 
from postgres_logs;

Filtering with regex

The Logs use BigQuery Style Regex with the regex_contains function. In its most basic form, it will just check if a string is present in a specified column.

select
  cast(timestamp as datetime) as timestamp,
  event_message, 
  metadata 
from postgres_logs
where
  regexp_contains(event_message, 'is present');

However, there are multiple regex rules that you should consider using:

Find messages that start with a phrase

^ only looks for values at the start of a string

-- find only messages that start with connection
regexp_contains(event_message, '^connection')

Find messages that end with a phrase:

$ only looks for values at the end of the string

-- find only messages that ends with port=12345
regexp_contains(event_message, '$port=12345')

Ignore case sensitivity:

(?i) ignores capitalization for all proceeding characters

-- find all event_messages with the word "connection"
regexp_contains(event_message, '(?i)COnnecTion')

Wildcards in regex:

. can represent any string of characters

-- find event_messages like "hello<anything>world"
regexp_contains(event_message, 'hello.world')

Alphanumeric ranges in regex:

[1-9a-zA-Z] finds any strings with only numbers and letters

-- find event_messages that contain a number between 1 and 5 (inclusive)
regexp_contains(event_message, '[1-5]')

Repeated values in regex:

x* zero or more x
x+ one or more x
x? zero or one x
x{4,} four or more x
x{3} exactly 3 x

-- find event_messages that contains any sequence of 3 digits
regexp_contains(event_message, '[0-9]{3}')

Escaping reserved characters:

\. interpreted as period . instead of as a wildcard

-- escapes . 
regexp_contains(event_message, 'hello world\.')

or statements in regex:

x|y any string with x or y present

-- find event_messages that have the word 'started' followed by either the word "host" or "authenticated"
regexp_contains(event_message, 'started host|authenticated')

and/or/not statements in SQL:

and, or, and not are all native terms in SQL and can be used in conjunction with regex to filter results

select
  cast(timestamp as datetime) as timestamp,
  event_message, 
  metadata 
from postgres_logs
where
  (
    regexp_contains(event_message, 'connection')
      and 
    regexp_contains(event_message, 'host')
  )
  or
    not regexp_contains(event_message, 'received');

Understanding field references

Each product has its own log table. Unlike traditional tables, log tables contain nested fields. The first and most accessible fields are always the event_message and timestamp. At deeper layers are metadata and beyond:

field reference examples from postgres

field	description
timestamp	time event was recorded
event_message	the log's message
metadata.parsed.user_name	the database role that executed a query
metadata.parsed.error_severity	if the log is an error, its severity

The event_message can be accessed immediately.

select
  event_message, 
from postgres_logs;

The metadata.parsed.user_name and metadata.parsed.error_severity must be unnested in a cross-join. This type of querying is used mostly with JSON and array columns, so it may look unfamiliar

select
  event_message,
  error_severity, -- found in parsed
  user_name -- found in parsed
from postgres_logs
-- extract first layer
cross join unnest(postgres_logs.metadata) as metadata
-- extract second layer
cross join unnest(metadata.parsed) as parsed;

Complete Example

Filter Postgres errors encountered by the auth server

select
  cast(postgres_logs.timestamp as datetime) as timestamp,
  parsed.error_severity,
  parsed.user_name,
  event_message
from postgres_logs
  cross join unnest(metadata) as metadata
  cross join unnest(metadata.parsed) as parsed
where
  regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
    and
  parsed.user_name = 'supabase_storage_admin'
order by
  timestamp desc
limit 100

Limitations

Log tables cannot be joined together

All product tables are independent of each other. They lack connections (like foreign keys) to link them, making table joins impossible. This means merging PostgREST logs with Postgres logs directly wouldn't work.

with and ilike statements cannot be used

The query editor parses logs for optimization. The parser does not yet support with and subquery statements. Furthermore ilike is not supported by BigQuery's variant of SQL