bug/confusion with pg_log_standby_snapshot()
Is pg_log_standby_snapshot() expected to cause a WAL segment to be emitted in an otherwise idle system? In my lab setup, the primary did not, despite invoking pg_log_standby_snapshot() on it, even when several times the archive_timeout value passed after using that function. The setup was all Postgres 16.1, with a primary replicating to standby via log-shipping, and the standby (trying to) have a logical replication publication subscribed by a third Postgres server. In two separate trial runs, long waits happened on an idle system after invoking pg_log_standby_snapshot(). The function did not result in a new WAL segment being shipped to the standby, so on the third server the CREATE SUBSCRIPTION command hung until I eventually restarted the primary. At that point CREATE SUBSCRIPTION completed and everything began working. The documentation says: > If the primary is idle, creating a logical slot on standby may > take noticeable time. This can be sped up by calling the > pg_log_standby_snapshot function on the primary. https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS Using the function wasn't enough. Should I have done more to trigger WAL emission? If so, directly stating that in the documentation would have helped me, and maybe others. Thanks, Pete
Re: Unqualified relations in views
> PostgreSQL resolves tables and other objects according to the setting of
> "search_path" at CREATE VIEW time. The query itself is stored in parsed
form.
>
> pg_get_viewdef() deparses the query and only prepends a schema name if the
> schema is not on the "search_path". So the solution is to set
"search_path"
> empty:
>
> SET search_path = '';
>
> SELECT pg_get_viewdef('myschema.myview');
Thank you! That is the perfect answer to my question!
Is Postgres hiding the ball a bit here? Is there a reason that obscuring
the
known and static schema is better than showing it? In my case (tracking
down
execution differences between local and FDW view use) this has occupied a
lot of
time.
Thanks again,
Pete O'Such
Unqualified relations in views
For a view, how does one show what schema was used to qualify a relation, when the query used to create the view originally left the relation unqualified? The qualification of the view query seems static in all uses of the view. Using pg_get_viewdef() returns the unqualified relation, but Postgres always executes a qualified version of the view query, seemingly determined upon creation of the view. That implies the final qualifier is stored by Postgres, but I don't know how to show it. Thanks, Pete O'Such
Re: PG FDW query fails, works local, same credentials
> Check your search path assumptions. postgres_fdw runs remote > queries with a very minimal search_path setting, Indeed it was the search path, combined with references to user-defined functions that weren't schema-qualified. Thank you for the pointer! -Pete On Thu, Aug 31, 2023 at 12:46 AM Tom Lane wrote: > "Pete O'Such" writes: > > I've got a view on server A (PG 15.2) that fails when queried via FDW > from > > server B (also PG 15.2). Querying it as a foreign table from server B > > yields a message like "ERROR: function blah(type) does not exist". > > Check your search path assumptions. postgres_fdw runs remote queries > with a very minimal search_path setting, so that unqualified references > to non-built-in objects are likely to fail. > > regards, tom lane >
PG FDW query fails, works local, same credentials
I've got a view on server A (PG 15.2) that fails when queried via FDW from server B (also PG 15.2). Querying it as a foreign table from server B yields a message like "ERROR: function blah(type) does not exist". Confusingly, I succeed when: I log into server A, set my role to match the role used with the FDW, and query with the exact query text that server B sent to server A (according to the error on server B). The function that it complains about does exist, and I have made the effort to provide appropriate grants to the role in question. So it does work as expected from within server A, as the same role. But why would it then fail from server B? Both cases use the same role/credentials. Plenty of other foreign tables work in this same setup between these two servers, whether pointing to tables or views. I hit and resolved similar issues earlier where the role lacked necessary grants. Having experienced it before, this time I'm pretty sure that the right grants are in place, yet it still fails when used via FDW. I'm running out of things to try and wondering if this will turn out to be a bug. What should I be trying before reporting it as a bug? Thanks, Pete
Re: No psql md5 auth, psql 14.1 to PG 11
Yup, FIPS-mode is on for both ends. I'd love a pointer to docs or other resources for figuring out what the path forward looks like. Thanks for the fast and insightful diagnostic tip.. -Pete O'Such On Mon, Apr 18, 2022 at 5:08 PM Tom Lane wrote: > Adrian Klaver writes: > > On 4/18/22 13:12, Pete O'Such wrote: > >> I'm unable to authenticate with psql to a PG 11 database on server A > >> from server B which has PG 14.1 installed. So it's psql 14.1 not > >> authenticating to PG 11. Other clients can and do authenticate to the > >> PG 11 database, it only seems to fail with the psql 14.1 client. > >> > >> The PG 11 server uses md5. So is the PG 14.1, but I didn't expect that > >> to matter for a straight psql connection plus interactive password > entry. > > > My first thought was that is was a md5/scram-sha-256 issue but I'm not > > sure how as both versions support both password types. > > I just checked the case here, and I can connect fine from psql 14 to > a v11 server with a password stored in md5. So I'm not sure what's > up either, but there has to be something off-the-beaten-path about > this. > > Looking at the v14 code, the error message tells us that > pg_password_sendauth failed, which has several possible explanations: > > * pqGetnchar failed, which'd imply a malformed server challenge message. > Seems unlikely, if other clients can connect. > > * pg_md5_encrypt failed. Hard to believe ... unless the v14 psql > is running on a FIPS-mode machine, which'd refuse all MD5 operations? > > * pqPacketSend failed, ie server disconnected after sending the > challenge. Also seems doubtful. > > So my recommendation is to check for FIPS mode. > If it's a recent Linux, what does "sysctl crypto.fips_enabled" say? > (This theory also requires that v14 was built with openssl support.) > > FWIW, v15 will provide a more on-point error message in such cases. > > regards, tom lane >
No psql md5 auth, psql 14.1 to PG 11
I'm unable to authenticate with psql to a PG 11 database on server A from server B which has PG 14.1 installed. So it's psql 14.1 not authenticating to PG 11. Other clients can and do authenticate to the PG 11 database, it only seems to fail with the psql 14.1 client. The PG 11 server uses md5. So is the PG 14.1, but I didn't expect that to matter for a straight psql connection plus interactive password entry. The error is "fe_sendauth: error sending password authentication". The bigger picture is that I'm trying to set up a FDW connection from Server B's PG 14.1 back to Server A's PG 11. I hit this same error and shifted to troubleshooting with just psql but kept getting the error. I've checked for md5 use on both servers, but I still don't see a role for that with psql on Server B's psql. What can I do to get authentication working? Thanks, Pete O'Such
