Re: xmin and very high number of concurrent transactions
Vijaykumar Jain wrote: > I was asked this question in one of my demos, and it was interesting one. > > we update xmin for new inserts with the current txid. > now in a very high concurrent scenario where there are more than 2000 > concurrent users trying to insert new data, > will updating xmin value be a bottleneck? > > i know we should use pooling solutions to reduce concurrent > connections but given we have enough resources to take care of > spawning a new process for a new connection, You can read the function GetNewTransactionId in src/backend/access/transam/varsup.c for details. Transaction ID creation is serialized with a "light-weight lock", so it could potentially be a bottleneck. Often that is dwarfed by the I/O requirements from many concurrent commits, but if most of your transactions are rolled back or you use "synchronous_commit = off", I can imagine that it could matter. It is not a matter of how many clients there are, but of how often a new writing transaction is started. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: xmin and very high number of concurrent transactions
On Wed, Mar 13, 2019 at 9:50 AM Laurenz Albe wrote: > > Vijaykumar Jain wrote: > > I was asked this question in one of my demos, and it was interesting one. > > > > we update xmin for new inserts with the current txid. > > now in a very high concurrent scenario where there are more than 2000 > > concurrent users trying to insert new data, > > will updating xmin value be a bottleneck? > > > > i know we should use pooling solutions to reduce concurrent > > connections but given we have enough resources to take care of > > spawning a new process for a new connection, > > You can read the function GetNewTransactionId in > src/backend/access/transam/varsup.c for details. > > Transaction ID creation is serialized with a "light-weight lock", > so it could potentially be a bottleneck. Also I think that GetSnapshotData() would be the major bottleneck way before GetNewTransactionId() becomes problematic. Especially with such a high number of active backends.
ERROR: XX000: cannot update SecondarySnapshot during a parallel operation
Dear Sir/Madam I got an error when I execute the following select sentence. Would you please solve the problem for me? Thank you . Alan Fu. postgres=# \set VERBOSITY verbose postgres=# SELECT round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as NUMERIC),4)||'KM' field_value from had_link; ERROR: XX000: cannot update SecondarySnapshot during a parallel operation CONTEXT: SQL statement "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1" parallel worker LOCATION: GetLatestSnapshot, snapmgr.c:387
PostgreSQL temp table blues
Hi all, I'd like to share my (painful) experience, in which temp tables caused PostgreSQL shutdown. TL;DR. Do not use temp tables in PostgreSQL with connection pool. * My app uses connection pool AND temp tables, with default setting of ON COMMIT PRESERVE ROWS. * I found out later that autovacuum doesn't deal with temp tables. * The database ages as long as the connection is not closed. * So when the database age reaches XID STOP LIMIT, the database refuses to process any new transaction requests, saying "database is not accepting commands to avoid wraparound data loss... HINT: Stop the postmaster and use a standalone backend to vacuum that database. " After reading the docs, I expected this much. What happens after this surprised me. * Now the database needs to be shutdown. When shutting down, it tries to remove temp tables (of course), but since the database is not accepting any commands, ... The temp tables are then ORPHANED, although there was no database crash! * Because of these orphan temp tables, vacuuming the database in single mode won't work, as suggested by HINT. The orphaned temp tables must be manually dropped in single mode, and only then the database can be vacuumed back to normal state. Without dropping temp tables, vacuuming just takes (quite possibly a long) time and do (almost) nothing. Well, that's all. All of the above facts are documented, albeit tersely. If anybody I know ask me about temp tables in PostgreSQL, I'd just say "DON'T." Best Regards, Jahwan
PG 10 vs. 11: Large increase in memory usage when selecting BYTEA data (actually out of memory with PG11)
Dear mailing list,
I am currently testing an application for which I previously used PostgreSQL 10
with the current PostgreSQL 11.2 release. During the tests I experienced out of
memory errors of my database which I could not explain, they seem to affect
tables containing large BYTEA data.
I was able to narrow them down to a small example query where I also
experienced a much higher memory usage with PostgreSQL 11 compared to previous
versions.
For my comparison I worked on a local VM with only 4 GB memory configured, a
plain Ubuntu 18.04.2 installation (including all updates) and the current
PostgreSQL 10.7 resp. 11.2 installation installed from
http://apt.postgresql.org/pub/repos/apt/dists/bionic-pgdg/. I created a table
containing a large amount of BYTEA data using these statements:
> create table public.a (b bytea);
> insert into public.a select repeat('0', 1024 * 1024 * 100)::bytea from
> generate_series(1, 300);
> select pg_size_pretty(sum(length(b))) from public.a;
In total I now have ~ 29 GB of data (actually 300 rows à 100 MB) and start
selecting all data using a small Java program (current JDBC driver 42.2.5).
String sql = "SELECT b FROM public.a";
try (Connection connection =
DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres?currentSchema=public",
"username", "password")) {
connection.setAutoCommit(false);
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setFetchSize(5); // we do not want to run out of Java heap space
try (ResultSet rs = ps.executeQuery()) {
int i = 0;
while (rs.next()) {
// just loop over all data, get the data and do something with it (actually
we print a line every 10 rows containing the length, the other argument is
never true with my test data
i++;
byte[] b = rs.getBytes(1);
if (i % 10 == 0 || b == null || b.length <= i) {
System.err.println("Row " + i + ": " + (b != null ? b.length : null));
}
}
}
}
The Java program actually just executes SELECT b FROM public.a and keeps
streaming more rows doing something with the content, also the execution plan
for my query is fairly simple - actually it seems to be just a sequential scan
(with both versions).
With PostgreSQL 10.7 the program went through fine (with plenty of free memory
on my database VM, actually including the OS there was never used more than 1
GB on the VM). With PostgreSQL 11.2 the memory of my postgres process (pid of
my session) keeps increasing and finally crashes after I fetched only about 8
GB of the data:
TopMemoryContext: 67424 total in 5 blocks; 12656 free (10 chunks); 54768 used
TableSpace cache: 8192 total in 1 blocks; 2096 free (0 chunks); 6096 used
TopTransactionContext: 8192 total in 1 blocks; 7744 free (1 chunks); 448 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 1456 free (0
chunks); 6736 used
RowDescriptionContext: 8192 total in 1 blocks; 6896 free (0 chunks); 1296 used
MessageContext: 8192 total in 1 blocks; 6752 free (1 chunks); 1440 used
Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
smgr relation table: 16384 total in 2 blocks; 4600 free (2 chunks); 11784 used
TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256
used
Portal hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
TopPortalContext: 8192 total in 1 blocks; 7392 free (0 chunks); 800 used
PortalContext: 1024 total in 1 blocks; 552 free (0 chunks); 472 used: C_1
ExecutorState: 4294976384 total in 17 blocks; 4080 free (0 chunks); 4294972304
used
printtup: 314581120 total in 3 blocks; 7936 free (8 chunks); 314573184 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
PortalContext: 1024 total in 1 blocks; 744 free (1 chunks); 280 used:
Relcache by OID: 16384 total in 2 blocks; 3512 free (2 chunks); 12872 used
CacheMemoryContext: 524288 total in 7 blocks; 133056 free (5 chunks); 391232
used
[...]
Grand total: 4610628736 bytes in 192 blocks; 315312 free (137 chunks);
4610313424 used
2019-03-13 10:11:54.628 UTC [1350] postgres@postgres ERROR: out of memory
2019-03-13 10:11:54.628 UTC [1350] postgres@postgres DETAIL: Failed on request
of size 268435456 in memory context "ExecutorState".
2019-03-13 10:11:54.628 UTC [1350] postgres@postgres STATEMENT: SELECT b FROM
public.a
Am I even posting this to the right list (sorry if I choose the wrong one), I
also already thought about filing a bug report, but it could be a bug in either
PostgreSQL or the PG JDBC driver (I suspect the database itself as I use the
same driver against PG 10). Would we expect PG 11 to use that much more memory
than PG 10? Is it maybe a known bug (I did a quick look a pg-bugs but did not
find any concerning this problem).
Actually my process crashed after I've selected only about 80 rows - with PG 10
I was able to load all 300 rows (and even more data). Are there any
configuration options I could play with? Actually almost all
Permission to refresh materialized view
We did run this query: *GRANT ALL ON ALL TABLES IN SCHEMA X TO USER Y;* But user Y gets the message that he has to be the owner of a materialized view to be able to refresh it. Is that intended behaviour? Is there a way to enable the user to refresh materialized views in that schema? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: Permission to refresh materialized view
On 3/13/19 6:27 AM, Johann Spies wrote: We did run this query: /GRANT ALL ON ALL TABLES IN SCHEMA X TO USER Y;/ / / But user Y gets the message that he has to be the owner of a materialized view to be able to refresh it. What is the exact message? Is that intended behaviour? Is there a way to enable the user to refresh materialized views in that schema? What is the definition of the view? Regards Johann // -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- Adrian Klaver adrian.kla...@aklaver.com
Where to store Blobs?
Some days ago I asked "Where **not** to use PostgreSQL?" on this mailing list. Now I realized: Nobody talked about Blobs. I guess most people do not store Blobs in PostgresSQL. Where do you store Blobs? (In my case Blobs are PDF/image files with size up to 20 MByte. I do not talk about very big blobs which are several hundret MByte) -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programming-guidelines
Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation
On 3/12/19 7:54 PM, fuzk wrote: Dear Sir/Madam I got an error when I execute the following select sentence. Would you please solve the problem for me? What version of Postgres? Thank you . Alan Fu. postgres=# \set VERBOSITY verbose postgres=# SELECT round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as NUMERIC),4)||'KM' field_value from had_link; ERROR: XX000: cannot update SecondarySnapshot during a parallel operation CONTEXT: SQL statement "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1" parallel worker LOCATION: GetLatestSnapshot, snapmgr.c:387 I'm guessing ST_length is not parallel safe. What is your setting for?: max_parallel_workers_per_gather -- Adrian Klaver adrian.kla...@aklaver.com
Re: Where to store Blobs?
On 3/13/19 7:28 AM, Thomas Güttler wrote: Some days ago I asked "Where **not** to use PostgreSQL?" on this mailing list. Now I realized: Nobody talked about Blobs. I guess most people do not store Blobs in PostgresSQL. Where do you store Blobs? Probably the preferred method: https://www.postgresql.org/docs/11/datatype-binary.html Another method: https://www.postgresql.org/docs/11/largeobjects.html (In my case Blobs are PDF/image files with size up to 20 MByte. I do not talk about very big blobs which are several hundret MByte) -- Adrian Klaver adrian.kla...@aklaver.com
Re: Where to store Blobs?
Thomas Güttler wrote: > Now I realized: Nobody talked about Blobs. > > I guess most people do not store Blobs in PostgresSQL. > > Where do you store Blobs? > > (In my case Blobs are PDF/image files with size up to 20 MByte. > I do not talk about very big blobs which are several hundret MByte) Ideally outside the database, if they are many. Large databases are harder to backup than large file systems. If you keep 20MB binaries in the database, you'd use the "bytea" data type. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Where to store Blobs?
On 3/13/19 9:28 AM, Thomas Güttler wrote: Some days ago I asked "Where **not** to use PostgreSQL?" on this mailing list. Now I realized: Nobody talked about Blobs. I guess most people do not store Blobs in PostgresSQL. Where do you store Blobs? (In my case Blobs are PDF/image files with size up to 20 MByte. I do not talk about very big blobs which are several hundret MByte) We store PDFs and TIFFs of dozens to a few hundred KB in the database as bytea. -- Angular momentum makes the world go 'round.
Re: Where to store Blobs?
I store them as bytea in the database despite the fact that there are benefits to storing them in the file system. The reason is that it is easier to secure access to the database than to secure both the database and provide secure access to the file system. Chuck Martin Avondale Software On Wed, Mar 13, 2019 at 10:34 AM Laurenz Albe wrote: > Thomas Güttler wrote: > > Now I realized: Nobody talked about Blobs. > > > > I guess most people do not store Blobs in PostgresSQL. > > > > Where do you store Blobs? > > > > (In my case Blobs are PDF/image files with size up to 20 MByte. > > I do not talk about very big blobs which are several hundret MByte) > > Ideally outside the database, if they are many. > Large databases are harder to backup than large file systems. > > If you keep 20MB binaries in the database, you'd use the "bytea" data type. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > >
Re: Where to store Blobs?
On Wed, 13 Mar 2019 at 10:27, Thomas Güttler wrote: > I guess most people do not store Blobs in PostgresSQL. > > Where do you store Blobs? Things have changed, but at one time, we were using RT as our ticketing system (https://bestpractical.com/request-tracker) and it would capture documents as database objects. The table where RT stowed downloadable documents was one of the largest tables in the database because of there being a few 50MB copies of Access Databases and some many-MB spreadsheets in there. It worked fine; no problems evident from it. It was certainly surprising to find such large documents there, and if people had gotten in the habit of putting GBs of data into RT, that would have probably led to some policy changes to prevent it, but unless you're pretty actively trying to blow the system up, it just works. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
Re: Where to store Blobs?
On Wed, Mar 13, 2019 at 11:50:37AM -0400, Christopher Browne wrote: >> I guess most people do not store Blobs in PostgresSQL. - BYTEA puts practical limits on size - LO storage happens inside the system (!) table Nowadays, there are Foreign Data Wrappers which might encapsulate files as if they lived inside the database. Also, a combination of COPY TO FORMAT binary pg_read_binary_file() and suitable plpgsql security definer functions might provide for a Poor Man's binary file integrated external storage. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Where to store Blobs?
On 13/3/19 4:28 μ.μ., Thomas Güttler wrote: Some days ago I asked "Where **not** to use PostgreSQL?" on this mailing list. Where do you store Blobs? (In my case Blobs are PDF/image files with size up to 20 MByte. I do not talk about very big blobs which are several hundret MByte) bytea. Keeping those in the filesys and trying to keep filesys in sync with the db is a PITA. Also dont know what happens in the transactional dept (ACID) with lo_* large objects. In most cases bytea are just fine. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
RE: Permission to refresh materialized view
-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, March 13, 2019 10:24 AM To: Johann Spies ; pgsql-gene...@postgresql.org Subject: Re: Permission to refresh materialized view On 3/13/19 6:27 AM, Johann Spies wrote: > We did run this query: > > /GRANT ALL ON ALL TABLES IN SCHEMA X TO USER Y;/ / / But user Y gets > the message that he has to be the owner of a materialized view to be > able to refresh it. What is the exact message? > > Is that intended behaviour? Is there a way to enable the user to > refresh materialized views in that schema? What is the definition of the view? > > Regards > Johann > // > > -- > Because experiencing your loyal love is better than life itself, my > lips will praise you. (Psalm 63:3) -- Adrian Klaver adrian.kla...@aklaver.com -- Lacking the actual error message however: Y might lack usage on the containing schema ? Y might lack privilege on a function or sequence contained in the view ? Dave Day
Re: Where to store Blobs?
Hi, Am 13.03.19 um 15:28 schrieb Thomas Güttler: Where do you store Blobs? Within PostgreSQL, of course. The system I have in mind stores ZIP and PDF files, usually a few MBs each; we're currently at a total of about 100 GB and there are no evident problems. For this application, it is extremely important that saving/modifying the binaries is part of a larger transaction that completes/fails atomically. Replication is almost real-time and access to the binaries is provided with the same libraries (JDBC/Hibernate) as everything else. Best regards, -hannes
Re: Where to store Blobs?
Make dump/restore of database data unnecessarily expensive in terms of time and space imho.
varlena objects greater than 1GB
Hello, I have read through this thread started by pg-strom's Kohei KaiGai: https://www.postgresql.org/message-id/CADyhKSUP0PM6odyoV27q8CFG6mxMQFKY4R7XLz2NsXCHuwRZKA%40mail.gmail.com and have a similar need to Kohei for varlena objects greater than 1GB, in my case, also vector/matrix objects stored by the pggraphblas extension. Thinking through my options, I was wondering if it would be a good idea to use large object support to store matrix backing data, perhaps first as a varlena object up to the limit, and then switch (in the flatten_into function for my types) to an object storing the oid of the large object holding the matrix data? I'm going to experiment with this approach, but first wanted to raise the issue to shoot some holes in first before trying it. Thanks! -Michel
Re: [External] Re: xmin and very high number of concurrent transactions
Thank you everyone for responding. Appreciate your help. Looks like I need to understand the concepts a little more in detail , to be able to ask the right questions, but atleast now I can look at the relevant docs. On Wed, 13 Mar 2019 at 2:44 PM Julien Rouhaud wrote: > On Wed, Mar 13, 2019 at 9:50 AM Laurenz Albe > wrote: > > > > Vijaykumar Jain wrote: > > > I was asked this question in one of my demos, and it was interesting > one. > > > > > > we update xmin for new inserts with the current txid. > > > now in a very high concurrent scenario where there are more than 2000 > > > concurrent users trying to insert new data, > > > will updating xmin value be a bottleneck? > > > > > > i know we should use pooling solutions to reduce concurrent > > > connections but given we have enough resources to take care of > > > spawning a new process for a new connection, > > > > You can read the function GetNewTransactionId in > > src/backend/access/transam/varsup.c for details. > > > > Transaction ID creation is serialized with a "light-weight lock", > > so it could potentially be a bottleneck. > > Also I think that GetSnapshotData() would be the major bottleneck way > before GetNewTransactionId() becomes problematic. Especially with > such a high number of active backends. > -- Regards, Vijay
Re: Autovacuum Transaction Wraparound
On 3/11/19 1:24 PM, Perumal Raj wrote: Hi Adrian What was the full message? autovacuum: VACUUM (to prevent wraparound) Though i am running vacuum manually (nowadays) and autovacuum is running perfectly once its threshold reaches. What will happen if my DB reaches 200M transaction age again ? ( Here my understanding is no dead tuples to cleanup --- I may be missing full concept , Please correct me if i am wrong) . What will be impact to DB ( Performance ) During Vacuum freeze ( My Assumption is autovacuum will run "vacuum freeze" once DB age reached 200M ) ? I would read this: https://www.postgresql.org/docs/9.2/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND I believe it will answer most of your questions. When should i consider to increase pg_settings value with respect to Autovacuum ? Regards, -- Adrian Klaver adrian.kla...@aklaver.com
Re: Where to store Blobs?
I store large models in the database because I need to have a historical data to compare to. That said, I could probably also automate a git repo but it will be just that much more work and git with binary files really doesn't make sense. Storage is really cheap and I assume the database stores bytesa types like they do text within a separate and partitioned section of the database. Thanks, ~Ben On Wed, Mar 13, 2019 at 1:37 PM Jamesie Pic wrote: > Make dump/restore of database data unnecessarily expensive in terms of > time and space imho. >
Re: Where to store Blobs?
I don't think there is a suitable 'one size fits all' answer to this question. A lot will depend on how you intend to use the blobs and what sort of hardware architecture, especially storage systems, you have. At first glance, sticking everything in the DB seems like an easy choice. However, that can result in very large databases, which in turn can lead to issues with respect to backup, replication etc. If all your after is storage, then sometimes your better off using the file system for the blobs and keeping the metadata in the db. It can potentially be faster and easier to serve up blobs from the file system compared to the db if that is the main use case, but if the blobs are more dynamic or you use collections of blobs to build a master blob etc, the db has some advantages. If you really need database like functionality, given the relative cheapness of storage and the wealth of options available, storing the blobs in the database can have advantage. However, it will be important to select the most appropriate datatype. What some people think of as a 'blob' is just an array of bytes to many DBs and as usual, you need to make the decision as to what is the best storage representation for your requirements, keeping in mind that the more general 'blob' like storage type you choose often represents a loss in functionality but an increase in flexibility wrt to what can be inserted over more precise data types, which will be more restrictive about what can be inserted, but offer more functionality regarding what you can do with it (at the db level). Tim Thomas Güttler writes: > Some days ago I asked "Where **not** to use PostgreSQL?" on this mailing list. > > Now I realized: Nobody talked about Blobs. > > I guess most people do not store Blobs in PostgresSQL. > > Where do you store Blobs? > > (In my case Blobs are PDF/image files with size up to 20 MByte. > I do not talk about very big blobs which are several hundret MByte) -- Tim Cross
Re: PostgreSQL temp table blues
Wow, thanks for sharing your experience. What kind of connection pooling are we talking about? some connection pools implement a DISCARD ALL statement after a session close, that may help if possible to configure. On Wed, Mar 13, 2019 at 4:21 AM Jahwan Kim wrote: > Hi all, > > > I'd like to share my (painful) experience, in which temp tables caused > PostgreSQL shutdown. > TL;DR. Do not use temp tables in PostgreSQL with connection pool. > > * My app uses connection pool AND temp tables, with default setting of ON > COMMIT PRESERVE ROWS. > * I found out later that autovacuum doesn't deal with temp tables. > * The database ages as long as the connection is not closed. > * So when the database age reaches XID STOP LIMIT, the database refuses to > process any new transaction requests, saying "database is not accepting > commands to avoid wraparound data loss... HINT: Stop the postmaster and use > a standalone backend to vacuum that database. " > > After reading the docs, I expected this much. What happens after this > surprised me. > * Now the database needs to be shutdown. When shutting down, it tries to > remove temp tables (of course), but since the database is not accepting any > commands, ... The temp tables are then ORPHANED, although there was no > database crash! > * Because of these orphan temp tables, vacuuming the database in single > mode won't work, as suggested by HINT. The orphaned temp tables must be > manually dropped in single mode, and only then the database can be vacuumed > back to normal state. Without dropping temp tables, vacuuming just takes > (quite possibly a long) time and do (almost) nothing. > > Well, that's all. All of the above facts are documented, albeit tersely. > If anybody I know ask me about temp tables in PostgreSQL, I'd just say > "DON'T." > > > Best Regards, > Jahwan > > > > > -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: Notification or action when WAL archives fully restored and streaming replication started
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Hi, I've got master/slave replication setup between a few hosts. At any point a slave could become a master. I've got appropriate locking in place using an external system so that only one master can exist at a time. I'm having trouble determining when my slaves are in sync with the current master so that they can be a candidate for promotion. I want some form of indicator _without reading a log_ when streaming replication has started. My recovery.conf for slaves: standby_mode = on restore_command = 'gunzip < /archives/wal/%f > %p' recovery_target_timeline = 'latest' primary_conninfo = 'host=PGSQL_FRONTEND_NAME port=5432 user=PGSQL_RECOVERY_USER password=PGSQL_RECOVERY_PASS' Appropriate postgresql.conf: wal_level = replica archive_mode = on archive_command = 'test ! -f /archives/wal/%f && gzip < %p > /archives/wal/%f' archive_timeout = 15min Regards, Michael Cassaniti -BEGIN PGP SIGNATURE- iG0EAREIAB0WIQT0DIHSqEo48gI0VT9pF1oDt4Q+5wUCXInRqgAKCRBpF1oDt4Q+ 5661AN4nRJPXF/M0ZoLg3JVH8f0UsO1WlouHruIRMnsnAN4q9x4G6S4RcobUm5Kh qTNOD2F3v6A8ng4ABFpm =5qCA -END PGP SIGNATURE-
Re: Notification or action when WAL archives fully restored and streaming replication started
On Thu, Mar 14, 2019 at 02:59:38PM +1100, Michael Cassaniti wrote: > I've got master/slave replication setup between a few hosts. At any > point a slave could become a master. I've got appropriate locking in > place using an external system so that only one master can exist at a > time. I'm having trouble determining when my slaves are in sync with the > current master so that they can be a candidate for promotion. I want > some form of indicator _without reading a log_ when streaming > replication has started. pg_stat_replication on the primary, no? Here is its documentation: https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS -- Michael signature.asc Description: PGP signature
Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes
On Thu, Mar 7, 2019 at 4:19 PM Noah Misch wrote: > Has anyone else reproduced this? I tried, but could not reproduce this problem on "CentOS Linux release 7.6.1810 (Core)" using OpenLDAP "2.4.44-21.el7_6" (same as Mike reported, what yum install is currently serving up). I tried "make check" in contrib/dblink, and the only strange thing I noticed was this FATAL error at the top of contrib/dblink/log/postmaster.log: 2019-03-14 03:51:33.058 UTC [20131] LOG: database system is ready to accept connections 2019-03-14 03:51:33.059 UTC [20135] [unknown] FATAL: the database system is starting up I don't see that on other systems and don't understand it. I also tried a test of my own which I thought corresponded directly to what Mike described, on both master and REL_10_STABLE. I'll record my steps here so perhaps someone can see what's missing. 1. Run the regression test under src/test/ldap so that you get some canned slapd configuration files. 2. cd into src/test/ldap/tmp_check and run "slapd -f slapd.conf -h ldap://localhost:";. It should daemonify itself, and run until you kill it with SIGINT. 3. Put this into pg_hba.conf: host postgres test1 127.0.0.1/32 ldap ldapserver=localhost ldapport= ldapbasedn="dc=example,dc=net" 4. Create database objects as superuser: create user test1; create table t (i int); grant all on t to test1; create extension postgres_fdw; create server foreign_server foreign data wrapper postgres_fdw options (dbname 'postgres', host '127.0.0.1'); create foreign table ft (i int) server foreign_server options (table_name 't'); create user mapping for test1 server foreign_server options (user 'test1', password 'secret1'); grant all on ft to test1; 5. Now you should be able to log in with "psql -h 127.0.0.1 postgres test1" and password "secret1", and run queries like: select * from ft; When exiting the session, I was expecting the backend to crash, because it had executed libldap.so code during authentication, and then it had linked in libldap_r.so via libpq.so while connecting via postgres_fdw. But it doesn't crash. I wonder what is different for Mike; am I missing something, or is there non-determinism here? > > I propose this for master only, for now. I also think it'd be nice to > > consider back-patching it after a while, especially since this > > reported broke on CentOS/RHEL7, a pretty popular OS that'll be around > > for a good while. Hmm, I wonder if it's OK to subtly change library > > dependencies in a minor release; I don't see any problem with it since > > I expect both variants to be provided by the same package in every > > distro but we'd certainly want to highlight this to the package > > maintainers if we did it. > > It's not great to change library dependencies in a minor release. If every > RHEL 7 installation can crash this way, changing the dependencies is probably > the least bad thing. +1, once we get a repro and/or better understanding. -- Thomas Munro https://enterprisedb.com
Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes
On Thu, Mar 14, 2019 at 05:18:49PM +1300, Thomas Munro wrote: > On Thu, Mar 7, 2019 at 4:19 PM Noah Misch wrote: > > Has anyone else reproduced this? > > I tried, but could not reproduce this problem on "CentOS Linux release > 7.6.1810 (Core)" using OpenLDAP "2.4.44-21.el7_6" (same as Mike > reported, what yum install is currently serving up). > When exiting the session, I was expecting the backend to crash, > because it had executed libldap.so code during authentication, and > then it had linked in libldap_r.so via libpq.so while connecting via > postgres_fdw. But it doesn't crash. I wonder what is different for > Mike; am I missing something, or is there non-determinism here? The test is deterministic. I'm guessing Mike's system is finding ldap libraries other than the usual system ones. Mike, would you check as follows? $ echo "select pg_backend_pid(); load 'dblink'; select pg_sleep(100)" | psql -X & [1] 2530123 pg_backend_pid 2530124 (1 row) LOAD $ gdb --batch --pid 2530124 -ex 'info sharedlibrary ldap' [Thread debugging using libthread_db enabled] Using host libthread_db library "/lib64/libthread_db.so.1". 0x76303463 in __epoll_wait_nocancel () from /lib64/libc.so.6 >FromTo Syms Read Shared Object Library 0x765e1ee0 0x76613304 Yes (*) /lib64/libldap-2.4.so.2 0x7fffe998f6d0 0x7fffe99c3ae4 Yes (*) /lib64/libldap_r-2.4.so.2 (*): Shared library is missing debugging information.
Re: PostgreSQL temp table blues
In conjunction with some parameter to renew idle connections and those that have been opened for too long will help you prevent this in the future, this also helps prevent server processes from becoming too big memory wise. On Wed, Mar 13, 2019 at 4:32 PM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > Wow, thanks for sharing your experience. What kind of connection pooling > are we talking about? some connection pools implement a DISCARD ALL > statement after a session close, that may help if possible to configure. > > On Wed, Mar 13, 2019 at 4:21 AM Jahwan Kim wrote: > >> Hi all, >> >> >> I'd like to share my (painful) experience, in which temp tables caused >> PostgreSQL shutdown. >> TL;DR. Do not use temp tables in PostgreSQL with connection pool. >> >> * My app uses connection pool AND temp tables, with default setting of ON >> COMMIT PRESERVE ROWS. >> * I found out later that autovacuum doesn't deal with temp tables. >> * The database ages as long as the connection is not closed. >> * So when the database age reaches XID STOP LIMIT, the database refuses >> to process any new transaction requests, saying "database is not accepting >> commands to avoid wraparound data loss... HINT: Stop the postmaster and use >> a standalone backend to vacuum that database. " >> >> After reading the docs, I expected this much. What happens after this >> surprised me. >> * Now the database needs to be shutdown. When shutting down, it tries to >> remove temp tables (of course), but since the database is not accepting any >> commands, ... The temp tables are then ORPHANED, although there was no >> database crash! >> * Because of these orphan temp tables, vacuuming the database in single >> mode won't work, as suggested by HINT. The orphaned temp tables must be >> manually dropped in single mode, and only then the database can be vacuumed >> back to normal state. Without dropping temp tables, vacuuming just takes >> (quite possibly a long) time and do (almost) nothing. >> >> Well, that's all. All of the above facts are documented, albeit tersely. >> If anybody I know ask me about temp tables in PostgreSQL, I'd just say >> "DON'T." >> >> >> Best Regards, >> Jahwan >> >> >> >> >> > > -- > El genio es 1% inspiración y 99% transpiración. > Thomas Alva Edison > http://pglearn.blogspot.mx/ > > -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: Notification or action when WAL archives fully restored and streaming replication started
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 14/3/19 3:10 pm, Michael Paquier wrote: > On Thu, Mar 14, 2019 at 02:59:38PM +1100, Michael Cassaniti wrote: >> I've > got master/slave replication setup between a few hosts. At any >> point a slave could become a master. I've got appropriate locking in >> place using an external system so that only one master can exist at a >> time. I'm having trouble determining when my slaves are in sync with the >> current master so that they can be a candidate for promotion. I want >> some form of indicator _without reading a log_ when streaming >> replication has started. > > pg_stat_replication on the primary, no? Here is its documentation: > https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS > -- > Michael This at least points me in the right direction. I need something on the receiver side, so the state column in pg_stat_wal_receiver might cover. I can check for state = streaming, but the DB won't accept connections in standby mode. Thanks for the quick reply. -BEGIN PGP SIGNATURE- iG0EAREIAB0WIQT0DIHSqEo48gI0VT9pF1oDt4Q+5wUCXInxjAAKCRBpF1oDt4Q+ 562uAOCX3SfCi4ppOd0hBhzsdRWh/3yPeMm8F7c7BgrWAN4tGe+7RnRMrLeE6pOU /5kdwISbxH6fmBEZ1CZM =qCpv -END PGP SIGNATURE-
Re: [External] Re: PostgreSQL temp table blues
May be I am wrong here, but is it not the classic case of connections open too long idle in TX and xid wraparound ? How is connection pool (and which one ?) adding to the woes? I mean the same can be a problem with direct connections too right ? We use pgbouncer with mostly TX level pooling which closes the connection after a commit or a rollback. We have both idle TX timeouts at front end and back end of the pgbouncer setting. And we have monitoring of bloat, idle in TX sessions and pgbouncer connections. We have dbs of various sizes but all less than 1TB. So I do not know if I am comparing with the same set of resources, but just that we use temp tables with connection pooling but with the right monitoring and reasonable constraints and we yet to bite that bullet. So I guess we’ll add to the monitoring something like this too https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/ But I guess you have had a long day, but thanks for sharing. On Thu, 14 Mar 2019 at 11:45 AM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > In conjunction with some parameter to renew idle connections and those > that have been opened for too long will help you prevent this in the > future, this also helps prevent server processes from becoming too big > memory wise. > > On Wed, Mar 13, 2019 at 4:32 PM Rene Romero Benavides < > rene.romer...@gmail.com> wrote: > >> Wow, thanks for sharing your experience. What kind of connection pooling >> are we talking about? some connection pools implement a DISCARD ALL >> statement after a session close, that may help if possible to configure. >> >> On Wed, Mar 13, 2019 at 4:21 AM Jahwan Kim wrote: >> >>> Hi all, >>> >>> >>> I'd like to share my (painful) experience, in which temp tables caused >>> PostgreSQL shutdown. >>> TL;DR. Do not use temp tables in PostgreSQL with connection pool. >>> >>> * My app uses connection pool AND temp tables, with default setting of >>> ON COMMIT PRESERVE ROWS. >>> * I found out later that autovacuum doesn't deal with temp tables. >>> * The database ages as long as the connection is not closed. >>> * So when the database age reaches XID STOP LIMIT, the database refuses >>> to process any new transaction requests, saying "database is not accepting >>> commands to avoid wraparound data loss... HINT: Stop the postmaster and use >>> a standalone backend to vacuum that database. " >>> >>> After reading the docs, I expected this much. What happens after this >>> surprised me. >>> * Now the database needs to be shutdown. When shutting down, it tries to >>> remove temp tables (of course), but since the database is not accepting any >>> commands, ... The temp tables are then ORPHANED, although there was no >>> database crash! >>> * Because of these orphan temp tables, vacuuming the database in single >>> mode won't work, as suggested by HINT. The orphaned temp tables must be >>> manually dropped in single mode, and only then the database can be vacuumed >>> back to normal state. Without dropping temp tables, vacuuming just takes >>> (quite possibly a long) time and do (almost) nothing. >>> >>> Well, that's all. All of the above facts are documented, albeit tersely. >>> If anybody I know ask me about temp tables in PostgreSQL, I'd just say >>> "DON'T." >>> >>> >>> Best Regards, >>> Jahwan >>> >>> >>> >>> >>> >> >> -- >> El genio es 1% inspiración y 99% transpiración. >> Thomas Alva Edison >> http://pglearn.blogspot.mx/ >> >> > > -- > El genio es 1% inspiración y 99% transpiración. > Thomas Alva Edison > http://pglearn.blogspot.mx/ > > -- Regards, Vijay
