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

[Alexander Farber]

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 hourly.
>>
>
> ​Setup a cron job that invokes the servlet - probably via "curl"
>

I will go with curl, thanks for that and the other comments

Regards
Alex

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

[rihad]

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. Zero results.

Really?  You have no indexes on textual columns?  That seems surprising.
Oops, of course we do, around 10-15 per db. I was initially connected to 
the postgres database when I ran the query, I thought the query you gave 
me was global by looking at it.


So, deciding NOT to reindex all of them risks the corruption of their 
relevant tables?
It could be easier to simply drop and restore the db, albeit with some 
downtime.

Thank you so much for you help.


Do I have the green light to
set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'

Well, I'd double-check that result, but I suppose you can always reindex
later if you find you missed something.

regards, tom lane






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Stephen Frost]

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 that's
> > touched when the snapshot has finished, but really, trying to perform a
> > PG backup using hacked together shell scripts isn't recommended and
> > tends to have problems.
> 
> not saying to use scripts or pg tools, but if OP needs a script, may
> consider perl module IPC::Run instead of shell script - easy to use
> interacting with subprocesses over filehandles or pipes (even supports
> ptys). that way you can keep your active psql session, pass input to it,
> view and process it's output, while doesn't block you from doing other
> things meanwhile
> 
> of course it's perl, not shell, but looks "scripty" and can do what OP
> wants 

Sure, that's more-or-less what pgBackRest does (which is Perl, mostly).

Thanks!

Stephen


signature.asc
Description: Digital signature

[GENERAL] Registering aggregate function for a custom domain

[Lele Gaifax]

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) FROM mytable;
  could not find array type for data type id_t

and I must cast the value to the underlying concrete data type, for example:

  SELECT array_agg(id::uuid) FROM mytable;
  {----,c72ca134-655b-11e7...

Is there a way to smooth my issue, registering a "compatible" array_agg() that
accepts the domain too?

Thanks in advance,
ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR node removal and rejoin

[Zhu, Joshua]

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 
before a removed node (with database dropped and recreated) rejoining a BDR 
group.

From: Zhu, Joshua
Sent: Friday, July 07, 2017 2:59 PM
To: 'pgsql-general@postgresql.org' 
Subject: BDR node removal and rejoin

Hi, I am having difficulty removing a node from a BDR group (with nodes node1 
through node5) then rejoin the group.

Prior to removing a node, the BDR is running fine, query on bdr.bdr_nodes table 
shows all nodes having the status 'r'.

Here is what I have done for removing node5 and rejoining:


  *   On node1, do bdr.bdr_part_by_node_names

At this point the status of node5 in bdr.bdr_nodes becomes 'k'


  *   On node5, do bdr.remove_bdr_from_local_node
  *   On node5, drop and recreate the database, then rejoin using 
bdr.bdr_group_join (using the same node name and external dsn)

At this point the status of node5 on node1 though node4 still remains 'k', and 
the status of node5 on node5 (there is only one record) is 'i', and they stuck 
at these status codes.
[note: I tried using a different node name on rejoining, same result]

What have I done wrong, what is the correct way of doing removal and rejoining?

Thanks

Re: [GENERAL] debugging SSL connection problems

[Magnus Hagander]

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 -s8192",
> but since it is binary it is difficult to figure out where it begins and
> ends within the larger server response method.
>
>
PQgetssl() or PQsslStruct() should give you the required struct from
OpenSSL, which you can then use OpenSSL to inspect. You should be able to
use (I think) SSL_get_peer_certificate() to get at it.

(this is what libpq does and stores it in ->peer, but that's a private api.
But you can see be-secure-openssl.c for some examples)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [GENERAL] Imperative Query Languages

[Christopher Browne]

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 employee in employees:
> for department in department:
> if employee.department == department.department and
>department.name == "infosec":
> yield employee.employee, employee.name, employee.location,
> employee.favorite_drink
>
> would be planned and executed like this:
>
> SELECT employee.employee, employee.name, employee.location,
> employee.favorite_drink
>   FROM employee JOIN department USING (department)
>  WHERE department.name == "infosec"
>
> The only language I can think of that is vaguely like this is Fortress, in
> that it attempts to emulate pseudocode and Fortran very closely while being
> fundamentally a dataflow language.

It's probably of broader interest to consider some sort of "more relational"
language that would, in effect, be "more declarative" as opposed to
"more imperative" than SQL.  (I'd not be keen on heading back to
CODASYL!!!)

The notable example of such would be the "Tutorial D" language
attributable to Darwen and Date's "Third Manifesto"

https://en.wikipedia.org/wiki/D_(data_language_specification)
http://wiki.c2.com/?TutorialDee

Unfortunately, the attempts to construct implementations of D
have all pretty much remained at the "toy" point, experiments
that few beyond the implementors seem to treat as realistic
SQL successors.

Another option, in principle, would be to consider QUEL, which
was what Stonebraker used initially as the query languages for
Ingres and Postgres.

https://en.wikipedia.org/wiki/QUEL_query_languages

None of these options seem to be dominantly better than SQL,
and for something to supplant SQL, it would need to be a
fair bit better.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] debugging SSL connection problems

[Jeff Janes]

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 within the larger server response method.

Thanks,

Jeff

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

[Tom Lane]

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 indexes on textual columns?  That seems surprising.

> Do I have the green light to
> set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'

Well, I'd double-check that result, but I suppose you can always reindex
later if you find you missed something.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 ensure that only 1 servlet runs the custom
> PL/pgSQL function by using "synchronized" in Java as I do it right now:
>

​Probably not.  UPDATE takes out a lock that will prevent other updates
from acting on the same records concurrently.​


> Or if maybe there is some condition (maybe "UPDATE SKIP LOCKED"?) I could
> add to my custom function copy-pasted below? -
>

Why are you trying random syntax that isn't documented?

https://www.postgresql.org/docs/9.6/static/sql-update.html​

UPDATE  words_games
> SET finished = CURRENT_TIMESTAMP
> WHERE   finished IS NULL
>

​That should be sufficient.  Do you have any examples that show it is not?

In short, one of the main reasons for "UPDATE RETURNING" is so that one
needn't determine the records to be updated separately from the actual act
of updating.  Instead you update first and then capture the results for
subsequent use.

David J.

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

[Brian Dunavant]

"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 can skip over rows that should have been selected
but were not because another process was updating data that was
unrelated.   Without SKIP LOCKED you risk deadlock if you are
selecting multiple rows.



On Mon, Jul 10, 2017 at 3:22 PM, Alexander Farber
 wrote:
> 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'
> OR   played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
> RETURNING
> gid,
> CASE WHEN played1 < played2 THEN player1 ELSE player2 END,
> CASE WHEN played1 < played2 THEN player2 ELSE player1 END
> FOR UPDATE SKIP LOCKED
>   LOOP
> ...
>   END LOOP;
>
> but this fails with:
>
> ERROR:  syntax error at or near "FOR"
>
> I have also described my problem at SO:
>
>
> https://stackoverflow.com/questions/45015368/how-to-handle-simultaneous-for-in-update-returning-loops
>
> Thank you
> Alex


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[rihad]

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, you'd be ok
... I think.  Testing on a scratch copy of the database would be
a good idea, if this is valuable data.

Thank you, Tom. But can I still do it for the template1 database?
update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname='template1';

It'd be safe to do it on template0, and also on template1 as long as that
has only the original contents ...


It's empty, only hosting a few extensions.

... which that isn't.  I'd suggest checking for indexes that might need
to be rebuilt with this query borrowed from the regression tests:

SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
FROM (SELECT indexrelid, indrelid,
  unnest(indclass) as iclass, unnest(indcollation) as icoll
   FROM pg_index) ss
WHERE icoll != 0 AND iclass !=
 (SELECT oid FROM pg_opclass
  WHERE opcname = 'text_pattern_ops' AND opcmethod =
(SELECT oid FROM pg_am WHERE amname = 'btree'));

I ran the query on our production database. Zero results. Do I have the 
green light to


set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'

for all our working databases? :) Or for template0 & template1 only?




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alexander Farber]

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'
OR   played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
RETURNING
gid,
CASE WHEN played1 < played2 THEN player1 ELSE player2 END,
CASE WHEN played1 < played2 THEN player2 ELSE player1 END
FOR UPDATE SKIP LOCKED
  LOOP
...
  END LOOP;

but this fails with:

ERROR:  syntax error at or near "FOR"

I have also described my problem at SO:


https://stackoverflow.com/questions/45015368/how-to-handle-simultaneous-for-in-update-returning-loops

Thank you
Alex

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

[Thomas Munro]

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 starts subquery
> t1: Query A starts self-join
> t2. Query A starts UPDATE with data from self-join and subquery
> t3. Query B starts subquery
> t4. Query B starts self-join (note: data from t1!)
> [...]
> tN. Query A finishes UPDATE
> tN+1. Query B finishes UPDATE, missing any new_data from Query A
>
> My assumption is that t1 and t4 (the self-joins) use SELECT but not
> SELECT FOR UPDATE. If they did, I think the race condition would go
> away.
>
> Did I analyze that right?

Yeah, I think so.  There is no EvalPlanQual[1] behaviour on "new_data"
meaning that you can finish up self-joining versions of "tbl" from two
different times.  SELECT FOR UPDATE in "new_data" (as a subselect or
CTE etc) would activate that, or you could use SERIALIZABLE isolation
to abort transactions where the race would change the outcome, or some
other serialisation scheme like table or advisory locks.

[1] 
https://github.com/postgres/postgres/blob/master/src/backend/executor/README#L297

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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 on a scratch copy of the database would be
>> a good idea, if this is valuable data.

> Thank you, Tom. But can I still do it for the template1 database?

> update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
> where datname='template1';

It'd be safe to do it on template0, and also on template1 as long as that
has only the original contents ...

> It's empty, only hosting a few extensions.

... which that isn't.  I'd suggest checking for indexes that might need
to be rebuilt with this query borrowed from the regression tests:

SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
FROM (SELECT indexrelid, indrelid,
 unnest(indclass) as iclass, unnest(indcollation) as icoll
  FROM pg_index) ss
WHERE icoll != 0 AND iclass !=
(SELECT oid FROM pg_opclass
 WHERE opcname = 'text_pattern_ops' AND opcmethod =
   (SELECT oid FROM pg_am WHERE amname = 'btree'));

> Now I can't even create a database having a different collation:
> $ createdb -O myuser --locale='en_US.UTF-8' mydb
> createdb: database creation failed: ERROR:  new collation (en_US.UTF-8) is 
> incompatible with the collation of the template database (C)
> HINT:  Use the same collation as in the template database, or use template0 
> as template.

Read the HINT ...

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Seamus Abshere]

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 self-join and subquery
t3. Query B starts subquery
t4. Query B starts self-join (note: data from t1!)
[...]
tN. Query A finishes UPDATE
tN+1. Query B finishes UPDATE, missing any new_data from Query A

My assumption is that t1 and t4 (the self-joins) use SELECT but not
SELECT FOR UPDATE. If they did, I think the race condition would go
away.

Did I analyze that right?

Thanks!

--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[rihad]

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 multilingual and all its data is stored in UTF-8. Sorting
doesn't work correctly, though. To fix that, can I just do this:
update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname='mydb';

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 on a scratch copy of the database would be
a good idea, if this is valuable data.

regards, tom lane


Thank you, Tom. But can I still do it for the template1 database?

update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname='template1';

It's empty, only hosting a few extensions. Now I can't even create a 
database having a different collation:


$ createdb -O myuser --locale='en_US.UTF-8' mydb
createdb: database creation failed: ERROR:  new collation (en_US.UTF-8) 
is incompatible with the collation of the template database (C)
HINT:  Use the same collation as in the template database, or use 
template0 as template.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] CREATE AGGREGATE on jsonb concat

[Seamus Abshere]

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 what I am proposing is:

* jsonb_object_agg(expression)

Is that sane?

Best,
Seamus


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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 stored in UTF-8. Sorting 
> doesn't work correctly, though. To fix that, can I just do this:

> update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8' 
> where datname='mydb';

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 on a scratch copy of the database would be
a good idea, if this is valuable data.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Changing collate & ctype for an existing database

[rihad]

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 correctly, though. To fix that, can I just do this:



update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8' 
where datname='mydb';



This does seem to work on a testing copy of the database, i.e. select 
lower('БлаБлаБла') now works correctly when connected to that database.



Is there still any chance for corrupting data by doing this, or indexes 
stopping working etc?


p.s.: postgres 9.6.3

Thanks.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alexander Farber]

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,

player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

played1 timestamptz,
played2 timestamptz
);

And every hour I run a custom PL/pgSQL function to forcibly finish games,
where one of the players hasn't played any move since more than 24h:
https://gist.github.com/afarber/416da460e5722ab1e3ed25385cea6cae (also
copy-pasted at the bottom of this mail).

However there is a problem: I can not use a "single-instance" cronjob to
run words_expire_games hourly.

I have to use the HttpServlet (so that I can send notifications to the
websocket-clients) and as result multiple servlet's might end up running at
the same time.

My question is if I should ensure that only 1 servlet runs the custom
PL/pgSQL function by using "synchronized" in Java as I do it right now:

private static final String SQL_EXPIRE_GAMES =
"SELECT " +
"out_uid  AS uid,  " +
"out_gid  AS gid,  " +
"out_fcm  AS fcm,  " +
"out_apns AS apns, " +
"out_sns  AS sns,  " +
"out_note AS note  " +
"FROM words_expire_games()";

// the timestamp in milliseconds of the last successful hourly job run
private static long sLastRun = 0L;

// this method is run every time the servlet is called (i.e. very often)
private void hourlyJob() throws SQLException, IOException {
if (System.currentTimeMillis() - sLastRun < ONE_HOUR) {
return;
}

synchronized (MyListener.class) {
if (System.currentTimeMillis() - sLastRun < ONE_HOUR) {
return;
}

try (PreparedStatement st =
mDatabase.prepareStatement(SQL_EXPIRE_GAMES)) {
try (ResultSet rs = st.executeQuery()) {
while (rs.next()) {
Notification n = new Notification(
rs.getInt(KEY_UID),
rs.getInt(KEY_GID),
true,
rs.getString(KEY_FCM),
rs.getString(KEY_APNS),
rs.getString(KEY_SNS),
rs.getString(KEY_NOTE)
);
sendNotification(n);  // send notifications about
forcibly finished games via websockets
}
}
}

sLastRun = System.currentTimeMillis();
}
}

Or if maybe there is some condition (maybe "UPDATE SKIP LOCKED"?) I could
add to my custom function copy-pasted below? -

Thank you for any insights
Alex

CREATE OR REPLACE FUNCTION words_expire_games(
) RETURNS TABLE (
out_uid  integer, -- the player to be notified
out_gid  integer, -- which game has expired
out_fcm  text,
out_apns text,
out_sns  text,
out_note text
) AS
$func$
DECLARE
_gidinteger;
_loser  integer;
_winner integer;
BEGIN
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'
OR   played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
RETURNING
gid,
CASE WHEN played1 < played2 THEN player1 ELSE
player2 END,
CASE WHEN played1 < played2 THEN player2 ELSE
player1 END
LOOP
-- log the last "move"
INSERT INTO words_moves (
action,
gid,
uid,
played,
tiles
) VALUES (
'expire',
_gid,
_loser,
CURRENT_TIMESTAMP,
null
);

-- notify the loser
SELECT
uid,
_gid,
fcm,
apns,
sns,
'You have lost (game expired)!'
FROM words_users
WHERE   uid = _loser
INTO STRICT
out_uid,
out_gid,
   

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

[mariusz]

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  pg_stop_backup();”
> 
> Hopefully you are also doing WAL archiving...
> 
> > Reading the PostgreSQL9.6 documentation, the advice/future is to use the 
> > non-exclusive method, where I’ll need to keep a session *open* while the 
> > snapshot takes place, and after that I’ll have to issue the 
> > pg_stop_backup(false); in that active connection that issued the 
> > pg_start_backup(‘backup’,false,false);
> 
> Right.
> 
> > 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 that's
> touched when the snapshot has finished, but really, trying to perform a
> PG backup using hacked together shell scripts isn't recommended and
> tends to have problems.


not saying to use scripts or pg tools, but if OP needs a script, may
consider perl module IPC::Run instead of shell script - easy to use
interacting with subprocesses over filehandles or pipes (even supports
ptys). that way you can keep your active psql session, pass input to it,
view and process it's output, while doesn't block you from doing other
things meanwhile

of course it's perl, not shell, but looks "scripty" and can do what OP
wants 


> In particular WAL archiving- there's no simple way for a shell script
> which is being used for archiving to confirm that the WAL it has
> "archived" has been completely written out to disk (which is required
> for proper archiving).  Further, simple shell scripts also don't check
> that all of the WAL has been archived and that there aren't any holes in
> the WAL between the starting point of the backup and the end point.
> 
> > Especially how to do error checking from the commands as psql -c “select 
> > pg_start_backup{‘test’,false,false);” not going to work?
> 
> I'd recommend considering one of the existing PG backup tools which know
> how to properly perform WAL archiving and tracking the start/stop points
> in the WAL of the backup.  Trying to write your own using shell scripts,
> even with ZFS snapshots, isn't trivial.  If you trust the ZFS snapshot
> to be perfectly atomic across all filesystems/tablespaces used for PG,
> you could just take a snapshot and forget the rest- PG will do crash
> recovery when you have to restore from that snapshot but that's not much
> different from having to do WAL replay of the WAL generated during the
> backup.
> 
> As for existing solutions, my preference/bias is for pgBackRest, but
> there are other options out there which also work, such as barman.
> 
> Thanks!
> 
> Stephen




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general