Re: Version 10.7 of postgres

[Michael Paquier]

On Thu, Oct 10, 2019 at 10:22:22AM +0530, Shankar Bhaskaran wrote:
> We are planning to use postgres 10.7 version as that is the latest
> version supported on Aurora DB. Since we have an on premise installation
> also , i was trying to download the same version of postgres for windows
> and linux.
> Unfortunately that version is not available in the download site as well as
> the ftp site .Is there any reason why an installer for 10.7 version in
> windows and linux is not added to archives?

I cannot speak about Aurora DB and its compatibility or
incompatibilities, but the latest minor version of 10 is 10.10, and a
lot of bugs have been fixed since 10.7, hence it is sort of logic in
my opinion to only have an installer for the latest version available,
and that eases a lot the maintenance as the Windows installer likely
needs to bundle OpenSSL and such dependencies.  I would recommend also
that you do not use a version which has known bugs.
--
Michael


signature.asc
Description: PGP signature

Re: Segmentation fault with PG-12

[Andres Freund]

On 2019-10-09 10:16:37 -0400, Tom Lane wrote:
> Andreas Joseph Krogh  writes:
> > Attached is output from "bt full". Is this helpful? 
> 
> Well, it shows that the failure is occurring while trying to evaluate
> a variable in a trigger's WHEN clause during
> "UPDATE origo_email_delivery SET folder_id=$1, created=$2\nWHERE entity_id IN 
> ($3)\nRETURNING entity_id"
> And I'd bet that the root cause is something to do with Andres' tuple slot
> work.  But (at least to my eye) it's not apparent exactly what's wrong.

It looks like this could "just" be another report of #16036, which was
already fixed in:

commit d986d4e87f61c68f52c68ebc274960dc664b7b4e
Author: Andres Freund 
Date:   2019-10-04 11:59:34 -0700

Fix crash caused by EPQ happening with a before update trigger present.


> This doesn't seem to correlate with your original report, btw,
> as that claimed the crash was during COMMIT.

That however, would be confusing, unless there's some deferred trigger
that causes another update, which then fires a before update trigger
causing the problem.

Greetings,

Andres Freund

Version 10.7 of postgres

[Shankar Bhaskaran]

Hi ,

We are planning to use postgres 10.7 version as that is the latest
version supported on Aurora DB. Since we have an on premise installation
also , i was trying to download the same version of postgres for windows
and linux.
Unfortunately that version is not available in the download site as well as
the ftp site .Is there any reason why an installer for 10.7 version in
windows and linux is not added to archives?

Regards,
Shankar

Re: pgutils, pglogger and pgutilsL out

[Adrian Klaver]

On 10/9/19 5:02 PM, Thiemo Kellner wrote:

Hi all

I do not mean to spam so please tell me if this is not the right place 
for release announcements of OSS software for PostgreSQL. Be it as may, 
I am happy to have:


For future reference:

https://www.postgresql.org/list/pgsql-announce/



  - pgutils out: providing very basic functionality for PostgreSQL base 
