Re: PG11.2 - wal_level =minimal max_wal_senders = 0

[Benoit Lobréau]

Hi,

Make sure that the new settings of wal_level and max_wal_senders don't
interfere with your backup strategy.
The two parameters have an impact on wal archiving as well.

Ben.

Le jeu. 5 sept. 2019 à 08:33, Luca Ferrari  a écrit :

> On Wed, Sep 4, 2019 at 10:44 PM Jason Ralph
>  wrote:
> >
> > Thank you Luca,
> > Can I ask one more related question, I have no need for replication as
> noted below, can I safely disable the worker process via the setting below?
> In my sandbox it does turn off the logical replication launcher, I just
> wanted to be sure I am not affecting anything other than replication with
> this setting.
> >
> >
> > max_logical_replication_workers = 0 # taken from max_worker_processes
> > # (change requires restart)
> >
>
> Quite frankly I've never done, but I don't see why you should not turn it
> off.
>
> Luca
>
>
>

Primary Key Update issue ?

[Patrick FICHE]

Hello,

While doing some testing on a Postgresql database, I encountered a strange 
behavior which is very simple to reproduce.
I just wanted to know if this is expected behavior or if it should be 
considered as an issue.

The scenario to reproduce it is the following.

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( 
pKey ) );

INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );

UPDATE Test SET pKey = pKey + 1;

Here is the error that I get.

SQL Error [23505]: ERROR: duplicate key value violates unique constraint 
"pk_test"
  Detail: Key (pkey)=(2) already exists.

I was expecting pKey to be incremented for each row, which would still respect 
the unique constraint

I'm currently using PostgreSQL 11.5 but have the same problem on PostgreSQL 
10.3 server.

Best Regards,

Patrick

Sv: Primary Key Update issue ?

[Andreas Joseph Krogh]

På fredag 06. september 2019 kl. 11:06:04, skrev Patrick FICHE <
patrick.fi...@aqsacom.com >: 
Hello,



While doing some testing on a Postgresql database, I encountered a strange 
behavior which is very simple to reproduce.

I just wanted to know if this is expected behavior or if it should be 
considered as an issue.



The scenario to reproduce it is the following.



CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( 
pKey ) );



INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );

INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );



UPDATE Test SET pKey = pKey + 1;



Here is the error that I get.



SQL Error [23505]: ERROR: duplicate key value violates unique constraint 
"pk_test"

 Detail: Key (pkey)=(2) already exists.



I was expecting pKey to be incremented for each row, which would still respect 
the unique constraint….



I’m currently using PostgreSQL 11.5 but have the same problem on PostgreSQL 
10.3 server.



Best Regards, 
It works if you add "DEFERRABLE INITIALLY DEFERRED" to the PK: CREATE TABLE 
Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) 
DEFERRABLE INITIALLY DEFERRED );
andreak@[local]:5432 11.5 test=# CREATE TABLE Test ( pKey integer, Val 
integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
 CREATE TABLE
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
 INSERT 0 1
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );
 INSERT 0 1
andreak@[local]:5432 11.5 test=# UPDATE Test SET pKey = pKey + 1; 
 UPDATE 2
-- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 
andr...@visena.com  www.visena.com 
  

RE: Primary Key Update issue ?

[Patrick FICHE]

Hi Andreas,

Thanks a lot for your answer, which solves this case.
I was still a bit surprised as this is linked to transaction management while I 
have here a single statement until I saw the Compatibility Remark in 
documentation : Also, PostgreSQL checks non-deferrable uniqueness constraints 
immediately, not at end of statement as the standard would suggest.

Regards,

Patrick Fiche

From: Andreas Joseph Krogh 
Sent: Friday, September 6, 2019 11:17 AM
To: pgsql-general@lists.postgresql.org
Subject: Sv: Primary Key Update issue ?

På fredag 06. september 2019 kl. 11:06:04, skrev Patrick FICHE 
mailto:patrick.fi...@aqsacom.com>>:
Hello,

While doing some testing on a Postgresql database, I encountered a strange 
behavior which is very simple to reproduce.
I just wanted to know if this is expected behavior or if it should be 
considered as an issue.

The scenario to reproduce it is the following.

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( 
pKey ) );

INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );

UPDATE Test SET pKey = pKey + 1;

Here is the error that I get.

SQL Error [23505]: ERROR: duplicate key value violates unique constraint 
"pk_test"
  Detail: Key (pkey)=(2) already exists.

I was expecting pKey to be incremented for each row, which would still respect 
the unique constraint….

I’m currently using PostgreSQL 11.5 but have the same problem on PostgreSQL 
10.3 server.

Best Regards,

It works if you add "DEFERRABLE INITIALLY DEFERRED" to the PK:
CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( 
pKey ) DEFERRABLE INITIALLY DEFERRED );


andreak@[local]:5432 11.5 test=# CREATE TABLE Test ( pKey integer, Val integer, 
CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
CREATE TABLE
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
INSERT 0 1
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );
INSERT 0 1
andreak@[local]:5432 11.5 test=# UPDATE Test SET pKey = pKey + 1;
UPDATE 2

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com
www.visena.com
[cid:image001.png@01D564A5.CCF075F0]

RE: Primary Key Update issue ?

[Andreas Joseph Krogh]

På fredag 06. september 2019 kl. 11:25:36, skrev Patrick FICHE <
patrick.fi...@aqsacom.com >: 
Hi Andreas,



Thanks a lot for your answer, which solves this case.

I was still a bit surprised as this is linked to transaction management while 
I have here a single statement until I saw the Compatibility Remark in 
documentation :Also, PostgreSQL checks non-deferrable uniqueness constraints 
immediately, not at end of statement as the standard would suggest.
FWIW - PostgreSQL behaves like Oracle in this regard. -- Andreas Joseph Krogh 
CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com 
 www.visena.com   

Bad estimates on GIN bigint[] index

[Arnaud L.]

Le 03/09/2019 à 15:43, Tom Lane a écrit :

"Arnaud L."  writes:
   ->  Bitmap Index Scan on planet_osm_ways_nodes_idx 
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268 
rows=1 loops=1)

 Index Cond: (nodes && '{1}'::bigint[])


The planner should be able to do better than that, given up-to-date
statistics on the "nodes" column.



Sorry to up this thread, but is there anything I can do to help the 
planner in this particular case ?
REINDEXing did not help, nor did ANALYZEing with different STATISTICS 
target for this specific column.



Regards
--
Arnaud

RE: SQL equivalint of #incude directive ?

[Kevin Brannen]

> From: stan 
>
> I thought this would be common. But a quick Google only revealed what look to 
> be workarounds.
>
> I am defining a bunch of functions, and I would prefer to store them in a 
> separate file, which then gets "source" by the main DB init file.
>
> Is there a standard way to do this?

Besides what the others have said, not if you stick to SQL. This is really
more of an application level question where you have something that
will do your init/load process.

If you're willing to use other tools, then there are solutions. IIRC, your
other thread had "#include" in the title. You could build a small file with:

#include "function1.sql"
#include "function2.sql"
...

Then run it thru "cpp" and use the output of that. Might need some
post-processing if you're unlucky; I'd have to try it to see if there are
extraneous lines.

Personally, I'd probably just have a shell script that goes into that dir
and does something like:

cat *.sql | psql -d DB # and other args

and call it done...translate if you're not on a Unix-like system.

I could write a Perl program to do it because Perl can do almost anything. ;)

Your imagination is the limit.

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

RE: pg_restore issues with intarray

[Kevin Brannen]

>From: Jerry Sievers 
>
>>Kevin Brannen  writes:
>>
>> It feels like the restore is adding the intarray extension, which does
>> a CREATE OPERATOR FAMILY on its own, then later the restore does
>> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
>> happen on most of our databases, just a few. It's maddening to me.
>>
>
>I've seen this sort of problem before.
>
>It was due to some legacy DBs where I work having a few missing extension 
>membership registrations.
>
>pg_dump wants to include any such things in the output  which may run afoul  
>of same having been already created by the extension load.

Ugh! That would explain why it's only on some installs and only on the
"older" ones. Thanks for the info!

Did you ever find any work around? Or is it just a matter of recognizing
what's going on, ignore those "warnings", and moving to a later version to
get past this so it doesn't happen again?

Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

RE: pg_restore issues with intarray

[Kevin Brannen]

>From: Adrian Klaver 
>On 9/5/19 5:05 PM, Kevin Brannen wrote:
>>
>> It feels like the restore is adding the intarray extension, which does
>> a CREATE OPERATOR FAMILY on its own, then later the restore does
>> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
>> happen on most of our databases, just a few. It's maddening to me.
>>
>What does \dx show in the database you taking the dump from?

Sadly, I don't have access to that system.

>What if you do a restore to a file only the schema e.g.:
>
>pg_restore -s -f some_file.sql
>
>This will create a plain text version of only the schema objects in 
>some_file.sql instead of restoring to the database. It might help shed some 
>light.

No CREATE EXTENSION or CREATE OPERATOR FAMILY statements.

