search

DASSL / ClassDB

An open-source system to let students experiment with relational data
https://dassl.github.io/ClassDB/
Other
8 stars 2 forks source link

Add session id to table DDLActivity (N) #237

Closed smurthys closed 6 years ago

smurthys commented 6 years ago

Adding column SessionID to table DDLActivity makes it possible to connect each DDL acitivity to an application and a connection. This information is necessary for analysis operations.

Postgres doesn't have a function to get session id, but the following query (based on pg doc for log_line_prefix) should work:

SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
       to_hex(pid)
FROM pg_stat_activity
WHERE pid = pg_backend_pid();
wildtayne commented 6 years ago

Oh I understand the function now. I had mentioned in #235 that this function might be an issue, however I didn't realize that pg_stat_activity exposes backend_start. I will confirm that this function produces a matching session ID.

EDIT: On the surface this query seems to produce correct SessionIDs. If I have time in M3, I will try integrating it into the DDL trigger.

smurthys commented 6 years ago

Adding SessionID to DDLActivity changes many functions and views, and needs many documentation changes. However, the changes are well defined and can be done in M3.

Also, making this change now prevents later API-compatibility issues. So, we should try to get this done in M3.