applications (https://sourceforge.net/p/pgutils/wiki/Home/)


  - pglogger in a new version: a logging facility inspired by log4j 
(https://sourceforge.net/p/pglogger/wiki/Home/)


  - pgutilsL out: providing common functionality for PostgreSQL being 
logged by pglogger (https://sourceforge.net/p/pgutilsl/wiki/Home/)


pgutils and especially pgutilsL are small but it is a beginning after 
all. :-)


Kind regards

Thiemo




--
Adrian Klaver
adrian.kla...@aklaver.com

pgutils, pglogger and pgutilsL out

[Thiemo Kellner]

Hi all

I do not mean to spam so please tell me if this is not the right place  
for release announcements of OSS software for PostgreSQL. Be it as  
may, I am happy to have:


 - pgutils out: providing very basic functionality for PostgreSQL  
base applications (https://sourceforge.net/p/pgutils/wiki/Home/)


 - pglogger in a new version: a logging facility inspired by log4j  
(https://sourceforge.net/p/pglogger/wiki/Home/)


 - pgutilsL out: providing common functionality for PostgreSQL being  
logged by pglogger (https://sourceforge.net/p/pgutilsl/wiki/Home/)


pgutils and especially pgutilsL are small but it is a beginning after all. :-)

Kind regards

Thiemo

--
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B

Re: plpgsql copy import csv double quotes

[Tom Lane]

PASCAL CROZET  writes:
> I’ve experience issues with double quotes \34 inside fields, in a csv file.

> Ex :
> "value1","some text","other text with "double quotes" inside","last field"

I don't know of any definition of CSV format by which that's legal data.
The typical rule is that double quotes that are data must be doubled;
at least, that's what COPY expects by default.  You can also get COPY
to handle variants like backslash-quote.

regards, tom lane

Re: Is my lecturer wrong about PostgreSQL? I think he is!

[Pól Ua Laoínecháin]

Hi, and thanks for responding,

> First off- please try to craft a new email in the future...

My apologies to you and the group - I'll do that in future.

> > 1) Is my lecturer full of it or does he really have a point?

> He's full of it, as far as I can tell anyway, based on what you've
> shared with us.  Just look at the committers and the commit history to
> PostgreSQL, and look at who the largest contributors are and who they
> work for.  That alone might be enough to surprise your lecturer with.

The only non-PostgreSQL company that I could find was Fujitisu - where
can I find a (list of) the others?

> Databases that do direct I/O don't depend on fsync.  That said, I do
> think this could have been an issue for Oracle if you ran it without
> direct i/o.

I think that Oracle are big into asyncio? I know that you have to sudo
dnf install some_library with a name like asio/asyncio or something
like that?

Anyway, why doesn't PostgreSQL use Direct I/O?

Thanks again and rgs,

Pól...

> Stephen

Re: Event Triggers and GRANT/REVOKE

[Miles Elam]

Using my example below from another thread, GRANTs and REVOKEs leave all
fields NULL except for command_tag (set to 'GRANT' or 'REVOKE'),
object_type (set to upper case target like 'TABLE'), and in_extension (set
to whatever is appropriate, but typically false).

-

CREATE TABLE IF NOT EXISTS ddl_info (
  classid oid,
  objid oid,
  objsubid integer,
  command_tag text,
  object_type text,
  schema_name text,
  object_identity text,
  in_extension bool,
  transaction_id bigint NOT NULL DEFAULT txid_current(),
  inserted timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE OR REPLACE FUNCTION ddl_log()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO ddl_info (
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
  )
  SELECT
classid, objid, objsubid, command_tag, object_type,
schema_name, object_identity, in_extension
  FROM pg_event_trigger_ddl_commands();
END;
$$;

CREATE EVENT TRIGGER aa_ddl_info ON ddl_command_end
EXECUTE PROCEDURE ddl_log();

On Wed, Oct 9, 2019 at 2:27 PM Adrian Klaver 
wrote:

> On 10/9/19 1:56 PM, Miles Elam wrote:
> > GRANT and REVOKE trigger on a ddl_command_end event trigger but don't
> > provide any information beyond whether it was a table, schema, function,
> > etc. that was affected. No object IDs or the like are included. How
> > would you find out which table had its ACLs modified?
>
> What is the code for trigger and function?
>
> >
> > Also, why do grants and revokes have an object_type of 'TABLE' instead
> > of lower case names like 'table' for all other event types?
> >
> >
> > Thanks,
> >
> > Miles Elam
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Event Triggers and GRANT/REVOKE

[Adrian Klaver]

On 10/9/19 1:56 PM, Miles Elam wrote:
GRANT and REVOKE trigger on a ddl_command_end event trigger but don't 
provide any information beyond whether it was a table, schema, function, 
etc. that was affected. No object IDs or the like are included. How 
would you find out which table had its ACLs modified?


What is the code for trigger and function?



Also, why do grants and revokes have an object_type of 'TABLE' instead 
of lower case names like 'table' for all other event types?



Thanks,

Miles Elam




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: plpgsql copy import csv double quotes

[Adrian Klaver]

On 10/9/19 2:20 PM, PASCAL CROZET wrote:

Hi, MailingList

PG 9.3 under Ubuntu 14.04 (I know, that’s obsolete, but we’re planning 
to move to pg 10 or more during 2020)


I’ve experience issues with double quotes \34 inside fields, in a csv file.

Ex :

"value1","some text","other text with "double quotes" inside","last field"

When I import this line in a table, with the exact column number, with 
the « copy » command in plpgslq, the import fails.


What is the actual command?

This is being done inside a plpgsql function, correct?

If inside a function what is the function code?

My guess is you need to use the CSV format to COPY:

https://www.postgresql.org/docs/11/sql-copy.html



Saying that too many fields are present.

I tried to catch the double quotes and delete then, but that’s hard 
under AWK or sed


*_*

Cordialement, *Pascal CROZET*

*DBA - *Qualis Consulting 



•www.qualis-consulting.com •04 78 22 
74 90

•Le Bois des Côtes 1 – Bâtiment A
•300 Route Nationale 6 – 69760 LIMONEST
*_*

GRANT and REVOKE trigger on a ddl_command_end event trigger but don't 
provide any information beyond whether it was a table, schema, function, 
etc. that was affected. No object IDs or the like are included. How 
would you find out which table had its ACLs modified?


Also, why do grants and revokes have an object_type of 'TABLE' instead 
of lower case names like 'table' for all other event types?



Thanks,

Miles Elam




--
Adrian Klaver
adrian.kla...@aklaver.com

plpgsql copy import csv double quotes

[PASCAL CROZET]

Hi, MailingList

PG 9.3 under Ubuntu 14.04 (I know, that’s obsolete, but we’re planning to move 
to pg 10 or more during 2020)
I’ve experience issues with double quotes \34 inside fields, in a csv file.

Ex :
"value1","some text","other text with "double quotes" inside","last field"

When I import this line in a table, with the exact column number, with the « 
copy » command in plpgslq, the import fails.
Saying that too many fields are present.


I tried to catch the double quotes and delete then, but that’s hard under AWK 
or sed

_

Cordialement, Pascal CROZET

DBA - [cid:image002.png@01D57EF8.10B0A960]  
Qualis Consulting
• www.qualis-consulting.com • 04 78 22 74 90
• Le Bois des Côtes 1 – Bâtiment A
• 300 Route Nationale 6 – 69760 LIMONEST
_
GRANT and REVOKE trigger on a ddl_command_end event trigger but don't provide 
any information beyond whether it was a table, schema, function, etc. that was 
affected. No object IDs or the like are included. How would you find out which 
table had its ACLs modified?

Also, why do grants and revokes have an object_type of 'TABLE' instead of lower 
case names like 'table' for all other event types?


Thanks,

Miles Elam

Event Triggers and GRANT/REVOKE

[Miles Elam]

GRANT and REVOKE trigger on a ddl_command_end event trigger but don't
provide any information beyond whether it was a table, schema, function,
etc. that was affected. No object IDs or the like are included. How would
you find out which table had its ACLs modified?

Also, why do grants and revokes have an object_type of 'TABLE' instead of
lower case names like 'table' for all other event types?


Thanks,

Miles Elam

Re: Allowing client access

[Adrian Klaver]

On 10/9/19 11:39 AM, Timmy Siu wrote:

Dear Bob,
Thank you.?? hostssl works out of the box!?? It does not require extra 


That depends on how you installed Postgres. My guess is whatever package 
manager/installer you used did the setup/configuration for you. That 
would be:


1) In postgresql.conf set ssl = on (default is off)

https://www.postgresql.org/docs/11/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SSL

2) Created the SSL certificate and key and put them in the appropriate 
location:


https://www.postgresql.org/docs/11/ssl-tcp.html

There is also the chance that you may not be actually be connecting 
using SSL. If you connect via psql do see something like?:


psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)


NOTE:
In pg_hba.conf the first line that matches wins, so if you have a line 
before the hostssl one that matches it will be used.



Also:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

"hostssl

This record matches connection attempts made using TCP/IP, but only 
when the connection is made with SSL encryption.


To make use of this option the server must be built with SSL 
support. Furthermore, SSL must be enabled by setting the ssl 
configuration parameter (see Section 18.9 for more information). 
Otherwise, the hostssl record is ignored except for logging a warning 
that it cannot match any connections.

"


configuration.?? I can connect to my own pgsql server via pgadmin 4.?? I 
personally feel that Postgresql v11 is much clever than Mysql v5.7 (I 
haven't tried its v8).


I also have tested postgres against TCP Wrappers but it is not compiled 
against TCP wrappers library.?? May I suggest the community to have 
postgres to work with TCP wrappers.?? Its security will be better.


Regards,
Timmy



Hi Timmy

You need to use CIDR form in your pg_hba.conf.  So:

host all testuser  111.222.333.444/32  md5

Most likely you would probably want to ensure ssl connection if coming
over untrusted network.  So, at minimum, this is better:

hostssl all testuser  111.222.333.444/32  md5

This is better still:

hostssl testdb testuser  111.222.333.444/32  md5

Better still (IMHO) is to keep it local and use ssh tunnel, but I
understand that might be difficult and not necessarily desirable,
depending on the context.

Regards
Bob







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is my lecturer wrong about PostgreSQL? I think he is!

[Tim Clarke]

On 09/10/2019 20:45, Alan Hodgson wrote:
> Assuming you're not a troll ...
>
> On Wed, 2019-10-09 at 20:06 +0100, Pól Ua Laoínecháin wrote:
>> 1) Is my lecturer full of it or does he really have a point?
> He's more than full of it. PostgreSQL has had a few bugs over the year
> that could have resulted in data corruption, but they're pretty rare
> and fixed as soon as they're found. PostgreSQL is the most reliable
> software I run, and virtually the only major piece I don't hesitate to
> upgrade without waiting to see what bugs other people find first.
>> 4) What is the OS of choice for *_serious_* PostgreSQL installations?
> That's a religious question, not a technical question. I think even
> Microsoft makes a decent server OS nowadays. But I expect a large
> majority of PostgreSQL installations are running on Linux, as are the
> vast majority of all server apps nowadays. Having said that, I don't
> run a "serious" PostgreSQL installation; some of the people here run
> databases that do tens of thousands of TPS and hold many TiB of data.
> You'd have to ask them I guess.


