Re: PostgreSQL's Max Identifier Length in Amazon RDS

I believe that would not be possible. One quick read- https://til.hashrocket.com/posts/8f87c65a0a-postgresqls-max-identifier-length-is-63-bytes With it being RDS, changing source and recompiling is not an option.

Re: PostgreSQL's Max Identifier Length in Amazon RDS

Also- https://www.postgresql.org/docs/10/runtime-config-preset.html "The following “parameters” are read-only, and are determined when PostgreSQL is compiled or when it is installed." >

Re: Migration to PGLister - After

On Mon, Nov 20, 2017 at 9:45 AM, Stephen Frost wrote: > > This list has now been migrated to new mailing list software known as > 'PGLister'. This migration will impact all users of this mailing list > in one way or another. > Is there more information available about PGLister somewhere, ie, is

Re: On Judging the Value of Tests

to achieve what it aimed at. So it proved a point. >> 4. In the src/test/examples directory (which are all libpq tests), >> why is the “examples” directory not included when building postgres? (Why >> weren't these libpq tests added to src/interface/libpq/test or in regression >> test suite instead?) In short, how to know where (in which file/directory in >> source code) to put a test? > > Dunno, sorry. Those are mainly present as example programs. -- Michael

Re: On Judging the Value of Tests

ion. Please see here for more details: https://wiki.postgresql.org/wiki/Submitting_a_Patch -- Michael

Re: How clear the cache on postgresql?

On Fri, Nov 24, 2017 at 8:54 AM, hmidi slim wrote: > I'm trying to analyze some queries using the explain instructions and the > option analyze and buffers. I realized that the query refers to the cache > memory to return the results. Is there any solution to clear the cache in > postgresql inord

Re: duplicate primary key

freeze-the-dead bug, where a VACUUM FREEZE brings back dead tuples: https://www.postgresql.org/message-id/e5711e62-8fdf-4dca-a888-c200bf6b5...@amazon.com There is a patch in the works for it that should land in the next round of minor releases. -- Michael

Re: copy error with json/jsonb types

inserted, operation done using UTF-8. And both json and jsonb are proving to work. -- Michael

Re: JSON out of memory error on PostgreSQL 9.6.x

tgresql.org nodeProjectSet.c really makes tuple-level memory handling way easier based on my studies of this code. -- Michael

Re: Replication causing publisher node to use excessive cpu over time

for example. -- Michael

Re: WAL reducing size

ou directly, but in Postgres 11 WAL segments get recycled after one completed checkpoint thanks to commit 4b0d28d. -- Michael

Re: clean out ./data/base/pgsql_tmp

On Thu, Dec 7, 2017 at 1:18 PM, Dylan Luong wrote: > How do we clean up the pgsql_tmp folder? Will Postgres periodically clean > it? Ie CHECKPOINT? A postmaster restart cleans up those files automatically. -- Michael

Re: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 6:23 AM, Nick Dro wrote: > Hi, > Why PostgreSQL doesn't have build-in function to calculate percentage? > somthing like percent(number,% > for example: > select percent(100,1) will calculate 1% of 100 = 1 > select percent(25,20) will calculate 20% of 25 = 5 > > Seems like

Re: Debugging a function - what's the best way to do this quickly?

On Tue, Dec 19, 2017 at 1:24 PM, Kevin Burke wrote: > I'm writing a function that looks a little like this: > > > This seems pretty cumbersome. Is there an easier way I am missing? > Specifically it would be neat if it was easier to visualize the > intermediate steps in the query production. If t

Re: Foreign Data Wrapper

ou are using for remote access. That's up to each FDW to use the set of APIs PostgreSQL provides, and to support them when they come out. -- Michael signature.asc Description: PGP signature

Re: High CPU usage in postgres servers

ndows which has no POSIX fork() implementation, note that the build runs under the context of EXEC_BACKEND, where each process is directly called via automaticly-built command lines. Still you are giving close to no information regarding your system, so general pieces of advices are the best you can get. -- Michael signature.asc Description: PGP signature

Re: Deadlock between concurrent index builds on different tables

low-level libraries like SSL or such as the behavior is in integrality linked with PostgreSQL internals and the physical representation of how transactions are handled with system catalogs. In short there is no need to be fancy :) -- Michael signature.asc Description: PGP signature

Re: [table partitioning] How many partitions are possibel?

memory does not fail me. -- Michael signature.asc Description: PGP signature

Re: [table partitioning] How many partitions are possibel?

On Wed, Dec 27, 2017 at 08:53:11AM +0100, Michelle Konzack wrote: > Is there already a release date for v11? Based on the pace of the most recent major releases, this could happen around September. This depends on any issues encountered post-development though. -- Michael signature.

Re: pool connection

r. This will save your server much CPU by minimizing the effects of connections still around but idle. -- Michael signature.asc Description: PGP signature

Re: Request to Enforce the password Strength for PostgreSQL Databases

of perl APIs present in the code tree to do out-of-the-tree compilation, but that requires skills a bit more advanced (I tend to do the second for some of my stuff, that's more portable long-term and needs no patching of upstream Postgres). -- Michael signature.asc Description: PGP signature

Re: Replication questions - read-only and temporary read/write slaves

by promoting a standby, and then do your testing. Then you would need to re-create a standby from scratch. What does "discard all the changes" mean? -- Michael signature.asc Description: PGP signature

Re: Replication questions - read-only and temporary read/write slaves

copying segments from an archive before running the rewind. In all cases be careful of bloat in the partition of pg_xlog. -- Michael

Re: How Many Partitions are Good Performing

re partition-wise logics. -- Michael signature.asc Description: PGP signature

Re: Missing WAL file after running pg_rewind

g strategy, I guess that you should have set archive_mode = 'always' so as the server which was the standby before the promotion is also able to store them. -- Michael signature.asc Description: PGP signature

Re: Updating a pre-10 partitioned table to use PG 10 partitioning

man (https://github.com/keithf4/pg_partman/) can become handy? Perhaps Keith, who maintains the tool, has some insight on the matter. -- Michael signature.asc Description: PGP signature

Re: Missing WAL file after running pg_rewind

What are the contents of the history file for this new timeline? You are looking at 0006.history which should be archived as well. You could do that assuming that WAL has forked on this segment at promotion as both segments would have the same contents up to the point where WAL has forked

Re: Missing WAL file after running pg_rewind

segment on the new timeline 5, while it should start at TLI 5. Are you sure that the standby had the means been able to fetch segment 0005038300BE? Something looks weird from your operational point of view with your archives.. -- Michael signature.asc Description: PGP signature

Re: Insert results in 0 1

hen oid is the OID > assigned to the inserted row. The single row must have been inserted > rather than updated. Otherwise oid is zero. Please refer to the documentation as well, section "Outputs": https://www.postgresql.org/docs/current/static/sql-insert.html -- Michael signature.asc Description: PGP signature

Re: SSD filesystem aligned to DBMS

On Tue, Jan 16, 2018 at 08:02 Scott Marlowe wrote: > On Tue, Jan 16, 2018 at 7:47 AM, Neto pr wrote: > > Hi all > > > > Sorry, but I'm not sure that this doubt is appropriate for this list, > but I > > do need to prepare the file system of an SSD disk in a way that pointed > me > > to, which wou

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

13:28:53 -0400 Fix corrupt GIN_SEGMENT_ADDITEMS WAL records on big-endian hardware. Both involved 9.4.8. -- Michael signature.asc Description: PGP signature

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

t; duplicate toast rows (or duplicate index entries pointing at one row), > though of course that would just move to the next question of how it > got that way. Good point here. This could be a consequence of freeze-the-dead whose fix will be available in the next round of minor releases. -- Mi

Re: Notify client when a table was full

ient? > > How do you define "full"? There could be two definitions here: 1) A table contains more data than a customly-defined amount of data on-disk. 2) The partition where the table data is located runs out of disk space. -- Michael signature.asc Description: PGP signature

Re: Best non-networked front end for postgresql

This is the kind of area where pgadmin can help I think: https://www.pgadmin.org/ Still, the most interesting portion in hacking is by doing things yourself, so why not giving a shot to perl and write your own set of scripts? -- Michael signature.asc Description: PGP signature

Slow alter sequence with PG10.1

lieve that PG10.1 was changed that dramatically without providing a workaround or a way to switch to the old PG9.6 performance, at least I can't find anything in the documentation. Is this a bug? Thanks in advance, Michael -- Email: michael@kruegers.email Mobile: 0152 5891 8787

Re: Slow alter sequence with PG10.1

h the performance impact but more safety if needed. I will try if Adrians proposal does the trick for my application. Sounds promising, thanks. Regards, Michael Adrian Klaver schrieb am Mo., 22. Jan. 2018 um 22:29 Uhr: > On 01/22/2018 07:24 AM, Michael Krüger wrote: > > Dear community, >

Re: Slow alter sequence with PG10.1

new release of Postgres caused some severe headaches among our customers. If you all agree that this changed function should be equivalent to the original one, then its at least an easy fix. Thank you all for your fast responses. Regards, Michael Michael Krüger schrieb am Mo., 22. Jan. 2018 um 23:11

Re: Slow alter sequence with PG10.1

Hello all, I think a good alternative was found and seems to be working fine. I really do appreciate all the help and feedback. Many thanks. Regards, Michael Adrian Klaver schrieb am Di., 23. Jan. 2018 um 02:12 Uhr: > On 01/22/2018 02:47 PM, Michael Krüger wrote: > > Hello all, >

Re: Sync replication

ween a primary and its standbys. One small correction to what Stephen says here. It is possible to define multiple synchronous standbys in v9.6. v10 has added the possibility to define quorum groups. Note that the grammar as been kept backward-compatible across versions. -- Michael signature.asc Description: PGP signature

Re: Please help me understand unlogged tables

rs is how much operation is generated between the time you created the table, like random writes on it. For an initial load on a very large table, you could reduce wal_level temporarily to minimal, and make the WAL generated less painful. For a one-time load this can matter. -- Michael signature.asc Description: PGP signature

Re: Alter table set logged hanging after writing out all WAL

ny ideas? Is it still doing something that I need to wait for? Do you have a backtrace with the process doing the ALTER TABLE hanging? How is structured you table with its indexes? It is a bit hard to guess much without more information. -- Michael signature.asc Description: PGP signature

Parallel Query - Can it be used within functions?

even if the function itself is not invoked in parallel (maybe does not even make sense here), the function body for sure should run in parallel if I'm not mistaken. So what do I obviously do wrong here? Regards, Michael

Re: Alter table set logged hanging after writing out all WAL

are done on it... I cannot put my finger on the thread though. > This is all I see - please help me if there's a better command I can > run: If the process is still running, can you attach gdb to it and then run the command bt? You may need to install debugging symbols to make the trace readable. -- Michael signature.asc Description: PGP signature

Re: Pgcrypto (PostgreSQL 10) on Debain 9?

andom_uuid()); > > Only a guess, but maybe you need to install the extension first? - > >create extension ; For gen_random_uuid(), you need to enable pgcrypto: create extension pgcrypto; For a couple of other UUID-related functions, you need to enable uuid-ossp: create extension &

Re: Alter table set logged hanging after writing out all WAL

sessions are running in parallel, and take proper backups before doing it. -- Michael signature.asc Description: PGP signature

Re: Barman 2.3 errors

://www.pgbarman.org/support/ -- Michael signature.asc Description: PGP signature

Re: Upgrading from Postgresql 9.1 to 10

wntime, way lower than pg_upgrade for example even if you use its --link mode. pg_upgrade --link can work very quickly as well, so if you care about being close to zero you may want to consider it. -- Michael signature.asc Description: PGP signature

Re: Parallel Query - Can it be used within functions?

Dear all, still same behavior with Postgres 10.2 ... Just as a reminder that the issue still exists. Regards, Michael Andreas Kretschmer schrieb am Di., 6. Feb. 2018 um 08:35 Uhr: > Hi, > > > Am 06.02.2018 um 08:24 schrieb Michael Krüger: > > create or replace function rep

Re: pgBackRest backup from standby

where the project is maintained: https://github.com/pgbackrest/pgbackrest -- Michael signature.asc Description: PGP signature

Re: pgBackRest backup from standby

On Sun, Feb 18, 2018 at 06:48:30PM -0600, Don Seiler wrote: > On Sun, Feb 18, 2018 at 6:43 PM, Michael Paquier wrote: >> You may want to contact the maintainers directly through github where >> the project is maintained: >> https://github.com/pgbackrest/pgbackrest > >

Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

ere is always something different happening and breaking. There has never been any discussion around ResolveLocaleName() though. A downside is that this would increase the minimal version support bar on Windows. Still that would be worth a serious look. -- Michael signature.asc Description: PGP signature

Re: initdb when data/ folder has mount points

n be set up with an absolute directory value. So there is no actual need for a symlink with pg_log. This also reduces the amount of data transfered as part of base backups without actually needing them. -- Michael signature.asc Description: PGP signature

Re: Getting a primitive numeric value from "DatumGetNumeric"?

data types or when hacking out functions which manipulate arguments of an existing datatype, looking at the input and output functions help a lot. In your case, numeric_in and numeric_out in src/backend/utils/adt/numeric.c is full of hints. -- Michael signature.asc Description: PGP signature

Re: is libpq and openssl 1.1.* compatible?

t it will become EOL after 1.1.0, porting 1.1.0 does not matter much as most application are going to use 1.0.2 in priority (I do so for one). > Do you have openssl-dev(el) installed? Most likely that's the problem. Debian does this package split for example. -- Michael signature.asc Description: PGP signature

Re: is libpq and openssl 1.1.* compatible?

ld be needed as far as I know in the scripts in src/tools/msvc. The set of APIs present in 1.1.0 is the same whatever the platform so the compatibility is the same, and the dependent libraries should be ssleay32.lib and libeay32.lib whose location depend on your installation of OpenSSL. -- Michael

Re: Empty query_id in pg_stat_activity

ID while we should know it (there are slight cases where we could finish without one, like some PL contexts). If you could provide more details for the reproduction of the problem that does not involve benchbase, that would save time. -- Michael signature.asc Description: PGP signature

Re: verify checksums online

he > paranoid double-check I proposed a patch some years ago but it was rejected or at least not accepted without major changes[1]. There is an external version of pg_checksums which can do online checksums verification here (using the above patch): https://github.com/credativ/pg_checksums Michael [1] https://commitfest.postgresql.org/patch/1733/

Re: pg_stat_statements has duplicate entries for the same query & queryId

s.c. Or an unknown bug has been found, but there is no data proving that here. -- Michael signature.asc Description: PGP signature

Re: The same query_id for different queries

happen and how can it be fixed? Hard to say based on the information you are giving here. First, what you are posting is not a self-contained case. It sounds to me that you may be seeing two entries with one for a top-level query and one for a non-top-level query. In this case the s

Password reset link / 'less' does not exit in psql version 13.4

Hello, I have two very simple questions: 1) I have an account at postgresql.org, but a link to a 'forgot password' seems to be missing on the login page. I have my password stored only on an old Fedora 32 computer. To change the password when logged in, you need to supply the old password. In s

Re: Password reset link / 'less' does not exit in psql version 13.4

On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote: > On 7/25/22 03:01, Michael J. Baars wrote: > > Hello, > > > > I have two very simple questions: > > > > 1) I have an account at postgresql.org, but a link to a 'forgot password' > >

Re: Password reset link / 'less' does not exit in psql version 13.4

On Mon, 2022-07-25 at 09:13 -0700, Adrian Klaver wrote: > On 7/25/22 09:03, Michael J. Baars wrote: > > On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote: > > > On 7/25/22 03:01, Michael J. Baars wrote: > > > > Hello, > > > Are all the clients running on

Re: Password reset link / 'less' does not exit in psql version 13.4

On Mon, 25 Jul 2022, 18:41 Adrian Klaver, wrote: > On 7/25/22 09:23, Michael J. Baars wrote: > > On Mon, 2022-07-25 at 09:13 -0700, Adrian Klaver wrote: > >> On 7/25/22 09:03, Michael J. Baars wrote: > >>> On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote: >

libpq and multi-threading

Hi All, I have a question about libpq and multi-threading. In the PostgreSQL documentation ( https://www.postgresql.org/docs/15/libpq-threading.html) it says that results can be passed around freely between threads. However, when I try to read the result from the parent thread, the program crashe

Re: libpq and multi-threading

I was able to reproduce from here. On Tue, 2 May 2023, 15:49 Laurenz Albe, wrote: > On Tue, 2023-05-02 at 11:38 +0200, Michael J. Baars wrote: > > I have a question about libpq and multi-threading. > > > > In the PostgreSQL documentation ( > https://www.postgresql.org

Re: libpq and multi-threading

Hi David, My mistake. Too much fiddling around, but better than no fiddling around. It appears both sides make mistakes, or does your freely passing around work better than mine? On Tue, 2 May 2023, 17:57 David G. Johnston, wrote: > On Tue, May 2, 2023 at 2:38 AM Michael J. Ba

Re: libpq and multi-threading

ously will result in internal interference. Because libpq makes use of malloc to store results, you will come to find that the CLONE_VM option was not the option you were looking for. On Tue, 2 May 2023, 19:58 Peter J. Holzer, wrote: > On 2023-05-02 17:43:06 +0200, Michael J. Baars wrote: >

Re: libpq and multi-threading

Hi Michael, Are pthread_* functions really such an improvement over clone? Does it make an 'freely passing around' of PGresult objects possible? Like it matters, process or thread. We were talking about the documentation and this 'freely passing around' PGresult object. I ju

Hung Query with No Locking Issues

Hey team - I have 2 stored procedures that need to run back to back. It could convert to a single one - but it's easier from a maintenance perspective to keep them separated. The first procedure effectively is INSERT INTO table_b () SELECT FROM _table_a_; COMMIT; Total execution time - about

Re: Hung Query with No Locking Issues

Okay - that worked. How did you know that would work? That's incredible. On Sun, May 7, 2023 at 4:25 PM Tom Lane wrote: > "Michael P. McDonnell" writes: > > I have 2 stored procedures that need to run back to back. It could > > convert to a single one - but

alter table xxx alter column yyy set (n_distinct=nnnn );

I have been struggling to set n_distinct on a few table columns, and confirm that my changes have actually been accepted. I have a 400-million row table with 81 partitions. PostgreSQL version is 14.11. Column p_id has 13 million distinct values but pg_stats says n_distinct is only 82k. Column pi

RE: alter table xxx alter column yyy set (n_distinct=nnnn );

negative, but none carry the value I attempted to set Thanks, Mike Tefft -Original Message- From: Laurenz Albe Sent: Monday, March 11, 2024 9:21 AM To: Tefft, Michael J ; pgsql-general@lists.postgresql.org Subject: Re: alter table xxx alter column yyy set (n_distinct= ); On Mon

RE: alter table xxx alter column yyy set (n_distinct=nnnn );

where schemaname='sss' and tablename='xxx' and attname = ‘col1’; So setting n_distinct on the column at the parent/partitioned-table level is not relevant for this. Thanks again. Mike Tefft From: Greg Sabino Mullane Sent: Monday, March 11, 2024 12:23 PM To: Laurenz Albe

Removing the default grant of EXECUTE on functions/procedures to PUBLIC

I am trying to remove the default grant of EXECUTE on all functions/procedures to PUBLIC. >From my reading, there is no straightforward way to do this. For example, ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; Does not apply this across the entire cluster (or database) but onl

RE: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

be checking instead? Thanks, Mike Tefft From: Tom Lane Sent: Friday, July 5, 2024 10:51 AM To: Tefft, Michael J Cc: pgsql-general@lists.postgresql.org Subject: Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC "Tefft, Michael J" writes: > I am trying t

RE: Removing the default grant of EXECUTE on functions/procedures to PUBLIC

d a default =X/rolename. Examining \ddp and its underlying quuery, I see that view column pg_default_acl gets a new row with defaclacl populated after the ALTER DEFAULT PRIVILEGES. Thanks very much for your guidance, I am on track now. Mike Tefft From: Tom Lane Sent: Friday, July 5, 2024 2:2

Replicate consistent snapshot to a second PG instance using FDWs

We have a setup where we run 2 locations with both locations containing a full HA setup (using Patroni, etcd, Consul). Each location has 2 PG servers + 1 witness node. At certain time intervals or on demand, our customer would want to send the data from one location to the other over the wir

Autovacuum and visibility maps

We have some batch queries that had occasionally having degraded runtimes: from 2 hours degrading to 16 hours, etc. Comparing plans from good and bad runs, we saw that the good plans used index-only scans on table "x", while the bad plans used index scans. Using the pg_visibility utility, we fo

RE: Autovacuum and visibility maps

: Adrian Klaver Sent: Tuesday, December 3, 2024 11:57 AM To: Tefft, Michael J ; pgsql-general@lists.postgresql.org Subject: Re: Autovacuum and visibility maps On 12/3/24 08: 32, Tefft, Michael J wrote: > We have some batch queries that had occasionally having degraded > runtimes: from 2

Capturing both IP address and hostname in the log

The documentation for log_hostname says: log_hostname (boolean) By default, connection log messages only show the IP address of the connecting host. Turning this parameter on causes logging of the host name as well. Note that depending on your host name resolution setup this might impose a non-n

ICU Collations and Collation Updates

Good morning, long time reader, first time writer. Where I currently work my colleagues used libc collations before I arrived. While using libc collations, they stumbled upon the collation update problem after SLES updates (15.4 to 15.5) (collation version difference for database and operating sy

Re: GSSAPI authentication

This sounds like your PG service was unable to authenticate itself to AD. There's probably a trick to that somewhere - AD doesn't really want to be a Kerberos server, it just happens to use it 😉 On Mon, 6 June 2022, 10:05 pm Niels Jespersen, wrote: > Hello all > > > > We are running Postgres 14

Re: GSSAPI authentication

your question. --Michael On Mon, Jun 6, 2022 at 10:26 PM Michael van der Kolff < mvanderko...@gmail.com> wrote: > This sounds like your PG service was unable to authenticate itself to AD. > > There's probably a trick to that somewhere - AD doesn't really want to be >

Re: GSSAPI authentication

cs/14/gssapi-auth.html. The important part to note here is that $hostname must match what is registered in the SPN for the user that you're using as the service account in AD. It might (I don't know) have to match what AD believes about the host from its PTR records for that domain as w

Re: GSSAPI authentication

Oh wait, I see. On Mon, Jun 6, 2022 at 11:41 PM Michael van der Kolff < mvanderko...@gmail.com> wrote: > The part that you're missing, I think, is that Kerberized services require > a service account. > > The SPN (service principal name) is the name that is used in Kerbe

Re: GSSAPI authentication

and you might want to capture DNS traffic on the two hosts. Of course, I have no idea whether that is actually the issue. I remember reading these docs ages ago - best of luck! --Michael On Mon, Jun 6, 2022 at 11:42 PM Michael van der Kolff < mvanderko...@gmail.com> wrote: > Oh wait, I

Re: Tuning a query with ORDER BY and LIMIT

What do you see when you remove the LIMIT clause? It may be possible to rewrite this using ROW_NUMBER. --Michael On Thu, Jun 23, 2022 at 5:39 AM Dirschel, Steve < steve.dirsc...@thomsonreuters.com> wrote: > I am fairly new to tuning Postgres queries. I have a long background > t

Re: Modelling versioning in Postgres

One thing you could consider is a range type for your "versionTS" field instead of a single point in time. So that would be: CREATE TABLE objects ( objectID uuid, versionID uuid, validRange tsrange, objectData text, ); See https://www.postgresql.org/docs/12.5/rangetypes.html for more inf

Re: Overriding natural order of query results for a subset

Have you considered use of the "nulls last" option in order by ( https://www.postgresql.org/docs/13/queries-order.html)? Alternatively, you could write your own type, with its own ordering primitive 😉 On Sun, 30 May 2021, 12:15 am Laura Smith, < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi >

<    4   5   6   7   8   9