You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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(timestampas datetime) astimestamp,
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 digts
regexp_contains(event_message, '[0-9]{3}')
Escaping reserved characters:
\. interpreted as period . instead of as a wildcard
-- 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(timestampas datetime) astimestamp,
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 field is always the event_message. At deeper layers are metadata and beyond:
field reference examples from postgres
field
description
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 parsedfrom postgres_logs
-- extract first layercross join unnest(postgres_logs.metadata) as metadata
-- extract second layercross join unnest(metadata.parsed) as parsed;
Complete Example
Filter Postgres errors encountered by the auth server
select
cast(postgres_logs.timestampas datetime) astimestamp,
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')
andparsed.user_name='supabase_storage_admin'order bytimestampdesclimit100
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.
There is one exception. The function_edge_logs and function_logs can be joined by metadata.deployment_id
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
timestamps are included in all outputs
Whether the timestamp field is included or not in your query, it will be returned as a column in the output table.
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Querying the Logs
For greater control over the Logs, consider using the Query Explorer.
Basic Queries
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.
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 stringFind messages that end with a phrase:
$
only looks for values at the end of the stringIgnore case sensitivity:
(?i)
ignores capitalization for all proceeding charactersWildcards in regex:
.
can represent any string of charactersAlphanumeric ranges in regex:
[1-9a-zA-Z]
finds any strings with only numbers and lettersRepeated values in regex:
x*
zero or more xx+
one or more xx?
zero or one xx{4,}
four or more xx{3}
exactly 3 xEscaping reserved characters:
\.
interpreted as period.
instead of as a wildcardor
statements in regex:x|y
any string withx
ory
presentand/or/not statements in SQL:
and
,or
, andnot
are all native terms in SQL and can be used in conjunction with regex to filter resultsUnderstanding field references
Each product has its own log table. Unlike traditional tables, log tables contain nested fields. The first and most accessible field is always the event_message. At deeper layers are metadata and beyond:
field reference examples from postgres
The
event_message
can be accessed immediately.The
metadata.parsed.user_name
andmetadata.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 unfamiliarComplete Example
Filter Postgres errors encountered by the auth server
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.
There is one exception. The function_edge_logs and function_logs can be joined by
metadata.deployment_id
with
andilike
statements cannot be usedThe query editor parses logs for optimization. The parser does not yet support
with
and subquery statements. Furthermoreilike
is not supported by BigQuery's variant of SQLtimestamps are included in all outputs
Whether the timestamp field is included or not in your query, it will be returned as a column in the output table.
Beta Was this translation helpful? Give feedback.
All reactions