Re: "INSERT ON CONFLICT UPDATE" - Use of indexes ?

Laura Smith schrieb am 08.06.2020 um 14:42: > Hi, > > What'st the current state of play with indexes and ON CONFLICT ?  The docs > seem to vaguely suggest it is possible, but this SO question > (https://stackoverflow.com/questions/38945027/) seems to suggest it is not. > > I've got a unique named

Dependencies of Matviews?

I would like to extract the dependency between materialized views. e.g. in the following situation: create materialized view mv1 as select ; create materialized view mv2 as select ... from mv1, ...; I would like to know that mv2 depends on mv1. I assumed this could be done through pg

Re: Dependencies of Matviews?

Tom Lane schrieb am 23.06.2020 um 23:25: I would like to extract the dependency between materialized views. e.g. in the following situation: create materialized view mv1 as select ; create materialized view mv2 as select ... from mv1, ...; or more readably, regression=# select pg_

timestamp - timestamp result

I regularly see people suggesting to use extract(day from one_timestamp - other_timestamp) to calculate the difference between two timestamps in days. But I wonder if the "format" of the resulting interval is guaranteed to only have days (and not months or years) The following: times

Does TOAST really compress the complete row?

I am confused about one claim in this blog post: https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-binary-objects All columns that come after data > 2000 bytes participate in The Large Attribute Strorage Technique (TOAST). This storage is for the row, not the column. Your id column comes

Is this a bug in pg_current_logfile() on Windows?

Hello, I noticed the following strage output when running Postgres 12.3 (not psql) on Windows postgres=# select pg_current_logfile(); pg_current_logfile pg_log/postgresql-2020-07-08.log\r (1 row) Note the "\r" at the end of the

Re: Is this a bug in pg_current_logfile() on Windows?

Tom Lane schrieb am 08.07.2020 um 18:41: Somehow, the reading file is being left in binary mode and thus it's failing to convert \r\n back to plain \n. Now the weird thing about that is I'd have expected "r" and "w" modes to imply Windows text mode already, so that I'd have figured that _setmode

Re: Postgresql-12 taking more time to execute the query

Vishwa Kalyankar schrieb am 10.07.2020 um 08:50: > Previously we are running postgresql-10with postgis 2.5.3 and now we > are updated to postgresql-12 and postgis-3.0.1, and in postgresql-10 > one query is taking 20 sec and same query is taking upto 80 sec. Most of the slowdowns I have seen when u

Logical replication from 11.x to 12.x and "unique key violations"

Hello, I have a strange error when using logical replication between a 11.2 (I know!) source database and a 12.3 target. If I create the publication with all needed tables (about 50) at once, I get "duplicate key value violates unique constraint xxx_pkey" errors during the initial replication

Re: JDBC driver version for a given Postgres version

Shantanu Shekhar schrieb am 14.07.2020 um 14:34: > We are upgrading our Postgres instance from 9.6.11 to 10.11. Then as > part of a second upgrade we will go from 10.11 to 11.6. Currently > (with 9.6.11) we are using the 42.2.1 JDBC driver. I am trying to > figure out the impact our database upgrad

Re: Logical replication from 11.x to 12.x and "unique key violations"

> I have a strange error when using logical replication between a 11.2 > source database and a 12.3 target. > > If I create the publication with all needed tables (about 50) at > once, I get "duplicate key value violates unique constraint xxx_pkey" > errors during the initial replication (when crea

Re: Extension vs Implementing Wire Protocol

Matthew Tamayo-Rios schrieb am 20.07.2020 um 22:13: Examples of behaviors we'd like to have: * 'SELECT * FROM table;' should return masked versions of the columns based on policy for that specific user. * 'SELECT * FROM table;' should return just the columns accessible to a  specific user.

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 20.07.2020 um 16:45: > On 7/20/20 7:22 AM, Thomas Kellerer wrote: >>> I have a strange error when using logical replication between a 11.2 >>> source database and a 12.3 target. >>> >>> If I create the publication with all need

Re: Logical replication from 11.x to 12.x and "unique key violations"

Tom Lane schrieb am 20.07.2020 um 20:04: > Adrian Klaver writes: >> I'm probably missing something, but would that not result in a 'key not >> found' type of error. The OP is seeing "duplicate key value violates >> unique constraint xxx_pkey". To me that indicates a doubling up of at >> least some

Re: Logical replication from 11.x to 12.x and "unique key violations"

John Ashmead schrieb am 20.07.2020 um 20:11: > I have had this problem with logical replication on PG 10 repeatedly. > In a clean build no problem. > > But if I am restarting replication because of some problem I’ve seen > problems with rows already present. > > My own fix, which has worked in my s

Re: Logical replication from 11.x to 12.x and "unique key violations"

Tom Lane schrieb am 21.07.2020 um 09:39: > In any case, we do offer as standard advice that you should reproduce > a problem on the latest minor release before filing a bug report. I know ;) I already told the "powers to be" and it's being addressed (I also went through the 11.x release notes, bu

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 21.07.2020 um 17:07: >> No, as mentioned, those are varchar(20) columns. >> The values are generated by the application (no default value defined for >> the column) > > Aah I see my mistake I was going off your follow up question not the > original post. In that original p

