Re: [GENERAL] debugging SSL connection problems

On Tue, Jul 11, 2017 at 6:32 AM, Magnus Hagander wrote: > 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

[GENERAL] Please say it isn't so

Hi all, Please tell me this is a mistake: https://wiki.postgresql.org/wiki/Systemd Why a database system should care about how processes get started is beyond me. Systemd is an entangled mess that every year subsumes more and more of the operating system, in a very non-cooperative way. There

Re: [GENERAL] loading file with en dash character into postgres 9.6.1 database

"Hu, Patricia" writes: > The server and client encoding are both set to UTF8, and according to this > http://www.fileformat.info/info/unicode/char/2013/index.htm en dash is a > valid UTF8 character, but when running a script with insert statement with en > dash character

[GENERAL] loading file with en dash character into postgres 9.6.1 database

The server and client encoding are both set to UTF8, and according to this http://www.fileformat.info/info/unicode/char/2013/index.htm en dash is a valid UTF8 character, but when running a script with insert statement with en dash character in it, I got the error below. mydb=> select name,

[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

Re: [GENERAL] Imperative Query Languages

They said it couldn't be done... dandl schrieb am Di. 11. Juli 2017 um 06:58: > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Merlin Moncure > > > It's probably of broader interest to consider some sort of "more > relational"

Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

On Tue, Jul 11, 2017 at 8:36 AM, Seamus Abshere wrote: > Given an update that uses CTEs like this: > > WITH > lock_rows AS ( > SELECT 1 FROM tbl WHERE [...] FOR UPDATE > ) > UPDATE [...] > > Will the rows in `tbl` remain locked until the UPDATE is finished? > > ​Yes​ -

Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

Seamus Abshere writes: > Given an update that uses CTEs like this: > WITH > lock_rows AS ( > SELECT 1 FROM tbl WHERE [...] FOR UPDATE > ) > UPDATE [...] > Will the rows in `tbl` remain locked until the UPDATE is finished? Yes, locks are associated with a transaction not a

[GENERAL] Does a row lock taken out in a CTE stay in place?

Given an update that uses CTEs like this: WITH lock_rows AS ( SELECT 1 FROM tbl WHERE [...] FOR UPDATE ) UPDATE [...] Will the rows in `tbl` remain locked until the UPDATE is finished? Also, does it matter if `lock_rows` is referenced? (IIUC the query wouldn't be run if the CTE isn't

Re: [GENERAL] Imperative Query Languages

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Merlin Moncure > 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.

Re: [GENERAL] Imperative Query Languages

On Mon, Jul 10, 2017 at 4:26 PM, Christopher Browne wrote: > 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

Re: [GENERAL] Materialised view - refresh

You can use something like cron, windows task scheduler (if you're running windows), pgagent (or jpgagent), pg_cron, or any of the others. I personally use (and wrote) jpgagent, at the time pgagent was the only alternative and it was very unstable for me. Here is the link if interested:

Re: [GENERAL] tsquery error

Stephen Davies wrote: > 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

[GENERAL] Materialised view - refresh

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

[GENERAL] tsquery error

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