Re: How to schedule running of a script?
On Wed, Jan 19, 2022 at 05:10:29AM +, Shaozhong SHI wrote: > Can a script be scheduled to run within Postgres? https://www.depesz.com/2021/01/15/how-to-run-some-tasks-without-user-intervention-at-specific-times/ https://www.depesz.com/2021/01/28/how-to-run-some-tasks-without-user-intervention-at-specific-times-part-2/ depesz
Re: could not accept SSL connection: Success
Thanks, Michael, that's it, indeed! I had missed that part of the OpenSSL docs. These PG instances are running on Ubuntu Focal hosts that come with OpenSSL 1.1.1. We had never seen these in the previous Xenial images because those were using OpenSSL 1.0.2, and from what I've seen the bug was introduced in 1.1.0. Thanks again, Carla On Wed, Jan 19, 2022 at 5:42 AM Michael Paquier wrote: > On Mon, Jan 17, 2022 at 05:05:52PM +0100, Carla Iriberri wrote: > > I saw previous discussions where different errors were logged with the > > "Success" > > message and this was corrected/treated as a bug, but I couldn't find > similar > > reports specific to "could not accept SSL connection". Is this a known > > issue or > > case? > > Not based my recent mailing list memories, but I may be running short. > The error comes from the backend as you say, where this log would > expect something in saved_errno to feed %m. > > And, upstream documentation tells that: > https://www.openssl.org/docs/manmaster/man3/SSL_get_error.html > > "On an unexpected EOF, versions before OpenSSL 3.0 returned > SSL_ERROR_SYSCALL, nothing was added to the error stack, and errno was > 0. Since OpenSSL 3.0 the returned error is SSL_ERROR_SSL with a > meaningful error on the error stack." > > This would mean that relying on %m would be wrong for this case. And > I guess that you are using a version of OpenSSL older than 3.0? > -- > Michael >
Connecting Postgresql to Google Sheets
Hi, I am trying to get data from PostgreSQL to Google sheets but I am unable to do the same. Google script allows connecting using JDBC Service - https://developers.google.com/apps-script/reference/jdbc/jdbc#getcloudsqlconnectionurl. I think JDBC Doesn't support PostgreSQL. Is there any alternative on how we can import data to google sheets? Thanks
Re: Connecting Postgresql to Google Sheets
El día Mittwoch, Januar 19, 2022 a las 11:24:26 +, Siddharth Golia escribió: > Hi, > > I am trying to get data from PostgreSQL to Google sheets but I am unable to > do the same. Google script allows connecting using JDBC Service - > https://developers.google.com/apps-script/reference/jdbc/jdbc#getcloudsqlconnectionurl. > > I think JDBC Doesn't support PostgreSQL. Is there any alternative on how we > can import data to google sheets? > We develop and run a huge Library Management System on top of PostgreSQL (novadays 13.1) and this makes use of JDBC (postgresql-42.2.24.jar on Linux). matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: Connecting Postgresql to Google Sheets
On 1/19/22 7:56 AM, Matthias Apitz wrote: El día Mittwoch, Januar 19, 2022 a las 11:24:26 +, Siddharth Golia escribió: Hi, I am trying to get data from PostgreSQL to Google sheets but I am unable to do the same. Google script allows connecting using JDBC Service - https://developers.google.com/apps-script/reference/jdbc/jdbc#getcloudsqlconnectionurl. I think JDBC Doesn't support PostgreSQL. Is there any alternative on how we can import data to google sheets? We develop and run a huge Library Management System on top of PostgreSQL (novadays 13.1) and this makes use of JDBC (postgresql-42.2.24.jar on Linux). And I manage multiple systems who's applications connect via JDBC. -- Angular momentum makes the world go 'round.
Re: Connecting Postgresql to Google Sheets
On Wed, Jan 19, 2022 at 6:22 AM Siddharth Golia < siddharth.go...@altlifelab.com> wrote: > I think JDBC Doesn't support PostgreSQL. Is there any alternative on how > we can import data to google sheets? > It seems a bit ironic that searching on Google for "jdbc postgresql" turns up a driver as the first search result. David J.
Slony mailing list activity
Apologies for posting here, but.. I have attempted to sign up to the Slony mailing lists and have been waiting over a week to be approved. I’m suspicious that the mailing list functionality there is broken, perhaps? Or the moderator is MIA. When attempting to sign up, the following is displayed: *Your subscription request has been received, and will soon be acted upon. Depending on the configuration of this mailing list, your subscription request may have to be first confirmed by you via email, or approved by the list moderator. If confirmation is required, you will soon get a confirmation email which contains further instructions.* Cheers, Tim
Re: Slony mailing list activity
I’ve just found the list maintainers address, perhaps that goes somewhere. slony1-general-ow...@lists.slony.info On Wed, 19 Jan 2022 at 18:50, Tim Kane wrote: > Apologies for posting here, but.. I have attempted to sign up to the Slony > mailing lists and have been waiting over a week to be approved. > > I’m suspicious that the mailing list functionality there is broken, > perhaps? Or the moderator is MIA. > > When attempting to sign up, the following is displayed: > > *Your subscription request has been received, and will soon be acted upon. > Depending on the configuration of this mailing list, your subscription > request may have to be first confirmed by you via email, or approved by the > list moderator. If confirmation is required, you will soon get a > confirmation email which contains further instructions.* > > > Cheers, > > Tim >
Re: Slony mailing list activity
On Wed, 19 Jan 2022, Tim Kane wrote: I’ve just found the list maintainers address, perhaps that goes somewhere. slony1-general-ow...@lists.slony.info I am overseeing the slony list. Signup requests shouldn't require moderator intervention. I haven't seen any pending requests. Something might be wrong I will investigate and let you know off-list. Steve On Wed, 19 Jan 2022 at 18:50, Tim Kane wrote: Apologies for posting here, but.. I have attempted to sign up to the Slony mailing lists and have been waiting over a week to be approved. I’m suspicious that the mailing list functionality there is broken, perhaps? Or the moderator is MIA. When attempting to sign up, the following is displayed: Your subscription request has been received, and will soon be acted upon. Depending on the configuration of this mailing list, your subscription request may have to be first confirmed by you via email, or approved by the list moderator. If confirmation is required, you will soon get a confirmation email which contains further instructions. Cheers, Tim
Can we get the CTID value
Hi, I am creating a new type and would like to know if it was possible to access the CTID for the row affected by the INPUT and RECEIVE functions of the new type? Actually, would it be possible from the OUTPUT and SEND functions as well? Regards, Garfield
Re: could not accept SSL connection: Success
On Thu, Jan 20, 2022 at 12:06 AM Carla Iriberri wrote: > On Wed, Jan 19, 2022 at 5:42 AM Michael Paquier wrote: >> "On an unexpected EOF, versions before OpenSSL 3.0 returned >> SSL_ERROR_SYSCALL, nothing was added to the error stack, and errno was >> 0. Since OpenSSL 3.0 the returned error is SSL_ERROR_SSL with a >> meaningful error on the error stack." > Thanks, Michael, that's it, indeed! I had missed that part of the > OpenSSL docs. These PG instances are running on Ubuntu Focal hosts that come > with OpenSSL 1.1.1. Good news, I'm glad they nailed that down. I recall that this behaviour was a bit of a moving target in earlier versions: https://www.postgresql.org/message-id/CAEepm%3D3cc5wYv%3DX4Nzy7VOUkdHBiJs9bpLzqtqJWxdDUp5DiPQ%40mail.gmail.com
Re: WAL Archiving and base backup
On Fri, Jan 14, 2022 at 8:27 PM Ron wrote: > On 1/14/22 12:31 PM, Stephen Frost wrote: > > Greetings, > > > > * Issa Gorissen (issa-goris...@usa.net) wrote: > >> Thx a lot. I thought about it but was not so sure about having a complex > >> script (compared to the very simple version when using the exclusive > backup > >> - but this this is deprecated...). > >> > >> I will test your option with the simpler version and post it back to it > can > >> maybe land in PostgreSQL documentation. > > The PG docs show how the command works and that's it. The commands > > in the docs aren't intended to be actually used in production > > environments. Writing a full solution involves having a good > > understanding of the PG code and how WAL archiving, backups, et al, are > > done. If you're not familiar with this portion of the PG code base, I'd > > strongly suggest you look at using solutions written and maintained by > > folks who are. > > Needing to read the PG source code to write a workable PITR recovery > solution is a serious flaw in PG documentation (and why I use PgBackRest). > > The documentation of two other RDBMSs that I've worked with (Rdb/VMS and > SQL > Server) are perfectly clear on how to do such backups and restores with > relatively small amounts of scripting. > So when I was writing my own backup solutions many years ago, I didn't generally read the code to do that. I think the problem is that there is a lot of stuff that goes on around the backup and recovery process where to make it safe you need to understand all the other things going on. I can remember at least one case from those years ago when a needed backup suddenly wasn't PITR-restorable when I needed it to be and that took some urgent troubleshooting. I got it resolved but I also understand why those building,such tools read the code and more importantly understand implications of design choices in that context. Backups are critical pieces of infrastructure and one wants to make sure that weird corner cases don't suddenly render your backup useless when your production system dies. And while I do think the docs could be improved, I agree they will probably never be good enough for people to just roll their own solutions. > > > Trying to write documentation on how to develop a complete solution > > would be quite an effort and would certainly go beyond bash scripting > > and likely wouldn't end up getting used anyway- those who are developing > > such solutions are already reading through the actual code. > > > Thanks, > > > > Stephen > > -- > Angular momentum makes the world go 'round. > > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: could not accept SSL connection: Success
On Thu, Jan 20, 2022 at 09:05:35AM +1300, Thomas Munro wrote:
> Good news, I'm glad they nailed that down. I recall that this
> behaviour was a bit of a moving target in earlier versions:
>
> https://www.postgresql.org/message-id/CAEepm%3D3cc5wYv%3DX4Nzy7VOUkdHBiJs9bpLzqtqJWxdDUp5DiPQ%40mail.gmail.com
Ahh.. So you saw the same problem a couple of years back. This
thread did not catch much attention.
I don't think that it makes much sense to leave this unchecked as the
message is confusing as it stands. Perhaps we could do something like
the attached by adding a note about OpenSSL 3.0 to revisit this code
once we unplug support for 1.1.1 and avoiding the errno==0 case? The
frontend has its own ideas of socket failures as it requires thread
support, making things different with the backend, but it seems to me
that we could see cases where SOCK_ERRNO is also 0. That's mostly
what you suggested on the other thread.
The error handling changes are really cosmetic, so I'd rather leave
the back-branches out of that. Thoughts?
--
Michael
diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c
index 3d0168a369..12ddc6f82f 100644
--- a/src/backend/libpq/be-secure-openssl.c
+++ b/src/backend/libpq/be-secure-openssl.c
@@ -493,7 +493,13 @@ aloop:
WAIT_EVENT_SSL_OPEN_SERVER);
goto aloop;
case SSL_ERROR_SYSCALL:
-if (r < 0)
+/*
+ * OpenSSL 1.1.1 and older versions return nothing on
+ * an unexpected EOF, and errno may not be set. Note that
+ * in OpenSSL 3.0, an unexpected EOF would result in
+ * SSL_ERROR_SSL with a meaningful error set on the stack.
+ */
+if (r < 0 && errno != 0)
ereport(COMMERROR,
(errcode_for_socket_access(),
errmsg("could not accept SSL connection: %m")));
diff --git a/src/interfaces/libpq/fe-secure-openssl.c b/src/interfaces/libpq/fe-secure-openssl.c
index 9f735ba437..b47ea87b43 100644
--- a/src/interfaces/libpq/fe-secure-openssl.c
+++ b/src/interfaces/libpq/fe-secure-openssl.c
@@ -314,6 +314,12 @@ pgtls_write(PGconn *conn, const void *ptr, size_t len)
n = 0;
break;
case SSL_ERROR_SYSCALL:
+ /*
+ * OpenSSL 1.1.1 and older versions return nothing on
+ * an unexpected EOF, and errno may not be set. Note that
+ * in OpenSSL 3.0, an unexpected EOF would result in
+ * SSL_ERROR_SSL with a meaningful error set on the stack.
+ */
if (n < 0)
{
result_errno = SOCK_ERRNO;
@@ -322,11 +328,14 @@ pgtls_write(PGconn *conn, const void *ptr, size_t len)
libpq_gettext("server closed the connection unexpectedly\n"
"\tThis probably means the server terminated abnormally\n"
"\tbefore or while processing the request.\n"));
-else
+else if (result_errno != 0)
appendPQExpBuffer(&conn->errorMessage,
libpq_gettext("SSL SYSCALL error: %s\n"),
SOCK_STRERROR(result_errno,
sebuf, sizeof(sebuf)));
+else
+ appendPQExpBuffer(&conn->errorMessage,
+ libpq_gettext("SSL SYSCALL error: internal failure\n"));
}
else
{
signature.asc
Description: PGP signature
Re: could not accept SSL connection: Success
Michael Paquier writes: > I don't think that it makes much sense to leave this unchecked as the > message is confusing as it stands. Perhaps we could do something like > the attached by adding a note about OpenSSL 3.0 to revisit this code > once we unplug support for 1.1.1 and avoiding the errno==0 case? If I'm reading this patch correctly, you have it calling the case "EOF detected" in one place, "internal failure" in another, and failing to touch several more places where we deal with SSL_ERROR_SYSCALL. I don't find that to be an improvement --- inconsistency is worse than a confusing error message. Personally I'm satisfied to leave it as-is, since this issue apparently occurs only in a minority of OpenSSL versions, and not the newest. regards, tom lane
Re: could not accept SSL connection: Success
On Wed, Jan 19, 2022 at 07:58:43PM -0500, Tom Lane wrote: > Personally I'm satisfied to leave it as-is, since this issue apparently > occurs only in a minority of OpenSSL versions, and not the newest. Leaving things in their current state is fine by me. Would it be better to add a note about the business with 3.0 though? My gut is telling me that we'd better revisit those code paths in a couple of years when support for legacy OpenSSL is removed, and most likely we would have forgotten about all those details. -- Michael signature.asc Description: PGP signature
Re: could not accept SSL connection: Success
Michael Paquier writes: > Leaving things in their current state is fine by me. Would it be > better to add a note about the business with 3.0 though? What do you envision saying? "We don't need to do anything here for 3.0" doesn't seem helpful. regards, tom lane
Re: could not accept SSL connection: Success
On Wed, Jan 19, 2022 at 08:06:30PM -0500, Tom Lane wrote: > Michael Paquier writes: > > Leaving things in their current state is fine by me. Would it be > > better to add a note about the business with 3.0 though? > > What do you envision saying? "We don't need to do anything here > for 3.0" doesn't seem helpful. Nope, but the idea would be to keep around a note that we may want to revisit this area of the code based on the state of upstream, because our code is currently shaped based on problems that OpenSSL has dealt with. I am not completely sure, but something among the line of: "OpenSSL 1.1.1 and older versions return nothing on an unexpected EOF, and errno may not be set. 3.0 reports SSL_ERROR_SSL with a meaningful error set on the stack, so this could be reworked once support for older versions is removed." Perhaps that's just nannyism from my side, this is really minor at the end. -- Michael signature.asc Description: PGP signature
