Re: Non-Stored Generated Columns

On Wed, Feb 28, 2024 at 8:11 PM Tom Lane wrote: > Dominique Devienne writes: > > Views can have foreign-keys? > > Surely you'd put the FK on the underlying table. > Again, the FKs are on the *generated* columns. So > > Generated view columns be indexed? > > [...[ it's hard to see much

Where the data stitching/update/deduplication should happen

Hello All, It's related to data flow to OLTP database which is mostly going to be postgres. We are designing a system which is going to move data from input files(in Avro format) to Goldengate to kafka topics to the database. Incoming files-->GGS--> KAFKA-->OLTP Database. This would be a heavy

RE: walreceiver fails on asynchronous replica [EXTERNAL] [SEC=UNOFFICIAL]

UNOFFICIAL Hello, Thanks for looking at this. I think I complicated things by including barman. I was just wanting to point out each primary streams to two locations - the walreceiver on the replica and the walreciver used by barman. We think the reason the barman WAL-receiver didn't fail is

Content for talk on Postgres Type System at PostgresConf

I am to talk about Postgres’s type system at PGConf: https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system I picked the issue because I think it’s poorly understood, greatly under-discussed, and an excellent way to empower postgres users. I am reasonably conversant

Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes

On 2024-02-28 21:22 +0100, Anthony Apollis wrote: > Please assist. I am using SSIS to read data from an Excel sheet into > Postgres. I have increased the column size a few times, just cant seem to > get the data in. Getting errors relating to destination column size. What is the data type of that

Re: Non-Stored Generated Columns

On Wed, Feb 28, 2024 at 2:11 PM Tom Lane wrote: > Dominique Devienne writes: > > Views can have foreign-keys? > > Surely you'd put the FK on the underlying table. > > > Generated view columns be indexed? > > You want an index on a virtual column? Sure, just build an expression > index (on the

Re: Documentation diff

> On 28 Feb 2024, at 19:59, Marcos Pegoraro wrote: > > > jian he escreveu: > > but I guess the OP wants a side by side rendered html comparison. > > like you buy a iphone then you can compare it like this: > > https://www.apple.com/iphone/compare/ > > No, I want show what changes ocurred on

Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes

Data in Excel On Wed, 28 Feb 2024 at 22:22, Anthony Apollis wrote: > Please assist. I am using SSIS to read data from an Excel sheet into > Postgres. I have increased the column size a few times, just cant seem to > get the data in. Getting errors relating to destination column size. > > [Excel

Re: Question on Table creation

On Wed, 28 Feb 2024 at 01:24, sud wrote: > While testing the pg_partman extension I see it by default creates a > "default partition" even if we provide the parameter p_start_partition as > "current date". But if someone purposely doesn't want to have the default > partitions , so as to avoid

Re: Orphan files filling root partition after crash

On Wed, 2024-02-28 at 20:30 +0100, Dimitrios Apostolou wrote: > Lucky you.  It should have been "relfilenode" rather than "oid", > > and some catalog tables don't have their files listed in the catalog, > > because they are needed *before* the database can access tables. > > I actually double

Re: Orphan files filling root partition after crash

On 2/28/24 11:30, Dimitrios Apostolou wrote: Thanks for the feedback Laurenz, On Wed, 28 Feb 2024, Laurenz Albe wrote: On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote: I ended up doing some risky actions to remediate the problem: Find the filenames that have no identically

Re: Orphan files filling root partition after crash

Thanks for the feedback Laurenz, On Wed, 28 Feb 2024, Laurenz Albe wrote: On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote: I ended up doing some risky actions to remediate the problem: Find the filenames that have no identically named "oid" in pg_class, and delete (move to

Re: Non-Stored Generated Columns

Dominique Devienne writes: > Views can have foreign-keys? Surely you'd put the FK on the underlying table. > Generated view columns be indexed? You want an index on a virtual column? Sure, just build an expression index (on the underlying table) that matches it. I agree with Laurenz that

Re: Documentation diff

> jian he escreveu: > but I guess the OP wants a side by side rendered html comparison. > like you buy a iphone then you can compare it like this: > https://www.apple.com/iphone/compare/ No, I want show what changes ocurred on both versions, the page your

Re: Need Assistance: Command to display procedures does not work

On 2/28/24 10:15, Sasmit Utkarsh wrote: Hi Adrian, Sorry, Yes I did connect to the correct DB and it's just a test database and there's no shc schema. After giving the pronamespace::regnamespace. I do see it has another schema as test. Thanks a lot for your guidance, pgAdmin4 was telling

Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

Greetings, * Matthew Dennison (m...@matty-uk.co.uk) wrote: > Sorry, yes I tried that and received the same results. Did you try doing a 'klist' after? Would be interesting to know if you got a Kerberos ticket or not. Thanks, Stephen signature.asc Description: PGP signature

Moving PostgreSQL servers to a new server

Hi, I am new to PostgreSQL. My scenario is as follows: https://cdn.bloghunch.com/uploads/uNxNoi5uVKeDibd5.webp I have a Master server and two Replica (Replica-1 and Replica-2) servers. One of these Replica servers is in read-only mode and a report is prepared from it using Microsoft PowerBI.

Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

Sorry, yes I tried that and received the same results. Sent from my iPhone On 28 Feb 2024, at 17:11, Stephen Frost wrote: Greetings, * Matthew Dennison (m...@matty-uk.co.uk) wrote: I have subsequence discovered that the psql command running from remote Windows (server/client) and RHEL8

Re: Need Assistance: Command to display procedures does not work

Hi Adrian, Sorry, Yes I did connect to the correct DB and it's just a test database and there's no shc schema. After giving the pronamespace::regnamespace. I do see it has another schema as test. Thanks a lot for your guidance, will make next time i verify with this details first, switching

Re: PostgreSQL Guard

Read https://www.tecmint.com/configure-postgresql-streaming-replication-in-centos-8/ . On Wed, Feb 28, 2024 at 10:36 AM Jason Long wrote: > Hi, > Thanks again. > I have another questions: > > 1- Should I just install PostgreSQL normally on the Standby server? > > 2- Are the steps the same for

Re: Non-Stored Generated Columns

On Wed, Feb 28, 2024 at 5:59 PM Laurenz Albe wrote: > On Wed, 2024-02-28 at 16:09 +0100, Dominique Devienne wrote: > > From https://www.postgresql.org/docs/16/ddl-generated-columns.html: > > > PostgreSQL currently implements only stored generated columns > > > > We use generated columns

Re: Need Assistance: Command to display procedures does not work

On 2/28/24 09:13, Sasmit Utkarsh wrote: Reply to list also Ccing list HI Adrian, Related to shc shema, they were part of different host/machine. Currently, I'm seeing the result for this discrepancy on another server machine where we have only a public schema. 1) I doubt that if this is

Re: Need Assistance: Command to display procedures does not work

Hello Adria/Rob, Please note, 'shc' schema was part of a different machine/host. For now, I see issue on another host machine which i connect using psql and PgAdmin4 and get different result on both and we have only public schema here on this machine shc_data=> SELECT proname AS

Re: Need Assistance: Command to display procedures does not work

On 2/28/24 09:08, Sasmit Utkarsh wrote: Thanks Rob So which one could I rely on for better information? probably, results shown by PgAdmin4 right? As  I get the below when I try to drop procedure through psql cmd and i see the procedure getting fetched from the query shc_data=> SELECT

Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

Greetings, * Matthew Dennison (m...@matty-uk.co.uk) wrote: > I have subsequence discovered that the psql command running from remote > Windows (server/client) and RHEL8 works as expected using GSS. PGAdmin4 also > works via Kerberos (was on my list of things to get working). It's just >

Re: Need Assistance: Command to display procedures does not work

Thanks Rob So which one could I rely on for better information? probably, results shown by PgAdmin4 right? As I get the below when I try to drop procedure through psql cmd and i see the procedure getting fetched from the query shc_data=> SELECT proname AS function_name,proacl AS privileges FROM

Re: Need Assistance: Command to display procedures does not work

On 2/28/24 08:52, Sasmit Utkarsh wrote: Hi Adrian, Thanks for the info, But I have another question: I could see the below functions list with the help on query execution on psql command line   shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM pg_proc WHERE proname in

Re: Non-Stored Generated Columns

On Wed, 2024-02-28 at 16:09 +0100, Dominique Devienne wrote: > From https://www.postgresql.org/docs/16/ddl-generated-columns.html: > > PostgreSQL currently implements only stored generated columns > > We use generated columns extensively. > And we have foreign-keys attached to those generated

Re: Need Assistance: Command to display procedures does not work

> On Feb 28, 2024, at 8:53 AM, Sasmit Utkarsh wrote: > > Hi Postgresql Team, > > Getting error while executing the below \df command to list the > procedures/functions. whereas query gives the appropriate results Please > assist on how to troubleshoot this. > >

Re: Need Assistance: Command to display procedures does not work

Hi Adrian, Thanks for the info, But I have another question: I could see the below functions list with the help on query execution on psql command line shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM pg_proc WHERE proname in

Re: Need Assistance: Command to display procedures does not work

On 2/28/24 07:53, Sasmit Utkarsh wrote: Hi Postgresql Team, Getting error while executing the below \df command to list the procedures/functions. whereas query gives the appropriate results Please assist on how to troubleshoot this. [sutkars...@dxctravel.svcs.entsvcs.com@zuccmshcd2

Need Assistance: Command to display procedures does not work

Hi Postgresql Team, Getting error while executing the below \df command to list the procedures/functions. whereas query gives the appropriate results Please assist on how to troubleshoot this. [sutkars...@dxctravel.svcs.entsvcs.com@zuccmshcd2 data-exports]$ psql "host=10.166.18.116 port=5432

Re: Efficient rows filter for array inclusion with gin index

Replying to myself after more investigation. On 28/02/2024 12:05, Shanti-Dominique wrote: 2)     SELECT  *     FROM    items i1     JOIN item_paths p1 ON i1.ref_id = p1.ref_id     JOIN items i2 ON ARRAY[i2.ref_id] <@ p1.item_path     WHERE   ... 2) uses the operator <@ which

Re: PostgreSQL Read-only mode usage

Hi,1- What does it mean, not the whole database? 2- Can this be considered a kind of guard? 3- Can you tell me a read-only scenario? For example, if we have three servers, then one of the servers can be the main server and the other two servers can be read only. On Wed, Feb 28, 2024 at

Re: PostgreSQL Guard

Hi,Thanks again.I have another questions: 1- Should I just install PostgreSQL normally on the Standby server? 2- Are the steps the same for other Linux distributions like Debian? On Wed, Feb 28, 2024 at 9:29 AM, Ron Johnson wrote: As before, I encourage you to read 

Non-Stored Generated Columns

Hi. >From https://www.postgresql.org/docs/16/ddl-generated-columns.html: > PostgreSQL currently implements only stored generated columns We use generated columns extensively. And we have foreign-keys attached to those generated columns. The fact they are always Stored thus wastes space in our

Re: Orphan table files at data/base/

I *know*, beyond reasonable doubt. Also, I have the luxury of stopping connections temporarily and having a backup ready. I know backups do not include such data. db restored from a backup is ~50GB. I can afford to screw up even. Thanks. Kontakt Laurenz Albe () kirjutas kuupäeval K, 28.

Re: Orphan files filling root partition after crash

On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote: > yesterday I was doing: > > ALTER TABLE x ADD UNIQUE ... , ADD PRIMARY KEY ...; > > The table is almost a billion rows long but lies in its own TABLESPACE > that has plenty of space. But apparently the ALTER TABLE command is >

Re: PostgreSQL Read-only mode usage

På onsdag 28. februar 2024 kl. 13:55:34, skrev Andreas Kretschmer < andr...@a-kretschmer.de >: Am 28.02.24 um 13:34 schrieb Jason Long: > Hello, > What is the use of a database in read-only mode? a standby-database will also be in read-only mode. Right, we have

Re: Orphan table files at data/base/

No, I would not remove those files without making 100% sure they do not belong to that database or any other. Are you sure you are inside database 16384 when you ran those commands? Does a 'stat' on those files line up with the time of the crash? If so, I would stop pg, move the files someplace

Re: Orphan table files at data/base/

On Wed, 2024-02-28 at 15:44 +0200, Riivo Kolka wrote: > I was an unfortunate sequence of commands (all in single transaction) > > DROP TABLE huge; > CREATE TABLE huge AS... (huge PostGIS ST_Union operation); > CREATE INDEX ON huge USING GIST (geometry); > > by a well-meaning user, that caused a

Re: PostgreSQL Guard

On Tue, Feb 27, 2024 at 3:48 PM Jason Long wrote: > Suppose you want to transfer the database of a website like Amazon.com to > a new server and delete the old one. Many users are buying and selling on > this website at the same time and it is not possible to turn off the > server. What do you

Orphan table files at data/base/

I was an unfortunate sequence of commands (all in single transaction) DROP TABLE huge; CREATE TABLE huge AS... (huge PostGIS ST_Union operation); CREATE INDEX ON huge USING GIST (geometry); by a well-meaning user, that caused a crash+recovery: server process (PID 92411) was terminated by signal

RE: Unable to get PostgreSQL 15 with Kerberos (GSS) working

I have subsequence discovered that the psql command running from remote Windows (server/client) and RHEL8 works as expected using GSS. PGAdmin4 also works via Kerberos (was on my list of things to get working). It's just locally on the server psql will not work to postgresql running on the

Re: Orphan files filling root partition after crash

Hi Sergey, On Wed, 28 Feb 2024, Sergey Fukanchik wrote: Hi Dimitrios, Do you have wal archiving enabled? $PGDATA/pg_wal/ is a bit different from tablespaces (including "default" one). It stores transaction journal. I don't think I have WAL archiving enabled. Here are the relevant WAL options

Re: PostgreSQL Read-only mode usage

Am 28.02.24 um 13:34 schrieb Jason Long: Hello, What is the use of a database in read-only mode? a standby-database will also be in read-only mode. Regards, Andreas -- Andreas Kretschmer CYBERTEC PostgreSQL Services and Support

Re: PostgreSQL Read-only mode usage

On Wed, 28 Feb 2024 at 13:34, Jason Long wrote: > What is the use of a database in read-only mode? Normally it is a connection/session to the database what is in read-only mode, not the whole database. As it has been pointed out, you connect in read only mode to read, so you are protected from

Sv: PostgreSQL Read-only mode usage

På onsdag 28. februar 2024 kl. 13:34:30, skrev Jason Long mailto:hack3r...@yahoo.com>>: Hello, What is the use of a database in read-only mode? Thank you. To be able to read, not modify, the data. Reporting-tools like Microsoft PowerBI can connect to PG and generate nice reports. --

PostgreSQL Read-only mode usage

Hello, What is the use of a database in read-only mode? Thank you.

Re: Orphan files filling root partition after crash

Hi Dimitrios, Do you have wal archiving enabled? $PGDATA/pg_wal/ is a bit different from tablespaces (including "default" one). It stores transaction journal. Instructions are here: https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL Some more info here:

Orphan files filling root partition after crash

Hello list, yesterday I was doing: ALTER TABLE x ADD UNIQUE ... , ADD PRIMARY KEY ...; The table is almost a billion rows long but lies in its own TABLESPACE that has plenty of space. But apparently the ALTER TABLE command is writing a lot to the default tablespace (not the temp_tablespace,

Efficient rows filter for array inclusion with gin index

Hello, I have a problem writing performant queries that requires to test value inclusion within an array. First: the schema consists of an "items" table with a "ref_id" primary key (uuid, primary key). The items are hierarchical and can have a "parent_ref_id" (uuid) that references their