Re: Logical replication from 11.x to 12.x and "unique key violations"

Thomas Kellerer schrieb am 13.07.2020 um 11:52: > If I create the publication with all needed tables (about 50) at > once, I get "duplicate key value violates unique constraint xxx_pkey" > errors during the initial replication (when creating the > subscription). Turns out

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 22.07.2020 um 16:42: Or is this a copy/paste issue? That.

Re: Need free PG odbc driver for Windows 10

David Gauthier schrieb am 28.07.2020 um 18:40: Hi: I need a free odbc driver for PG to be installed on Windows 10 that my user community can easily install. By "easily install" I mean no binaries, no zipped file, etc... just point-click-install (with the usual confirmations and accepting defaul

Re: PostgreSQL transaction aborted on SQL error

Urko Lekuona schrieb am 04.08.2020 um 10:44: > First time writing here, I hope this is the right place to ask this > kind of question. I've been working with PostgreSQL for a while now > but i've just found out that PostgreSQL marks my transaction for > ROLLBACK and even stops the execution of the

Re: How bad is using queries with thousands of values for operators IN or ANY?

Thorsten Schöning schrieb am 31.08.2020 um 12:37: > So for what query size or number of IDs to compare in IN would you > consider a different approach at all? In my experience "hundreds" of IDs tend to be quite slow if used with an IN clause. Rewriting the IN to a JOIN against a VALUES clause i

Re: PostgreSQL processes use large amount of private memory on Windows

Øystein Kolsrud schrieb am 17.09.2020 um 11:19: All settings typically referred to (like "work_mem" and "temp_buffers") are at default settings, and I have a very hard time seeing how those settings could add up to memory in the magnitude of 200MB. The "max_connections" settings is quite high (>1

Re: How to write such a query

Igor Korot schrieb am 18.09.2020 um 19:29: [code] CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int); CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2)); SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id; [/code] Assuming that the SELECT return 10 rows

Re: How to write such a query

Igor Korot schrieb am 18.09.2020 um 22:18: Thank you for the info. My problem is that I want to emulate Access behavior. As I said - Access does it without changing the query internally (I presume). I want to do the same with PostgreSQL. I'm just trying to understand how to make it work for an

Re: PostgreSQL on Windows' state

Alessandro Dentella schrieb am 23.09.2020 um 10:37: Hi, disclaimer: I've never been a Windows user and I send this email just on behalf of a friend that has problems convincing his team to use PostgreSQL. I'd like to understand if what the team replies to him is correct or not. The final p

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

tutilu...@tutanota.com schrieb am 25.09.2020 um 06:41: All lowercase is good That's your personal opinion -- not some kind of fact, and it definitely goes against everything that I believe. You have to accept that every programming environment has its own best practices (in terms of coding

Re: horizontal scaling

I think currently the best option is something like Citus[1] if you want to stick with vanilla Postgres. Otherwise Greenplum might be another option. Thomas [1] https://github.com/citusdata/citus Rita schrieb am 25.09.2020 um 13:51: There was a lot of talk

Re: horizontal scaling

I think currently the best option is something like Citus[1] if you want to stick with vanilla Postgres. Otherwise Greenplum might be another option. Thomas [1] https://github.com/citusdata/citus Rita schrieb am 25.09.2020 um 13:51: There was a lot of talk

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

