Re: Postgres do not support tinyint?

On 2025-Jan-08, Igor Korot wrote: > Also - there are not too many records in that table... In that case, you've probably wasted more time on this discussion than the computer will ever save by storing a smaller column. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.

Re: Postgres 17 domains with NOT NULL and pg_get_constraintdef()

On 2025-Jan-08, sham...@gmx.net wrote: Hello, > However, because the domain is defined with NOT NULL, this fails with > > > ERROR: invalid constraint type "n" > > with 17.2 on Windows and Linux. > > This is a result of storing the NOT NULL constraint in pg_constraint > and can easily be avoide

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

Hello, On 2024-Nov-29, Paul Foerster wrote: > > On 29 Nov 2024, at 18:15, Alvaro Herrera wrote: > > This reports case 2 as OK and case 1 as bogus, as should be. I tried > > adding more partitions and this seems to hold correctly. I was afraid > > though that this wo

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

On 2024-Nov-27, Tom Lane wrote: > I doubt that there's anything actually wrong with the catalog state at > this point (perhaps Alvaro would confirm that). That leads to the > conclusion that what's wrong is the release notes' query for fingering > broken constraints, and it needs some additional

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

On 2024-Nov-27, Tom Lane wrote: > I doubt that there's anything actually wrong with the catalog state at > this point (perhaps Alvaro would confirm that). That leads to the > conclusion that what's wrong is the release notes' query for fingering > broken constraints, and it needs some additional

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

Hi Paul, On 2024-Nov-19, Paul Foerster wrote: > the PostgreSQL 15.9 release notes instruct to look out for especially > detached partitions with foreign key constraints. I'm in the process > of updating our databases from 15.8 to 15.9 now and found a case where > the select statement returns a co

Re: Fwd: A million users

On 2024-Nov-13, Kaare Rasmussen wrote: > Sorry if my original post was unclear, but I don't expect that there > will be much more than perhaps a hundred roles. Each may have from a > few up to a million users in them, though. In Postgres, a user is a role. So if you have a hundred roles and a mi

Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

On 2024-Nov-10, Tom Lane wrote: > This surprised me a bit too, because I thought we took a > slightly-less-than-exclusive lock for FK additions or deletions. > Tracing through it, I find that CloneFkReferencing opens the > referenced relation with ShareRowExclusiveLock as I expected. > But then we

Re: Fwd: A million users

On 2024-Nov-13, Vijaykumar Jain wrote: > I tried to grant select permissions to 5000 different roles on one table, > It failed with row size too big already at 2443. But you can grant select to one "reader" role, and grant that one role to however many other roles you want. This way you can have

Re: CREATE SCHEMA ... CREATE M.V. support

Hello Kirill On 2024-Nov-11, Kirill Reshke wrote: > I was exploring the PostgreSQL parser and discovered a very > interesting feature. Users can create schema along with schema objects > in single SQL. Yeah, it's pretty cool. > Support for materialized views began in 9.3. Perhaps, then, this is

Re: What are best practices wrt passwords?

On 2024-Oct-16, mb...@mbork.pl wrote: > I understand why giving the password on the command line or in an > environment variable is a security risk (because of `ps`), but I do not > understand why `psql` doesn't have an option like `--password-command` > accepting a command which then prints the p

Re: CLOSE_WAIT pileup and Application Timeout

On 2024-Oct-07, KK CHN wrote: > On Mon, Oct 7, 2024 at 12:07 AM Alvaro Herrera > wrote: > Where do I have to introduce the TCP keepalives ? in the OS level or > application code level ? > > [root@dbch wildfly-27.0.0.Final]# cat /proc/sys/net/ipv4/tcp_keepalive_time > 7200

Re: CLOSE_WAIT pileup and Application Timeout

On 2024-Oct-04, KK CHN wrote: > The mobile tablets are installed with the android based vehicle > tracking app which updated every 30 seconds its location fitted inside the > vehicle ( lat long coordinates) to the PostgreSQL DB through the java > backend application to know the latest location of

Re: update faster way

On 2024-Sep-14, yudhi s wrote: > Hello, > We have to update a column value(from numbers like '123' to codes like > 'abc' by looking into a reference table data) in a partitioned table with > billions of rows in it, with each partition having 100's millions rows. Another option is to not update an

Re: Error:could not extend file " with FileFallocate(): No space left on device

On 2024-Sep-11, Pecsök Ján wrote: > In our case: > Kernel: Linux version 4.18.0-513.18.1.el8_9.ppc64le > (mockbu...@ppc-hv-13.build.eng.rdu2.redhat.com) (gcc version 8.5.0 20210514 > (Red Hat 8.5.0-20) (GCC)) #1 SMP Thu Feb 1 02:52:53 EST 2024 > File systém type:xfs Can you please share the out

Re: Error:could not extend file " with FileFallocate(): No space left on device

avior, it just report more things when a problem occurs.) I'm CCing Thomas Munro and Andres Freund, who authored the new code. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "El sudor es la mejor cura para un pensamiento enfermo" (Bardia)

Re: Is there a way to change email for subscription ?

On 2024-Sep-05, Muhammad Ikram wrote: > I want to change email for my PostgreSQL community subscriptions. Is there > a way to do it without unsubscribing and then subscribing to a new email ? Yes. You need to add your new email address as a secondary here https://www.postgresql.org/account/profi

Re: unable to upgrade postgres extensions

On 2024-Aug-21, plsqlvids01 plsqlvids01 wrote: > AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per > https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x > am > trying to upgrade pg_cron and pgaudit extensions but i

Re: Dropping column from big table

On 2024-Jul-11, Ron Johnson wrote: > Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time > (depending on whether or not you populate the column with a default value). Actually, ADD COLUMN with a default does not rewrite the entire table either, starting from pg11. "Major e

Re: How to attach partition with primary key

On 2024-Jun-18, Philipp Faster wrote: > I've done a bad job describing my issue in the first place: I left out a > key definition statement that I thought doesn't play any role in the issue: > another unique index on the same columns as PK. I see. That unique index seems quite useless. Why not j

Re: How to attach partition with primary key

On 2024-Jun-18, Philipp Faster wrote: > As I understand, PostgreSQL refuses to use existing primary key for > some reason and tries to create its own as a children of > "Transactions" table's primary key. Yeah. Your case sounds like the primary key in the partitioned table has some slight defini

Re: UPDATE with multiple WHERE conditions

On 2024-Jun-12, David G. Johnston wrote: > On Wed, Jun 12, 2024 at 2:28 PM Rich Shepard > wrote: > > > I have a table with 3492 rows. I want to update a boolean column from > > 'false' to 'true' for 295 rows based on the value of another column. > > I'll often just use a spreadsheet to build th

Re: Backup failure Postgres

On 2024-May-23, Jethish Jethish wrote: > I have tried by increasing the max_standby_streaming_delay but I'm facing > lag issues on the replica server. > > When i increase the max_standby_streaming_delay even if a query runs for 2 > minutes I'm facing lag issues for 2 minutes. You could use a sep

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

On 2024-May-22, XChy wrote: > Hi everyone, > > I'm a compiler developer working on detecting missed optimization in > real-world applications. Recently, we found that LLVM missed a dead store > elimination optimization in the PostgreSQL code >

Re: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

On 2024-May-14, Dirschel, Steve wrote: > But when I try and run the command inside the procedure it throws this error: > > STATE: 25001 > MESSAGE: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction > block > CONTEXT: SQL statement "alter table t2.test1 detach partition > t2.tes

Re: Clarification Needed on Postgresql License Requirement for Hybrid Environment Cluster Configuration

