Comments (7)
I looked into issue regarding the inaccurate error messages.
In September of last year, a commit was made to worker.c that incorporated the version number into the return message, i.e. the message went from "pg_net worker" to "pg_net VERSION worker". The net.check_worker_is_up function was not updated to reflect this change.
This issue can be resolved in 3 ways:
- Remove the version number from the worker.c return message
- Hard code the return number into the net.check_worker_is_up function
- Change the comparison in the net.check_worker_is_up function to be general purpose for all new versions.
I prefer the third option because it would prevent this type of issue in the future. I took the liberty of rewriting the offending line:
Old Comparison:
... where backend_type = 'pg_net worker') ...
New Comparison:
... where backend_type ~ 'pg_net \d+(\.\d+){0,2} worker')
As for why your requests are idling, I'm not sure. That's a more complex issue that is not something I can resolve, at least not quickly.
I suspect your specific issue may have to do with your Docker configuration and its permission to access the internet. It might be helpful to monitor network traffic in your container using a terminal tool, such as nload, to see if the extension is making any requests.
from pg_net.
Amazing! That certainly looks like the information required to get this working.
I made a quick attempt to use ALTER SYSTEM
to modify the config but the postgres user in the image does not have sufficient permissions for this purpose.
However, adding pg_net.database_name=<"Your_new_database">
to the postgresql.conf
file does work - and pg_net
operates correctly once this is set.
This is the solution :) Really appreciate the time you've spent assisting me.
To summarize:
When using pg_net
with a database named other than postgres
- the variable pg_net.database_name
must be set to the name of the database the worker should attach to. This may be able to be done at runtime - however, is best achieved via postgresql.conf
.
NB. This suggests that only one instance of pg_net
can be enabled per postgres instance.
Thanks again :) B
from pg_net.
Thanks @TheOtherBrian1
I should have thought the timeout would still function in the case that network conditions/config were the issue here.
At the least, some sort of error messaging 🤔
I have configured the docker container to have internet access (as far as I can tell - i.e. I can curl things from a bash command line within the docker container)
Unsure where to turn now :/
Thanks again
from pg_net.
I spent some time looking through the extensions code, and it seems that many of the errors are directly written to the PostgreSQL log file. On your set up, it may be located in the var/log/postgresql directory. You should try viewing it if you haven't. If does show the cause of the error, I would be very curious to know what it is.
from pg_net.
Thanks for looking into this. You've pointed me in a few directions that really helped me today.
It appears as though the devil is in the detail here:
In my repro instructions I put 'create a database' - this is the key issue.
pg_net
does not work (or is not configured to work) on any database other than the postgres
database that is present by default in this docker image.
When I install the extension and add a request in the existing postgres database - I have no issues with pg_net
- (other than the previously noted bug in check_worker_is_up()
)
Curiously though - there are no logs where you indicated.
Maintainers: The documentation should either:
- Note that
pg_net
only works with the specific config present in the existing 'postgres' database (and why), or, - Give instructions on how to configure it for an arbitrary database.
from pg_net.
Wow, you are right, it is by default setup only to work on the postgres database, as coded in Line 41 in worker.c
char* database_name = "postgres";
Unfortunately, there were no hints in the documentation alluding to extension configuration. The documentation most certainly should be updated.
In the _PG_Init function in worker.c, there is the following code:
DefineCustomStringVariable(
"pg_net.database_name",
"database where the pg_net worker is connected",
NULL,
&database_name,
"postgres",
PGC_SIGHUP, 0,
NULL, NULL, NULL);
}
DefineCustomStringVariable
comes from the GUC.c library in PostgreSQL, which stands for Grand Unified Configuration. This code makes it possible to set the database_name variable in the postgres.conf file.
Looking further into the code, it seems that the extension creates 3 configurable variables:
- pg_net.batch_size (default: 200): An integer that limits the max number of rows that the extension will process from net.http_request_queue during each read
- pg_net.ttl (default: 6 hours): An interval that defines the max time a row in the _net.http_response will live before being deleted
- pg_net.database_name (default: 'postgres'): A string that defines which database the extension is applied to
All these variables can be viewed with the following command:
select * from pg_settings WHERE name LIKE 'pg_net%'
The postgres.conf, file can be found with the following SQL command:
SHOW config_file;
I'm not too familiar with modifying the postgres.conf, so I Google searched for some hints on how to handle it properly.
I found a supabase post called Protecting reserved roles with PostgreSQL Hooks that included a section about modifying configuration variables for a "supautils.c" extension:
postgresql.conf
# set the extension as preloaded, this will require a restart
shared_preload_libraries="supautils"
# the reserved roles
supautils.reserved_roles="supabase_storage_admin, supabase_auth_admin"
Extrapolating from that example, my guess is that you could modify your postgresql.conf file with the following line(s) to get pg_net to work:
#The below line may be necessary
#shared_preload_libraries="pg_net"
pg_net.database_name=<"Your_new_database">
You may also be able to change the variable in SQL by using an ALTER SYSTEM
query, as outlined by this stackoverflow post
After saving the file, you can execute select pg_reload_conf()
to update postgres.conf for your database. If the extension does not respond to the update, it may be necessary to restart your entire server.
If this solves your issue, I'll add some configuration guidelines to the readme.md for future readers.
from pg_net.
Thanks all, and sorry for the confusion! I'm updating the README here to clarify on this.
from pg_net.
Related Issues (20)
- pg_net prevents `DROP database foo` from succeeding
- Can't send HTTP requests to localhost from a trigger function HOT 16
- `net._http_response` rows do not respect TTL
- Failed to toggle PG_NET: failed to create pg.extensions: function net.http_collect_response HOT 8
- Cannot enable pg_net extension HOT 1
- pg_net not working in self-hosted supabase HOT 7
- pg_net doesn't support 308 redirects HOT 1
- Test Failures due to Timeout HOT 5
- cleanup of net._http_response table HOT 11
- i can not compile pg_net on centos HOT 3
- Multiple versions of nixpkgs HOT 1
- Add retries HOT 6
- ERROR: extension "pg_net" has no installation script nor update path for version "0.7.3" HOT 2
- "SSL connect error", "Error in the HTTP2 framing layer" while using edge functions HOT 3
- Show libcurl version
- Fail to compile with the latest libcurl 7.76.1
- Error with net.http_post : query has no destination for result data HOT 1
- UNLOGGED table segfault causing whole cluster to restart HOT 1
- Order is not guaranteed on rapidly generated requests to same end point.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pg_net.