Re: [GENERAL] debugging SSL connection problems

[Michael Paquier]

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

Yes that will work. The SSL context stored in PGconn offers enough
entry point to access all the SSL-related data.
-- 
Michael


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

[GENERAL] Please say it isn't so

[Steve Litt]

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 are almost ten init systems. In every one of those init systems,
one can run a process supervisor, such as runit or s6 or
daemontools-encore, completely capable of starting the postgres server.

Every year, systemd further hinders interoperability, further erodes
interchangeability of parts, and continues to address problems with
WONTFIX. In the long run, you do your users no favor by including
init-system specific code in Postgres or its makefiles. If systemd
can't correctly start Postgres, I guarantee you that s6 or runit,
running on top of systemd, can. 

Postgres doesn't care which language makes a query to it. Why
should Postgres care which init system started it? I hope you can free
Postgres of init-specific code, and if for some reason you can't do
that, at least don't recommend init-specific code.

Thanks,

SteveT

Steve Litt 
July 2017 featured book: Quit Joblessness: Start Your Own Business
http://www.troubleshooters.com/startbiz


-- 
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] loading file with en dash character into postgres 9.6.1 database

[Tom Lane]

"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 in it, I got the error below.
> psql:activity_type.lst:379: ERROR:  invalid byte sequence for encoding 
> "UTF8": 0x96

Well, that certainly isn't valid UTF8, so your script file isn't in UTF8.

> If I set client_encoding to WIN1252, the same file will be run ok

0x96 does seem to be an en-dash in WIN1252, so this is probably the
appropriate fix.  Testing here says that PG will correctly convert
0x96 in WIN1252 to an en-dash (U+2013) in UTF8, so I think you are
getting the right thing inserted.

> but afterwards the en dash character showed up as "û", instead of the en dash 
> character "-"

This indicates that your terminal program does *not* think its encoding
is WIN1252.  Having loaded that script file, you need to revert
client_encoding to whatever your terminal program is using, or non-ASCII
characters are going to be displayed wrong.

A bit of poking around suggests that your terminal may be operating
with code page 437 or similar, as 0x96 is "û" in that encoding ---
according to Wikipedia, at least:
https://en.wikipedia.org/wiki/Code_page_437
I don't think Postgres supports that as a client_encoding setting,
so one way or another you're going to need to switch the terminal
program's character set setting.

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] loading file with en dash character into postgres 9.6.1 database

[Hu, Patricia]

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, setting from pg_settings where name like '%encoding%';
  name   | setting
-+-
client_encoding | UTF8
server_encoding | UTF8

mydb=> \i activity_type.lst
psql:activity_type.lst:379: ERROR:  invalid byte sequence for encoding "UTF8": 
0x96

If I set client_encoding to WIN1252, the same file will be run ok and records 
inserted in, but afterwards the en dash character showed up as "û", instead of 
the en dash character "-"
mydb=> show client_encoding;
client_encoding
-
WIN1252

I created a database with WIN1252 encoding so both server and client encoding 
are WIN1252, loaded the same file in, en dash character still showed up as 
"û"(actually that is on a windows box, on a linux box the character didn't show 
up at all), so the client setting still makes a difference, even though 
client_encoding showed the same value.

Is there any way I will be able to load the en dash character into the 
postgresql database as is? I had worked around it by editing the input file to 
replace en dash with a plain dash, but that's quite some manual work each time 
when a new dump is generated from an oracle database with WE8MSWIN1252 
characterset.


Thanks,
Patricia

Confidentiality Notice::  This email, including attachments, may include 
non-public, proprietary, confidential or legally privileged information.  If 
you are not an intended recipient or an authorized agent of an intended 
recipient, you are hereby notified that any dissemination, distribution or 
copying of the information contained in or transmitted with this e-mail is 
unauthorized and strictly prohibited.  If you have received this email in 
error, please notify the sender by replying to this message and permanently 
delete this e-mail, its attachments, and any copies of it immediately.  You 
should not retain, copy or use this e-mail or any attachment for any purpose, 
nor disclose all or any part of the contents to any other person. Thank you.

