Re: [GENERAL] archive_command fails but works outside of Postgres

Alvaro Herrera-9 wrote > I saw one installation with "gsutil cp" in archive_command recently. > Ithad the CLOUDSDK_PYTHON environment variable set in the > archive_commanditself. Maybe that's a problem. After all, this was the solution: archive_command = 'CLOUDSDK_PYTHON=/usr/bin/python gsutil c

Re: [GENERAL] archive_command fails but works outside of Postgres

Mark Watson-12 wrote > I think the parameter %p contains the complete path of the file It does not, see the link to the official documentation above. -- View this message in context: http://www.postgresql-archive.org/archive-command-fails-but-works-outside-of-Postgres-tp5979040p5979061.html Se

Re: [GENERAL] archive_command fails but works outside of Postgres

Alvaro Herrera-9 wrote > I saw one installation with "gsutil cp" in archive_command recently. > Ithad the CLOUDSDK_PYTHON environment variable set in the > archive_commanditself. Maybe that's a problem. That's not the case here, I don't have this variable set anywhere where gsutil works. Alvaro

Re: [GENERAL] archive_command fails but works outside of Postgres

Scott Marlowe-2 wrote > Sounds like it depends on some envvar it doesn't see when run from the > postmaster. If you sudo -u postgres and run it does it work? Yes, I can do su postgres, execute the command and it works. -- View this message in context: http://www.postgresql-archive.org/archive

[GENERAL] archive_command fails but works outside of Postgres

I changed my archive_command to the following: archive_command = 'gsutil cp /storage/postgresql/9.6/main/%p gs://my_bucket/pg_xlog/' and it fails, leaving the following in the log: 2017-08-18 18:34:25.057 GMT [1436][0]: [104319] LOG: archive command failed with exit code 12017-08-18 18:34:25.057

Re: [GENERAL] Text search dictionary vs. the C locale

Tom Lane-2 wrote > Presumably the problem is that the dictionary file parsing functionsreject > anything that doesn't satisfy t_isalpha() (unless it matchest_isspace()) > and in C locale that's not going to accept very much. That's what I also guessed and the fact that setting lc-ctype=en_US.UTF-8

Re: [GENERAL] Text search dictionary vs. the C locale

Initializing the cluster with initdb   --locale=C   --lc-ctype=en_US.UTF-8   --lc-messages=en_US.UTF-8   --lc-monetary=en_US.UTF-8   --lc-numeric=en_US.UTF-8   --lc-time=en_US.UTF-8   --encoding=UTF8 allows me to use my text search dictionary. Now it only remains to see whether index creation wi

[GENERAL] Text search dictionary vs. the C locale

I am having problems creating an Ispell-based text search dictionary for Czech language. Issuing the following command: create text search dictionary czech_ispell (   template = ispell,   dictfile = czech_ispell,   affFile = czech_ispell ); ends with ERROR: syntax error CONTEXT: line 25

Re: [GENERAL] 'pg_ctl restart' does not terminate

That makes perfect sense. Thank you for a great help, Adrian! -- View this message in context: http://postgresql.nabble.com/pg-ctl-restart-does-not-terminate-tp5932070p5932095.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list

Re: [GENERAL] 'pg_ctl restart' does not terminate

Ah, it didn't occur to me to try hitting ENTER. Still, this would be fine for manually running the script, but as I am restarting the server as a part of SaltStack config, I need pg_ctl to terminate without me intervening. The solution with the -l argument is fine, I think. Even if I use it, the s

Re: [GENERAL] 'pg_ctl restart' does not terminate

Yes, I am using that, thank you. But just by themselves these settings do not make pg_ctl terminate. -- View this message in context: http://postgresql.nabble.com/pg-ctl-restart-does-not-terminate-tp5932070p5932090.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] 'pg_ctl restart' does not terminate

Adrian Klaver-4 wrote > You also specify a log file to pg_ctl by using -l: > > https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html This did the trick, thank you! -- View this message in context: http://postgresql.nabble.com/pg-ctl-restart-does-not-terminate-tp5932070p5932076.html Sen

[GENERAL] 'pg_ctl restart' does not terminate

I am restarting the server using the following: su postgres -c "/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main -o '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' restart" The server is restarted properly, but the the command never finishes. After the restart, it displa

Re: [GENERAL] SELECT blocks UPDATE

Further observation: Now I managed to get rid of the blocking. I am not sure if you are familiar with the NHibernate ORM, but it has a concept of a stateful and stateless sessions. Session holds a connection to the database and transaction is created on a particular session. In this case, 'begin tr

Re: [GENERAL] SELECT blocks UPDATE