Hello, On 2024-May-06, Prasanna Chavan wrote: > I hope this email finds you well. I am reaching out to seek > clarification on a matter regarding the configuration of a cluster in > a hybrid environment. > Specifically, I am wondering whether we require a PostgreSQL license > for setting up a clu

Re: Failing streaming replication on PostgreSQL 14

On 2024-Apr-15, Nicolas Seinlet wrote: > I'm using the Ubuntu/cyphered ZFS/PostgreSQL combination. I'm using > Ubuntu LTS (20.04 22.04) and provided ZFS/PostgreSQL with LTS > (PostgreSQL 12 on Ubuntu 20.04 and 14 on 22.04). What exactly is "cyphered ZFS"? Can you reproduce the problem with some

Re: Tracing libpq client: Only with PQtrace()?

On 2024-Apr-09, Greg Sabino Mullane wrote: > On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch > wrote: > > > Is the PQtrace() API the only way to enable libpq client tracing? > > > > I thought about some environment variable of client configuration > > setting... > > That's generally the job of

Re: Tracing libpq client: Only with PQtrace()?

Hello, On 2024-Apr-09, Sebastien Flaesch wrote: > Is the PQtrace() API the only way to enable libpq client tracing? Yes. Regards -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

On 2024-Mar-21, Daniel Gustafsson wrote: > On 21 Mar 2024, at 13:28, Alvaro Herrera wrote: > > > I very much doubt that they realized that comments were going to be > > omitted. But clearly it's just a mistake, and easily fixed. > > It sure looks like a search/rep

Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

lopment team." Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php >From baa4bd18f751cde68c5637c4cb8065cf94e92c1c Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 21 Mar 2024 13:27:37 +0100 Subject: [PATCH v1] fix dump of role comments with --no-role-pas

Re: Question about PostgreSQL upgrade from version 12 to version 15

On 2024-Mar-21, Joseph Kennedy wrote: > I'm planning to upgrade my PostgreSQL database from version 12 to > version 15 using pg_upgrade. After completing the upgrade process, I'm > curious to know whether it's necessary to reindex the database. > > Could anyone please clarify whether reindexing is

Re: Is it possible to keep track of SELECTs?

On 2024-Mar-12, Dominique Devienne wrote: > So is it possible to track the last time a SELECT was performed on some > TABLE? Perhaps you could use the pgAudit module for this purpose. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Re: Non-Stored Generated Columns

On 2024-Feb-29, Dominique Devienne wrote: > Honestly, I'm not sure why supporting the non-stored variant of generated > columns is so controversial... I don't think there's anything controversial about virtual generated columns, really ... it's just that it's tricky to implement and we don't have

Re: Cannot COPY xmin?

On 2024-Feb-27, Dominique Devienne wrote: > Hi. I just tried adding the xmin pseudo-column to my COPY, > and I get an error. I suggest you find some other way to achieve whatever it is you think you'll achieve with this. It's not reliable and you'll regret it eventually. Maybe take a step back

Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.

On 2023-Nov-28, Kyotaro Horiguchi wrote: > By the way, just out of curiosity, but errno should not be zero at the > time the message above was output, yet "%m" is showing "success", > which implies errno = 0 in Linux. How can that happen? If the file is exactly of the length given then seek will

Re: LibPQ: PQresultMemorySize as proxy to transfered bytes

On 2023-Jun-28, Dominique Devienne wrote: > And if there's a better proxy to programmatically know the network > traffic exchanged on the connection's socket, that's cross-platform? > Obviously > libpq itself knows, but I don't see any way to access that info. > > Perhaps tracing might? But will

Re: New addition to the merge sql standard

