Re: Identity column data type difference in PostgreSQL and Oracle

[Peter Eisentraut]

On 03.05.24 12:57, Muhammad Ikram wrote:
Tables which have an identity column in Oracle when migrated to 
PostgreSQL, the data type of Identity column is changed to bigint from 
number by the tools. This causes the size of column to be reduced to max 
value supported by bigint which is way lower than the oracle  number max.


Sequences in PostgreSQL are limited to bigint, there is nothing that can 
be changed about that.  But you are approximately the first person to 
complain that the range of ids provided by bigint is too small, so maybe 
it's worth reconsidering whether it would work for you.  If it really is 
too small, maybe a uuid would work.  But yes, you'll need to make some 
adjustments to the rest of your database or application code if you make 
such changes.

Re: (When) can a single SQL statement return multiple result sets?

[Peter Eisentraut]

On 11.04.24 01:02, Tom Lane wrote:

And if not, why can't I write a stored procedure
or function that returns multiple result sets?


[ shrug... ] Lack of round tuits, perhaps.  We don't have any
mechanism today whereby a stored procedure could say "please ship
this resultset off to the client, but I want to continue afterwards".
But you can do that in other RDBMSes and probably somebody will be
motivated to make it possible in Postgres.


The development of this feature was the subject of this thread: 
https://www.postgresql.org/message-id/flat/6e747f98-835f-2e05-cde5-86ee444a7...@2ndquadrant.com


But it has not concluded successfully yet.

Re: Non-Stored Generated Columns

[Peter Eisentraut]

On 28.02.24 16:09, Dominique Devienne wrote:

We use generated columns extensively.
And we have foreign-keys attached to those generated columns.
The fact they are always Stored thus wastes space in our case.
Any chance PostgreSQL might gain actual virtual / non-stored generated 
columns soon? Ever?


I plan to work on this for PG18.

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

[Peter Eisentraut]

On 19.02.24 04:32, Darryl Green wrote:
I note that in Postgresql 16 identity column handling in partitioned 
tables has been aligned to the view that the partitioned table as a 
whole is a single relation (and so a unique identity across partitions). 
This makes sense.


The change that I think you are referring to is in the not-yet-released 
version 17.

Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version

[Peter Eisentraut]

On 10.10.23 08:22, Laurenz Albe wrote:
Apart from that, it is a good idea to use table names that are standard 
SQL identifiers, so that you don't have to double quote them all the time.


FWIW, the Chinese character sequences posted here would be valid 
unquoted identifiers if PostgreSQL implemented standard SQL 
correctly/completely.

Re: function signature allow "default" keyword

[Peter Eisentraut]

On 15.05.23 10:33, jian he wrote:

function idea.
allow function calling using the default keyword for any of the input 
arguments.


example: https://dbfiddle.uk/FQwnfdmm 
So something like this "SELECT * FROM customer_orders(2579927, 
'order_placed_on DESC', default, 2);"

should work.


This is currently not supported by PostgreSQL.  I have half a patch for 
it, though, so maybe we'll get to it one day.

Re: ICU, locale and collation question

[Peter Eisentraut]

On 10.05.23 07:02, Kirk Wolak wrote:
On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut 
<mailto:peter.eisentr...@enterprisedb.com>> wrote:


On 09.05.23 08:54, Oscar Carlberg wrote:
 > Our initdb setup would then look like this for compatibility;
 > -E 'UTF-8'
 > --locale-provider=icu
 > --icu-locale=sv-SE-x-icu
 > --lc_monetary=sv_SE.UTF-8
 > --lc-numeric=sv_SE.UTF-8
 > --lc-time=sv_SE.UTF-8
 > --lc-messages=en_US.UTF-8
 >
 > Should we still provide createdb with --lc-collate=C and
--lc-ctype=C,
 > or should we set those to sv_SE.UTF-8 as well?

You should set those to something other than C.  It doesn't matter much
what exactly, so what you have there is fine.

Setting it to C would for example affect the ability of the text search
functionality to detect words containing non-ASCII characters.

Doesn't searching LIKE 'abc%'  behave much better for C than others.  
This was the driving force for choosing C for us.

[EXPLAIN made it clear that it was range bound until 'abd']


For that use, I would recommend making an index specifically on the 
tables you need, instead of switching the whole database.


Also, if you are using the ICU provider for the database, then setting 
lc_collation=C wouldn't even affect LIKE optimization, because the ICU 
locale would be used.

Re: ICU, locale and collation question

[Peter Eisentraut]

On 09.05.23 08:54, Oscar Carlberg wrote:

Our initdb setup would then look like this for compatibility;
-E 'UTF-8'
--locale-provider=icu
--icu-locale=sv-SE-x-icu
--lc_monetary=sv_SE.UTF-8
--lc-numeric=sv_SE.UTF-8
--lc-time=sv_SE.UTF-8
--lc-messages=en_US.UTF-8

Should we still provide createdb with --lc-collate=C and --lc-ctype=C, 
or should we set those to sv_SE.UTF-8 as well?


You should set those to something other than C.  It doesn't matter much 
what exactly, so what you have there is fine.


Setting it to C would for example affect the ability of the text search 
functionality to detect words containing non-ASCII characters.

Re: PostgreSQL optimizations for CoW FS

[Peter Eisentraut]

On 22.02.23 10:41, HECTOR INGERTO wrote:
Let’s say we have to run a PostgreSQL instance on top of a copy on write 
filesystem like ZFS or BTRFS. In adittion to set full_page_writes = off, 
what other optimizations can be done on the PostgreSQL side?


Look at the settings wal_init_zero and wal_recycle.

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

[Peter Eisentraut]

On 07.02.23 11:43, Sebastien Flaesch wrote:

select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname


Just a side note: You might find the quote_ident() function useful.

Re: Switching identity column to serial

[Peter Eisentraut]

On 04.02.23 21:55, Erik Wienhold wrote:

  Why doesn't this work?
  BEGIN;
  DROP SEQUENCE t_id;


[This won't work, you need to use ALTER TABLE / DROP IDENTITY.]


  CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id;
  ALTER SEQUENCE new_t_id_seq OWNER TO new_owner;
  SELECT setval('new_t_id', (SELECT MAX(id) FROM t));
  SELECT nextval('new_t_id');
  COMMIT;

This should work but I want to preserve the existing sequence instead of
re-creating it with the same properties.  That's why I was looking for a
shortcut (also code golfing and sheer curiosity).


This is possible in principle, by implementing the inverse of the 
"Upgrading" recipe from 
. 
But if you don't want to figure that out, I think dropping and 
recreating the sequences as suggested here seems the best solution.

Re: Fwd: Postgre und GIS Umstellung von MD5 auf SCUM SHA 256

[Peter Eisentraut]

You can send questions in German to .

On 23.11.22 11:22, Nikolas Hanry wrote:

Liebe Community,

Wir haben versucht, die Authentizierung  in unserer postgresql-Umgebung 
von MD5 zu SCRUM SHA 256 zu ändern (PostgreSQL V.11)



- Stoppen Sie die Dienste und beenden alle aktiven Verbindungen
- mit alter and set pw den Editor-Benutzer auf sha (psql) geändert - 
überprüft, ob die pw-Änderung erfolgreich war (psql und pg4admin)
- versucht, ein neues Dokument vom GIS-Desktop aus zu öffnen und zu 
bearbeiten --> Fehlermeldung. Verbindung nicht vorhanden
- nach ein paar minuten synchronisierte sich das pw und änderte sich 
automatisch zurück auf MD5


Meine Fragen sind:
Ist es möglich, mit GIS-Dateien (Desktop und Web) zu arbeiten, nachdem 
pw auf SHA 256 geändert wurde? Wir  möchten es auch nicht direkt für 
gisadmin (Owner all DB'S) ändern


Werden die Änderungen aufgrund der Synchronisierung automatisch auf MD5 
zurückgesetzt? In unserer Testumgebung haben wir es auch gemacht und 
erfolgreich getestet und die einzige Unterschied war, dass wir auch 
pg_hba_conf geändert haben

host     postgres      all        172.xxx.xxx.83/32   scram-sha-256
sowie   DB  USER      ADDRESS     METHOD    --> trust and scram-sha-256 
überall gesetzt.


Vielen Dank im Voraus
Mit freundlichen Grüßen
Nikola Anri

Re: Postgres calendar?

[Peter Eisentraut]

On 04.10.22 23:02, Bruce Momjian wrote:

Would people be interesting in subscribing to a Postgres calendar that
includes dates for minor releases, final minor release dates for major
versions, commit fests, and even Postgres events?


Events already exists: 
https://calendar.google.com/calendar/ical/57a0a03a4925700a1bdfeef20cbb341330aa3433ebe678e9d5cd34f605a2fd92%40group.calendar.google.com/public/basic.ics

Re: lippq client library and openssl initialization: PQinitOpenSSL()

[Peter Eisentraut]

On 11.09.22 23:43, Daniel Gustafsson wrote:

On 11 Sep 2022, at 23:35, Tom Lane  wrote:

Daniel Gustafsson  writes:

On 11 Sep 2022, at 17:08, Tom Lane  wrote:

Don't believe so.  The HAVE_CRYPTO_LOCK stuff is all obsolete and
not compiled if you built against 1.1.0.  The only thing left that
will happen if you don't call PQinitOpenSSL is an extra call to
OPENSSL_init_ssl, which should be harmless as far as I can see
from the OpenSSL docs.



To the best of my knowledge, thats entirely correct.


Should we document these functions as obsolete when using
OpenSSL >= 1.1.0 ?


Given that 1.1.0+ is very common, it's probably not a bad idea to document them
as obsolete but harmless.  Unless you beat me to it I can propose a patch.


AFAICT, RHEL 7 ships with an older version.  I think that's still pretty 
widespread.

Re: Would it be possible to add functions to tab-completion in psql?

[Peter Eisentraut]

On 16.08.22 16:14, hubert depesz lubaczewski wrote:

Specifically, I'd ask what is the harm of increasing what tab completion
can do by a lot - for example, make it tab-complete fields from all
tables. And make it possible to tab-complete column name anywhere in
where clause. But function name in select would be (for me(!)) great
first step, and I can't really see the drawbacks, aside from using
developer time to work on it.


I think in this case it would be straightforward to write a patch and 
then we can see what the experience is in practice.  I agree this could 
be a useful feature.

Re: unable to understand query result

[Peter Eisentraut]

On 06.07.22 11:31, Stefan Froehlich wrote:

I have a database returing these result sets for two almost
identical queries:

#v+
$ select id, pid, length(pid), md5(pid) from product where pid  like '800';
  id   | pid | length |   md5
--+-++--
  3594 | 800 |  7 | 60b5792913f4acbccf45c281fa9e3c9f
(1 row)

$ select id, pid, length(pid), md5(pid) from product where pid  like '%800';
  id   | pid | length |   md5
--+-++--
  3594 | 800 |  7 | 60b5792913f4acbccf45c281fa9e3c9f
   722 | 800 |  7 | 60b5792913f4acbccf45c281fa9e3c9f
(2 rows)
#v-


Check the EXPLAIN output for the two queries.  Maybe there is index 
corruption somewhere.

Re: ADD COLUMN ts tsvector GENERATED too slow

[Peter Eisentraut]

On 06.07.22 10:42, Florents Tselai wrote:

I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a table 
holding (key text, text text,) of around 50M rows.
These are text fields extracted from 4-5 page pdfs each.

I’m adding the following generated col to keep up with tsvectors

ALTER TABLE docs_text ADD COLUMN ts tsvector GENERATED ALWAYS AS 
(to_tsvector(’simple', left(text, 1048575))) STORED

I expect this to be slow, but it’s been running for 18hrs already and I 
certainly hope I’ve done something wrong and there’s a smarter way.


Maybe it's stuck on a lock?  ALTER TABLE / ADD COLUMN requires an 
exclusive lock on the table.

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Peter Eisentraut]

On 23.06.22 20:21, Adrian Klaver wrote:
Not sure why that is necessary? Is seems this is low hanging fruit 
that could dealt with by the equivalent of lower('en_US.UTF-8') = 
lower('en_US.utf-8').


Well that was clear as mud. My point was that I don't see why the end 
user should have to do this when it could be handled internally in the 
pg_restore code.


There are also cases where on one system the locale is spelled 
xx_YY.UTF8 and on another xx_YY.UTF-8.  If we start adjusting for case, 
should we adjust for this as well?  What other cases are there?


So this would need a bit more research.

Re: Error dydl : image not found when trying to install pg on Catalina

[Peter Eisentraut]

On 17.04.22 13:28, cecile rougnaux wrote:

dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.70.dylib


Whenever libicu's major version changes, you need to rebuilt the 
postgresql package.

Re: Strange results when casting string to double

[Peter Eisentraut]

On 16.02.22 14:27, Carsten Klein wrote:
AFAIK, this conversion is done by internal function float8in, which, 
when called directly, yields the same results:


SELECT float8in('1.56');

--> 1.55   (wrong!)   on one server, and
--> 1.56   (correct!) on all other servers.


float8in() really just calls the operating system's strtod() function. 
I would test that one directly with a small C program.


It's also possible that different compiler options lead to different 
optimizations.

Re: How to read an external pdf file from postgres?

[Peter Eisentraut]

On 12.01.22 12:16, Amine Tengilimoglu wrote:
      I want to read an external pdf file from postgres. pdf file will 
exist on the disk. postgres only know the disk full path as metadata. Is 
there any software or extension that can be used for this? Or do we have 
to develop software for it?  Or what is the best approach for this? I'd 
appreciate it if anyone with experience could make suggestions.


You could write a function in PL/Perl or PL/Python to open and read the 
file and process the PDF data, using some third-party module that surely 
exists somewhere.

Re: PGBouncer logs explanation required

[Peter Eisentraut]

On 19.12.21 12:50, Shubham Mittal wrote:
2021-11-25 14:45:45.244 IST [18307] LOG C-0x6ae270: 
sit/postgres@abcdlogin attempt: db=sit user=postgres tls=no
2021-11-25 14:45:45.299 IST [18307] LOG S-0x6b4f48: sit/postgres@abcd 
new connection to server (from abcd)
2021-11-25 14:46:17.843 IST [18307] LOG S-0x6b4d10: sit/postgres@abcd 
*closing because: query timeout (age=185s)*
2021-11-25 14:46:17.843 IST [18307] LOG C-0x6ae038: sit/postgres@abcd 
*closing because: query timeout (age=185s)*
2021-11-25 14:46:17.843 IST [18307] WARNING C-0x6ae038: 
sit/postgres@*abcd pooler error: query timeout*
2021-11-25 14:46:25.763 IST [18307] LOG stats: 0 xacts/s, 0 queries/s, 
in 30 B/s, out 141 B/s, xact 3660 us, query 4362 us, wait 152 us


Does this log mean that connection is closed and returned to the pool 
and can be reused again?? Or something else?? Please help.


A closed connection cannot be reused, since it's closed.  But after the 
connection is closed, there is a free slot in the pool to possibly open 
a new connection if needed.

Re: error connecting to pgbouncer admin console

[Peter Eisentraut]

On 07.12.21 17:10, Zwettler Markus (OIZ) wrote:

I did a pgbouncer configuration using the following ini file:



[databases]
* = host=localhost port=5433 auth_user=pgbouncer


Using the name "pgbouncer" for auth_user is buggy.  Try using a 
different name.

Re: Are Foreign Key Disabled During Logical Replication Initial Sync?

[Peter Eisentraut]

On 07.12.21 08:51, Avi Weinberg wrote:

Just to clarify, they are disabled during initial sync only or are always 
disabled on subscriber side?
Are all triggers disabled during initial sync or just foreign keys?


All triggers are by default disabled on replicas.  See the ALTER TABLE 
clauses DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER to change this.



How can I know that initial sync completed for all tables?  Is it checking when 
pg_subscription_rel.srsubstate is 'i' or 'd' for all tables or there is a 
better way?


There are various ways to phrase this.  The test suite often uses this 
query:


SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN 
('r', 's');

Re: Are Foreign Key Disabled During Logical Replication Initial Sync?

[Peter Eisentraut]

On 06.12.21 15:50, Avi Weinberg wrote:
Does it mean that populating each table is done in a single 
transaction?  If so, when I have tables with foreign keys between them, 
is it guaranteed that logical replication will populates the tables in 
the proper order so the foreign key will be enforced?  Or maybe the 
foreign keys are disabled during initial sync and only after all tables 
are populated the FKs are enabled again.


Foreign keys are disabled on logical replication subscribers.

Re: case insensitive collation of Greek's sigma

[Peter Eisentraut]

On 26.11.21 08:37, Jakub Jedelsky wrote:

postgres=# SELECT
postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US",
postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US"
postgres-# ;
  ?column? | ?column?
--+--
  t        | f
(1 row)

postgres=# SELECT
postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu",
postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en-US-x-icu";
  ?column? | ?column?
--+--
  f        | t
(1 row)

If I could start, I think both results are wrong as both should return 
True. If I got it right, in the background there is a lower() function 
running to compare strings, which is not enough for such cases (until 
the left side isn't taken as a standalone word).


The reason for these results is that for multibyte encodings, a ILIKE b 
basically does lower(a) LIKE lower(b), and


select lower('ΣΣ' COLLATE "en_US"), lower('ΣΣ' COLLATE "en-US-x-icu");
 lower | lower
---+---
 σσ| σς

Running lower() like this is really the wrong thing to do.  We should be 
doing "case folding" instead, which normalizes these differences for the 
purpose of case-insensitive comparisons.

Re: Issues cross-compiling libpq 14.x to MacOS armv8

[Peter Eisentraut]

On 30.11.21 22:04, Tom Lane wrote:

Daniel Gustafsson  writes:

On 30 Nov 2021, at 20:59, Tom Lane  wrote:
AFAICS this is the only test in our configure script that is a hard
fail when cross-compiling, and I don't see a reason for it to be that.
We could just assume that /dev/urandom will be available --- that's no
worse than a lot of the other optimistic assumptions that configure
makes in that mode.



Agreed, I don't see a problem with that.  I'm not terribly familiar with
supporting cross compilation in autoconf, would a reasonable approach be to
just remove the check altogether like the below sketch?


It seems like a useful test when *not* cross compiling, which is most
of the time.


You still don't know whether the file will exist on the system you are 
running this on.



(I'm a bit surprised that the AC_CHECK_FILE macro doesn't provide
an action-if-cross-compiling option, but it apparently doesn't.)


Because you are only supposed to look for files that you need during the 
build.

Re: Understanding the differences between Temporal tables, CDC and Time Series.

[Peter Eisentraut]

On 10.11.21 07:43, SQL Padawan wrote:
I'm looking into Temporal Tables (TT - i.e. System/Application time 
versioning) but I would like to understand the differences between TTs 
and two other features (which appear similar in many ways) - Change Data 
Capture and Time Series.


Are Temporal Tables (in a sense) a query framework framework around CDC?

And if that's the case, what then are Time Series - which are 
specifically designed to host (time) changing data?


Are Temporal Tables (again, in a sense) Time Series databases which only 
record changes in time(-stamps)?


I'm unclear as to the "philosophical" distinctions here and would be 
grateful if anybody could explain the diffence(s) between them?


A time series database contains data records containing a time stamp (a 
point in time).  These records represent things that happened, for 
example log files, measurements, events.  This data is then usually used 
for analytics, such as, how many web site visitors did we have per hour 
over the last week.


A temporal database contains records that have a timestamp range (in 
PostgreSQL terms, also known as period or interval, delimited by two 
points in time).  There are (at least) two ways to use this.  One is 
that the database system automatically records when a record was 
inserted, updated, and deleted, so that historical versions of records 
are automatically kept, and you can then query, what was the value of 
this at this time, or more generally what was the result of this query 
at this time.  Use cases here are record-keeping, auditing, and the 
like.  The other is that the database users themselves enter when a 
record is supposed to be valid, and the database system takes this into 
account in queries.  So you could have product prices or salaries that 
automatically change at a certain time.  This is basically a tool to 
make development of certain database applications more convenient.


So these two concepts are really totally separate and independent 
things, except that they have a word based on "time" in their name.

Re: lower() and unaccent() not leakproof

[Peter Eisentraut]

On 26.08.21 16:00, Tom Lane wrote:

Generally speaking, we've been resistant to marking anything leakproof
unless it has a very small code footprint that can be easily audited.

In particular, anything that shares a lot of infrastructure with
not-leakproof functions seems quite hazardous.  Even if you go through
the code and convince yourself that it's OK today, innocent changes
to the shared infrastructure could break the leakproofness tomorrow.


I think the complexity of the implementation of upper() and lower() is 
on the same order as bttextcmp() and similar, so it wouldn't be totally 
out of scope.

Re: lower() and unaccent() not leakproof

[Peter Eisentraut]

On 26.08.21 10:40, Daniel Gustafsson wrote:

On 26 Aug 2021, at 09:58, Peter Eisentraut  
wrote:

On 26.08.21 06:52, David G. Johnston wrote:

On Wednesday, August 25, 2021, Christophe Pettus mailto:x...@thebuild.com>> wrote:
lower() and unaccent() (and most string functions) are not marked as
leakproof.  Is this due to possible locale / character encoding
errors they might encounter?
I think you are partially correct.  Its due to the fact that error messages, 
regardless of the root cause, result in the printing of the input value in the 
error message as context, thus exists a leak via a violation of “ It reveals no 
information about its arguments other than by its return value. ”


I think if you trace the code, you might find that lower() and upper() can't 
really leak anything.  It might be worth taking a careful look and possibly 
lifting this restriction.


Wouldn’t the difference in possible error messages in upper/lower be able to
leak whether the input is ascii or wide chars, and/or the collation?


Yeah, but there aren't any error messages that relate to the argument 
string, if you look through the code.  There isn't any "could not find 
lower case equivalent of %s" or anything like that.  Once you have found 
the right collation and locale and server encoding and have allocated 
some memory, the conversion always succeeds.


The collation is not secret, it's determined by parse analysis.

Re: lower() and unaccent() not leakproof

[Peter Eisentraut]

On 26.08.21 06:52, David G. Johnston wrote:
On Wednesday, August 25, 2021, Christophe Pettus > wrote:


lower() and unaccent() (and most string functions) are not marked as
leakproof.  Is this due to possible locale / character encoding
errors they might encounter?


I think you are partially correct.  Its due to the fact that error 
messages, regardless of the root cause, result in the printing of the 
input value in the error message as context, thus exists a leak via a 
violation of “ It reveals no information about its arguments other than 
by its return value. ”


I think if you trace the code, you might find that lower() and upper() 
can't really leak anything.  It might be worth taking a careful look and 
possibly lifting this restriction.

Re: sort order

[Peter Eisentraut]

On 27.07.21 19:07, Marc Millas wrote:

so, obviously, both lc_collate knows about the é
but obviously, too, they do behave differently on the impact of the 
beginning white space.


I didn't see anything about this behaviour on the doc, unless the 
reference at the libc should be understood as please read and test libc 
doc on each platform.

So my first question is: why ?
My second question is: how to make the centos postgres behave like the 
w10 one ??
ie. knowing about french characters AND taking beginning white spaces 
into account ?


There are multiple standard ways to deal with space and punctuation 
characters when sorting.  See 
 
for a description.  Not all collation providers implement all of them, 
but the behavior you happen to get is usually one of them.  The centos 7 
behavior corresponds to "shift-trimmed", the Windows one appears to 
match "non-ignorable".  If you want to get that latter one on Linux as 
well, you can use the ICU locales, which also default to non-ignorable. 
For example


select * from test order by ble collate "fr-x-icu";

matches your Windows output for me.

Re: pg_wal lifecycle

[Peter Eisentraut]

On 13.07.21 09:07, Luca Ferrari wrote:

I'd like to see if I get it right about pg_wal: it grows up to pretty
much max_wal_size, at which point a checkpoint is triggered.
If the server triggers a timed checkpoint before the pg_wal is at
max_wal_size, the system recycles the wals thus keeping the pg_wal
size lower than max_wal_size.
Is this correct? In particular, what is in simple words, the
discrimination between recycling a segment and creating a new one?


Recycling in this context just means that instead of creating a new WAL 
file for new WAL traffic, it reuses an old file.  So if you have WAL 
files 5, 6, 7, 8, 9, and you know that you don't need 5 and 6 anymore, 
when you need to start WAL file 10, instead of creating a new file "10", 
the system just renames "5" to "10" and starts overwriting what was in 
there.  This is just an optimization to use the file system better; it 
doesn't affect the logical principles of what is going on.

Re: libicu global support

[Peter Eisentraut]

On 11.07.21 23:52, Jakub Jedelsky wrote:
during the adoption of Centos 8 on our servers we ran into problems with 
Postgresql (13.3), glibc (delivered by the Centos) and performance of 
sorting. Because of that we're planning to use the ICU collations 
(en-x-icu), but the current implementation is quite complicated to adopt 
as there isn't support of global setup per cluster (initdb) nor creating 
of database.


So, my silly question: is there any chance a work can be done on it with 
a new version anytime soon?


It's definitely high on various developers' wishlists, but I am not 
aware of any activity on it right now.

Re: pg_upgrade as a way of cloning an instance?

[Peter Eisentraut]

On 07.07.21 08:53, Luca Ferrari wrote:

Hi all,
someone pointed me out that pg_upgrade can be used to do a clone of
the database, specifying the same binaries such as

pg_upgrade -B /usr/pgsql-13/bin -b /usr/pgsql-13/bin -D /data/clone -d /data/src

I tested it and it seems to work, even if I don't see any point in
running it (and most notably it requires a downtime on the original
cluster).
Any opinion about that?


Yeah, seems pretty pointless.  You can just copy the data directory 
directly and get the same effect.  pg_upgrade basically does a data 
directory copy plus some extra stuff to convert the system catalogs 
between versions, and if you don't need that second part, you might as 
well do the first part directly.

Re: Question about how to handle numeric (decimal) data types while using libpq

[Peter Eisentraut]

On 06.07.21 13:04, Sudheer H R wrote:

I am trying to use libpq for interfacing with PostgreSQL from a C/C++ based 
application.

I have tried to use binary format of data for both sending and receiving data 
to and from server (resultFormat = 1).

As I understand most binary types, int, float etc… are encoded in bing-endian 
byte order and they can be converted to appropriate host specific memory 
representations.

However NUMERIC datatype is a more complex structure with multiple ints and 
pointers.

I would like to know if there are published library functions that convert (to 
and from) NUMERIC field to say, long double datatype


There is a third-party library called libpqtypes out there that adds 
support for handling specific types on the libpq side.  But I'm not sure 
how up to date it is.

Re: When to REINDEX a serial key?

[Peter Eisentraut]

On 06.07.21 14:19, Ron wrote:

On 7/6/21 4:52 AM, David Rowley wrote:

On Tue, 6 Jul 2021 at 21:35, Ron  wrote:
The legacy RDBMS which I used to manage has a tool for analyzing (not 
in the Postgresql meaning of the word) an index, and displaying a 
histogram of how many layers deep various parts of an index are.  
Using that histogram, you can tell whether or not an index needs to 
be rebuilt.


How does one get the same effect in Postgresql?

There are a few suggestions in
https://wiki.postgresql.org/wiki/Show_database_bloat


How does bloat relate to a lopsided b-tree?


There is no such thing as a lopsided B-tree, because a B-tree is by 
definition self-balancing.  Perhaps that answers your original question.


Bloat is generally something people are concerned about when they think 
about reindexing their indexes.  But append-only workloads, such as what 
you describe, normally don't generate bloat.

Re: Set COLLATE on a session level

[Peter Eisentraut]

On 2020-12-04 17:18, Tom Lane wrote:

There is a SET COLLATION command in the SQL standard that does this.
Someone just has to implement it.  It wouldn't be terribly difficult, I
think.


[ squint... ]  Just because it's in the standard doesn't mean it's a
good idea.  It sounds like this is morally equivalent to a GUC that
changes query semantics.  We have learned painfully that such behaviors
are best avoided, because they break things you didn't expect to break.


I think it would be analogous to the schema search path.

Re: Set COLLATE on a session level

[Peter Eisentraut]

On 2020-11-20 08:13, Dirk Mika wrote:

we come from the Oracle world and we have an application that, depending on a 
setting, sends the command ALTER SESSION SET NLS_SORT=... when connecting to 
the database.

Is there a similar way to set a COLLATE for a session in PostgreSQL?

I know that I can specify a COLLATE for a SELECT statement in the ORDER BY 
Clause, but then I would have to adjust the statements in the client and 
statements that are automatically generated by the database components used, 
would not be affected.


There is a SET COLLATION command in the SQL standard that does this. 
Someone just has to implement it.  It wouldn't be terribly difficult, I 
think.

Re: Unable to compile postgres 13.1 on Slackware current x64

[Peter Eisentraut]

On 2020-11-16 18:30, Tom Lane wrote:

In this case, I see one use of the constant TRUE in collationcmds.c,
but I wonder how come that's there given that we deprecated upper-case
TRUE some time ago.


In 2eb4a831e5fb5d8fc17e13aea56e04af3efe27b4, I intentionally left that 
there because it was the documented ICU API.  Now that that's changed, 
we should just do s/TRUE/true/ there as suggested.

Call for translations

[Peter Eisentraut]

Hello,

I would like to remind everyone about the PostgreSQL message translation 
effort and invite those capable of contributing to join in.  Several 
once-well maintained languages have fallen by the wayside a bit lately, 
and of course there is always the possibility of starting a new language 
or reviewing and revising work in existing languages.


If you want to help, see <https://babel.postgresql.org/> for status, 
instructions, and other information.  If there are already active 
translation teams, please communicate with them first.  The mailing list

 is available for general
discussion and coordination of translation activities.

The time is now.  Even though PostgreSQL 13 was just released, 
translation updates for the PostgreSQL 13 stable branch are still welcome.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: PG13 partitioned table logical replication

[Peter Eisentraut]

On 2020-09-10 14:13, Nicolas Sornin wrote:
I just made some test of upcoming version of porstgresql, especially the 
ability to publish partitioned table via root partition.


I tried to setup PG13 to PG12 replication with different partitioning 
schemes.


My first attempt was to replicate time partitioned table in PG13 cluster 
to a regular table in PG12 cluster, but after looking at the logs I saw 
the following error :


2020-09-09 12:21:33.964 CEST [22976] LOG:  logical replication table 
synchronization worker for subscription "rep_part_sub", table 
"stock_sales" has started


2020-09-09 12:21:33.997 CEST [22976] ERROR:  table "public.stock_sales" 
not found on publisher


2020-09-09 12:21:33.999 CEST [20346] LOG:  background worker "logical 
replication worker" (PID 22976) exited with exit code 1


While not being aware of underlying mechanics of logical replication, I 
was expecting this use case to work given table names and column 
names/types are the same on both sides.


Is this the intended behavior and simply out of the scope of the changes 
introduced by PG13 ?


When the subscriber running on PG12 is trying to fetch information about 
the table from the publisher, it is looking for a real table, which it 
doesn't find, because it's a partitioned table.  So this combination 
doesn't work.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: SSL between Primary and Seconday PostgreSQL DBs

[Peter Eisentraut]

On 2020-09-03 14:01, Susan Joseph wrote:
Unfortunately I am not allowed to use wireshark in my environment.  Good 
idea though


The system view pg_stat_ssl, in combination with pg_stat_activity, will 
show you whether a connection is using SSL.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: SSL between Primary and Seconday PostgreSQL DBs

[Peter Eisentraut]

On 2020-08-27 12:57, Susan Joseph wrote:

So has no one done this before?


I'm sure people have done this.  But I suggest that if you ask a 
question on this mailing list, you ask something more concrete, like, I 
tried to do this, and got stuck here, and tried this and got this error. 
 People can help with that sort of thing.  What we have here is a 
complex security setup and you are asking people to do an open-ended 
review.  No one wants to do that.



-Original Message-
From: Susan Joseph 
To: pgsql-gene...@postgresql.org 
Sent: Mon, Aug 24, 2020 10:10 am
Subject: SSL between Primary and Seconday PostgreSQL DBs

I have setup a Primary and Secondary PostgreSQL DBs.  They were setup up 
with basic replication then I went back and modified them to use SSL.  I 
am just not sure if I did it correctly.  Everything is working but I 
want to make sure I have the settings correctly.  I am using PostgreSQL 
11.2.


  * I have a PKI that I stood up so I issued 2 server certificates one
for each database from my CA.
  * Primary server certificate - Primary Database
  o The FQDN and IP address are set in the SAN field.
  o FQDN is also the CN in the DN
  o Key Usage is set to Digital Signature and Key encipherment
  o EKU is set to Server Authentication and Client Authentication
  * Rep_user certificate - Secondary Database
  o CN is set to the rep_user account name
  o Key Usage is set to digital signature and key encipherment
  o EKU is set to client authentication
  * Each certificate file contains the certificate and the subCA
certificate who issued the certificate and put in a file called
server.crt for the Primary and client.crt for the secondary.
  * The key for each certificate is stored in a separate file
unencrypted (I have questions about this later on) in a file called
server.key and client.key
  * The server.crt, server.key, and root.crt are put onto the primary
database server in the /data/pgsql/data location, the owner and
group of these files is set to postgres
  * The client.crt, client.key, and root.crt are put onto the primary
database server in the /data/pgsql/data location, the owner and
group of these files is set to postgres
  * On the Primary in postgresql.conf I set:
  o ssl=on
  o ssl_ca_file='root.crt'
  o ssl_cert_file='server.crt'
  o ssl_key_file='server.key'
  o ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
  * On the Primary in pg_hba.conf I add a replication line:
  o hostssl    replication 
rep_user  cert

  * On the Secondary I set the following information in the
postgresql.conf to:  (DO I NEED TO DO THIS??)
  o ssl=on
  o ssl_ca_file='root.crt'
  o ssl_cert_file='client.crt'
  o ssl_cert_fkey='client.key'
  o ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
  * On the Secondary I edit the recovery.conf file to the following:
  o primary_conninfo = 'user=rep_user passfile=''/data/.pgpass''
host= port=5432 sslmode=verify-ca
sslcert=client.crt sslkey=client.key sslcompression=0
target_session_attrs=any'
  * On the Secondary I edit the pg_hba.conf file and change the rep_user
line to:
  o hostssl  replication rep_user   /32  cert clientcert=1
  * On the Secondary I move the root.crt to /data/pgsql/data/.postgresql
  * Then I restart the databases


My questions are:

  * Do I need to set the information in the Secondary postgresql.conf? 
Originally I did not set this and everything worked but I saw errors

in my log files that said to do SSL these needed to be set so I went
back and set them.  Are there pgsql commands I can run to test that
my SSL is working in both directions?
  * Are my pg_hba.conf files set correctly?  Is that how you get SSL
"turned on" for communications between the primary and the rep_user
account?
  * If I leave my key file encrypted then every time my databases have
to be started have to enter the password.  So you can either leave
the passwords unencrypted and set the permissions on the file to
0600 accessible only by postgres or you can enter the key password
each time the database is started up.  As someone in the security
field I have a tough time leaving the key unencrypted but as some
setting up a production system that is located on a network that you
can't get to without directly accessing the server I feel that is
enough security that I can leave them unencrypted.  Thoughts?
  * Am I missing anything?  There are no videos out there that show how
to stand up a 2 way SSL communication channel between the primary
and secondary, or does anyone have one that they can share?


Thanks,
   Susan






--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgbouncer bug?

[Peter Eisentraut]

On 2020-08-21 19:49, Achilleas Mantzios wrote:


On 21/8/20 7:56 μ.μ., greigwise wrote:

Not sure if this is the right place to post this, but if not someone please
point me in the right direction.

My issue is with pgbouncer 1.14.   This does not seem to happen on 1.13.

If I do a service pgbouncer restart, then anytime I try to connect to my
databases via pgbouncer, I get ERROR: no such user regardless of what user
I'm using.  It's almost like it's not recognizing the auth_query I have
configured.  But then if I issue a reload, then it seems to work fine and I
no longer get the user not found.  The problem is easy enough to work around
as I don't restart pgbouncer all that much, but it doesn't seem like this is
probably the intended behavior.


You may go here :
https://github.com/pgbouncer/pgbouncer/commits/pgbouncer_1_14_0

and review all commits between 1.13 and 1.14


It could be related to the SCRAM pass-through.

Greig, if you have a way to reproduce it, please file a complete bug 
report on GitHub.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication stuck in catchup state

[Peter Eisentraut]

On 2020-06-09 23:30, Dan shmidt wrote:
We have a setup in which there are several master nodes replicating to a 
single slave/backup node. We are using Postgres 11.4.

Recently, one of the nodes seems to be stuck and stopped replicating.
I did some basic troubleshooting and couldn't find the root cause for that.


Have you checked the server logs?  Maybe it has trouble applying a 
change, for example due to a unique constraint or something like that.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical Replication and table bloat

[Peter Eisentraut]

On 2020-06-05 21:53, Martín Fernández wrote:

Yesterday we stumbled upon a performance issue that we were not expecting. We 
are replicating our database using AWS DMS which uses logical replication to 
capture changes. We have some hot tables that get updated very regularly and 
with the DMS turned on we started noticing that in those table, table bloat 
increased considerably ~15 times more free_tuples than the average.

When doing logical replication, the subscriber will hold the tuples that could 
be flagged for reuse until they are sent ? Just trying to understand a little 
bit better how the logical replication is affecting the vacuuming.


As far as vacuum is concerned, it is very similar to a normal client 
session: It may insert tuples, update tuples, delete tuples; update and 
delete create bloat, autovacuum should come along to clean up.  There 
isn't normally any separate vacuum tuning necessary for this, but if you 
are experiencing issues, first treat it like a normal vacuum 
configuration problem.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Postgresql 9.6 -> AWS RDS Postgresql 12.2 with pg_logical

[Peter Eisentraut]

On 2020-05-31 06:33, Igor Polishchuk wrote:
I need to replicate Postgresql 9.6 to  AWS RDS Postgresql 12.2 with 
pg_logical.

AWS RDS Pg 12.2 (target) only supports pg_logical 2.3.0.
Can I use v2.3.1 on the source and v2.3.0 on the target?


In principle yes, since the compatibility is controlled by a protocol 
version that is separate from the release version.


Note, however, that 2.3.1 is a bug-fix release on top of 2.3.0, and 
there is now also a 2.3.2.  Just like with PostgreSQL core, it's 
advisable to use the latest minor release, and if anything goes wrong, 
the first piece of advice would likely be to upgrade.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication troubles

[Peter Eisentraut]

On 2020-05-25 10:19, Anders Bøgh Bruun wrote:
Thank you for that clarification. It helps me understand how things work 
a lot better.
I know this might be a bit off topic, but my challenge here is that we 
are using Patroni (by using Zalando's postgres-operator for Kubernetes), 
and any replication slot not created by Patroni, seems to be removed, 
whenever the master pod restarts. We therefore specify in the Patroni 
config, that a permanent replication slot should be created for our 
usage to do logical replication of some select tables, to our data 
warehouse. That means that the replication slot is created as soon as 
the database is ready to use, which is also before any tables, data or 
publications are created. Can you give me a hint as to what the correct 
way to set this up would be?

Or do I need to try contacting the Patroni devs instead?


That would probably be best.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication troubles

[Peter Eisentraut]

On 2020-05-20 17:16, Anders Bøgh Bruun wrote:
[67] LOG:  logical replication apply worker for subscription 
"widgets_sub" has started
[67] DEBUG:  connecting to publisher using connection string 
"dbname=testdb host=master port=5432 user=repuser password=abc123"
[67] ERROR:  could not receive data from WAL stream: ERROR:  publication 
"widgets_pub" does not exist
   CONTEXT:  slot "my_slot", output plugin "pgoutput", in the change 
callback, associated LSN 0/1674958
[1] DEBUG:  unregistering background worker "logical replication worker 
for subscription 16396"
[1] LOG:  background worker "logical replication worker" (PID 67) exited 
with exit code 1


I can verify that the publication called widgets_pub does exist, and I 
am not seeing any errors on the sending side.


The SQL-file named "works" just has the creation of the replication slot 
moved down to after I insert some data into the table I want to 
replicate. And that works as expected.


You need to create the publication before the replication slot.  The 
walsender's view of the world moves along with the WAL it is 
decoding/sending.  So when the subscription worker connects, it 
initially sees a state as of the creation of the replication slot, when 
the publication did not exist yet.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication troubles

[Peter Eisentraut]

On 2020-05-19 09:22, Anders Bøgh Bruun wrote:
I have run into a (to me) weird issue with logical replication. We are 
running Zalandos postgres-operator in our Kubernetes clusters and have 
recently had a use-case where we wanted to start doing logical 
replication of select tables to a data warehouse, also running postgres. 
It worked as expected at first, but then after a pod-restart in 
Kubernetes, the replication slots that were created for the subscription 
were gone. A bit of reading later, and I learn we need to tell Patroni 
which slots should be permanently available, so we specify a slot and 
try to set this up, but then run into an error which says the 
publication does not exist, even though we can verify that it does. At 
first I suspected Patroni handling the replication slots to be the cause 
of the problem, but about a week's worth of learning and experimenting 
later, I can now reliably replicate the problem in pure postgres. 
Patroni is kind of the catalyst, since my findings are that if the 
replication slot is created before data is inserted into the source 
database, and a publication is created, then it breaks. If the 
replication slot is created after data is inserted and the publication 
is created, then it works. We just can't tell Patroni to not create it 
until some arbitrary point in time. I am guessing this is either a bug 
or a case of us not knowing what we are doing...


What does "works" and "breaks" mean?  Are there error messages shown 
from commands or in the server logs?  Is replication progressing, but 
doing something your are not expecting?  etc.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Seamless Logical Replication during Fail-over

[Peter Eisentraut]

On 2020-05-15 12:05, Moor Ali wrote:
Is there any way to continue logical replication (either in-built or 
using pglogical) between a promoted replica and existing subscribers 
without data loss?


I could find some discussions about fail-over slots and other mechanisms 
for addressing this as part of PostgreSQL 9.6 release. But I am not sure 
if these changes made it to any of the releases.


You are right that the fail-over slot mechanism was supposed to address 
this but it never made it into a release.  I'm not aware of an 
open-source solution for this right now.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: PG12.2 Configure cannot enalble SSL

[Peter Eisentraut]

On 2020-05-15 13:54, Gavan Schneider wrote:

checking openssl/ssl.h usability... no
checking openssl/ssl.h presence... no
checking for openssl/ssl.h... no
configure: error: header file  is required for OpenSSL


Then the next step is to check in config.log for the details of the test 
failure.  (Search the file for "openssl/ssl.h" to find the right place.)


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Mixed Locales and Upgrading

[Peter Eisentraut]

On 2020-04-07 18:41, Don Seiler wrote:
Follow-up question, the locale setting on the host would still be set to 
en_US (as would the postgres and template0 databases). Should I look to 
change that locale on the system to en_US.UTF-8, or even just for the 
postgres user that the DB cluster runs as? What are the ramification for 
doing (or not doing) so?


I think the only place where this would really matter is that psql by 
default sets the client encoding based on the current OS locale setting. 
 So if you don't change the setting to en_US.UTF-8, then you might get 
encoding errors when selecting data that is not representable as LATIN1 
or whatever.  However, if you change the setting, that doesn't mean your 
terminal setup will actually display Unicode correctly.  You said you're 
dealing with mostly ASCII-ish data anyway, so it will probably not make 
a difference.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Reading WALs

[Peter Eisentraut]

On 2020-03-14 14:48, PegoraroF10 wrote:

On my built in logical replication I see that sometimes it tooks a long time
to be updated with master. The question is how can I see what is being done
on replica ? I know I can have a WAL reader, I tried WAL2JSON, it listen all
data which comes from master, but as I understood when I use that plugin
that data is discarded, so, not replicated.

Is that correct ? Can I see what is coming to replica server and apply that
change to server as usual ?


You can have multiple logical decoding streams in parallel that track 
their position independently of each other.  So you can have a logical 
replication stream and wal2json next to each other, and they won't 
interfere with each other.  So what you are considering is possible and 
safe.  (I don't know whether it will give you satisfactory insights.)


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Is it safe to transfer logical replication publication/subscription?

[Peter Eisentraut]

On 2020-01-08 23:55, Mike Lissner wrote:

That's a great point, thanks. The DROP SUBSCRIPTION notes say you can:


Disassociate the subscription from the replication slot by executing ALTER 
SUBSCRIPTION ... SET (slot_name = NONE). After that, DROP SUBSCRIPTION will no 
longer attempt any actions on a remote host.


I'll read some more about the replication slots themselves (I did read
about them a while back), but doing the above seems like a good way to
break B from A, before resubscribing C to A instead?


Yes, that's the one you want.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Is it safe to transfer logical replication publication/subscription?

[Peter Eisentraut]

On 2020-01-08 22:22, Mike Lissner wrote:

Hi all, this is a follow up from an earlier question I asked about
shortening a chain of logically replicating servers. Right now we have
three servers replicating like this:

A --> B --> C

And we want to remove B so that we have:

A --> C

Is it possible to DROP the subscription on B to A and then to
SUBSCRIBE C to the previously used publication on A without losing
data?


What you are not taking into account here are replication slots, which 
are the low-level mechanism to keep track of what a replication client 
has consumed.  When you drop the subscription on B, that (by default) 
also drops the associated replication slot on A, and therefore you lose 
the information of how much B has consumed from A.  (This assumes that 
there is concurrent write activity on A, otherwise this is uninteresting.)


What you need to do instead is disassociate the B-from-A subscription 
from the replication slot on A, then let all changes from B trickle to 
C, then change the C-from-B subscription to replicate from A and use the 
existing replication slot on A.


See 
https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT 
for details.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Max locks

[Peter Eisentraut]

On 2019-12-19 10:33, James Sewell wrote:
I have a system which is giving me the log hint to increase 
max_locks_per_transaction. This is somewhat expected due to the workload 
- but what I can't understand is the numbers:


Based on the docs I calculate my theoretical max locks as:

max_locks_per_transaction * (max_connections + max_prepared_transactions)
256 * (600 + 0) = *153600*

However, looking at my Prometheus monitoring (polling every 15s) which 
does a SELECT from pg_locks and groups by mode I can see there are over 
500K AccessShareLocks consistently (up to around 570K at peak).


max_locks_per_transactions only affects relation locks (also known as 
heavy weight locks), but pg_locks also shows other kinds of locks. 
Filter by locktype = 'relation' to get the appropriate view.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical replication DNS cache

[Peter Eisentraut]

On 2019-12-12 01:37, Mike Lissner wrote:
I've got a server at example.com <http://example.com> that currently 
publishes logical replication to a server in AWS RDS. I plan to move the 
server at example.com <http://example.com> so that it has a new IP 
address (but same domain name).


I'm curious if anybody knows how the logical replication subscriber in 
AWS would handle that.


There's at least three layers where the DNS might be cached, creating 
breakage once the move is complete:


  - Postgres itself

  - AWS's postgresql fork in RDS might have something

  - The OS underlying amazon's RDS service


Postgres itself doesn't cache any host name resolution results.  I don't 
know about the other two pieces.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Logical Replication of Multiple Schema Versions

[Peter Eisentraut]

On 2019-12-10 08:55, Dan shmidt wrote:

What is the correct way to perform such an operation?
Is there a way to keep constraint #1 or the only option is to not allow 
"breaking" schema changes between versions.


It all depends on the specific schema changes you want to make.  You can 
add columns on the subscriber and remove columns on the publisher 
without breaking things (unless there are not-null constraints). 
Renaming columns will break replication until you rename them 
everywhere.  Column type changes will usually just work as long as the 
data fits into both the old and the new type.


You really need to carefully plan and test each class of scenarios 
separately.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Identity columns, DEFAULT keyword and multi-row inserts

[Peter Eisentraut]

On 2019-12-10 17:53, Tom Lane wrote:

However, a multi-row insert like the following:
 insert into test (id, data)
 values
   (default,1),
   (default,2);
fails with:
 ERROR: cannot insert into column "id"
   Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
   Hint: Use OVERRIDING SYSTEM VALUE to override.

Yeah, in principle a multi-row INSERT could grovel through all the
rows of the VALUES clause and confirm that every one of them has
DEFAULT there.  Not sure it's worth the trouble, but if it's not
a lot of code then maybe.  It certainly seems a bit inconsistent.


It looks like the multi-row case in transformInsertStmt() would have to 
develop a bit more smarts to discover this case and then replace the RTE 
reference in the target list with a single SetToDefault node?


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: upgrade and migrate

[Peter Eisentraut]

On 2019-12-04 08:56, Laurenz Albe wrote:

On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote:

On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote:

Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
server, with minimal downtime?
The caveat is the source has about 80 databases overall almost 30
TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
pg_restore, but the largest hot database is almost 17 tb, and I am
not sure how to approach this effort in a better and efficient way?


pg_upgrade could be one way to go here.  That's not the scale pg_dump
would be very good at.  I would have personally avoided using pg_dump
above 10~20GB.  Depending on the downtime you are ready to accept,
a migration based on Slony could be something to investigate.


Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.


Also consider Londiste.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: logical replication - negative bitmapset member not allowed

[Peter Eisentraut]

On 2019-11-07 16:18, Jehan-Guillaume de Rorthais wrote:

On Thu, 7 Nov 2019 16:02:21 +0100
Peter Eisentraut  wrote:


On 2019-11-05 17:05, Jehan-Guillaume de Rorthais wrote:

I have simplified your reproduction steps from the previous message to a
test case, and I can confirm that your proposed fix addresses the issue.


Thanks for the feedback and the test case. I wonder if ALTER SUBSCRIPTION
DISABLE/ENABLE is useful in the test case?


Turns out it's not necessary.  Attached is an updated patch that
simplifies the test even further and moves it into the
008_diff_schema.pl file.


OK. No further comments on my side.


Committed and backpatched.  Thanks!

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: logical replication - negative bitmapset member not allowed

[Peter Eisentraut]

On 2019-11-05 17:18, Andres Freund wrote:

On 2019-11-05 16:02:51 +0100, Peter Eisentraut wrote:

  $node_publisher->stop('fast');
+
+
+# TODO: 
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
+
+$node_publisher = get_new_node('publisher3');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+$node_subscriber = get_new_node('subscriber3');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;


Do we really have to create a new subscriber for this test? The creation
of one isn't free. Nor is the amount of test code duplication
neglegible.


I changed that in the v2 patch.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: logical replication - negative bitmapset member not allowed

[Peter Eisentraut]

On 2019-11-05 17:05, Jehan-Guillaume de Rorthais wrote:

I have simplified your reproduction steps from the previous message to a
test case, and I can confirm that your proposed fix addresses the issue.


Thanks for the feedback and the test case. I wonder if ALTER SUBSCRIPTION
DISABLE/ENABLE is useful in the test case?


Turns out it's not necessary.  Attached is an updated patch that 
simplifies the test even further and moves it into the 
008_diff_schema.pl file.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From dcc12ec8315ecb8613190052d4f787cf0554e2c2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Thu, 7 Nov 2019 13:48:59 +0100
Subject: [PATCH v2] Fix negative bitmapset member not allowed error in logical
 replication

This happens when we add a replica identity column on a subscriber
that does not yet exist on the publisher, according to the mapping
maintained by the subscriber.  Code that checks whether the target
relation on the subscriber is updatable would check the replica
identity attribute bitmap with a column number -1, which would result
in an error.  To fix, skip such columns in the bitmap lookup and
consider the relation not updatable.  The result is consistent with
the rule that the replica identity columns on the subscriber must be a
subset of those on the publisher, since if the column doesn't exist on
the publisher, the column set on the subscriber can't be a subset.

Reported-by: Tim Clarke 
Analyzed-by: Jehan-Guillaume de Rorthais 
Discussion: 
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
---
 src/backend/replication/logical/relation.c |  3 +-
 src/test/subscription/t/008_diff_schema.pl | 37 --
 2 files changed, 37 insertions(+), 3 deletions(-)

diff --git a/src/backend/replication/logical/relation.c 
b/src/backend/replication/logical/relation.c
index f938d1fa48..b386f8460d 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -340,7 +340,8 @@ logicalrep_rel_open(LogicalRepRelId remoteid, LOCKMODE 
lockmode)
 
attnum = AttrNumberGetAttrOffset(attnum);
 
-   if (!bms_is_member(entry->attrmap[attnum], 
remoterel->attkeys))
+   if (entry->attrmap[attnum] < 0 ||
+   !bms_is_member(entry->attrmap[attnum], 
remoterel->attkeys))
{
entry->updatable = false;
break;
diff --git a/src/test/subscription/t/008_diff_schema.pl 
b/src/test/subscription/t/008_diff_schema.pl
index 3ad00eae3b..d1c8fb7061 100644
--- a/src/test/subscription/t/008_diff_schema.pl
+++ b/src/test/subscription/t/008_diff_schema.pl
@@ -3,7 +3,7 @@
 use warnings;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 4;
+use Test::More tests => 5;
 
 # Create publisher node
 my $node_publisher = get_new_node('publisher');
@@ -29,7 +29,7 @@
 # Setup logical replication
 my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
 $node_publisher->safe_psql('postgres',
-   "CREATE PUBLICATION tap_pub FOR TABLE test_tab");
+   "CREATE PUBLICATION tap_pub FOR ALL TABLES");
 
 $node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' 
PUBLICATION tap_pub"
@@ -88,5 +88,38 @@
 is($result, qq(3|3|3|3),
'check extra columns contain local defaults after apply');
 
+
+# Check a bug about adding a replica identity column on the subscriber
+# that was not yet mapped to a column on the publisher.  This would
+# result in errors on the subscriber and replication thus not
+# progressing.
+# 
(https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info)
+
+$node_publisher->safe_psql('postgres',
+   "CREATE TABLE test_tab2 (a int)");
+
+$node_subscriber->safe_psql('postgres',
+   "CREATE TABLE test_tab2 (a int)");
+
+$node_subscriber->safe_psql('postgres',
+   "ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION");
+
+# Add replica identity column.  (The serial is not necessary, but it's
+# a convenient way to get a default on the new column so that rows
+# from the publisher that don't have the column yet can be inserted.)
+$node_subscriber->safe_psql('postgres',
+   "ALTER TABLE test_tab2 ADD COLUMN b serial PRIMARY KEY");
+
+$node_publisher->safe_psql('postgres',
+   "INSERT INTO test_tab2 VALUES (1)");
+
+$node_publisher->wait_for_catchup('tap_sub');
+
+is($node_subscriber->safe_psql('postgres',
+  "SELECT count(*), 
min(a), max(a) FROM test_tab2"),
+   qq(1|1|1),
+   'check replicated inserts on subscriber');
+
+
 $node_subscriber->stop;
 $node_publisher->stop;

base-commit: e5cfb8cbbe91e73ee92d9e4ab023ca208f3b748a
-- 
2.23.0

Re: logical replication - negative bitmapset member not allowed

[Peter Eisentraut]

On 2019-10-25 17:38, Jehan-Guillaume de Rorthais wrote:

On Thu, 10 Oct 2019 15:15:46 +0200
Jehan-Guillaume de Rorthais  wrote:

[...]

Here is a script to reproduce it under version 10, 11 and 12:


I investigated on this bug while coming back from pgconf.eu. Bellow what I found
so far.


I have simplified your reproduction steps from the previous message to a 
test case, and I can confirm that your proposed fix addresses the issue. 
 A patch is attached.  Maybe someone can look it over.  I target next 
week's minor releases.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 12c3021110a1b30afbc5fddd1b3dc78f2010fb4e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Tue, 5 Nov 2019 15:49:56 +0100
Subject: [PATCH] Fix negative bitmapset member not allowed error in logical
 replication

Reported-by: Tim Clarke 
Analyzed-by: Jehan-Guillaume de Rorthais 
Discussion: 
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
---
 src/backend/replication/logical/relation.c |  3 +-
 src/test/subscription/t/100_bugs.pl| 54 +-
 2 files changed, 55 insertions(+), 2 deletions(-)

diff --git a/src/backend/replication/logical/relation.c 
b/src/backend/replication/logical/relation.c
index 85269c037d..ab80d4b4e0 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -340,7 +340,8 @@ logicalrep_rel_open(LogicalRepRelId remoteid, LOCKMODE 
lockmode)
 
attnum = AttrNumberGetAttrOffset(attnum);
 
-   if (!bms_is_member(entry->attrmap[attnum], 
remoterel->attkeys))
+   if (entry->attrmap[attnum] < 0 ||
+   !bms_is_member(entry->attrmap[attnum], 
remoterel->attkeys))
{
entry->updatable = false;
break;
diff --git a/src/test/subscription/t/100_bugs.pl 
b/src/test/subscription/t/100_bugs.pl
index 366a7a9435..2bd07b1cf6 100644
--- a/src/test/subscription/t/100_bugs.pl
+++ b/src/test/subscription/t/100_bugs.pl
@@ -3,7 +3,7 @@
 use warnings;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 3;
+use Test::More tests => 4;
 
 # Bug #15114
 
@@ -100,3 +100,55 @@
 );
 
 $node_publisher->stop('fast');
+
+
+# TODO: 
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
+
+$node_publisher = get_new_node('publisher3');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+$node_subscriber = get_new_node('subscriber3');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+$publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+
+$node_publisher->safe_psql('postgres',
+   "CREATE TABLE tab1 (a int)");
+
+$node_subscriber->safe_psql('postgres',
+   "CREATE TABLE tab1 (a int)");
+
+$node_publisher->safe_psql('postgres',
+   "CREATE PUBLICATION pub1 FOR ALL TABLES");
+
+$node_subscriber->safe_psql('postgres',
+   "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION 
pub1");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$node_subscriber->safe_psql('postgres',
+   "ALTER SUBSCRIPTION sub1 DISABLE");
+
+$node_subscriber->safe_psql('postgres',
+   "ALTER TABLE tab1 ADD COLUMN b serial PRIMARY KEY");
+
+$node_publisher->safe_psql('postgres',
+   "INSERT INTO tab1 VALUES (1)");
+
+$node_publisher->safe_psql('postgres',
+   "ALTER TABLE tab1 ADD COLUMN b serial PRIMARY KEY");
+
+$node_subscriber->safe_psql('postgres',
+   "ALTER SUBSCRIPTION sub1 ENABLE");
+
+$node_publisher->wait_for_catchup('sub1');
+
+is($node_subscriber->safe_psql('postgres',
+  "SELECT count(*), 
min(a), max(a) FROM tab1"),
+   qq(1|1|1),
+   'check replicated inserts on subscriber');
+
+$node_publisher->stop('fast');
+$node_subscriber->stop('fast');
-- 
2.23.0

Re: Postgres 12: backend crashes when creating non-deterministic collation

[Peter Eisentraut]

On 2019-10-04 19:36, Tom Lane wrote:
> Hm.  This trace says that the crash happened somewhere down inside ICU
> itself, during the ucol_open() call in get_collation_actual_version().
> There isn't much we could have done to mess up the arguments to that
> function.  That would seem to mean that it's ICU's bug not ours.

Some build farm coverage of Windows+ICU would be nice.  We have test
cases in place that might have caught this.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_receivexlog or archive_command

[Peter Eisentraut]

On 2019-09-23 10:25, Vikas Sharma wrote:
> I am wondering which one is the best way to archive the xlogs for Backup
> and Recovery - pg_receivexlog or archive_command.

I recommend using pg_receivexlog.  It has two important advantages over
archive_command:  1) You can have multiple instances of pg_receivexlog
running and copying things to different places.  This is complicated to
do correctly with archive_command.  2) pg_receivexlog will fsync the
files it writes.  This is also complicated to do correctly with
archive_command.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: question about zeroes in the wal file names

[Peter Eisentraut]

On 2019-08-18 16:17, Luca Ferrari wrote:
> I'm just curious to better understand the naming convention behind wal
> files, because I've seen on a system of mine that the wals created
> were:
> 
> 0005020E00FF
>  0005020F
> 
> while I was expecting 20E0x100.

You are in principle correct.  This naming system is a historical
accident.  The actual LSN associated with the first file is

020EFF00

and so the next one is naturally

020F

The reason the zeroes are in there comes from a time when PostgreSQL
didn't fully support 64-bit integers, and the LSNs and the files were
tracked internally as pairs of 32-bit integers.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Changing work_mem

[Peter Eisentraut]

On 2019-08-13 17:16, rihad wrote:
> If I increase it in postgresql.conf and SIGHUP the master server, will 
> the change be applied to all running backends, or only to the ones 
> started after the change? Thanks.

It will be applied to all running backends.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Default ordering option

[Peter Eisentraut]

On 2019-07-25 09:43, Cyril Champier wrote:
> It might be an interesting exercise to implement this as a post-parsing
> hook.
> 
>  
> I known nothing about that, but that sounds interesting, do you have any
> documentation pointer to help me implement that?

Look for post_parse_analyze_hook.  Walk the parsed query tree, look for
queries without ordering clause and manufacture one.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Default ordering option

[Peter Eisentraut]

On 2019-07-23 17:43, Cyril Champier wrote:
> In this documentation
> <https://www.postgresql.org/docs/9.1/queries-order.html>, it is said:
> 
> If sorting is not chosen, the rows will be returned in an
> unspecified order. The actual order in that case will depend on the
> scan and join plan types and the order on disk, but it must not be
> relied on.
> 
> 
> I would like to know if there is any way to change that to have a "real"
> random behaviour.

It might be an interesting exercise to implement this as a post-parsing
hook.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pglogical extension. - 2 node master <-> master logical replication ?

[Peter Eisentraut]

On 2019-07-02 19:54, Day, David wrote:
> On attempting the reverse subscription from host1 to host2
> 
> select pglogical.create_subscription('alabama_sub', 
> 'host=alabama port=5432 dbname=ace_db user=replicator',
> '{connections}', false, false, '{}' )
> 
> could not connect to the postgresql server: FATAL:  role "pgsql" does not 
> exist
> DETAIL:  dsn was:  host=georgia port=5432 dbname=ace_db
> 
> --
> Wrong dsn and role ?
> The  postgres installed superuser role is not pgsql.
> I did  not see this issue in the working subscription direction.

The problem might be in the dsn that you gave to create_node().  Hard to
tell without a fully reproducible script.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_repack issue

[Peter Eisentraut]

On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
> gcc -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv
> -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
> -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
> -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
> pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
> -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
> -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib
> -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
> -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
> */usr/bin/ld: cannot find -lreadline
> collect2: error: ld returned 1 exit status
> make[1]: *** [pg_repack] Error 1
> make[1]: Leaving directory `/home/postgres/software/pg_repack-1.4.4/bin'
> make: *** [all] Error 2*

It's a bit bogus that pg_repack would require this, but perhaps
installing the readline-devel (or similar) package would get you past this.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Refresh Publication takes hours and doesn´t finish

[Peter Eisentraut]

On 2019-05-20 23:30, Tom Lane wrote:
> Hmm ... given that pg_get_publication_tables() shouldn't return any
> duplicate OIDs, it does seem unnecessarily inefficient to put it in
> an IN-subselect condition.  Peter, is there a reason why this isn't
> a straight lateral join?  I get a much saner-looking plan from
> 
> FROM pg_publication P, pg_class C
> -JOIN pg_namespace N ON (N.oid = C.relnamespace)
> -   WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname));
> +JOIN pg_namespace N ON (N.oid = C.relnamespace),
> +LATERAL pg_get_publication_tables(P.pubname)
> +   WHERE C.oid = pg_get_publication_tables.relid;

No reason I think, just didn't quite manage to recognize the possibility
of using LATERAL at the time.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Upgrading locale issues

[Peter Eisentraut]

On 2019-05-03 15:35, Daniel Verite wrote:
> 'b' < 'a' is never true for any locale AFAIK,

But there is 'bb' < 'aa' in Danish. :-)

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Seeded Replication

[Peter Eisentraut]

On 2019-04-03 13:44, Lou Tseng wrote:
> However, our database is about 200G and it will take a long time to
> complete the initial data copy.  We would like to manually seed the
> subscriber database with data dump and then turn on the subscription
> like depesz showed in this
> post 
> https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/
>  .

I doubt you will get a huge speedup, since the dump and the initial data
copy use the same COPY command internally.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: logical replication - negative bitmapset member not allowed

[Peter Eisentraut]

On 2019-04-01 23:43, Alvaro Herrera wrote:
> Maybe the replica identity of a table got set to a unique index on oid?
> Or something else involving system columns?  (If replication is
> otherwise working, the I suppose there's a separate publication that's
> having the error; the first thing to isolate would be to see what tables
> are involved in that publication).

Looking through the code, the bms_add_member() call in
logicalrep_read_attrs() does not use the usual
FirstLowInvalidHeapAttributeNumber offset, so that seems like a possible
problem.

However, I can't quite reproduce this.  There are various other checks
that prevent this scenario, but it's plausible that with a bit of
whacking around you could hit this error message.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Case Insensitive

[Peter Eisentraut]

On 2019-03-28 09:20, Sridhar N Bamandlapally wrote:
> Can we achieve CASE INSENSITIVE in PostgreSQL?

Use the citext extension.

In PostgreSQL 12, there will be support for case-insensitive collations.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Property Graph Query Language proposed for SQL:2020

[Peter Eisentraut]

On 2019-02-25 01:09, Stefan Keller wrote:
> Anyone aware and following this standardization activities?
> Forthcoming SQL:2020 seems to contain "Property Graph Query Extensions".
> See:
> * GQL: a standard for property graph querying
> https://www.gqlstandards.org/
> * Property Graph Query Language (PGQL), an SQL-like query language for
> graphs, including an Open-sourced parser and static query validator on
> GitHub by Oracle.
> http://pgql-lang.org/

Yes, I was just at this meeting this week:
https://www.w3.org/Data/events/data-ws-2019/

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: python install location

[Peter Eisentraut]

On 2019-03-07 01:12, Alan Nilsson wrote:
> How does postgres determine which install of python to use in conjunction 
> with plpythonu?

It looks for a "python" program and then uses that to find the location
of the required library.

> Is there a way, in postgres, short of rebuilding that we can tell postgres 
> which install of python to use when invoking plpython?

No, that it determined at build time.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Channel binding not supported using scram-sha-256 passwords

[Peter Eisentraut]

On 2019-02-26 23:35, Michael Paquier wrote:
> What I do in such cases is to compile OpenSSL by myself and link
> Postgres to it, here is a command to build shared libraries (all that
> is documented in INSTALL):
> ./config --prefix=$INSTALLPATH shared

I did test it now using a custom-built OpenSSL, and I can confirm it works.

> Another trick would be to comment out the sections in libpq where
> HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH is used to emulate a compilation
> with OpenSSL 1.0.1 features and older, while still linking with
> 1.0.2.

Yeah, that might have been easier. ;-)

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Channel binding not supported using scram-sha-256 passwords

[Peter Eisentraut]

On 2019-02-22 06:28, Michael Paquier wrote:
>> Is that right?  Won't we then just select nothing if the macro is not
>> defined?
> In the context of an SSL connection, the server would send both SCRAM
> and SCRAM_PLUS as valid mechanisms if it supports channel binding
> (HAVE_BE_TLS_GET_CERTIFICATE_HASH).  If the server does not support
> channel binding, then only SCRAM is sent.

After reading it again a few more times, I think your patch is correct.

I tried reproducing the issue locally, but the required OpenSSL version
is too old to be easily available.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Channel binding not supported using scram-sha-256 passwords

[Peter Eisentraut]

On 2019-02-21 05:47, Michael Paquier wrote:
>   if (conn->ssl_in_use)
> + {
> + /*
> +  * The server has offered SCRAM-SHA-256-PLUS, 
> which is only
> +  * supported by the client if a hash of the 
> peer certificate
> +  * can be created.
> +  */
> +#ifdef HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH
>   selected_mechanism = SCRAM_SHA_256_PLUS_NAME;
> +#endif
> + }

Is that right?  Won't we then just select nothing if the macro is not
defined?

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: procedures and transactions

[Peter Eisentraut]

On 2019-02-20 17:45, Rob Nikander wrote:
>> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut 
>>  wrote:
>>
>> You can run SET TRANSACTION ISOLATION LEVEL in a procedure.
> 
> I tried that before but I get this error:
> 
> create or replace procedure t_test(n integer)
> as $$
> begin

You need to commit or rollback the preceding transaction here.  Yeah I
know it's a bit weird.

> set transaction isolation level serializable;
> raise notice 'current isolation level: %', (select 
> current_setting('transaction_isolation'));
> raise notice 'current txid: %', (select txid_current());
> end;
> $$ language plpgsql;
> 
> mydb=# call t_test(1);
> ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
> CONTEXT:  SQL statement "SET transaction isolation level serializable"

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: procedures and transactions

[Peter Eisentraut]

On 2019-02-19 22:48, Rob Nikander wrote:
> I thought if I had some application logic that needed a certain kind of
> transaction (maybe a non-default isolation level), I could hide that
> fact in a procedure. App code (Java/Python/whatever) could remain
> unaware of transactions (except maybe needing to retry after a failure)
> and simply send `call foo(?, ?)` to the DB.

You can run SET TRANSACTION ISOLATION LEVEL in a procedure.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Plpythonu extension

[Peter Eisentraut]

On 2019-02-19 14:40, Vincent Predoehl wrote:
> I have PostgreSQL 11.1 installed on MacOS X Mojave and I used brew to do the 
> install.  It appears that the extension plpython was not included in the 
> default installation and I would like to know how to install it.

Apparently, it was removed here:
https://github.com/Homebrew/homebrew-core/pull/36074

If you don't care about understanding that, maybe try this alternative
tap: https://github.com/petere/homebrew-postgresql

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Channel binding not supported using scram-sha-256 passwords

[Peter Eisentraut]

On 2019-02-18 02:06, Michael Paquier wrote:
> Now, the error message "channel binding not supported by this build"
> would show up by either the backend or the frontend if
> X509_get_signature_nid() is not present in the version of OpenSSL your
> version of libpq (for the frontend) or your backend are linked to.
> This function has been added in OpenSSL 1.0.2, so it seems to me that
> you have an OpenSSL version mismatch between your client and the
> server.  My guess is that the client uses OpenSSL 1.0.2, but the
> server is linked to OpenSSL 1.0.1 or older.

I think there is a bug in the frontend code.  If the server offers
SCRAM-SHA-256-PLUS, the client will choose it if SSL is in use, but it
will later fail with this error message if not
HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH.

The code in src/interfaces/libpq/fe-auth.c:pg_SASL_init() should take
HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH into account before selecting
SCRAM-SHA-256-PLUS.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: oracle_fwd - is it safe or not?

[Peter Eisentraut]

On 31/01/2019 18:53, Bruce Momjian wrote:
> On Thu, Jan 31, 2019 at 05:37:00PM +0100, Piotr Włodarczyk wrote:
>> Our administrator have some doubt about this extension. He asked us, are we
>> sure that this extension safe enough and didn't make any kernel panic 
>> sytuation
>> beacuse as all another extension, this one can operate on system kernel 
>> level. 
>>
>> Therefore if it's possible can You tell me more about safety this extension 
>> and
>> release procedure and community of this module?
> 
> Uh, oracle_fwd uses a kernel module?  Are you sure?  That is surprising.

Perhaps he meant that the extension runs in the PostgreSQL "kernel" and
could crash PostgreSQL, which is true of any extension.  But oracle_fdw
seems reasonably popular, so I would expect a base level of quality.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: getting pg_basebackup to use remote destination

[Peter Eisentraut]

On 29/12/2018 20:04, Chuck Martin wrote:
> I thought I knew how to do this, but I apparently don't. I have to set
> up a new server as a standby for a PG 11.1 server. The main server has a
> lot more resources than the standby. What I want to do is run
> pg_basebackup on the main server with the output going to the data
> directory on the new server. But when I give this command:
> 
> pg_basebackup -D "ssh root@10.0.1.16:/mnt/dbraid/data" -P -v -X s
> 
> 
> it instead writes to my root drive which doesn't have the space, so it
> fails and deletes the partial backup. 

What you might be thinking of is the "old" method of doing base backups
before pg_basebackup:  Call pg_start_backup() and then do file system
operations (tar, scp, whatever) to move the data files to where you want
them.  This is mostly obsolete.  You should run pg_basebackup on the
host where you want to set up your standby.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Problem with stored procedure and nested transactions

[Peter Eisentraut]

On 30/10/2018 15:03, p.piero...@mmbb.it wrote:
> I thought that the “BEGIN/END” block was used to create new transactions
> and that each of them could be managed individually.

In PL/pgSQL, BEGIN/END just create syntactic blocks, they don't manage
transactions.

COMMIT and ROLLBACK manage top-level transactions, but those cannot be
nested (since they are top-level).

In order to create a nested transaction structure, you need to use
subtransactions.  In PL/pgSQL, you can use BEGIN/END blocks with an
exception clause to create subtransactions.

I'm not sure what your code is actually trying to do, but you might need
to reorganize it a bit.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: scram-sha-256 authentication broken in FIPS mode

[Peter Eisentraut]

On 11/09/2018 05:02, Michael Paquier wrote:
> Hence, intrinsically, we are in contradiction with the upstream docs.  I
> have worked on the problem with the patch, which works down to OpenSSL
> 0.9.8, and should fix your issue.  This is based on what you sent
> previously, except that I was not able to apply what was sent, so I
> reworked the whole.  Alessandro, does this fix your problems?  I would
> like to apply that down to v10 where SCRAM has been introduced.

I recommend letting this bake in the master branch for a while.  There
are a lot weirdly patched and alternative OpenSSL versions out there
that defy any documentation.

Of course, we should also see if this actually fixes the reported problem.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: User documentation vs Official Docs

[Peter Eisentraut]

On 17.07.18 02:13, Joshua D. Drake wrote:
> On 07/16/2018 05:08 PM, Alvaro Herrera wrote:
>>
>> Sounds like wiki pages could solve need this pretty conveniently.  If
>> and when the content is mature enough and migrates to the tutorial main
>> documentation pages, the wiki pages can be replaced with redirects to
>> those.
> 
> Anyone who writes a lot is going to rebel against using a wiki. They are 
> one of the worst to write in from a productivity perspective. I would 
> rather write in Docbook, at least then I can template everything and we 
> could have a standard xsl sheet etc...

I don't really buy that.  The wiki seems just fine for writing short to
medium size how-to type articles.  We already have good content of that
sort in the wiki right now.  It's not like there isn't going to be
anyone who will rebel against any of the other tool chains that have
been mentioned.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc

[Peter Eisentraut]

On 17.07.18 03:41, Igor Polishchuk wrote:
> We are trying to use logical decoding for detecting database changes.
> However, when we create a replication slot, the data processing pauses
> if there are still transactions running from before the slot creation.
> If I understand correctly, the slot is waiting for creating a consistent
> snapshot and is blocked by the long transactions.
> In our application, we don't need it, as we only want to see if some
> tables were modified. Is it possible to create a  logical replication
> slot with  NOEXPORT_SNAPSHOT option using jdbc?

That doesn't do what you want.  You still need to wait for the snapshot
to be created; there is no way around that.  The NOEXPORT_SNAPSHOT
option just means that the snapshot, once created, won't be exported for
use by other sessions.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Can I disable sslcert/sslkey in pg_service.conf?

[Peter Eisentraut]

On 6/14/18 15:06, George Woodring wrote:
> If I put the blanks into pg_service.conf:
> 
> [mydb]
> dbname=mydb
> host=host1
> sslmode=require
> sslcert=
> sslkey=
> 
> It does not work.

I can believe that.

> [woodring@ibeam]$ PGSERVICE=mydb psql
> psql: SSL error: tlsv1 alert unknown ca
> 
> I tried the opposite of moving the .postgresql directory to a different
> name and putting a hard coded certificate path in pg_service, but it
> looks to have its own sets of challenges.

I think that's probably the best way out, though.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Can we run pg_basebackup master is still working normally (selects, updates, deleted, etc)

[Peter Eisentraut]

On 5/4/18 06:41, Adrian Heath wrote:
> The system is used 24/7. Can we run pg_basebackup while the master is 
> still receiving updates without worrying about physical file corruptions 
> in the generated archive file?

Yes, as long as you follow the instructions about saving the WAL
generated during the backup and replaying it on the newly created standby.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How to monitor logical replication initial sync?

[Peter Eisentraut]

On 3/15/18 09:19, bricklen wrote:
> How does one monitor the status or progress of an initial sync under
> logical replication?  For example:
> 
> * I create a publication in database db_pub
> * I create a subscription in database db_sub
> * In 15 minutes I want to check an see that the initial sync is N%
> complete
> 
> Is it possible to tell when the initial sync is complete, or better
> yet, how complete it is?
> 
> 
> ​This is a question I'm quite interested in as well (and one I do not
> have an answer to).​
>  
> ​Does anyone with more familiarity ​with logical replication have any
> suggestions on how to determine the status of the initial sync?

Something like

select * from pg_subscription_rel where srsubstate <> 'r' and srsubid =
(select oid from pg_subscription where subname = 'mysub');

The key is checking the srsubstate column for 'r' (ready).

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: List all columns referencing an FK

[Peter Eisentraut]

On 2/8/18 05:31, Andreas Joseph Krogh wrote:
> Back in 2008 I asked this
> question: 
> http://www.postgresql-archive.org/Finding-all-tables-that-have-foreign-keys-referencing-a-table-td2153236.html

> I wonder, is this now possible using information_schema only, or are
> there still pieces missing in the standard holding this back?

I think you'll still have the same problems if the same constraint name
appears more than once per schema.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: LDAP Bind Password

[Peter Eisentraut]

On 4/3/18 16:12, Kumar, Virendra wrote:
> Is anybody aware of how to encrypt bind password for ldap authentication
> in pg_hba.conf. Anonymous bind is disabled in our organization so we
> have to use bind ID and password but to keep them as plaintext in
> pg_hba.conf defeat security purposes. We want to either encrypt it or
> authenticate without binding. Any insights into this is appreciated.

You can use the "simple bind" method that is described in the
documentation.  That one doesn't involve a second bind step.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Problem with connection to host (wrong host)

[Peter Eisentraut]

On 3/31/18 11:44, Mike Martin wrote:
> Hi
> I am just setting up a postgres server, details
> Host 192.168.0.3
> pg_hba.conf
> # TYPE  DATABASE    USER    ADDRESS METHOD
> 
> # "local" is for Unix domain socket connections only
> local   all all peer
> # IPv4 local connections:
> host    all all 192.168.0.0/32
> <http://192.168.0.0/32>    ident

This entry should probably be something like

192.168.0.0/24

Writing .0/32 doesn't make much sense.

> # IPv6 local connections:
> host    all all 127.0.0.1/32
> <http://127.0.0.1/32>    ident
> 
> host    all all ::1/128 ident
> 
> postgresql.conf
> listen_addresses-'*'
> 
> however when I try to connect from my laptop (ip 192.168.0.2) I get
> 
> psql -h 192.168.0.3 -U usevideo -W
> Password for user usevideo:
> psql: FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
> "usevideo", database "usevideo", SSL off
> 
> So a bit confused, is psql ignoring the host parameter

.3 is the host you are connecting to, as seen from the client.

.2 is the host your connection is coming from, as seen from the server.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services