Re: [EXT] Re: DBeaver session populating pg_stat_activity.backend_xmin

On 1/25/23 16:21, Dirschel, Steve wrote: [snip] The problem is users will connect using DBeaver and their sessions will sit idle. Idle is not a problem, "idle in transaction" is. From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions

Re: How to control pg_catalog results for each users?

On 1/30/23 02:41, Laurenz Albe wrote: On Mon, 2023-01-30 at 14:00 +0900, hirose shigeo(廣瀬 繁雄 □SWC○ACT) wrote: all users can get the all of table name , table structure and other information from pg_catalog, which is considered a security problem. The belief that restricting that will improve

Re: Sequence vs UUID

And populate that column with UUIDs generated by the gen_random_uuid() function. (Requires v13.) On 1/30/23 13:46, Adrian Klaver wrote: On 1/30/23 11:43, veem v wrote: Thank You So much for the details. I am a bit new to postgres. And these test results I picked were from a dev system. If I

Re: Sequence vs UUID

, Ben On Sat, Jan 28, 2023, 3:39 PM Erik Wienhold wrote: > On 27/01/2023 01:48 CET Ron wrote: > > On 1/26/23 15:55, Erik Wienhold wrote: > > > > There are arguments against sequential PK, e.g. they give away too much info and > > allow attack

Re: Indexes mysteriously change to ON ONLY

I cheat by using sed to remove "ONLY ON " from the CREATE statements. On 1/27/23 15:30, Rumpi Gravenstein wrote: Tom/Christophe  I now understand.  Thanks for the clear explanation. On Fri, Jan 27, 2023 at 4:16 PM Tom Lane wrote: Rumpi Gravenstein writes: > We are using the

Re: Sequence vs UUID

On 1/26/23 15:55, Erik Wienhold wrote: On 26/01/2023 20:17 CET veem v wrote: Hello, We were trying to understand whether we should use UUID or Sequence in general for primary keys. In many of the blogs (one is below) across multiple databases, I saw over the internet and all are mostly stating

Re: Sequence vs UUID

Then it's not a Type 4 UUID, which is perfectly fine; just not random. Also, should now() be replaced by clock_timestamp(), so that it can be called multiple times in the same transaction? On 1/28/23 21:28, Miles Elam wrote: On Sat, Jan 28, 2023 at 6:02 PM Ron wrote: Type 4 UUIDs are sub

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

We regularly use "history" databases.  Put them on slow media, and only take a backup when data is added to them (monthly, quarterly, etc). On 1/15/23 15:57, HECTOR INGERTO wrote: > But you cannot and should not rely on snapshots alone That’s only for non atomic (multiple pools) snapshots.

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

On 1/18/23 10:54, Rob Sargent wrote: On 1/18/23 09:38, HECTOR INGERTO wrote: I wanted to understand the underlying issue. I use ZFS snapshots instead of a “correct” backup because with only two machines it allows me to have backups in the main machine and in the secondary too that acts as

Re: Database size different on Primary and Standby?

On 1/18/23 17:09, Hilbert, Karin wrote: I manage some PostgreSQL clusters on Linux.  We have a Primary & two Standby servers & for Production, there is also a DR server.  We use repmgr for our HA solution & the Standbys are cloned from the Primary using the *repmgr standby clone* command. My

Re: pg_restore error on function

On 11/3/22 09:28, Post Gresql wrote: Hello I first successfully ran pg_dump -U postgres -b -E UTF8 -f my_dump --format=custom -n my_schema --no-owner -v my_db but then pg_restore --single-transaction -v -U postgres -O -e -d my_other_db my_dump failed with pg_restore: connecting to

Re: unable to install postgreql 13.4

On 11/3/22 01:48, shashidhar Reddy wrote: Hello, I need to install postgresql 13.4 on development server , but getting below errors, please help to resolve this sudo apt-get install postgresql-13.4 Reading package lists... Done Building dependency tree Reading state information... Done E:

Re: Setting up replication on Windows, v9.4

On 11/4/22 17:59, Brad White wrote: I'm setting up a backup for our primary postgres server using the archived WAL files. Then I'll try to upgrade it to Streaming Replication. Then I'll upgrade the system to v.latest. For now, we are on v.9.4. FYI: v9.4 has been EOL for 2 years 9 months.  

Re: Q: documentation improvement re collation version mismatch

On 11/10/22 02:33, Julien Rouhaud wrote: [snip] For now, the only safe way to go is either reindex everything, or everything except some safe cases (non-partial indexes on plain-non-collatable datatypes only). Usually, those safe cases are usually enough to avoid most of useless reindex

Re: Q: documentation improvement re collation version mismatch

On 11/10/22 08:33, Julien Rouhaud wrote: On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote: On 11/10/22 02:33, Julien Rouhaud wrote: [snip] For now, the only safe way to go is either reindex everything, or everything except some safe cases (non-partial indexes on plain-non-collatable

Re: copy file from a client app to remote postgres isntance

On 11/7/22 10:57, Вадим Самохин wrote: Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020 ). It boils down to

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 09:43, Tom Lane wrote: Ron writes: On 11/7/22 08:02, Tom Lane wrote: call. It'd still be recommendable to pg_dumpall and restore into a freshly-initdb'd cluster, because otherwise you can't be real sure that you identified and cleared all the data corruption. Why *just* pg_dumpall

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 08:02, Tom Lane wrote: [snip] call. It'd still be recommendable to pg_dumpall and restore into a freshly-initdb'd cluster, because otherwise you can't be real sure that you identified and cleared all the data corruption. Why *just* pg_dumpall instead of "pg_dumpall --globals-only"

Re: Feature suggestions for backup and replication

On 11/7/22 16:03, da avory wrote: Hi all, not sure whether this is the right list to ask. I do have two (small!?) feature suggestions, both regarding backup and replication, which might appeal to a wider audience. 1) it would be very great to have the option to add a timestamp to all lines

Re: Reducing bandwidth usage of database replication

On 11/2/22 09:56, Sascha Zenglein wrote: Hi all, I want to use the postgres-native logical replication to have multiple clients receive and send data to a central database. Real-time is far less important than network usage, and with my current test setup it appears both instances communicate

Re: pg_restore error on function

On 11/3/22 09:57, Adrian Klaver wrote: On 11/3/22 07:45, Ron wrote: On 11/3/22 09:28, Post Gresql wrote: Hello I first successfully ran pg_dump -U postgres -b -E UTF8 -f my_dump --format=custom -n my_schema --no-owner -v my_db What was the *complete* pg_dump command? I'm going to say

Re: Autovacuum on Partitioned Tables

On 10/31/22 17:26, Ryan Ruenroeng wrote: Hello there! There is a statement in the Postgres Docs: "Partitioned tables are not processed by autovacuum." What does the above statement mean? Does autovacuum not process both the parent and the child tables in a partition relationship?

Re: Autovacuum on Partitioned Tables