Pretty sure if you run through Oracle's back catalogue you'll find a
similar data corruption for an entirely unrelated reason. Just guessing
but chances are


Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: Is my lecturer wrong about PostgreSQL? I think he is!

[Alan Hodgson]

Assuming you're not a troll ...

On Wed, 2019-10-09 at 20:06 +0100, Pól Ua Laoínecháin wrote:
> 1) Is my lecturer full of it or does he really have a point?
> 

He's more than full of it. PostgreSQL has had a few bugs over the year
that could have resulted in data corruption, but they're pretty rare and
fixed as soon as they're found. PostgreSQL is the most reliable software
I run, and virtually the only major piece I don't hesitate to upgrade
without waiting to see what bugs other people find first.

> 
> 4) What is the OS of choice for *_serious_* PostgreSQL installations?

That's a religious question, not a technical question. I think even
Microsoft makes a decent server OS nowadays. But I expect a large
majority of PostgreSQL installations are running on Linux, as are the
vast majority of all server apps nowadays. Having said that, I don't run
a "serious" PostgreSQL installation; some of the people here run
databases that do tens of thousands of TPS and hold many TiB of data.
You'd have to ask them I guess.

Re: Is my lecturer wrong about PostgreSQL? I think he is!

[Stephen Frost]

Greetings,

First off- please try to craft a new email in the future rather than
respond to an existing one.  You may not realize this but there's some
headers that get copied when you do a reply that cause the email to show
up as being a reply, even if you remove all the "obvious" bits from it.

* Pól Ua Laoínecháin (lineh...@tcd.ie) wrote:
> 1) Is my lecturer full of it or does he really have a point?

He's full of it, as far as I can tell anyway, based on what you've
shared with us.  Just look at the committers and the commit history to
PostgreSQL, and look at who the largest contributors are and who they
work for.  That alone might be enough to surprise your lecturer with.

> 2) The actual concrete acknowledged problem with fsync that affected
> PostgreSQL - why didn't it affect Oracle? Or MySQL? Or did it but it
> was so rare that it never became apparent - it wasn't that obvious
> with PostgreSQL either - one of those rare and intermittent problems?

Databases that do direct I/O don't depend on fsync.  That said, I do
think this could have been an issue for Oracle if you ran it without
direct i/o.

> 3) Were there ever any problems with BSD?

As I understand it, no.

> 4) What is the OS of choice for *_serious_* PostgreSQL installations?

BSD and Linux are both quite popular platforms for running PG, and
people run very serious workloads on both.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: I messed up and my disk utlization is HUGE

[stan]

On Wed, Oct 09, 2019 at 02:59:17PM -0400, melvin6925 wrote:
> Have you tried a VACUUM FULL of the db?Sent via the Samsung Galaxy S?? 6, an 
> AT&T 4G LTE smartphone
>  Original message From: stan  Date: 10/9/19  
> 14:54  (GMT-05:00) To: melvin6925  Subject: Re: I 
> messed up and my disk utlization is HUGE On Wed, Oct 09, 2019 at 01:30:33PM 
> -0400, melvin6925 wrote:> >How can I shrink this back to a>reasonable 
> size???Have you tried a vacuum full of the db?Sent via the Samsung Galaxy S?? 
> 6, an AT&T 4G LTE smartphone>  Original message From: stan 
>  Date: 10/9/19?? 13:21?? (GMT-05:00) To: 
> pgsql-general@lists.postgresql.org Subject: I messed up and my disk 
> utlization is HUGE I was going to try to do some testing on very large 
> databases, and I tried toload the Mouse Gerome DB. It failed, and now I know 
> why :-) I filled thedisk up. Presently, with virtually nothing in any 
> database the postgresstorage location has 43G allocated. The DB was also 
> crashed but i did freeup some space and get it started again. How can I 
> shrink this back to areasonable size? The WAL also seems pretty large.-- 
> "They that would give up essential liberty for temporary safety 
> deserveneither liberty nor safety."   
>  -- Benjamin FranklinSorry your reply was garbled.-- "They that would 
> give up essential liberty for temporary safety deserveneither liberty nor 
> safety." -- Benjamin Franklin

I ran vacuumdb from the comand line as postgres , which I beleive does
this. Did not take very long to run BTW.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Is my lecturer wrong about PostgreSQL? I think he is!

[Pól Ua Laoínecháin]

Hi all,

I recently started a Masters in Computer Science (and not at the
institution in my email address).

One of my courses is "Advanced Databases" - yummy I thought - it's not
even compulsory for me but I just *_had_* to take this module. The
lecturer is a bit of an Oracle fan-boy (ACE director no less...
hmmm...) and I want(ed) - becoming less enthusiasic by the minute - to
do my dissertation with him. So, we're having a chat and I make plain
my love of good 'ol PostgreSQL as my RDBMS of choice and he tells me
that there are problems with random block corruption with PostgreSQL.
I said "really" and before that conversation could go any further,
another student came over and asked a question.

So, I toddled off and did some research - I had heard something about
this before (vague fuzzy memories) of a problem with the Linux kernel
so I searched for a bit and duly dug up a couple of pages

https://lwn.net/Articles/752063/ : PostgreSQL's fsync() surprise - and

https://news.ycombinator.com/item?id=19238121 : Linux Fsync Issue for
Buffered IO and Its Preliminary Fix for PostgreSQL

So, this week I go back to my lecturer and say, yep, there was some
issue but it was a Linux kernel problem and not PostgreSQL's fault and
has been resolved.

He tells me that he knew about that but that there was another issue
(he had "spoken to people" at meetings!). I said "well, why isn't it
fixed?" and he replied "where's the impetus?" to which I responded
(quite shocked at this stage) something like "well, I know that the
core team values correctness very highly" to which he came back with
"yes, but they have no commercial imperative to fix anything - they
have to wait until somebody is capable enough and interested enough to
do the work". He then muttered something about this mysterious flaw
having been fixed in EnterpriseDB.

At this point, I lost interest. Having lurked on lists and going by my
general "gut feeling" - if there was a serious issue causing
irrecoverable block corruption, I'm pretty sure that it would be "all
hands on deck" until this problem had been solved and "nice-to-haves"
(GENERATED AS... for example) would have been parked till then.

Now, I have four questions:

1) Is my lecturer full of it or does he really have a point?

2) The actual concrete acknowledged problem with fsync that affected
PostgreSQL - why didn't it affect Oracle? Or MySQL? Or did it but it
was so rare that it never became apparent - it wasn't that obvious
with PostgreSQL either - one of those rare and intermittent problems?

3) Were there ever any problems with BSD?

4) What is the OS of choice for *_serious_* PostgreSQL installations?

I hope that I have been clear, but should anyone require any
clarification, please don't hesitate to ask me.

Tia and rgs,

Pól...

Re: Allowing client access

[Timmy Siu]

Dear Bob,
Thank you.?? hostssl works out of the box!?? It does not require extra 
configuration.?? I can connect to my own pgsql server via pgadmin 4.?? I 
personally feel that Postgresql v11 is much clever than Mysql v5.7 (I 
haven't tried its v8).


I also have tested postgres against TCP Wrappers but it is not compiled 
against TCP wrappers library.?? May I suggest the community to have 
postgres to work with TCP wrappers.?? Its security will be better.


Regards,
Timmy



Hi Timmy

You need to use CIDR form in your pg_hba.conf.  So:

host all testuser  111.222.333.444/32  md5

Most likely you would probably want to ensure ssl connection if coming
over untrusted network.  So, at minimum, this is better:

hostssl all testuser  111.222.333.444/32  md5

This is better still:

hostssl testdb testuser  111.222.333.444/32  md5

Better still (IMHO) is to keep it local and use ssh tunnel, but I
understand that might be difficult and not necessarily desirable,
depending on the context.

Regards
Bob

Re: Pg11 -- MultiXactId xxxx has not been created yet -- apparent wraparound

[Alvaro Herrera]

On 2019-Oct-07, Moreno Andreo wrote:

> Unfortunately, it didn't work :(
> 
> db0=# select * from failing_table where ctid='(3160,31)' for update;
> ERROR:  MultiXactId 12800 has not been created yet -- apparent wraparound

Oh well.  It was a long shot anyway ...

> Since the probability we are into corruption is very high, what if I \copy
> all the table but the failing row(s) to an external file, drop and recreate
> the table, and then \copy clean data back inside?

Yes, that should work.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I messed up and my disk utlization is HUGE

[stan]

I was going to try to do some testing on very large databases, and I tried to
load the Mouse Gerome DB. It failed, and now I know why :-) I filled the
disk up. Presently, with virtually nothing in any database the postgres
storage location has 43G allocated. The DB was also crashed but i did free
up some space and get it started again. How can I shrink this back to a
reasonable size? The WAL also seems pretty large.



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Repmgr and pglogical

[Sonam Sharma]

How repmgr and pglogical are different and which one is more efficient way
of replication ?
Can someone please help

Re: Segmentation fault with PG-12

[Tom Lane]

Andreas Joseph Krogh  writes:
> Is it OK if I send you the table/trigger-definitions off-list?