Adrian Klaver schrieb am 25.09.2020 um 17:02: Would it be nice if I could use special characters like öäü in the names of tables and columns (without the hassle of quoting them)? Yes, absolutely. But you can use them without quoting: select version();   ve

Re: pg_upgrade issue upgrading 10 -> 13

Sean Brown schrieb am 01.10.2020 um 16:51: I’m having a little problem using pg_upgrade to move from 10 to 13, I’m assuming the issue is related to the removal of pg_pltemplate, but I can’t find anything related to how to handle it. pg_upgrade —check reports that the clusters are compatible,

Re: How to migrate column type from uuid to serial

Hemil Ruparel schrieb am 07.10.2020 um 16:02: > Yes the id is stored as a uuid. Then it should be declared with the data type uuid, which only needs 16 bytes.

Re: How to migrate column type from uuid to serial

Hemil Ruparel schrieb am 07.10.2020 um 16:21: > it is declared as uuid. But how does it occupy only 16 bytes? Because a UUID is internally simply a 128bit number - the dashes you see are just formatting. But if you can only send the text represnation, then yes 32 characters aren't enough.

Re: How to migrate column type from uuid to serial

>>> it is declared as uuid. But how does it occupy only 16 bytes? >> Because a UUID is internally simply a 128bit number - the dashes you see are >> just formatting. > Sorry if this is silly but if it is a 128 bit number, why do we need 32 > characters to represent it? The 36 (or 32 without the

Re: Any interest in adding match_recognize?

Rob Sargent schrieb am 09.10.2020 um 18:33: On Oct 9, 2020, at 10:25 AM, Guyren Howe wrote: I just became aware of the SQL 2016 standard match_recognize feature, which allows for rich pattern matching across groups of rows. It’s a great feature, currently only supported in Oracle. I can find n

Re: Parameter value from (mb/gb) to bytes

Raul Kaubi schrieb am 14.10.2020 um 12:22: > Is there a simple way to dynamically get for example parameter > „shared buffers“ value (megabytes or gigabytes) to bytes, for > monitoring perspective..?> >   > > At the moment, this gives me value in GB. > > # psql -U postgres -Atc "show shared_buffers

Re: Parameter value from (mb/gb) to bytes

Thomas Kellerer schrieb am 14.10.2020 um 15:55: > Raul Kaubi schrieb am 14.10.2020 um 12:22: >> Is there a simple way to dynamically get for example parameter >> „shared buffers“ value (megabytes or gigabytes) to bytes, for >> monitoring perspective..?> >>   >

New "function tables" in V13 documentation

In case someone is interested: there is a little discussion going on on Reddit whether the new format of presenting functions in V13 is a step backwards: https://www.reddit.com/r/PostgreSQL/comments/jpi0rp/does_anyone_else_feel_like_the_v13_docs_are_a/ Thomas

Re: vacuum vs vacuum full

Ron schrieb am 18.11.2020 um 10:44: > No matter how long it takes, this is an excellent argument for > partitioning Very Large Tables: many maintenance tasks are made > *much* easier. The problem is, you can't partition every table as long as Postgres does not support a primary key that is indepen

Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 11:29: > I have a column called |targeting| in a table called |campaigns| . > [...] > and I need to select all the |ids| in |includes|. > Currently, I am doing it like this  > > SELECT  > |targeting#>'{targets,0,audienceSegments,0,includes,0,segments,allOf,

Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 17:00: I actually have version 9.3.17 😬 On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer mailto:sham...@gmx.net>> wrote: Snjezana Frketic schrieb am 18.11.2020 um 11:29: > I have a column called |targeting| in a table called |

Re: Multiple result set to be returned in procedure/function

Muthukumar.GK schrieb am 19.11.2020 um 09:27: > is it possible to return Multiple results set from procedure/function > on single execution. Please advise me on this. we are planning to > migrate things from sqlserver to postgresql where my existing sql > stored procs will return multiple result se

Re: Multiple result set to be returned in procedure/function

Daniel Verite schrieb am 19.11.2020 um 13:06: >>arthur=> select * from get_results(); >>get_results >> >> >> > > Friendlier names may be used by assigning them in the function, > i.e. plpgsql does support: > > declare > c1 refcursor := 'mycursorname'

Re: Determine if postgresql cluster running is primary or not

Raul Kaubi schrieb am 20.11.2020 um 09:53: > CentOS 7 > Postgres 9 to 12 > > For monitoring purpose, I would like that certain scripts are only run in > primary server. > So I am looking ways to universally discover if postgresql cluster that is > running is primary or not. As the standby will b

Re: Calling Procedure from another procedure in Postgres

Muthukumar.GK schrieb am 02.12.2020 um 11:20: > I need to call the procedure(not function) and insert the records > into a temporary table from another procedure in postgres. When > executing the procedure 'Sampleproc2',I got some below syntax error. > Kindly let me know whether postgres supports

Re: Is the Halloween problem an issue in Postgres

guy...@icloud.com schrieb am 02.12.2020 um 21:27: The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE clause of the same query. I just saw a presentation from someone about how in SQL Server he recommended writing changes to a tem

Re: Deferrable FK not behaving as expected.

Ron schrieb am 07.12.2020 um 19:15: Referenced by:     TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"   FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think if you only mention "deferrable" this is the same as "deferrable ini

Re: PL/java

Rob Sargent schrieb am 07.12.2020 um 23:22: > If I'm tracking correctly I can say the java implementation of > CopyManager is, to me, blindingly fast. So if the E and the T are in > java then certainly the L can be also. I can confirm that using CopyManager in JDBC has pretty much the same perfo

Re: Improving performance of select query

Karthik Shivashankar schrieb am 14.12.2020 um 12:38: > I have a postgres(v9.5) table named customer holding 1 billion rows. > It is not partitioned but it has an index against the primary key > (integer). I need to keep a very few records (say, about 10k rows) > and remove everything else. > > /ins

Re: How to REMOVE a fillfactor set by accident?

Thorsten Schöning schrieb am 31.12.2020 um 21:13: Is there some way to REMOVE the fillfactor where it is set, so that Postgres applies it's defaults? Would be great to have an output of NULL everywhere where fillfactor is output instead of sometimes e.g. 100 and more often NULL. I've already tri

Re: CROSSTAB( .. only one column has values... )

Adam Tauno Williams schrieb am 05.01.2021 um 16:46: I'm using the crosstab feature and do not understand why I am only getting values in the first column. The query: SELECT date_trunc('month', t2.value_date) AS invoice_date, t1.value_string AS invoice_type COUNT(*) FROM document d LE

Suggestion: provide a "TRUNCATE PARTITION" command

Hello, I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres? Especially during bulk loads it's more efficient to TRUNCATE a partition if I know I want to replace all rows, rather than doing a DELETE. Currently this requires dynamic SQL which isn't always feasible (and mi

Re: Suggestion: provide a "TRUNCATE PARTITION" command

legrand legrand schrieb am 08.01.2021 um 14:57:> maybe a naïve plpgsql as proposed in https://www.postgresql-archive.org/Partitionning-support-for-Truncate-Table-WHERE-td5933642.html may be an answer Yes I am aware of that (and that's what I have used so far) - I just thought it would make li

Re: Suggestion: provide a "TRUNCATE PARTITION" command

Michael Lewis schrieb am 08.01.2021 um 16:32: On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer mailto:sham...@gmx.net>> wrote: Hello, I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres? Especially during bulk loads it's more efficient to

Re: Suggestion: provide a "TRUNCATE PARTITION" command

Michael Lewis schrieb am 08.01.2021 um 17:47: > For me, it seems too easily error prone such that a single typo in > the IN clause may result in an entire partition being removed that > wasn't supposed to be targeted. I don't see how this is more dangerous then:      del

Re: upgrade postgres 9.5 to 9.6

Atul Kumar schrieb am 15.01.2021 um 14:17: > I want to upgrade my server from postgres 9.5 to 9.6, but my DB size > is in TBs and I want to do it in minimum downtime (2-3 hours) so > please help me how should I perform it. > > > Please share the document, if possible, it will be grateful. pg_upgra

Re: Accounting for between table correlation

Atul Kumar schrieb am 15.01.2021 um 16:29: As per Ron, you are not supposed to ask your questions here. As According to him, we should keep on doing research on internet rather than asking for support directly even you have done enough research and until unless “Ron” won’t be satisfied you have

Re: interval data type

James B. Byrne schrieb am 21.01.2021 um 22:22: What is the difference between interval(3)[] and simply interval(3)? Where in the documentation is the [] syntax discussed? The [] denotes an array of intervals. So in a column defined as interval[] you can store multiple intervals, just like in

Re: Postgres blog sites centrally

Yambu schrieb am 28.01.2021 um 16:21: > Is there a central place where i can get postgres blogs as they are > written by different blog sites That would be https://planet.postgresql.org/

Re: Foreign table performance issue / PostgreSQK vs. ORACLE

Markhof, Ingolf schrieb am 29.01.2021 um 13:56: > The set-up basically is a production database and a reporting > database. As names indicate, the production database is used for > production, the reporting database is for analysis. On the reporting > database, the only way to access product data i

Re: Postgres 9.4 Needed

Taranum Fatima schrieb am 09.02.2021 um 01:59: > I have to install Postgres 9.4 which is compatible with Jira 7.2.2 .  > > We have to migrate the data from older version to New  but before we need to > restore the Data . Hence, Postgres 9.4 is needed. > > I do not find the 9.4 repo or postgresql94

Re: Turn jit off for slow subquery in Postgres 12

Andrus schrieb am 16.02.2021 um 07:48: > Last year I posted testcase [1] which shows that using jit makes execution > magnitude slower ( 2.5s vs 0.4 sec) in typical shopping cart application > product search in Postgres 12. > > There are also other reports on this [2,3]. > > I tried to turn jit of

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

Guy Burgess schrieb am 15.02.2021 um 11:52: > The mystery now is that the only process logged as touching the > affected WAL files is postgres.exe (of which there are many separate > processes). Could it be that one of the postgres.exe instances is > holding the affected WAL files in use after anot

Re: Slow while inserting and retrieval (compared to SQL Server)

sivapostg...@yahoo.com schrieb am 17.02.2021 um 13:01: > To populate some basic data we try to insert few records (max 4 > records) in few tables (around 6 tables) from one window. We feel > that the insert time taken is longer than the time taken while using > Sql Server. We tested almost a sim

Re: Slow while inserting and retrieval (compared to SQL Server)

sivapostg...@yahoo.com schrieb am 17.02.2021 um 14:27: > We use datawindows. Datawindows will send the required DML > statements to the database. > And it sent in format 1 . > > IN start of the application, Autocommit set to True. > Before update of any table(s) > Autocommit is set to False > Inse

Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber schrieb am 20.02.2021 um 19:39: So I am trying: # SELECT                 JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day,                 JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int) AS completed,                 JSONB_AGG(SUM(CASE

Re: Script checking to see what database it's connected to

Julien Rouhaud schrieb am 22.02.2021 um 02:19: >> The output: >> $ psql12 -f test_pg.sql >> t >> connected to postgres >> got here >> psql:test_pg.sql:15: ERROR: syntax error at or near "exit" >> LINE 1: exit > > Well, the supported commands did work. You should probably look at > https://www.pos

Re: Unexpected zero results

Viliam Ďurina schrieb am 23.03.2022 um 17:56: Hello all, I'm experimenting with JSON-path functions, and stumbled upon this query:   SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)') It returns 0 rows. I expected it to return one row with `null` value. Isn't it the case that `SELECT ` shoul

Re: TO_DATE function between PostgreSQL 8.2 and 9.4

gzh schrieb am 17.05.2022 um 17:55: I have had a Perl Website working for 7 years and have had no problems until at the weekend I replace my database server with a newer one. Database server (old): PostgreSQL 8.2 32bit Database server (new): PostgreSQL 9.4 64bit I run following sql in Postgre

Re: Automatic PK values not added to new rows

Rich Shepard schrieb am 25.05.2022 um 20:15: On Wed, 25 May 2022, Adrian Klaver wrote: Do: select * from people_person_nbr_seq; and report back the results. Adrian, Huh! bustrac=# select * from people_person_nbr_seq;  last_value | log_cnt | is_called +-+---

How to drop a subscription inside a stored procedure?

I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. For that, I essentially want to drop and re-create the subscription. In order to be able to do that, the tables need to be empty. So the approach is: Run a query to get all replicated t

Re: How to drop a subscription inside a stored procedure?

Adrian Klaver schrieb am 10.06.2022 um 16:58: On 6/10/22 05:57, Thomas Kellerer wrote: I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. However, the "drop subscription" part results in this error: ERROR:  DROP SUBSCRIPT

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

Bryn Llewellyn schrieb am 04.06.2022 um 03:41: Am I missing a use case where an object with a key-value pair with a JSON null value is meaningfully different from one where the key is simply absent? It seems the JSON specification doesn't actually define equality. But the JSON patch RFC 6902[1]

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Sebastien Flaesch schrieb am 19.07.2022 um 18:50: > Tom, > > /If that's the behavior you want, you can build it out of standard SQL > facilities (e.g. update a one-row table). > / > > Can you elaborate please? > > Do you mean the code should use an UPDATE on a one-row table to acquire a >

Re: Copying records from TABLE_A to TABLE_B (in the same database)

Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are:     INSERT INTO table_a SELECT * FROM table_b; and     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);     \COPY table_b