On 10/31/22 23:05, Tom Lane wrote: [snip] TBH, if you've got 50m rows, I'm not sure you need partitions at all. Big rows (i.e. document storage tables with bytea or xml fields) can make databases explode in size even with only 50M rows. (Yes, I know the arguments against it, but it works

Re: shutdown Postgres (standby) host causing timeout on other servers in replication

On 11/3/22 15:55, Joanna Xu wrote: Hi All, We have PostgreSQL master-slave replication configuration with 4 servers. After shutting down one standby server, we found below errors on all other 3 servers and the applications couldn’t access the database due to the timeout. DAO-2

Re: Postgres to edb AS, need conversion?

On 11/3/22 04:44, milist ujang wrote: Hi all, Anyone have experiences or someone from edb can guid about change engine from postgres open source to edb as, di I need to convert the data or simply plug-and-play? Or need run a tool to add catalog table owned by edb as? You'd better ask EDB.

Re: copy file from a client app to remote postgres isntance

On 11/9/22 10:17, Peter J. Holzer wrote: On 2022-11-07 14:40:40 -0600, Ron wrote: On 11/7/22 10:57, Вадим Самохин wrote: I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https

Re: Table : Bloat grow high

On 11/12/22 08:05, Laurenz Albe wrote: On Fri, 2022-11-11 at 17:09 +, Alexis Zapata wrote: In postgresql 13.5 I have a table (size 3.1 GB) and in this table occurs near to 200 updates per second, after 2 days the size table is 7 GB and bloat grow to 45% and  the query operations are

Re: Upgrading to v12

Step #1: upgrade to 9.4.26.  You'll get *five years* of bug fixes. (If the client lets you, of course.  I had servers stuck on 8.4.17 and 9.2.7 that were only upgraded because PCI auditors were going to tell my client's client, and that scared /my/ client.  Now they're on 9.6.24...) On

Re: Upgrading to v12

On 11/11/22 23:09, Adrian Klaver wrote: On 11/11/22 20:59, Brad White wrote: On Fri, Nov 11, 2022, 9:57 PM Adrian Klaver > wrote: Yes. The backup is from production. V9.4 is running on 5432 on all servers. That particular restore happens to be on the dev

Re: Autovacuum on Partitioned Tables

ryan.ruenroeng> <https://www.linkedin.com/in/ryan-ruenroeng> On Tue, Nov 1, 2022 at 2:54 AM Ron wrote: On 10/31/22 23:05, Tom Lane wrote: [snip] > TBH, if you've got 50m rows, I'm not sure you need partitions at all. Big rows (i.e. document storage tables with bytea

Re: Setting up replication on Windows, v9.4

Note that WAL replication replicates *the whole instance* not just specific databases.  You need logical replication for that. Also, I just learned that 9.4 *does* have WAL replication slots (which makes replication *much* easier).  v9.6 is where replication is "like rolling off a log"

Re: Setting up replication on Windows, v9.4

On 11/10/22 21:24, Brad White wrote: On 11/7/2022 3:42 PM, Rob Sargent wrote: > Care to share some of the ways the app stopped working? You might get a leg up on where best to remediate. I don't recall, as that was a few months ago. We are running MS-Access as a front end with Postgres as the

Re: Table : Bloat grow high

On 11/11/22 11:09, Alexis Zapata wrote: Hi all, In postgresql 13.5 Upgrade to at least 13.8.  (13.9 was released yesterday.) I have a table (size 3.1 GB) and in this table occurs near to 200 updates per second, after 2 days the size table is 7 GB and bloat grow to 45% and the query

"SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?

v12.13 https://www.postgresql.org/docs/12/sql-select.html The docs say that one of these are required in the SELECT list. [ * |/|expression|/ [ [ AS ]/|output_name|/ ] [, ...] ] However, *not* mentioning anything also works, though acts like COUNT(*). test=# select * from sales_detail;  

Re: How does Postgres store a B-Tree on disk while using the OS file system?

On 3/6/23 18:24, Siddharth Jain wrote: I am trying to sharpen my understanding of Postgres. As I understand, Postgres does not write directly to disk blocks. It uses the file system provided by the OS:

Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

On 3/11/23 14:23, Atul Kumar wrote: Hi, Could someone help me in telling the difference between these three parameters 1. max_standby_archive_delay 2. max_standby_streaming_delay 3. recovery_min_apply_delay My basic motive is to make the standby database server to be delayed to apply the

Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

On 3/12/23 09:01, Laurenz Albe wrote: On Sun, 2023-03-12 at 01:53 +0530, Atul Kumar wrote: Could someone help me in telling the difference between these three parameters 1. max_standby_archive_delay 2. max_standby_streaming_delay 3. recovery_min_apply_delay My basic motive is to make the

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On 3/8/23 15:29, Bryn Llewellyn wrote: [snip] create table s.t(k int primary key, c1 int, c2 int, c3 int); insert into s.t(k, c1, c2, c3) values(1, 17, 42, 57); create type s.x as (c1 int, c2 int, c3 int); [snip] This is an excellent analysis. Native PG doesn't provide much metadata or

Re: Practice advice for use of %type in declaring a subprogram's formal arguments

On 3/10/23 15:28, Bryn Llewellyn wrote: I'm thinking about "language plpgsql" subprograms—but I expect that my question can be generalized to cover "language sql" too. The payload for "create [or replace]" for a "language plpgsql" subprogram specifies various metadata elements like its

psql \conninfo in tabular form?

v13.10 Instead of a sentence like this: You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433". I'd rather have something tabular like:    keyword  |   value +---   database  | postgres   user      |

Re: PostgreSQL vs MariaDB

What RDBMS is your data currently on? On 3/25/23 01:15, Inzamam Shafiq wrote: Hi Ben, We have a complex running, and we will be having a lot of Insert, update and deletes. We have many partitioned tables with huge data and some complex SQL is written at the application logic, some analytical

Re: Getting error while upgrading postgres from version 12 to 13

On 3/18/23 15:39, shashidhar Reddy wrote: Hello, I am in a process of upgrading postgresql from version 12 to 13 using pg_upgrdrade but I am getting error stating  FATAL:  database files are incompatible with server  DETAIL:  The data directory was initialized by PostgreSQL version 13 ,

pg_get_functiondef(), trailing spaces and + sign

Is there any direct way in Postgresql to get rid of the frankly anti-useful junk at the end of each line (which also infects pg_stat_activity.query), or must I resort to sed post-processing? test=# select pg_get_functiondef(oid) test-# from pg_proc test-# where proname = 'foo';    

Converting row elements into a arrays?

Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.  f1 | f2_array +- 1 | {1,2,3}   2 | {1,2,3,4}   3 | {1,2} The ultimate goal is to somehow use pg_index.indkey to get

Re: Converting row elements into a arrays?

On 3/2/23 15:34, David G. Johnston wrote: On Thu, Mar 2, 2023 at 1:58 PM Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number. Group By creates groups

Re: Converting row elements into a arrays?

On 3/2/23 15:01, Ray O'Donnell wrote: On 02/03/2023 20:58, Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.   f1 | f2_array +- 1 | {1,2,3}    2 | {1,2,3,4

Re: Converting row elements into a arrays?

On 3/2/23 15:45, Rob Sargent wrote: On 3/2/23 13:58, Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.  f1 | f2_array +- 1 | {1,2,3}   2 | {1,2,3,4}   3

Re: Dropping behavior for unique CONSTRAINTs

On 3/3/23 04:54, David Rowley wrote: On Fri, 3 Mar 2023 at 23:17, Conner Bean wrote: I wanted to avoid using a unique index since dropping them requires an exclusive lock and cannot be done concurrently. My thought was to then use a unique constraint, since I've read unofficial docs[0] that say

Re: Dropping behavior for unique CONSTRAINTs

On 3/4/23 02:03, Peter J. Holzer wrote: [snip] So your plan is to create a unique constraint (backed by a unique index) and then to drop the index and keep the constraint? That doesn't work. A unique constraint can't exist without a (unique) index. Think about it: With a unique constraint

psql \set variables in crosstab queries?

According to https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-VARIABLES and experience, variables don't interpolate inside of string literals: " Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as ':foo' doesn't

Re: psql \set variables in crosstab queries?

On 3/4/23 19:22, Tom Lane wrote: Ron writes: According to https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-VARIABLES and experience, variables don't interpolate inside of string literals: " Variable interpolation will not be performed within quoted SQL literals and identi

Re: Dropping behavior for unique CONSTRAINTs

On 3/4/23 05:51, Peter J. Holzer wrote: On 2023-03-04 02:34:02 -0600, Ron wrote: On 3/4/23 02:03, Peter J. Holzer wrote: [snip] So your plan is to create a unique constraint (backed by a unique index) and then to drop the index and keep the constraint? That doesn't work. A unique constraint

Re: psql \set variables in crosstab queries?

On 3/4/23 19:32, David G. Johnston wrote: On Sat, Mar 4, 2023 at 5:20 PM Ron wrote: But crosstab takes text strings as parameters.  How then do you use \set variables in crosstab queries? You need to dynamically write the textual query you want to send to the crosstab function

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On 2/16/23 09:47, cen wrote: Hi, I am running the same application (identical codebase) as two separate instances to index (save) different sets of data. Both run PostgreSQL 13. The queries are the same but the content in actual databases is different. One database is around 1TB and the

Re: Quit currently running query

On 2/28/23 04:53, Albert Cornelius wrote: How can I quit a currently running query? I've issued a query and my server does not respond anymore. Is there another solution than using kill -9? What *specifically* do you mean by "my server does not respond anymore"?  Because if "the server"

Re: Quit currently running query

On 2/28/23 11:31, Peter J. Holzer wrote: On 2023-02-28 07:42:08 -0600, Ron wrote: On 2/28/23 04:53, Albert Cornelius wrote: How can I quit a currently running query? I've issued a query and my server does not respond anymore. Is there another solution than using kill -9? What

Re: Repear operations on 50 tables of the same schema?

On 2/27/23 05:53, celati Laurent wrote: Good morning, I am new to Postgresql. I have 50 tables into a "ign" schema (schema other than public). I would like for each of these 50 tables: - Add a prefix to the name of the table: "IGN_bdTopo_" - Add a suffix to the table name: "_V1" - create a

Re: FW: Error!

Are you sure that you're entering the correct password? On 4/10/23 19:55, Arquimedes Aguirre wrote: Sent from Mail for Windows *From: *Arquimedes Aguirre *Sent: *Sunday, April 9, 2023 5:59 PM *To:

Re: Suppress logging of "pg_hba.conf rejects connection for host"

On 4/16/23 10:02, ertan.kucuko...@1nar.com.tr wrote: Hello, One of the systems running PostgreSQL 14.7 receive a lot of lines like in the subject. I have below pg_hba.conf line and that line causes these to be logged. host all all 0.0.0.0/0 reject If possible, I do not want to see these lines

Re: TEXT column > 1Gb

On 4/12/23 14:21, Rob Sargent wrote: On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? Not sure what OP is doing with plant genomes (other than some genomics) but the tools all use files and pipeline of sub-tools. In and out

Re: TEXT column > 1Gb

/Must/ the genome all be in one big file, or can you store them one line per table row? On 4/12/23 12:19, Joe Carlson wrote: I’ve certainly thought about using a different representation. A factor of 2x would be good, for a while anyway. For nucleotide sequence, we’d need to consider a 10

Re: Multiple Postgrest Verisons how to set one version as default.

On 4/29/23 07:28, Gautham Raj wrote: Hi, *Problem: Having multiple versions of Postgres installed in CentOS 7. I Want to set the 9.5 version as default. Not able to access Postgres 9.5 through the terminal as well.* 1. For Command *psql --version* I'm getting 9.5 as the version. 2. For

Re: COPY and custom datestyles. Or some other technique?

On 3/29/23 18:31, Adrian Klaver wrote: On 3/29/23 16:24, Ron wrote: Postgresql 13.10 $ psql -h myhost.example.com -X dba \ -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH DELIMITER '|';" ERROR:  date/time field value out of range: "2013061914122501&

Re: COPY and custom datestyles. Or some other technique?

On 3/29/23 21:06, David G. Johnston wrote: On Wed, Mar 29, 2023 at 6:51 PM Ron wrote: It would be really helpful to be able to reposition columns in tables.  That way, one could: add the new TIMESTAMP column, populate it using to_timestamp(), drop the text column

Re: COPY and custom datestyles. Or some other technique?

On 3/29/23 19:20, Thorsten Glaser wrote: On Wed, 29 Mar 2023, Ron wrote: There are 550+ tables, so something that I can do once on this end would make my life a lot easier. Some quick perl or awk or shell job to batch-change the field to an accepted syntax is probably quicker. Even easier

Re: Cluster table based on grand parent?

On 3/28/23 08:17, Dominique Devienne wrote: Hi again, I just sent a question regarding parent/child and cascading FKs. But in reality, our schema has not 2 but 3 "layers", with an additional grandchild "leaf" table (see below). Given that many acces patterns are parent-based, i.e. get all

Re: Cluster table based on grand parent?

On 3/28/23 11:28, Dominique Devienne wrote: On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: You can only get from parent to grandchild via//child.id <http://child.id> to grandchild.parent, so why not cluster grandchild on grandchild.parent? Hi. I don't understand your questio

Re: Patroni vs pgpool II

On 4/3/23 01:33, Inzamam Shafiq wrote: Hi Guys, Hope you are doing well. Can someone please suggest what is one (Patroni vs PGPool II) is best for achieving HA/Auto failover, Load balancing for DB servers. Along with this, can you please share the company/client names using these tools for

Re: Patroni vs pgpool II

On 4/6/23 23:16, Tatsuo Ishii wrote: But, I heard PgPool is still affected by Split brain syndrome. Can you elaborate more? If more than 3 pgpool watchdog nodes (the number of nodes must be odd) are configured, a split brain can be avoided. Split brain is a hard situation to avoid. I suppose

Re: Patroni vs pgpool II

On 4/7/23 05:46, Jehan-Guillaume de Rorthais wrote: On Fri, 07 Apr 2023 18:04:05 +0900 (JST) Tatsuo Ishii wrote: And I believe that's part of what Cen was complaining about: « It is basically a daemon glued together with scripts for which you are entirely responsible for. Any small

COPY and custom datestyles. Or some other technique?

Postgresql 13.10 $ psql -h myhost.example.com -X dba \     -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH DELIMITER '|';" ERROR:  date/time field value out of range: "2013061914122501" CONTEXT:  COPY t_id_master, line 1, column update_timestamp: "2013061914122501" The

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

On 4/13/23 09:44, Sebastien Flaesch wrote: Hello, Is there an easy way to convert JSON data containing ASP.NET AJAX Dates into PostgreSQL timestamp? I have this kind of JSON data: {     "PurchaseOrder" : "45",     "CreationDate" : "\/Date(167235840)\/",     "LastChangeDateTime"

Re: Question regarding UTF-8 data and "C" collation on definition of field of table

Why are you specifying the collation to be "C" when the default db encoding is UTF8, and UTF-8 has Greek, Chinese and English encodings? On 2/5/23 17:08, Dionisis Kontominas wrote: Hello all,   I have a question regarding the definition of the type of a character field in a table and more

Re: How to use the BRIN index properly?

1. The whole index does not need to fit in memory, just the parts of it you need at that time. 2. Partition the table by the primary key.  Each index will be *much* smaller, since each child will be smaller. On 2/8/23 16:14, Siddharth Jain wrote: OK so in that case we are left with the B-Tree

Re: How to use the BRIN index properly?

Is the data in your tables stored in natural correlation with those *three* columns?  I'm dubious that can even happen. BRIN is best for *range queries* on tables who's data is added in the same order as the key in the BRIN index (for example, a BRIN index on a timestamp field in a log table

Re: Switching identity column to serial

On 2/3/23 18:54, Erik Wienhold wrote: I was wondering if it's possible to drop a column identity (not the column itself) while keeping the attached sequence. This would avoid recreating an identical sequence (especially with a correct start value and owner). Why doesn't this work? BEGIN; DROP

Re: Switching identity column to serial

On 2/3/23 22:41, Ron wrote: On 2/3/23 18:54, Erik Wienhold wrote: I was wondering if it's possible to drop a column identity (not the column itself) while keeping the attached sequence. This would avoid recreating an identical sequence (especially with a correct start value and owner). Why

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

On 2/7/23 09:06, Adrian Klaver wrote: On 2/7/23 06:09, Philip Semanchuk wrote: On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote: On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to change the

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

On 2/7/23 09:06, Adrian Klaver wrote: On 2/7/23 06:09, Philip Semanchuk wrote: On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote: On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to change the

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

Ignore this... On 2/7/23 13:05, Ron wrote: On 2/7/23 09:06, Adrian Klaver wrote: On 2/7/23 06:09, Philip Semanchuk wrote: On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote: On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: I have a column defined GENERATED ALWAYS AS {my_expression

Re: Multi-column index: Which column order

On 2/15/23 02:46, Laurenz Albe wrote: [snip] Which one is best? CREATE UNIQUE INDEX ix1 ON art (code, etb) or CREATE UNIQUE INDEX ix1 ON art (etb, code) (or its PRIMARY KEY equivalent) Both are the same. There is an old myth that says that you should use the moew selective column first

Re: Query plan for "id IS NULL" on PK

On 2/14/23 18:21, David Rowley wrote: [snip] since it likely only applies to nearly zero real-world cases Are you sure? -- Born in Arizona, moved to Babylonia.

Re: Multi-column index: Which column order

On 2/15/23 21:45, Laurenz Albe wrote: On Wed, 2023-02-15 at 10:20 -0600, Ron wrote: On 2/15/23 02:46, Laurenz Albe wrote: [snip] Both are the same. There is an old myth that says that you should use the more selective column first (which would be "code"), but that is just a m

Re: Sequence vs UUID

On 2/2/23 17:11, Peter J. Holzer wrote: On 2023-02-02 10:22:09 -0500, Benedict Holland wrote: Well... until two processes generate an identical UUID. That happened to me several times. How did that happen? Pure software implementation with non-random seed? Hardware with insufficient entropy

Re: psql "\d" no longer working

On 2/12/23 03:02, Rob Sargent wrote: Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR:  column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still

Re: Reproducing incorrect order with order by in a subquery

On 6/14/23 05:03, Ruslan Zakirov wrote: [snip] Anyway, yesterday I tried my simplified case on Pg latest, Pg 11 and on mysql latest. Had no luck. Either my test case is too simple or I can not find the correct distribution of data between two tables. This is when you need a prod copy... --

Re: date format

On 6/14/23 13:02, Marc Millas wrote: On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston wrote: On Wed, Jun 14, 2023 at 9:42 AM Marc Millas wrote: Hi, I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster but.. One

Re: function to_char(unknown) is not unique at character 8

On 7/6/23 04:19, gzh wrote: Hi, I upgraded the version of PostgreSQL from 12.6 to 12.13, when I execute the sql below , the to_char function caused the following error. ---SQL-- select TO_CHAR('100'); Isn't '100' already a character string? How do

Re: track_activity_query_size max practical size?

On 7/7/23 09:51, Adrian Klaver wrote: On 7/7/23 07:42, Ron wrote: We've got some Very Large Queries that take a long time. An EXPLAIN(ANALYZE BUFFERS) would go a long way here. You can't run EXPLAIN(ANALYZE BUFFERS) if you don't have a query to run.  That's what track_activity_query_size

track_activity_query_size max practical size?

We've got some Very Large Queries that take a long time.  Even setting taqs to 10KB isn't adequate, so I want to significantly bump it, but am concerned about side effects of setting it to 48KB or even 64KB. -- Born in Arizona, moved to Babylonia.

Re: track_activity_query_size max practical size?

On 7/7/23 09:55, Adrian Klaver wrote: On 7/7/23 07:51, Adrian Klaver wrote: On 7/7/23 07:42, Ron wrote: We've got some Very Large Queries that take a long time. An EXPLAIN(ANALYZE BUFFERS) would go a long way here. Even setting taqs to 10KB isn't adequate, so I want to significantly bump

Re: track_activity_query_size max practical size?

On 7/7/23 10:13, Adrian Klaver wrote: On 7/7/23 07:58, Ron wrote: On 7/7/23 09:55, Adrian Klaver wrote: On 7/7/23 07:51, Adrian Klaver wrote: On 7/7/23 07:42, Ron wrote: We've got some Very Large Queries that take a long time. An EXPLAIN(ANALYZE BUFFERS) would go a long way here. Even

Re: suggestion about time based partitioning and hibernate

On 7/18/23 01: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_date date,

Re: Upgrade Failure

pg_ctl is not pg_controldata. (I bet you ran "pg_ctl stop --mode=immediate".  That's not a clean shutdown.) On 7/18/23 05:26, Johnathan Tiamoh wrote: I used pg_ctl to stop the cluster. When I used the same pg_ctl to check status, it says no server is running On Tue, Jul 18, 2023 at 6:14 AM

Re: what causes new temp schemas to be created

On 7/10/23 09:20, David G. Johnston wrote: On Mon, Jul 10, 2023 at 7:18 AM Ted Toth wrote: When a temp table is created I see a pg_temp_NNN (for example pg_temp_3, pg_toast_temp_3) schemas created when/why are additional temp schemas created( pg_temp_4/pg_toast_temp_4)? Temporary

Re: Rocky Linux 9 and postgres10

On 7/15/23 11:37, Daniel Gallo wrote: Good afternoon! I am writing to ask you the following question. Can postgres10 be installed on rocky linux 9? Although I know that it is a version that has already finished its life cycle, we are in migration processes We have a system with centos 7.5 and

Re: Reset Postgresql users password

On 7/12/23 14:28, Johnathan Tiamoh wrote: Hello, I wish to find out if there is a way to reset all users in Postgresql password to the same password at once. To the same value?? -- Born in Arizona, moved to Babylonia.

Re: Problem perhaps after upgrading to pgadmin4 7.4

On 7/13/23 06:20, Carl Erik Eriksson wrote: Query tool PGadmin on my mac If I enter a query like select count(*) from table_1I get a correct response from the server If I enter select * from table_1 I get an error message that I do not understand: Error Message:missing FROM-clause entry

Re: Query take a long time and use no index

On 7/17/23 04:13, basti wrote: [snip] The Indexes: volkszaehler=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE tablename LIKE 'data%' ORDER BY tablename,indexname;  tablename |   indexname    | indexdef

Re: suggestion about time based partitioning and hibernate

On 7/20/23 10:31, Luca Ferrari wrote: On Wed, Jul 19, 2023 at 6:45 PM Alvaro Herrera wrote: Therefore I suggest to avoid doing that. Either look at some other partitioning scheme that doesn't involve adding columns to the primary key, or disregard partitioning for this table entirely. What

<    5   6   7   8   9   10   11   12   13   14   >