Jerry's post indicates this is something that just happens with some older
versions and it seems I got unlucky. I do have a work around (ignore) but
I'd rather be proactive in knowing I'm ignoring something I should be and
not ignoring meaningful errors.

Thanks for the help Adrian, I really appreciate it!
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: pg_restore issues with intarray

[Jerry Sievers]

Kevin Brannen  writes:

>>From: Jerry Sievers 
>>
>>>Kevin Brannen  writes:
>>>
>>> It feels like the restore is adding the intarray extension, which does
>>> a CREATE OPERATOR FAMILY on its own, then later the restore does
>>> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
>>> happen on most of our databases, just a few. It's maddening to me.
>>>
>>
>>I've seen this sort of problem before.
>>
>>It was due to some legacy DBs where I work having a few missing extension 
>>membership registrations.
>>
>>pg_dump wants to include any such things in the output  which may run afoul  
>>of same having been already created by the extension load.
>
> Ugh! That would explain why it's only on some installs and only on the
> "older" ones. Thanks for the info!
>
> Did you ever find any work around? Or is it just a matter of recognizing
> what's going on, ignore those "warnings", and moving to a later version to
> get past this so it doesn't happen again?

Try running \dx+ for intarray on one of your deviant systems.  You may
find the item pg_dump is trying to be explicit about *missing* from the
extension member list.

In such a case, see the ALTER EXTENSION ADD... which can be run manually
to register whatever is missing.

You will do this on the system that is *origin* for the pg_dump.

This is what we've done.

YMMV




postgres# \dx+ intarray
   Objects in extension "intarray"
  Object description
  
--
 function boolop(integer[],query_int)
 function bqarr_in(cstring)
 function bqarr_out(query_int)
 function 
ginint4_consistent(internal,smallint,integer[],integer,internal,internal,internal,internal)
 function 
ginint4_queryextract(integer[],internal,smallint,internal,internal,internal,internal)
 function g_intbig_compress(internal)
 function g_intbig_consistent(internal,integer[],smallint,oid,internal)
 function g_intbig_decompress(internal)
 function g_intbig_penalty(internal,internal,internal)
 function g_intbig_picksplit(internal,internal)
 function g_intbig_same(intbig_gkey,intbig_gkey,internal)
 function g_intbig_union(internal,internal)
 function g_int_compress(internal)
 function g_int_consistent(internal,integer[],smallint,oid,internal)
 function g_int_decompress(internal)
 function g_int_penalty(internal,internal,internal)
 function g_int_picksplit(internal,internal)
 function g_int_same(integer[],integer[],internal)
 function g_int_union(internal,internal)
 function icount(integer[])
 function idx(integer[],integer)
 function intarray_del_elem(integer[],integer)
 function intarray_push_array(integer[],integer[])
 function intarray_push_elem(integer[],integer)
 function _intbig_in(cstring)
 function _intbig_out(intbig_gkey)
 function _int_contained(integer[],integer[])
 function _int_contained_joinsel(internal,oid,internal,smallint,internal)
 function _int_contained_sel(internal,oid,internal,integer)
 function _int_contains(integer[],integer[])
 function _int_contains_joinsel(internal,oid,internal,smallint,internal)
 function _int_contains_sel(internal,oid,internal,integer)
 function _int_different(integer[],integer[])
 function _int_inter(integer[],integer[])
 function _int_matchsel(internal,oid,internal,integer)
 function _int_overlap(integer[],integer[])
 function _int_overlap_joinsel(internal,oid,internal,smallint,internal)
 function _int_overlap_sel(internal,oid,internal,integer)
 function _int_same(integer[],integer[])
 function intset(integer)
 function intset_subtract(integer[],integer[])
 function intset_union_elem(integer[],integer)
 function _int_union(integer[],integer[])
 function querytree(query_int)
 function rboolop(query_int,integer[])
 function sort_asc(integer[])
 function sort_desc(integer[])
 function sort(integer[])
 function sort(integer[],text)
 function subarray(integer[],integer)
 function subarray(integer[],integer,integer)
 function uniq(integer[])
 operator class gin__int_ops for access method gin
 operator class gist__intbig_ops for access method gist
 operator class gist__int_ops for access method gist
 operator family gin__int_ops for access method gin
 operator family gist__intbig_ops for access method gist
 operator family gist__int_ops for access method gist
 operator ~(integer[],integer[])
 operator <@(integer[],integer[])
 operator |(integer[],integer)
 operator |(integer[],integer[])
 operator -(integer[],integer)
 operator -(integer[],integer[])
 operator @>(integer[],integer[])
 operator @(integer[],integer[])
 operator &(integer[],integer[])
 operator &&(integer[],integer[])
 operator #(integer[],integer)
 operator +(integer[],integer)
 operator +(integer[],integer[])
 operator @@(integer[],query_int)
 operator #(NONE,integer[])
 operator ~~(query_int,integer[])
 type intbig_gkey
 type query_int