Re: Postgres question

ajay venki schrieb am 17.08.2022 um 14:39: > Thanks. I am looking forward to install pgloader tool to migrate my MS SQL > data to postgres.  > I tried searching it online and the instructions were not clear to me. Is > there any article or video which talks about pgloader installation on windows

Re: Corrupted Postgresql Microsoft Binaries

Hillary Masha schrieb am 23.08.2022 um 20:58: I downloaded the microsoft postgresql binaries for versions 14.5, 13.8, 12.12, 11.17, 10.22 from https://www.enterprisedb.com/download-postgresql-binaries and found that there was an error with opening the zip files. Does anyone else use these files a

pg_upgrade to 15 fails on Windows because of xml_is_well_formed()

When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails with: pg_restore: creating FUNCTION "public.xml_is_well_formed("text")" pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 647; 1255 23216 FUNCTION xml_is_well_formed("text") postgres pg_restore: error: could

Re: pg_upgrade to 15 fails on Windows because of xml_is_well_formed()

Tom Lane schrieb am 13.10.2022 um 21:01: When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails with: pg_restore: error: could not execute query: ERROR: could not find function "xml_is_well_formed" in file "c:/Program Files/PostgreSQL/15/lib/pgxml.dll" I don't understa

Re: Postgres 15 upgrades and template1 public schema