The Postgres version is 9.3.9. The actual output of the lock query is (I added *locktype* and *mode* columns from the *pg_locks* table) *blocked_pid*: 7574 *blocked_statement*: UPDATE "TRANSLATION" SET fk_assignment_queue_item = 1009184 WHERE id IN (47049861) *blocked_locktype*: transactionid *b

[GENERAL] SELECT blocks UPDATE

Hello, if I am reading the documentation on explicit locking correctly, SELECT should never conflict with UPDATE. However, what I am observing as a result of this monitoring query: SELECT bl.pid AS bloc

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

In fact I did not partition by *fk_job* but by the *id* (primary key) instead thoughpartitioning by *fk_job* was my first idea. I use various columns when querying the table, *fk_job* is not always there. -- View this message in context: http://postgresql.nabble.com/The-fastest-way-to-update-

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

Thank you, I will look into those suggestions. Meanwhile, I started experimenting with partitioning the table into smaller tables, each holding rows with ID spanning 1 million values and using this approach, the UPDATE takes 300ms. I have to check if all the SELECTs I am issuing against the origi

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

林士博 wrote > Can you post execution plan of the original update sql.EXPLAIN (ANALYZE > ON, BUFFERS ON) update "TRANSLATION" setfk_assignmentwhere fk_job = 1000; Here it is: Update on "TRANSLATION" (cost=0.56..9645.13 rows=3113 width=391) (actual time=35091.036..35091.036 rows=0 loops=1)    Buffe

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

林士博 wrote > Try creating an index on TRANSLATION fk_job. The index is already there. -- View this message in context: http://postgresql.nabble.com/The-fastest-way-to-update-thousands-of-rows-in-moderately-sized-table-tp5859144p5859191.html Sent from the PostgreSQL - general mailing list archiv

[GENERAL] Re: The fastest way to update thousands of rows in moderately sized table

Adrian Klaver-4 wrote > Have you tried wrapping the above in a BEGIN/COMMIT block? Yes, I am running the tests inside a BEGIN TRANSACTION / ROLLBACK block. -- View this message in context: http://postgresql.nabble.com/The-fastest-way-to-update-thousands-of-rows-in-moderately-sized-table-tp5859

[GENERAL] The fastest way to update thousands of rows in moderately sized table

Hello,I have a table with 30 million records in which I need to update a single column for a couple of thousands of rows, let's say 10 000. The new column value is identical for all matching rows.Doing update "TRANSLATION" set fk_assignmentwhere fk_job = 1000; takes 45 seconds. I understand that

[GENERAL] Re: Server tries to read a different config file than it is supposed to

> I think that that unwritable postgresql.conf file had probably been > hanging around in your data directory for some time. It was not causing > any particular problem until we decided we ought to fsync everything in > the data directory after a crash. So this is indeed the same case > Christop

[GENERAL] Re: Server tries to read a different config file than it is supposed to

> From root, presumably ... Yes > I thought of a different theory: maybe the server's complaint is not due > to trying to read that file as a config file, but it's just because there > is an unreadable/unwritable file in the data directory. See Christoph > Berg's complaint at > http://www.post

[GENERAL] Re: Server tries to read a different config file than it is supposed to

> Testing this, the problem appears to be that you forgot the > keyword"start", so pg_ctl didn't really do anything. I am sorry, that was just a mistake on my part here, it is in the script. > I suspect this was left over from some previous attempt. It doesn't look like it. I tried several times

[GENERAL] Server tries to read a different config file than it is supposed to

I thought I understood how specifying a config file path for the server works, but that's apparently not the case. The cluster data is at */storage/postgresql/9.4/data*. The config files are at */etc/postgresql/9.4/main* (this is the default location on Ubuntu). This is how the beginning of */etc/p

Re: [GENERAL] Emulating flexible regex replace

Thank you Francisco. In fact, I am already solving part of the problem in my application - fetching from the DB the records matching the source pattern and then filtering them in the application's memory by matching against the target pattern, with the references replaced (it's a breeze in C#). I

Re: [GENERAL] Emulating flexible regex replace

Thank you Francisco, that's a clever idea. However, I don't think this would reduce the complexity since the target pattern can contain 1) regular back-references (referencing to matches of its own) 2) the special source text references I mentioned Obviously, these will have to be written in a di

[GENERAL] Emulating flexible regex replace

Hello, my scenario is this: I have a *SEGMENT* table with two text fields, *source* and *target*. From the user, I get the following input: /source pattern/ /target pattern/ Where both patterns are regexes and moreover the target pattern contains references to the source in the following way: