Hi
I have a materialised view which needs to refreshed every half an hour.
Is it possible to refresh the view without using triggers.
Do we have something similar to DBMS_SCHEDULER.CREATE_JOB in postgresql to
create a job that can be scheduled to refresh the views periodically.
Thanks in advanc
The following query give the error:
select
id,title,dtype,source,used_for,ts_headline('english',content,to_tsquery('english','
ma waterflux'),'minWords = 99, maxWords = 999') from info where clob @@
to_tsquery('english',' ma waterflux') order by title,dtype,source,used_for;
ERROR: syntax er
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
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.
Re
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
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) F
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
befo
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
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:
>
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 w
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 text
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 ensur
"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 ca
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 t
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'
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
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 co
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
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
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
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. So
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 cor
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,
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
24 matches
Mail list logo
