Re: Transaction user id through logical decoding
От | Craig Ringer |
---|---|
Тема | Re: Transaction user id through logical decoding |
Дата | |
Msg-id | CAMsr+YFhe8yBsNCNEAFnY1OvEU6EXjmrGNFxexK1N=pqHLdjwA@mail.gmail.com обсуждение исходный текст |
Ответ на | Transaction user id through logical decoding (valeriof <valerio_farruggio@hotmail.com>) |
Ответы |
Re: Transaction user id through logical decoding
(valeriof <valerio_farruggio@hotmail.com>)
|
Список | pgsql-hackers |
<p dir="ltr"><p dir="ltr">On 28 Sep. 2016 17:50, "valeriof" <<a href="mailto:valerio_farruggio@hotmail.com">valerio_farruggio@hotmail.com</a>>wrote:<br /> ><br /> > Hi all,<br/> > I'm developing a custom plugin to stream Postgres CDC changes to my client<br /> > application. One ofthe info the application needs is the user id of the<br /> > user who executed a certain transaction. I can see we haveaccess to other<br /> > transaction info (xid, lsn, changed data) but apparently the user id is not<br /> > available.<br/> > Does anyone know if it is possible to extract this info in any way?<p dir="ltr">It is not recorded inWAL so it isn't possible as-is.<p dir="ltr">Also you can't assume a tx is all done by one user id. SET ROLE, SECURITY DEFINER,etc. Even the session user can change during a tx (which IMO a defect).<p dir="ltr">You have a couple of options.You could patch pg to add an option to xlog user id with heap and heap2 rmgr writes, but I doubt it'd have much chanceof getting into core. You'd need to work out how to tell when the new info was there too.<p dir="ltr">You could adda new rmgr that logs use is at tx start and whenever it changes. Doing this robustly could be interesting but I thinkit'd have more chance. 10.0 at the earliest though.<p dir="ltr">You could use a FOR EACH ROW trigger added to each tableto xlog a logical wal message (9.6 only) with the user id changing the row. Maybe optimise by keeping a cache with thelast id logged and only log again if it changes. Care here is needed for cleanup at xact end, rolled back subxact handlingetc.<p dir="ltr">(If you don't care about handling the corner cases you could use a FOR EACH STATEMENT trigger instead.)<pdir="ltr">You could use a special table in an extension schema that you insert rows into to record the user whoperformed an action. Using a before trigger. Delete the row as soon as you insert it since you only care about the walrecord. Then when decoding inserts examine the affected table oid. If it's your special table, save the stored user idin output plugin state instead of sending it to the peer as a normal insert. BDR has some things similar to this for itshandling of ddl replication, TRUNCATE, and global sequence voting that you could take a look at; see bdr_output.c andbdr_apply.c .<br /><br /><p dir="ltr">> Thanks,<br /> > Valerio<br /> ><br /> ><br /> ><br /> > --<br/> > View this message in context: <a href="http://postgresql.nabble.com/Transaction-user-id-through-logical-decoding-tp5923261.html">http://postgresql.nabble.com/Transaction-user-id-through-logical-decoding-tp5923261.html</a><br />> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.<br /> ><br /> ><br /> > --<br /> >Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> > To make changes to your subscription:<br/> > <a href="http://www.postgresql.org/mailpref/pgsql-hackers">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/>
В списке pgsql-hackers по дате отправления: