"Marcelo de Moraes Serpa" <celoserpa@gmail.com> writes:
> I know that this PostgreSQL C module has a static var that in turn keeps the
> integer set by the function "set_session_id" - but is this var global to the
> server's service ? Does PostgreSQL mantain one "instance" of this var per
> requested connection ? I would like to know how this works.
PostgreSQL works using process, each backend that serves a connection
is a process and each process keeps and instance of the variable, so,
no, this variable is not shared globally (else it will not work as
expected).
> Take the following scenario:
> - user enters the web app;
> - web app takes a reference to a connection from the db connection pool;
> - web app does some db operation
>
> When the app takes a reference to a connection and does the operation, just
> after that, in the application, I set the session id. Then the db operation
> is performed and the audit stored procedure is ran. The audit-trail engine
> performs its work and logs the operation and modifications the user did as
> well as **which application user did it** (which is the purpose of the
> set_session_id technique - being able to pass the application user who did
> the operation to the server so that that the audit-trail can log it
> altogether).
>
> Once the operation is done and the connection is back to the pool, does
> PostgreSQL discart the associated var ? Does it mantain one "instance" per
> request made ? That's what I would like to know.
PostgreSQL does not discard the associated var and its value its keep
until the connection is closed (really closed not just returned to the
pool) and the process destroyed. That's why is important to reset its
value, just to be sure that the next time the connection is used (by
probably some other session) it does not get confused by some value
previously left.
BTW, having a C module has proved to be a PITA over the years, but
when this was constructed we didn't have any other option. Now I think
that the same idea can be constructed using custom variable clasess
http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html
but haven't got to the details yet.
Regards,
Manuel.