(76 rows)

meta_a:postgres# \h alter ext

RE: pg_restore issues with intarray

[Kevin Brannen]

>From: Jerry Sievers 
>
>
>Try running \dx+ for intarray on one of your deviant systems.  You may find 
>the item pg_dump is trying to be explicit about *missing* from the extension 
>member list.
>
>In such a case, see the ALTER EXTENSION ADD... which can be run manually to 
>register whatever is missing.
>
>You will do this on the system that is *origin* for the pg_dump.
>
>This is what we've done.

Hmm, I was really hoping for something on the restore side instead of the
dump side as that makes it harder, but we don't always get what we want. :)

Still, at least was have something to pursue and this is now documented
on the mailing list.

Thanks!
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: pg_restore issues with intarray

[Adrian Klaver]

On 9/6/19 8:45 AM, Kevin Brannen wrote:

From: Adrian Klaver 
On 9/5/19 5:05 PM, Kevin Brannen wrote:


It feels like the restore is adding the intarray extension, which does
a CREATE OPERATOR FAMILY on its own, then later the restore does
CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
happen on most of our databases, just a few. It's maddening to me.


What does \dx show in the database you taking the dump from?


Sadly, I don't have access to that system.


What if you do a restore to a file only the schema e.g.:

pg_restore -s -f some_file.sql

This will create a plain text version of only the schema objects in 
some_file.sql instead of restoring to the database. It might help shed some 
light.


No CREATE EXTENSION or CREATE OPERATOR FAMILY statements.


I do not see that as possible. Something ran CREATE OPERATOR FAMILY:

pg_restore: [archiver (db)] could not execute query: ERROR:  operator 
family "gin__int_ops" for access method "gin" already exists

Command was: CREATE OPERATOR FAMILY gin__int_ops USING gin;

So either someone opened up template0 and loaded the extension into it 
or the dump file has the CREATE OPERATOR FAMILY in it. Another thought 
search on intarray.




Jerry's post indicates this is something that just happens with some older
versions and it seems I got unlucky. I do have a work around (ignore) but
I'd rather be proactive in knowing I'm ignoring something I should be and
not ignoring meaningful errors.


We are not dealing with magic here, there is some mechanism at work. 
Dollars to donuts there is an unpackaged version of the extension in one 
or more of the source databases. See below for more information on this:


https://www.postgresql.org/docs/11/extend-extensions.html

38.16.5. Extension Updates

You will need to look at the source databases in situ.




Thanks for the help Adrian, I really appreciate it!




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

Re: PG11.2 - wal_level =minimal max_wal_senders = 0

[Jason Ralph]

Thanks,
I currently have the systems running a parallel pg_dump each night to a 
separate partition mounted on the VM. Then I perform a full backup of the VM 
and all mounted drives each night. Would this be affected by disabling wal 
archiving? I noted that I understood wal archiving was affected in my initial 
question. But I was not able to see how it could affect my setup. Please school 
me if I am being naive.

Jason Ralph

From: Benoit Lobréau 
Sent: Friday, September 6, 2019 3:41:44 AM
To: Luca Ferrari 
Cc: Jason Ralph ; 
pgsql-general@lists.postgresql.org 
Subject: Re: PG11.2 - wal_level =minimal max_wal_senders = 0

Hi,

Make sure that the new settings of wal_level and max_wal_senders don't 
interfere with your backup strategy.
The two parameters have an impact on wal archiving as well.

Ben.

Le jeu. 5 sept. 2019 à 08:33, Luca Ferrari 
mailto:fluca1...@gmail.com>> a écrit :
On Wed, Sep 4, 2019 at 10:44 PM Jason Ralph
mailto:jra...@affinitysolutions.com>> wrote:
>
> Thank you Luca,
> Can I ask one more related question, I have no need for replication as noted 
> below, can I safely disable the worker process via the setting below? In my 
> sandbox it does turn off the logical replication launcher, I just wanted to 
> be sure I am not affecting anything other than replication with this setting.
>
>
> max_logical_replication_workers = 0 # taken from max_worker_processes
> # (change requires restart)
>

Quite frankly I've never done, but I don't see why you should not turn it off.

Luca


This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.