Bruno Wolff III schrieb am 19.10.2022 um 22:36: I noticed when I did an upgrade from Postgres 14 to 15 that the public schema in template1 was still owned by postgres instead of pg_database_owner. I was expecting it to change because the release notes said that new database clusters would have

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

gogala.mla...@gmail.com schrieb am 19.10.2022 um 01:46: Amazon, lead by Kevin Closson, the guy who has famously designed Oracle Exadata among other things, even came up with the recipe how to migrate it to Postgres: https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-t

Re: High CPU usage

ertan.kucuko...@1nar.com.tr schrieb am 20.10.2022 um 21:59: Hello, I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very high CPU usage on my Linux system as below PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 2357756 postgres 20 0 244103

Calculating average block write time

I can easily calculate the average block read time using pg_stat_database and divide blk_read_time by blks_read. While there is a column blk_write_time, it seems that there is no cummulative measure for the total number of blocks written. Am I missing something, or is this simply not tracked (a

Re: pg_restore remap schema

Tom Lane schrieb am 08.08.2022 um 20:22: > Guillaume Lelarge writes: >> Le lun. 8 août 2022 à 18:28, Fabrice Chapuis a >>> Is a development in progress to add this option > >> Nope, never heard of someone working on this. > > People have asked for such a thing before, but it'd be quite difficult

Re: Calculating average block write time

Thomas Kellerer schrieb am 04.11.2022 um 10:19: > I can easily calculate the average block read time using > pg_stat_database and divide blk_read_time by blks_read. > > While there is a column blk_write_time, it seems that there is no > cummulative measure for the total number of

Re: Calculating average block write time

Laurenz Albe schrieb am 18.11.2022 um 16:51: I can easily calculate the average block read time using pg_stat_database and divide blk_read_time by blks_read. While there is a column blk_write_time, it seems that there is no cummulative measure for the total number of blocks written. Any ideas

Re: Get table catalog from pg_indexes

Igor Korot schrieb am 27.11.2022 um 23:13: >> I've never heard of a database referred to as a catalog. (That's always >> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) > > In the ODBC terminology the DB is usually referenced as catalog. JDBC uses the same term and t

Re: DBeaver session populating pg_stat_activity.backend_xmin

Dirschel, Steve schrieb am 25.01.2023 um 20:36: When I connect to the database through DBeaver with those 2 default settings changed and find that session in pg_stat_activity column xact_start is populated along with backend_xmin. Those get populated just by logging in. As you found out in the

Re: moving a database to a new 15.1 server

Matthias Apitz schrieb am 01.02.2023 um 13:24: > $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u lbs_lbsoclc01_dev_r1 > psql (15.1) > Type "help" for help. > > lbs_lbsoclc01_dev_r1=# > lbs_lbsoclc01_dev_r1=# \d > Did not find any relations. > lbs_lbsoclc01_dev_r1=# \d dbo.accession_index >

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

Sebastien Flaesch schrieb am 06.02.2023 um 18:17: Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY| Is there any built-in function that returns the underlying sequence name used for such column? Otherwise, an SQL query to return the sequence name?

Re: PostgreSQL

Joseph Kennedy schrieb am 07.02.2023 um 12:02: > I would like restrict access to sensitive or restricted information > for some users (eg. hide data of one or more clients for some > database users). > > PostgreSQL allows to create security policy as Row-Level Security, > policies based on the quer

Re: nested xml/json to table

Wim Bertels schrieb am 17.03.2023 um 11:05: > what would be the general idea: "easily" convert an hierarchical > structure like json or xml to a table; for example creating columns by > appending the key-names when going doing down the three, using null for > empty values, adding more columns as ne