Sure, but please share with Andres [cc'ed] as well.

regards, tom lane

Re: Segmentation fault with PG-12

[Andreas Joseph Krogh]

På onsdag 09. oktober 2019 kl. 16:16:37, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh  writes:
 > Attached is output from "bt full". Is this helpful?

 Well, it shows that the failure is occurring while trying to evaluate
 a variable in a trigger's WHEN clause during
 "UPDATE origo_email_delivery SET folder_id=$1, created=$2\nWHERE entity_id IN 
($3)\nRETURNING entity_id"
 And I'd bet that the root cause is something to do with Andres' tuple slot
 work. But (at least to my eye) it's not apparent exactly what's wrong.

 Can you show us the table definition and associated trigger definitions
 for origo_email_delivery?

 This doesn't seem to correlate with your original report, btw,
 as that claimed the crash was during COMMIT.

 regards, tom lane FWIW: It doesn't always happen when that UPDATE-statement 
is issued, so it's not reproducable. We'll see what the next core-dump gives us.
Is it OK if I send you the table/trigger-definitions off-list? -- Andreas 
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com
  www.visena.com   

Re: Segmentation fault with PG-12

[Tom Lane]

Andreas Joseph Krogh  writes:
> Attached is output from "bt full". Is this helpful? 

Well, it shows that the failure is occurring while trying to evaluate
a variable in a trigger's WHEN clause during
"UPDATE origo_email_delivery SET folder_id=$1, created=$2\nWHERE entity_id IN 
($3)\nRETURNING entity_id"
And I'd bet that the root cause is something to do with Andres' tuple slot
work.  But (at least to my eye) it's not apparent exactly what's wrong.

Can you show us the table definition and associated trigger definitions
for origo_email_delivery?

This doesn't seem to correlate with your original report, btw,
as that claimed the crash was during COMMIT.

regards, tom lane

Re: GSSAPI: logging principal

[Stephen Frost]

Greetings,

* Allan Jensen (pgl...@winge-jensen.dk) wrote:
> I have GSSAPI-login and user mapping to postgres working fine.

Great!

> Whenever i login to postgres I get a line like the following in the
> logfile:
> 
> connection authorized: user=testrole database=testdb SSL enabled
> (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256,
> compression=off)

Minor side-note: we have GSSAPI encryption built-in starting with v12,
removing the need to also have SSL.

> What bothers me, is that i can't see what kerberos prinicpal was used
> during authentication.

Yeah, I'm afraid that's probably right.  In looking, I don't see any
particularly easy way.  I could have sworn I complained about this ages
ago (there might even be a patch somewhere in the depths of -hackers
from 5 years ago or more) but clearly it never made it in.

One thing that is kind of nice is that with v12 there's a new view where
you can view the state of existing connections, including the principal
they authenticate with: pg_stat_gssapi.

> Is there any way to make postgres log the principal?

Would definitely be a good thing for us to have, and the CN for an
SSL-based connection.  I don't think it'd be hard for someone to hack up
a patch to do so.  I've added it to my list of "nice to haves" but it
seems unlikely I'll get any time in the near future to hack on it, so
if someone else wants to work on it, please feel free to do so...

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Allowing client access

[Bob Jolliffe]

Hi Timmy

You need to use CIDR form in your pg_hba.conf.  So:

host all testuser  111.222.333.444/32  md5

Most likely you would probably want to ensure ssl connection if coming
over untrusted network.  So, at minimum, this is better:

hostssl all testuser  111.222.333.444/32  md5

This is better still:

hostssl testdb testuser  111.222.333.444/32  md5

Better still (IMHO) is to keep it local and use ssh tunnel, but I
understand that might be difficult and not necessarily desirable,
depending on the context.

Regards
Bob

On Wed, 9 Oct 2019 at 13:34, Timmy Siu  wrote:
>
> Dear All Users,
>
> How do I allow an external client IP address access to my pgsql server?
>
> According to my own experience and test, if I set the external client IP 
> address to, for example, 111.222.333.444 in the file 
> /etc/postgresql/11/main/pg_hba.conf, it will not be able to connect to the 
> server:
> #TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
> ?? METHOD
> host?? all?? ?? ?? testuser  ?? 111.222.333.444?? 
> md5
>
> I must set "address" to "any" as in the following:
> #TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
> METHOD
> host?? all?? ?? ?? testuser  ?? 0.0.0.0/0?? ?? md5
>
>
> Can I limit access to my pgsql server through TCP Wrapper? What is the 
> related executable?
>
>
> Regards,
> Timmy
>
> ??

Allowing client access

[Timmy Siu]

Dear All Users,

 * How do I allow an external client IP address access to my pgsql server?

According to my own experience and test, if I set the external client IP 
address to, for example, 111.222.333.444 in the file 
/etc/postgresql/11/main/pg_hba.conf, it will not be able to connect to 
the server:

#TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
?? METHOD
host?? all?? ?? ?? testuser 111.222.333.444 md5

I must set "address" to "any" as in the following:
#TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
METHOD
host?? all?? ?? ?? testuser  ?? 0.0.0.0/0?? ?? md5


 * Can I limit access to my pgsql server through TCP Wrapper? What is
   the related executable?


Regards,
Timmy

Re: Event Triggers and Dropping Objects

[Luca Ferrari]

On Mon, Oct 7, 2019 at 10:12 PM Bruce Momjian  wrote:
> Do the Postgres docs need improvement here?

I don't know, but I would probably add a flag column in the firing
matrix to indicate when the related function will return a null tuple.

Luca

Re: Segmentation fault with PG-12

[Andreas Joseph Krogh]

På tirsdag 08. oktober 2019 kl. 17:24:21, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh  writes:
 > Will running a debug-enabled build slow things noticably down?

 gcc promises that the generated code is the same with or without debug.
 I think clang does too. With other compilers you may pay some penalty.

 > Is there a way
 > to make it dump a stack-trace (or back-trace in C-land?) on sig11?

 You should be able to get a core file from which you can extract a
 stack trace (and other info) after the fact.

 
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

 regards, tom lane Attached is output from "bt full". Is this helpful? 
Anything else I can do to help narrowing down the problem? Thanks. -- Andreas 
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com
  www.visena.com   
#0  slot_deform_heap_tuple (natts=24, offp=0x5598ec873d90, tuple=, slot=0x5598ec873d48)
at ./build/../src/backend/executor/execTuples.c:895
isnull = 0x5598ec8733e8
tup = 
bp = 
tupleDesc = 
values = 0x5598ec875de8
off = 
slow = 
hasnulls = 
attnum = 
tp = 
tupleDesc = 
values = 
isnull = 
tup = 
hasnulls = 
attnum = 
tp = 
off = 
bp = 
slow = 
thisatt = 
#1  tts_buffer_heap_getsomeattrs (slot=0x5598ec873d48, natts=24) at 
./build/../src/backend/executor/execTuples.c:676
bslot = 0x5598ec873d48
#2  0x5598e94534ac in slot_getsomeattrs_int 
(slot=slot@entry=0x5598ec873d48, attnum=24)
at ./build/../src/backend/executor/execTuples.c:1877
__errno_location = 
#3  0x5598e94443f1 in slot_getsomeattrs (attnum=, 
slot=0x5598ec873d48)
at ./build/../src/include/executor/tuptable.h:345
No locals.
#4  ExecInterpExpr (state=0x5598ec8776b8, econtext=0x5598ec876ea8, 
isnull=)
at ./build/../src/backend/executor/execExprInterp.c:441
op = 
resultslot = 0x0
innerslot = 
outerslot = 
scanslot = 0x0
dispatch_table = {0x5598e9443608 , 0x5598e94443f8 
, 
  0x5598e94443d0 , 0x5598e94443b0 
, 0x5598e9444380 , 
  0x5598e9444350 , 0x5598e9444328 
, 0x5598e9444318 , 
  0x5598e9444148 , 0x5598e94442e0 
, 0x5598e9444300 , 
  0x5598e94442c8 , 0x5598e9444120 
, 0x5598e94440f0 , 
  0x5598e94442a0 , 0x5598e9444270 
, 0x5598e9444250 , 
  0x5598e9444240 , 0x5598e94441d8 
, 0x5598e94441c0 , 
  0x5598e94441a8 , 0x5598e94435a8 
, 0x5598e94435af , 
  0x5598e9444168 , 0x5598e94435d0 
, 0x5598e94435d7 , 
  0x5598e94440b8 , 0x5598e94440b0 
, 0x5598e9444088 , 
  0x5598e9444080 , 0x5598e9444070 
, 0x5598e9444058 , 
  0x5598e9444028 , 0x5598e9444008 
, 0x5598e9443fe0 , 
  0x5598e9443fd0 , 0x5598e9443fb8 
, 0x5598e9443f60 , 
  0x5598e9443f90 , 0x5598e9443f38 
, 0x5598e9443d98 , 
  0x5598e9443f20 , 0x5598e9443f08 
, 0x5598e9443ef0 , 
  0x5598e9443ec0 , 0x5598e9443e28 
, 0x5598e9443dc0 , 
  0x5598e9443d60 , 0x5598e9443e48 
, 0x5598e9443ce8 , 
  0x5598e9443cd0 , 0x5598e9444598 
, 0x5598e9443cb8 , 
  0x5598e9443ca0 , 0x5598e9443c78 
, 0x5598e9443be8 , 
  0x5598e9443c18 , 0x5598e9443b98 
, 0x5598e9443b80 , 
  0x5598e9443b68 , 0x5598e9443b50 
, 0x5598e9443b38 , 
  0x5598e9443b18 , 0x5598e9443b00 
, 0x5598e9443c00 , 
  0x5598e9443ae8 , 0x5598e9443e90 
, 0x5598e9443aa0 , 
  0x5598e9443690 , 0x5598e9443ad0 
, 0x5598e9443ab8 , 
  0x5598e9443a88 , 0x5598e9443a48 
, 0x5598e9443a70 , 
  0x5598e9443a10 , 0x5598e94439f8 
, 0x5598e94439e0 , 
  0x5598e94439c0 , 0x5598e9443628 
, 0x5598e94438f8 , 
  0x5598e9443980 , 0x5598e94438a0 
, 0x5598e9443940 , 
  0x5598e94437f8 , 0x5598e9443710 
, 0x5598e94436f8 , 
  0x5598e94436e0 , 0x5598e9443608 
}
#5  0x5598e942326b in ExecEvalExprSwitchContext (isNull=0x7ffdf2aae7a7, 
econtext=, state=)
at ./build/../src/include/executor/executor.h:307
retDatum = 
oldContext = 
retDatum = 
oldContext = 
#6  ExecQual (econtext=, state=) at 
./build/../src/include/executor/executor.h:376
ret = 
isnull = false
ret = 
isnull = 
#7  TriggerEnabled (estate=estate@entry=0x5598eaeabdd0, 
trigger=trigger@entry=0x5598eaeac828, event=, 
modifiedCols=modifiedCols@entry=0x5598eb7d34b8, oldslot=0x5598ec876b88, 
newslot=0x5598ec873d48, relinfo=, 
relinfo=) at ./build/../src/backend/commands/trigger.c:3516
predicate = 
econtext = 
oldContext = 
i = 
relinfo = 
relinfo = 
oldslot = 0x5598ec876b88
modifiedCols = 0x5598eb7d34b8
estate = 0x5598eaeabdd0
newslot = 0x5598ec873d48
event = 
t

