Can spawned Postgresql process be controlled by the spawner PHP script?

Question

I have a PHP CLI script, that processes a csv file, inserting it's content to a table in Postgresql database. This is on an Ubuntu server. I use schedtool to control the affinity of the whole script. Schedtool is used to launch the script itself with the -e option. Unfortunately with htop I see that the database thread is spawned as a completely different thread and being not affected by the affinity options.

Is it possible to somehow make the database process inherit all affinity options from the spawner PHP script?

---

I have just found the answer -as I was reading some Postgresql example files for Ruby :).

My best choice would be to get the very exact PID of the Postgresql thread that is running the current script, with NOTIFY - LISTEN. As [depesz] told Postgres uses only 1 core. With schedtool I still can control on which core it runs and with cpulimit I can control it further.

Answer 1

No.

Postgres backend is always spawned from postmaster process, so there is no way to pass affinity (whatever that would be) options from php script to postgres backend.

On the other hand - if you'd describe what affinity is, and what kind of options you want to setup/pass, maybe there is a solution - the fact that there is no general solution, doesn't mean that there isn't specialized solution for some things.

Answer 2

Postgres doesn't use threads. Each connection is handled by a single threaded process. So, a single connection can only use one core. Though the OS may move it to different cores, it will only use one at a time.

If you insist on nicing postgres, "SELECT pg_backend_pid()" will give you the PID of the postgres process without any NOTIFY/LISTEN stuff. Though, in general, you don't want to try to nice a postgres process. You may cause other higher priority processes to block. Then what you thought was a low priority process isn't so low-priority any more.

If you're just doing a bunch of inserts from a csv, nicing your script probably won't help much. Postgres is probably doing more of the work, and it's probably IO, so again, nicing may not get you much. You could, however, sleep every once in a while. Insert for 50 milliseconds, sleep for a while, ...