[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

Re: [GENERAL] Imperative Query Languages

[Jason Dusek]

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"
> > 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.
>
> I'd like to see a SQL variant (maybe preprocessed) with an algebraic
> syntax.  My biggest gripes with SQL are all the keywords (there are other
> spoken languages than English??) and the unnecessarily irregular syntax.
>
> If you want a comprehensive list of what's wrong with SQL, it's easy
> enough to find. The list is long, but near the top are the failure to
> adhere to the relational model, NULLs, and language design (irregular
> syntax, etc). But SQL is deeply embedded and currently there are no
> competitors in its space. In the academic arena Datalog is preferred, and
> there are solid commercial implementations.
>
> It's easy enough to pre-process your own syntax, and Andl effectively does
> that by generating SQL on Postgres and SQLite. But that doesn't provide
> enough benefits on its own, and displacing SQL from any of the places it's
> currently used is not going to happen any time soon.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
>
>
>
>
>

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

[David G. Johnston]

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​ - locks persist to the end of the transaction.  Using a CTE doesn't
constitute creating a new statement.

Also, does it matter if `lock_rows` is referenced? (IIUC the query
> wouldn't be run if the CTE isn't referenced if it was for a SELECT, but
> since it's an UPDATE, it will be run anyway
> ​.
>

​Pretty sure it will not be.  The EXPLAIN​ command should be able to
provide a more definitive answer.

If the UPDATE was inside the CTE it definitely would be run regardless of
outer query references.  I'm not sure if the FOR UPDATE impacts whether the
select needs to be executed by I'm thinking no since it doesn't change the
semantics of the query.

David J.

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

[Tom Lane]

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 statement or
sub-statement.

> Also, does it matter if `lock_rows` is referenced? (IIUC the query
> wouldn't be run if the CTE isn't referenced if it was for a SELECT, but
> since it's an UPDATE, it will be run anyway)

Yes, it does --- unreferenced SELECT CTEs are discarded.  I thought maybe
there was an exception for FOR UPDATE, but a look at the code says
differently.  In any case we would only lock rows the sub-select had
actually read, so if it's not called by the outer statement it would
still be a no-op.

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] Does a row lock taken out in a CTE stay in place?

[Seamus Abshere]

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 referenced if it was for a SELECT, but
since it's an UPDATE, it will be run anyway)

Thanks!
Seamus

--
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] Imperative Query Languages

[dandl]

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.  (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.

I'd like to see a SQL variant (maybe preprocessed) with an algebraic syntax.  
My biggest gripes with SQL are all the keywords (there are other spoken 
languages than English??) and the unnecessarily irregular syntax.

If you want a comprehensive list of what's wrong with SQL, it's easy enough to 
find. The list is long, but near the top are the failure to adhere to the 
relational model, NULLs, and language design (irregular syntax, etc). But SQL 
is deeply embedded and currently there are no competitors in its space. In the 
academic arena Datalog is preferred, and there are solid commercial 
implementations.

It's easy enough to pre-process your own syntax, and Andl effectively does that 
by generating SQL on Postgres and SQLite. But that doesn't provide enough 
benefits on its own, and displacing SQL from any of the places it's currently 
used is not going to happen any time soon.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org








-- 
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] Imperative Query Languages

[Merlin Moncure]

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

I'd like to see a SQL variant (maybe preprocessed) with an algebraic
syntax.  My biggest gripes with SQL are all the keywords (there are
other spoken languages than English??) and the unnecessarily irregular
syntax.

merlin


-- 
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] Materialised view - refresh

[Adam Brusselback]

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: https://github.com/GoSimpleLLC/jpgAgent

Re: [GENERAL] tsquery error

[Albe Laurenz]

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 title,dtype,source,used_for;
> ERROR:  syntax error in tsquery: " ma waterflux"
> 
> Remove either the "ma" or the "waterflux" and the query works.
> 
> What is causing the error?
> 
> (MA Waterflux is a product name.)

It is the fact that the string contains two words.

You would have to place an operator between the words,
probably & in that case.
(https://www.postgresql.org/docs/current/static/datatype-textsearch.html#DATATYPE-TSQUERY)

Or you use "plainto_tsquery" instead of "to_tsquery".

Yours,
Laurenz Albe

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

[GENERAL] Materialised view - refresh

[Krithika Venkatesh]

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 advance..

[GENERAL] tsquery error

[Stephen Davies]

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 error in tsquery: " ma waterflux"

Remove either the "ma" or the "waterflux" and the query works.

What is causing the error?

(MA Waterflux is a product name.)

Cheers and 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