GSSAPI: logging principal

[Allan Jensen]

Hi,

I have GSSAPI-login and user mapping to postgres working fine.

Whenever i login to postgres I get a line like the following in the
logfile:

connection authorized: user=testrole database=testdb SSL enabled
(protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256,
compression=off)

What bothers me, is that i can't see what kerberos prinicpal was used
during authentication.

I have fiddled around with log-settings to no avail.

Is there any way to make postgres log the principal?

--
Regards,
Allan

Re: Case Insensitive Comparison with Postgres 12

[Pavel Křehula]

Hello,
use correct locale identifier, in your case it should be:
create collation "case_insensitive" (provider=icu, 
locale="en-US-u-ks-level2", deterministic = false);


See 
http://www.unicode.org/reports/tr35/tr35-collation.html#Setting_Options

for available options.

--
Pavel

Dne 09.10.2019 0:51:52, "Igal Sapir"  napsal:

I am trying to test a simple case insensitive comparison.  Most likely 
the collation that I chose is wrong, but I'm not sure how to choose the 
correct one (for English/US?).  Here is my snippet:


create collation case_insensitive(
provider=icu, locale='en-US-x-icu', deterministic=false
);
select 'Abc' = 'abc' collate case_insensitive;

I expected true but am getting false.

Any thoughts?

Thanks,

Igal

Re: Case Insensitive Comparison with Postgres 12

[Laurenz Albe]

Igal Sapir wrote:
> I am trying to test a simple case insensitive comparison.  Most likely the
> collation that I chose is wrong, but I'm not sure how to choose the correct
> one (for English/US?).  Here is my snippet:
> 
> create collation case_insensitive(
> provider=icu, locale='en-US-x-icu', deterministic=false
> );
> select 'Abc' = 'abc' collate case_insensitive;
> 
> I expected true but am getting false.
> 
> Any thoughts?

Yes, the LOCALE is wrong. Use

create collation case_insensitive (
   provider=icu, locale='en-US-u-ks-level2', deterministic=false
);

The name of the locale defines it.

My blog post can give a simple introduction:
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Case Insensitive Comparison with Postgres 12

[Wim Bertels]

Using the datatype citext might be an alternative solution

Igal Sapir  schreef op October 8, 2019 10:51:52 PM UTC:
>I am trying to test a simple case insensitive comparison.  Most likely
>the
>collation that I chose is wrong, but I'm not sure how to choose the
>correct
>one (for English/US?).  Here is my snippet:
>
>create collation case_insensitive(
>provider=icu, locale='en-US-x-icu', deterministic=false
>);
>select 'Abc' = 'abc' collate case_insensitive;
>
>I expected true but am getting false.
>
>Any thoughts?
>
>Thanks,
>
>Igal

-- 
Verstuurd vanaf een fairphone met K-9 Mail. Excuseer mijn beknoptheid.