Обсуждение: postgres_fdw and skip locked
Hi. Now select ... for update ... [skip locked|nowait] options are not pushed down to remote servers. I see the only reason is that we can speak to pre-9.5 server, which doesn't understand skip locked option. Are there any other possible issues? Should we add foreign table option to control this behavior? -- Best regards, Alexander Pyhalov, Postgres Professional
Вложения
On Mon, Feb 14, 2022 at 4:23 PM Alexander Pyhalov <a.pyhalov@postgrespro.ru> wrote: > > Hi. > > Now select ... for update ... [skip locked|nowait] options are not > pushed down to remote servers. I see the only reason is that we can > speak to pre-9.5 server, which doesn't understand skip locked option. > Are there any other possible issues? Should we add foreign table option > to control this behavior? Should we always push these clauses if remote server's version is newer than 9.5? There are quite a few options already. It will be good not to add one more. I see that these options will work for all kinds of relations. So no problem if foreign table is pointing to something other than a table. -- Best Wishes, Ashutosh Bapat
Ashutosh Bapat писал 2022-02-16 16:40: > On Mon, Feb 14, 2022 at 4:23 PM Alexander Pyhalov > <a.pyhalov@postgrespro.ru> wrote: >> >> Hi. >> >> Now select ... for update ... [skip locked|nowait] options are not >> pushed down to remote servers. I see the only reason is that we can >> speak to pre-9.5 server, which doesn't understand skip locked option. >> Are there any other possible issues? Should we add foreign table >> option >> to control this behavior? > > Should we always push these clauses if remote server's version is > newer than 9.5? There are quite a few options already. It will be good > not to add one more. > > I see that these options will work for all kinds of relations. So no > problem if foreign table is pointing to something other than a table. Hi. The issue is that we perform deparsing while planing, we haven't connected to server yet. Are there any ideas how to find out its version without specifying it, for example, in server options? -- Best regards, Alexander Pyhalov, Postgres Professional
On Wed, Feb 16, 2022 at 8:38 PM Alexander Pyhalov <a.pyhalov@postgrespro.ru> wrote: > > Ashutosh Bapat писал 2022-02-16 16:40: > > On Mon, Feb 14, 2022 at 4:23 PM Alexander Pyhalov > > <a.pyhalov@postgrespro.ru> wrote: > >> > >> Hi. > >> > >> Now select ... for update ... [skip locked|nowait] options are not > >> pushed down to remote servers. I see the only reason is that we can > >> speak to pre-9.5 server, which doesn't understand skip locked option. > >> Are there any other possible issues? Should we add foreign table > >> option > >> to control this behavior? > > > > Should we always push these clauses if remote server's version is > > newer than 9.5? There are quite a few options already. It will be good > > not to add one more. > > > > I see that these options will work for all kinds of relations. So no > > problem if foreign table is pointing to something other than a table. > > Hi. > The issue is that we perform deparsing while planing, we haven't > connected to server yet. > Are there any ideas how to find out its version without specifying it, > for example, in server options? Yes, you are right. I wish foreign servers, esp. postgresql, could be more integrated and if we could defer deparsing till execution phase. But that's out of scope for this patch. -- Best Wishes, Ashutosh Bapat
Ashutosh Bapat писал 2022-02-17 16:30: > On Wed, Feb 16, 2022 at 8:38 PM Alexander Pyhalov > <a.pyhalov@postgrespro.ru> wrote: >> >> Ashutosh Bapat писал 2022-02-16 16:40: >> > On Mon, Feb 14, 2022 at 4:23 PM Alexander Pyhalov >> > <a.pyhalov@postgrespro.ru> wrote: >> >> >> > I see that these options will work for all kinds of relations. So no >> > problem if foreign table is pointing to something other than a table. >> >> Hi. >> The issue is that we perform deparsing while planing, we haven't >> connected to server yet. >> Are there any ideas how to find out its version without specifying it, >> for example, in server options? > > Yes, you are right. I wish foreign servers, esp. postgresql, could be > more integrated and if we could defer deparsing till execution phase. > But that's out of scope for this patch. Hi. I've updated patch to provide server-level "deparse_wait_policy" option, which controls if we deparse SKIP LOCKED or SELECT FOR UPDATE (true by default). This will make possible for people with old servers to revert to old behavior. -- Best regards, Alexander Pyhalov, Postgres Professional