Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

Hi David, On Mon, Jul 10, 2017 at 10:02 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> >> However there is a problem: I can not use a "single-instance" cronjob to >> run words_expire_games

Re: [GENERAL] Changing collate & ctype for an existing database

On 07/11/2017 12:45 AM, Tom Lane wrote: rihad writes: On 07/10/2017 11:07 PM, Tom Lane wrote: ... which that isn't. I'd suggest checking for indexes that might need to be rebuilt with this query borrowed from the regression tests: I ran the query on our production database.

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Greetings, * mariusz (mar...@mtvk.pl) wrote: > On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost wrote: > > > How is this done inside a shell script? > > > > Generally, it's not. I suppose it might be possible to use '\!' with > > psql and then have a shell snippet that looks for some file

[GENERAL] Registering aggregate function for a custom domain

Hi all, I'm used to define data domains and use them to create my tables, something like CREATE DOMAIN id_t uuid; CREATE DOMAIN text_t text; CREATE mytable (id id_t, value text_t, PRIMARY KEY (id)); The only "annoyance" is that I cannot use some standard function: SELECT array_agg(id)

Re: [GENERAL] BDR node removal and rejoin

An update... after manually removing the record for 'node4' from bdr.bdr_nodes, corresponding record in bdr.bdr_connections, and associated replication slot (with pg_drop_replication_slot), rejoining was successful. I was under the impression that there is no need to perform manual cleanup

Re: [GENERAL] debugging SSL connection problems

On Mon, Jul 10, 2017 at 11:19 PM, Jeff Janes wrote: > > Is there a way to get libpq to hand over the certificate it gets from the > server, so I can inspect it with other tools that give better diagnostic > messages? I've tried to scrape it out of the output of "strace

Re: [GENERAL] Imperative Query Languages

On 5 July 2017 at 01:22, Jason Dusek wrote: > Hi All, > > This more of a general interest than specifically Postgres question. Are > there any “semi-imperative” query languages that have been tried in the > past? I’m imagining a language where something like this: > > for

[GENERAL] debugging SSL connection problems

Is there a way to get libpq to hand over the certificate it gets from the server, so I can inspect it with other tools that give better diagnostic messages? I've tried to scrape it out of the output of "strace -s8192", but since it is binary it is difficult to figure out where it begins and ends

Re: [GENERAL] Changing collate & ctype for an existing database

rihad writes: > On 07/10/2017 11:07 PM, Tom Lane wrote: >> ... which that isn't. I'd suggest checking for indexes that might need >> to be rebuilt with this query borrowed from the regression tests: > I ran the query on our production database. Zero results. Really? You have no

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > > However there is a problem: I can not use a "single-instance" cronjob to > run words_expire_games hourly. > ​Setup a cron job that invokes the servlet - probably via "curl" My question is if I should

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

"FOR UPDATE" is part of "SELECT" not part of "UPDATE". You can select the rows "for update" which will lock those rows. You can then loop over the the results of the 'select' to do the rest of your logic. Be careful doing this if other things are also updating these rows. With SKIP LOCKED you

Re: [GENERAL] Changing collate & ctype for an existing database

On 07/10/2017 11:07 PM, Tom Lane wrote: rihad writes: On 07/10/2017 08:42 PM, Tom Lane wrote: No, your indexes on text/char/varchar columns will be corrupted (because their sort order will now be wrong). If you can reindex them before doing anything more with the database,

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

I have tried: FOR _gid, _loser, _winner IN UPDATE words_games SET finished = CURRENT_TIMESTAMP WHERE finished IS NULL AND played1 IS NOT NULL AND played2 IS NOT NULL AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'

Re: [GENERAL] Concurrency and UPDATE [...] FROM

On Tue, Jul 11, 2017 at 6:23 AM, Seamus Abshere wrote: > The purpose is to concat new data onto existing values of c: > > UPDATE tbl > SET c = c || new_data.c > FROM ( [...] ) AS new_data > WHERE > tbl.id = new_data.id > > It appears to have a race condition: > > t0: Query A

Re: [GENERAL] Changing collate & ctype for an existing database

rihad writes: > On 07/10/2017 08:42 PM, Tom Lane wrote: >> No, your indexes on text/char/varchar columns will be corrupted >> (because their sort order will now be wrong). If you can reindex >> them before doing anything more with the database, you'd be ok >> ... I think. Testing

[GENERAL] Concurrency and UPDATE [...] FROM

The purpose is to concat new data onto existing values of c: UPDATE tbl SET c = c || new_data.c FROM ( [...] ) AS new_data WHERE tbl.id = new_data.id It appears to have a race condition: t0: Query A starts subquery t1: Query A starts self-join t2. Query A starts UPDATE with data from

Re: [GENERAL] Changing collate & ctype for an existing database

On 07/10/2017 08:42 PM, Tom Lane wrote: rihad writes: Hi there. We have a working database that was unfortunately created by initdb with default ("C") collation & ctype. All other locale specific settings have the value en_US.UTF-8 in postgresql.conf. The database itself is

Re: [GENERAL] CREATE AGGREGATE on jsonb concat

Seamus Abshere writes: > That aggregates into an array. Our `jsonb_collect` aggregates into an object. Postgres 9.6 has (per https://www.postgresql.org/docs/9.6/static/functions-aggregate.html): * jsonb_agg(expression) * jsonb_object_agg(name, value) In retrospect, I think

Re: [GENERAL] Changing collate & ctype for an existing database

rihad writes: > Hi there. We have a working database that was unfortunately created by > initdb with default ("C") collation & ctype. All other locale specific > settings have the value en_US.UTF-8 in postgresql.conf. The database > itself is multilingual and all its data is

[GENERAL] Changing collate & ctype for an existing database

Hi there. We have a working database that was unfortunately created by initdb with default ("C") collation & ctype. All other locale specific settings have the value en_US.UTF-8 in postgresql.conf. The database itself is multilingual and all its data is stored in UTF-8. Sorting doesn't work

[GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

Good afternoon, in PostgreSQL 9.5 with pgbouncer (having "pool_mode = session" and "server_reset_query = DISCARD ALL") 2-player games are stored in the following table: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz,

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost wrote: > Greetings, > > * hvjunk (hvj...@gmail.com) wrote: > > I’ve previously done ZFS snapshot backups like this: > > > > psql -c “select pg_start_backup(‘snapshot’);” > > zfs snapshot TANK/postgresql@`date ‘+%Ymd’` > > psql -c “select * from