On 2023-Nov-16, Nick DeCoursin wrote: > In my opinion, it would be better for merge to offer the functionality to > simply ignore the rows that cause unique violation exceptions instead of > tanking the whole query. "ignore" may not be what you want, though. Perhaps the fact that insert (coming

Re: procedure string constant is parsed at procedure create time.

On 2023-Nov-07, jian he wrote: > 2.this will have errors. > CREATE or replace PROCEDURE template_create() LANGUAGE SQL AS $proc$ > DROP TABLE if exists test cascade; > CREATE TABLE test(id int4range,valid_at tsrange,name text); > INSERT INTO test VALUES (NULL, tsrange('2018-01-01',

Re: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

On 2023-Oct-23, Achilleas Mantzios wrote: > I find PostgreSQL inheritance a great feature. The caveats are the same > since a long time, nothing changed in that regard, but as you say, the > implementation/limitations exist in native table partitioning as well. For partitioning, many of the limit

Re: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

On 2023-Oct-23, Achilleas Mantzios - cloud wrote: > I believe this text is false on too many accounts. So, what's the consensus > about Inheritance in PostgreSQL, I am going to give a talk on it in November > and I wouldn't like to advertise/promote/teach something that the community > has decided

Re: Parsing libpq PQtrace files

On 2023-Oct-17, Dominique Devienne wrote: > But then on Parse, the query text, which also contains embedded > newlines but also embedded double-quotes, for column aliases, or table > names, or whatever, even why not a single char literal like '"' (i.e. > single-quote, double-quote, single-quote),

Re: Can not drop partition if exist foreign keys

On 2023-Oct-11, Олег Самойлов wrote: > There are two common practice to drop partition from partitioned > table: just drop or detach-drop. But simple drop don't work if exist > foreign key. Example script attached. Yeah. Detach it first, then you should be able to drop it. > psql:test.sql:15: E

Re: Multiple inserts with two levels of foreign keys

On 2023-Oct-04, Dow Drake wrote: > I want to insert a farm record, then insert two crops associated with that > farm, then insert two deliveries for each of the the two crops so that in > the end, my tables look like this: If I understand you correctly, for each table you want one CTE with the da

Re: Calculating Days/Time(Are Loops Neccessary?)

On 2023-Sep-19, Anthony Apollis wrote: > I have inherited this code, problem is it is over code, i believe. The > package is gonna run once a month and this code run is a loop. How can this > loop be running and checking data up until last day, if it only run once a > month? I didn't stop to unde

Re: rollback to savepoint issue

On 2023-Sep-04, Erik Wienhold wrote: > On 04/09/2023 16:56 CEST David G. Johnston wrote: > > > On Monday, September 4, 2023, Erik Wienhold wrote: > > > > > On 04/09/2023 11:51 CEST Lorusso Domenico wrote: > > > > > > > The original code in embedded in a function, but the problem is the > > >

Re: Schema renaming cascade

On 2023-Aug-17, Lorusso Domenico wrote: > Hello guys, > I need to rename a schema, including each reference to it (also for > functions) Maybe you should consider removing schema name references in function source code, and instead refer to the tables (and other functions, etc) by their unqualifi

Re: Completely Removing PostgreSQL

On 2023-Jul-31, Amn Ojee Uw wrote: > In my Debian 12, I have removed the following apps from my system by using > the following commands: > *dpkg -l | grep postgres* > rc  postgresql-12 12.15-1.pgdg120+1  amd64    The > World's Most Advanced Open Source Relational Database > r

Re: \d don't print all the tables

On 2023-Jul-24, Ron wrote: > Add namespace_a and namespace_b to your search_path.  Then it will work. > > Off the top of my head: > SET search_path = namespace_a, namespace_b, public; Actually it won't, because the table in the earliest schema "shadows" any other tables of the same name in later

Re: suggestion about time based partitioning and hibernate

On 2023-Jul-18, Luca Ferrari wrote: > Dear all, > I'm looking for ideas here, and it could be someone already stepped > into declarative partitioning of an existing database where Hibernate > (a Java ORM) handles the tables. > The situation is as follows: > > create table foo( id primary key, a_d

Re: [Beginner Question]What's the use of ResTarget?

On 2023-Jul-02, Wen Yi wrote: > Hi community > When I read the Internals document (41.1. The Query Tree), > the introduction of  the 'the result relation' confuse me. There are "result relations" in commands that modify a relation, such as insert or update. The result relation is where the new t

Re: 15 pg_upgrade with -j

On 2023-May-23, Ron wrote: > We'd never hardlink.  Eliminates the ability to return to the old system if > something goes wrong. If you'd never hardlink, then you should run your test without the -k option. Otherwise, the timings are meaningless. -- Álvaro Herrera PostgreSQL Developer

Re: "PANIC: could not open critical system index 2662" - twice

On 2023-May-07, Thomas Munro wrote: > Did you previously run this same workload on versions < 15 and never > see any problem? 15 gained a new feature CREATE DATABASE ... > STRATEGY=WAL_LOG, which is also the default. I wonder if there is a > bug somewhere near that, though I have no specific ide

Re: Convert pg_constraint.conkey array to same-order array of column names

On 2023-Mar-23, Dominique Devienne wrote: > We have a query returning 1 row per constraint column, > which until recently we didn't realize wasn't preserving order of the > columns. > > A colleague fixed that, with something like below: > > SELECT ... > FROM pg_catalog.pg_constraint cnstr > ...

Re: Losing my latin on Ordering...

On 2023-Feb-14, Dominique Devienne wrote: > Honestly, who expects the same prefix to sort differently based on what > comes after, in left-to-right languages? Look, we don't define the collation rules. We just grab the collation rules defined by experts in collations. In this case the experts h

Re: How could I elog the tupleTableSlot to the fronted terminal?

On 2023-Jan-30, jack...@gmail.com wrote: > For example, I use "insert into t values(1)"; and I 'll get a tupleTableSlot, > > And Now I want to get the real data , that's 1, and then use elog() func > to print it. Could you give me some codes to realize that? futhermore, > what If the data type is

Re: https://wiki.postgresql.org/wiki/Working_with_Git link one link cannot open, another link is unrelated.

On 2022-Dec-29, Melih Mutlu wrote: > also in this link: https://wiki.postgresql.org/wiki/Working_with_Git > > > >> See the documentation and tutorials at http://git.or.cz/ for a more > >> detailed Git introduction. For a more detailed lesson, check out > >> http://progit.org and maybe get a hardco

Re: gexec from command prompt?

On 2023-Jan-12, Ron wrote: > Postgresql 12.11 > > This might be more of a bash question, or it might be a psql vs engine > problem. > > I want to run this query using psql from a bash prompt: > select format('SELECT ''%s'', MIN(part_date) FROM %s;', table_name, > table_name) > from dba.table_s

Re: PostgreSQL extension for processing Graph queries (Apache AGE)

On 2022-Nov-29, Young Seung Andrew Ko wrote: > Hello PostgreSQL users, > > https://github.com/apache/age > Apache AGE is an Apache 2-licensed open source PostgreSQL extension for > storing Graph data. > > The current version of Apache AGE is to enable PostgreSQL users to use > Neo4j's openCypher

Re: MERGE output doubt

On 2022-Nov-17, Alvaro Herrera wrote: > On 2022-Oct-20, Luca Ferrari wrote: > > > I was expecting an output tag like "MERGE 0" since both branches have > > "do nothing", so no tuples should be updated at all on the target > > table. > > Hmm,

Re: MERGE output doubt

e you saying that it should have been affected by the same bug? -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/ >From 4282eadc0af3061dc53a5bc1ffcdd51b03cc28c4 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 17 Nov 2022 11:58:42 +0100 Subject: [PATCH]

Re: possible bug

On 2022-Oct-21, Tom Lane wrote: > "David G. Johnston" writes: > > On Fri, Oct 21, 2022 at 4:52 PM Ravi Krishna wrote: > >> on a diff note, is the word memoize inspired from Perl Module memoize > >> which use to do the same thing. > > > It is a general functional programming concept - not sure o

Re: How to store "blobs" efficiently for small and large sizes, with random access

On 2022-Oct-19, Dominique Devienne wrote: > Anybody has an answer to my question regarding how substr() works on > bytea values? I.e. is it "pushed down" / optimized enough that it > avoids reading the whole N-byte value, to then pass it to substr(), > which then returns an M-byte value (where M

Re: How to store "blobs" efficiently for small and large sizes, with random access

On 2022-Oct-19, Dominique Devienne wrote: > Upfront, I have to state that I'm not keen on lo, because of security > considerations. We store blobs in many different schemas, and users > can access some schemas, and not others. So the fact the lo table is > unique for the whole database would allow

Re: Weird planner issue on a standby

On 2022-Oct-11, Tom Lane wrote: > Are there any tables in this query where extremal values of the join > key are likely to be in recently-added or recently-dead rows? Does > VACUUM'ing on the primary help? I remember having an hypothesis, upon getting a report of this exact problem on a customer

Re: fully qualified domain names and .pgpass

On 2022-Oct-04, Ron wrote: > Sometimes (both interactively and via script) I access a remote Pg server > via just the bare host name "foobar", and other times via the FQDN > "foobar.example.com". > > I've only been able to get this to work by having two lines in the .pgpass > file: Maybe it wou

Re: is there still a memory leak with hash joins in PG 12.11 ?

On 2022-Sep-28, Zwettler Markus (OIZ) wrote: > I found this blog post talking about a memory leak having hash joins due to a > larger work_mem. > https://gist.github.com/luhn/2b35a9b31255e3a6a2e6a06d1213dfc9 Oh dear, is that what passes for a blog post these days? > Does anyone know if this pro

Re: Missing query plan for auto_explain.

On 2022-Aug-30, Matheus Martin wrote: > Good idea on using an actual prepared statement but unfortunately it didn't > produce any different result. I should have also mentioned to try the EXPLAIN EXECUTE six times and see if the last one produces a different plan. That's when it switches from pl

Re: Missing query plan for auto_explain.

On 2022-Aug-30, Matheus Martin wrote: > Our Postgres recently started reporting considerably different > execution times for the same query. When executed from our JDBC > application the Postgres logs report an average execution time of 1500 > ms but when the query is manually executed through `ps

Re: Support for dates before 4713 BC

Hello Stefan, Alexander, On 2022-Aug-22, stefan eichert wrote: > I can also fully support what Alex has written. I am an archaeologist at > the Natural History Museum Vienna and PostgreSQL is a perfect Open Source > software and we really love working with it for our archaeological and > (pre)his

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

On 2022-Jul-25, Michael J. Baars wrote: > No, it's psql. Setting PAGER to "more -e" solved the problem l, but I never > had to before. There are no other variables affecting this behavior, so it > must be psql internal default piping command that has changed. Perhaps the settings are in the envir

Re: lifetime of the old CTID

On 2022-Jul-06, Matthias Apitz wrote: > This gives in the DB layer a CURSOR of say 100.000 rows of the > 3.000.000 in the table. Now the application fetches row by row and see > if something should be done with the row. If so, the DB layer must > LOCK the row for update. It does so using the CTID.

Re: ADD COLUMN ts tsvector GENERATED too slow

On 2022-Jul-06, Florents Tselai wrote: > Actually, I monitored my disk usage and it was **definitely** working as > It had already eaten up an additional 30% of my disk capacity. Adding a column like this requires creating a second copy of the table, copying all the contents from the old table (

Re: parallel-processing multiple similar query tasks - any example?

On 2022-Apr-28, Shaozhong SHI wrote: > Expand and explain please. No, thanks. -- Álvaro Herrera

Re: parallel-processing multiple similar query tasks - any example?

On 2022-Apr-28, Shaozhong SHI wrote: > Why sleep(1)? It is sleeping to show that they are running concurrently. If it runs five sleeps of one second each and the whole command lasts one second, then all sleeps ran in parallel. Had the whole command taken five seconds, you would know that the qu

Re: parallel-processing multiple similar query tasks - any example?

On 2022-Apr-28, Shaozhong SHI wrote: > multiple similar query tasks are as follows: > > select * from a_table where country ='UK' > select * from a_table where country='France' > and so on > > How best to parallel-processing such types of multiple similar query tasks? > > Any example available?

Re: "create function... depends on extension..." not supported. Why?

On 2022-Apr-26, Tom Lane wrote: > I suppose that "DEPENDS ON EXTENSION" was modeled after the commands > to control extension membership, which likewise exist only in ALTER > form because CREATE's behavior for that is hard-wired. If you wanted > to hand-wave a lot, you could maybe claim that owne

Re: support for DIN SPEC 91379 encoding

On 2022-Mar-28, Peter J. Holzer wrote: > On 2022-03-27 14:06:25 -0400, Tom Lane wrote: > > We follow that spec, so depending on what DIN 91379 *actually* says, > > we might have additional reasons not to be in compliance. I don't > > read German unfortunately. > > It defines minimal character s

Re: support for DIN SPEC 91379 encoding

On 2022-Mar-27, Ralf Schuchardt wrote: > where did you read, that this DIN SPEC 91379 norm is incompatible with UTF-8? > > In the document „String.Latin+ 1.2: eine kommentierte und erweiterte > Fassung der DIN SPEC 91379. Inklusive einer umfangreichen Liste häufig > gestellter Fragen. Herausgegeb

Re: PG12: Any drawback of increasing wal_keep_segments

On 2022-Mar-22, Shukla, Pranjal wrote: > Team, > Are there any disadvantages of increasing the “wal_keep_segments” to a > higher number say, 500? Will it have any impact on performance of > streaming replication, on primary or secondary servers? No. It just means WAL will occupy more disk space.

Re: alter function/procedure depends on extension

On 2022-Feb-17, Bryn Llewellyn wrote: > qu. 1. What is the use-case for "alter function/procedure depends on > extension"? Suppose you have an extension that implements change auditing for tables, which works by creating triggers on the audited tables. You enable auditing for specific tables by

Re: Can we go beyond the standard to make Postgres radically better?

On 2022-Feb-13, Guyren Howe wrote: > I’m back to just having no earthly idea why anyone who finds relations > to be a productive tool for building a model would think that SQL > being the only means to do that is Okay. There are aspects other than technical reasons alone why some things live on w

Re: Undetected Deadlock

On 2022-Jan-25, Michael Harris wrote: > We've recently updated our application to PG 14.1, and in the test instance we > have started to see some alarming undetected deadlocks. This is indeed suspicious / worrisome / curious. What version were you using previously? I reformatted the result sets

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 2022-Jan-10, Dominique Devienne wrote: > Given max_locks_per_transaction * (max_connections > > + max_prepared_transactions >

Re: Match 2 words and more

On 2021-Nov-28, Shaozhong SHI wrote: > this is supposed to find those to have 2 words and more. > > select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$'; > > But, it finds only one word as well. How about something like this? '^([[:<:]][[:alpha:]]+[[:>:]]( |$)){2}$' You

Re: Merge into does not work

On 2021-Nov-26, Adrian Klaver wrote: > On 11/26/21 11:44, Alvaro Herrera wrote: > > On 2021-Nov-26, Shaozhong SHI wrote: > > > > I am using the MERGE patch I posted here[1], on top of Postgres 15. > > > > https://postgr.es/m/20252245.byerxxac444d@alvherre.pg

Re: Merge into does not work

On 2021-Nov-26, Shaozhong SHI wrote: > MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id > WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume > WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); It does work for me: 55479 15devel 680346=# MERGE INTO Stock USING Buy

Re: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe

On 2021-Oct-15, Nick Renders wrote: > Hello, > > I have been trying to import a Postgres 11 database into Postgres 14, but > the pg_restore command exits with the following message: > > pg_restore: error: could not write to the communication channel: Broken > pipe > > The command I sent l

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On 2021-Jun-22, Mike Yeap wrote: > I have a Postgres version 11.11 configured with both physical replication > slots (for repmgr) as well as some logical replication slots (for AWS > Database Migration Service (DMS)). This morning, the server went panic with > the following messages found in the l

Re: datfrozenxid not dropping after vacuum

On 2021-Sep-01, Matthew Tice wrote: > Hi Alvaro, thanks for the quick reply. Hi. Glad to hear that your problem is now behind. > I'm scheduled to do my patching maintenance at the end of this month - > but at this point I don't think I'm going to make it. > > Other than patching, is there a wor

Re: datfrozenxid not dropping after vacuum

evalidate the rd_rel portion of a relcache entry when invalid. This implies a bit of extra complexity to deal with bootstrapping, but it's not too bad. The fix for 2) is simpler, simply always remove both the shared and local init files. Author: Andres Freund Review

Re: Can we get rid of repeated queries from pg_dump?

On 2021-Aug-28, Tom Lane wrote: > Here is a second patch, quite independent of the first one, that > gets rid of some other repetitive queries. Another pointlessly repetitive query is in getTriggers, which we run once per table to be dumped containing triggers. We could reduce that by running it

Re: Getting pg_stat_database data takes significant time

On 2021-Aug-11, hubert depesz lubaczewski wrote: > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > 1. this depends on reading the stats file; that's done once per > > transaction. So if you run the query twice in a transaction, the second > > time

Re: Getting pg_stat_database data takes significant time

Two things, 1. this depends on reading the stats file; that's done once per transaction. So if you run the query twice in a transaction, the second time will take less time. You can know how much time is spent reading that file by subtracting both times. 2. EXPLAIN (VERBOSE) will tell you which

Re: PostgreSQL reference coffee mug

On 2021-Jul-28, Adrian Klaver wrote: > On 7/28/21 8:01 AM, Georg H. wrote: > To add to above: > > \set\s allone -> alone Actually that's wrong, because \s prints history not variables. This needs to read \set alone ... I'm *not* going to get into this, because I know I'm capable of sp

Re: RDS Proxy war stories?

On 2021-Jul-27, Quinn David Weaver wrote: > Hi, > > Does anyone here have experience to share regarding Amazon's RDS > Proxy? Subjective or quantitative, positive or negative, anything you > have is great. I know one instance of its use. The RDS instance it serves is very large (it used the lar

Re: ERROR: cannot freeze committed xmax

this, with what version, when did you upgrade this to 10.14? That may help search the commit log for bugfixes that might explain the bug. I just remembered this one as my favorite candidate: Author: Alvaro Herrera Branch: master Release: REL_11_BR [d2599ecfc] 2018-05-04 18:24:45 -0300 B

Re: ERROR: cannot freeze committed xmax

Hi Sasha On 2021-Jul-14, Sasha Aliashkevich wrote: > lp | ctid | xmin| xmax | xmax_is_lock | xmax_committed | > xmax_rolled_back | xmax_multixact > +-+---+--+--++--+ > 19 | (75,21) | 571 | 572

Re: ALTER TABLE ... DETACH PARTITION just sitting there

On 2021-Jun-28, Ron wrote: > We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01') > TO (2011-08-01')), and I've been detaching partitions from oldest to newest, > one at a time. Whenever it's failed due to a FK constraint (and there are > many of them!), I dropped the "same

Re: Partitioned Table Index Column Order

On 2021-Jun-23, Rumpi Gravenstein wrote: > As a best practice is it better to create the primary key starting or > ending with the partition column? It is not relevant from the partitioning point of view. Other factors can be used to decide the column order. -- Álvaro Herrera Valdivia, C

  1   2   3   4   >