Re: Oracle to PostgreSQL Migration

Inzamam Shafiq schrieb am 20.03.2023 um 13:57: > We have an Oracle DB which is around 1TB and we want to migrate to > PostgreSQL that have a new table structure, so we want to perform > data transformation and real time CDC from Oracle to PostgreSQL. Do > we have any good open source tool to achiev

Re: PostgreSQL vs MariaDB

Inzamam Shafiq schrieb am 24.03.2023 um 12:07: Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)? As others have pointed out, this is really hard to quantify. For large datab

Re: Move from MySQL to PostgreSQL

basti schrieb am 29.03.2023 um 14:57: > https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Util/Aggregation.php > > There are several problems. > > One of that ist the 'REPLACE INTO'. Most probably INSERT ... ON CONFLICT > An other problem is '@prev_timestamp := timestamp' That's t

Re: Select "todays" timestamps in an index friendly way

Lutz Horn schrieb am 23.10.2018 um 11:38: > I can of course make an explicit select for `ts` values that are > "today": > > select ts, id > from t > where ts >= '2018-10-23T00:00:00'::timestamp >and ts <= '2018-10-23T23:59:59'::timestamp; > > This uses an Bitmap Index Scan

Re: Select "todays" timestamps in an index friendly way

Lutz Horn schrieb am 23.10.2018 um 12:19: > Hi Thomas, > > On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote: >> I typically use: >> >> where ts >= date '2018-10-23' >> and ts < date '2018-10-23' + 1 > > But h

Re: Tool for comparing parameters

bhargav kamineni schrieb am 24.10.2018 um 16:30: > Is there any opensource or paid tool  that compares postgresql database > parameters? (NOT SCHEMA) . You could dump the content of pg_settings into CSV files (sorted by name) and then run a diff on both CSV files. Thomas

Different memory allocation strategy in Postgres 11?

I have a Postgres instance running on my Windows laptop for testing purposes. I typically configure "shared_buffers = 4096MB" on my 16GB system as sometimes when testing, it pays off to have a bigger cache. With Postgres 10 and earlier, the Postgres process(es) would only allocate that memory

Re: Different memory allocation strategy in Postgres 11?

Jeff Janes schrieb am 26.10.2018 um 17:42: I typically configure "shared_buffers = 4096MB" on my 16GB system as sometimes when testing, it pays off to have a bigger cache. With Postgres 10 and earlier, the Postgres process(es) would only allocate that memory from the operating system w

<    1   2   3   4   >