Re: Kerberos-Postgresql implementation for user authentication
On Thu, Jul 9, 2020 at 11:22 AM Niels Jespersen wrote: > > > > > *Fra:* Brajendra Pratap Singh > *Emne:* Kerberos-Postgresql implementation for user authentication > > > > > Please share the steps to implement the Kerberos with postgresql for user > authentication purpose. Also share the document if any. > > If you are trying to do passwordless login for windows users, then this > explains that scenario quite well. We are using SSPI for end-users. > > > > > https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows > > > > You can also read these posts: https://info.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication https://www.highgo.ca/2020/03/18/postgresql-gssapi-authentication-with-kerberos-part-1-how-to-setup-kerberos-on-ubuntu/ https://www.highgo.ca/2020/03/26/postgresql-gssapi-authentication-with-kerberos-part-2-postgresql-configuration/ https://www.highgo.ca/2020/03/30/postgresql-gssapi-authentication-with-kerberos-part-3-the-status-of-authentication-encryption-and-user-principal/ > -- Regards, Michael Holzman
Autovacuum of independent tables
Hi, I have two applications A and B. A runs SELECT statements only and only on tableA. B actively updates tableB, A never looks into tableB. B has nothing to do with tableA. Still, if A is inside a long running transaction, autovacuum does not handle tableB. Why is it so? -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 10:46 AM Pavel Stehule wrote: > > autovacuum does cleaning of changes related to finished transactions. It > does nothing if possible dead tuples are assigned to open transactions. > This is the point. Autovacuum does not clean dead tuples of closed transactions in tableB while there is an open transaction on tableA. But the tables have nothing in common. They are handled by separate applications and there are no transactions that touch both tables simultaneously. Why does autovacuum create an artificial dependency on the tables? -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 11:28 AM Michael Paquier wrote: > > This is called MVCC, which applies to a session as a whole. The point > here is that even if your application knows that only tableA is used > by a given transaction, Postgres cannot know that, as it could be > possible that data from tableB is needed in this same transaction, so > old versions of the rows from tableB matching with the snapshot hold > by this long-running transaction still have to be around. > > Yes, I thought so. I just hoped there may be a workaround decoupling the tables. Thanks. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 12:28 PM Pavel Stehule wrote: > > You can try to reduce length of transactions, if possible. > > > This is the problem. A and B were developed for Oracle where SELECT does not open a transaction. We moved them to PG and now we have to very accurately add COMMITs without breaking the flow. It is quite a complex thing. I hoped we can avoid that. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 1:52 PM Ravi Krishna wrote: > > > Interesting. Are you telling the Oracle version of the code had no > intermittent COMMIT and relied on one > final COMMIT at the end. Even in Oracle developers must have planned for > commit since a long running > open transaction can lead to “snapshot too old” error. > Yes, I am saying just that. With one important clarification: there were no transactions as SELECT does not open them and the application does not change anything on that connection. So, no 'snapshot too old' and no COMMITs. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote: > > > Whether you have autocommit on or off, you can *always* control things > explicitly. And you can certainly run "multi-statement transactions" in > autocommit on -- in fact, it's what most people do since it's the default > configuration of the system (and I don't see why multi-table would even be > relevant). > > Autocommit on/off only controls what happens when you *don't* control > things explicitly. > I know that we can control things explicitly with "autocommit on". But we would need to add "BEGIN" statements to the code which is an even bigger change than adding COMMITs. We considered it and found that the development cost is too high. It seems I was not clear enough. I do not complain. I have been a PG fan since 2000 when I worked with it for the first time. I just wanted to understand it deeper and, fortunately, find a work around that would simplify our current development. Thanks to all. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 5:38 PM Tom Lane wrote: > Magnus Haganderwrites: > > Oh sure, but there is clearly *something* going on, so we should try to > > figure that out. Because a transaction running multiple independent > selects > > with the defaults settings will not actually block autovacuum. > > I don't think the OP is claiming that autovacuum is blocked, only that > it's failing to remove recently-dead rows that he thinks could be removed. > Yes, this is exactly what happens. The reason that's not so is that whether or not transaction A *has* > touched table B is irrelevant. It *could* read table B at any moment, > for all autovacuum knows. Therefore we cannot remove rows that should > still be visible to A's snapshot. > > There are some approximations involved in figuring out which rows are > potentially still visible to someone. So perhaps this is a situation > where an approximation is being used and tighter analysis would have > shown that indeed a row could be removed. But we haven't seen any > evidence of that so far. The basic fact that A's snapshot is limiting > removal of rows from a table it has not touched is not a bug. > It's obviously not a bug. I was just surprised when I figured that out. It's also quite complex to explain to my colleagues. Actually, this is the main reason I started this thread: I tried to explain to someone and felt that I miss something. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 5:47 PM Magnus Hagander wrote: > > I feel there is still some piece of information missing there, that could > explain the problem better... > I gave all the information I have (without real application and table names, of course). Both applications are C++ demons working with PG via ODBC on RHEL. We use default ODBC settings. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 6:15 PM Tom Lane wrote: > Well, we don't know what isolation level the OP is using. We use the default. We do not explicitly set the isolation level. We also don't know what PG version he's using. it's 12.1 >From memory, it hasn't been that long > since we fixed things so that an idle read-committed transaction > advertises no xmin. It's also possible that the transaction isn't really > idle between statements (eg, if it's holding open cursors, or the like). > There are no open cursors. -- Regards, Michael Holzman
Re: Autovacuum of independent tables
On Tue, Sep 8, 2020 at 6:33 PM Magnus Hagander wrote: > > Per his session list, 11.2. > 11.2 is on my personal testing machine. The real tests run with 12.1 Oh, now *cursors* is definitely something I didn't think of. And especially > in the context of ODBC, I wonder if it might be creating cursors > transparently, and that this somehow causes the problems. > > Michael, do you know if that might be the case? Or try enabling > log_statements to check if it is? > This is very interesting. I'll try to check it. I'll post here a reply if we do have open cursors. Thanks. -- Regards, Michael Holzman
