Re-2: New to PostgreSQL - looking for query writing tools

Hi, we use sqlmanager.net tools:  Query, DBCompare, Data Compare, Automatic Tasks, Backup+Restore, etc... There is a free version, but for professional work, you have to buy a license. But there is only a windows version.   Mit freundlichen Grüßen   Sacha Kerres Kerres-EDV Beratung Deglhof 45-4

Re: New to PostgreSQL - looking for query writing tools

Hi Troy, we both use pgAdmin4 and DBeaver (https://dbeaver.io/) Community version but mainly 'cos we need to get in control of other functions of the database server, query is only a part of the job. Another interesting tool is Aquafold (https://www.aquafold.com): Aqua Data Studio is interesting (

How to write a new tuple into page?

Hi, I'm trying to construct a new tuple type, that's not heaptuple, When I get a tupleTableSlot, I will get data info from it and the I will constuct a new tuple, and now I need to put it into a physical page, how should I do? -- jack...@gmail.com

Re: pgBackrest Error : authentication method 10 not supported

I have pgbackrest v.43 installed on the same server where we are running postgres v14.6 that is upgraded from postgres v.10.2 ls /opt/PostgreSQL-14/lib/ libecpg.alibecpg_compat.so.3.14 libpgcommon.a libpgport_shlib.a libpgtypes.so.3.14 libpq.so.5.14 libecpg_compat.a libec

Re: FATAL: database "xxx" does not exist when it does

Siddharth Jain writes: > postgres=> \c xxx > psql (13.9 (Debian 13.9-0+deb11u1), server 14.4) > WARNING: psql major version 13, server major version 14. > Some psql features might not work. I wonder whether your problem is related to this version mismatch. I'm not entirely sure how that would hap

Re: FATAL: database "xxx" does not exist when it does

Change the owner to xxx too. Rodrigo Dev Visite: https://rodrigoluna.net.br YouTube: GamesBR > Em 1 de fev. de 2023, à(s) 17:15, Rodrigo Luna > escreveu: > > Even on the create role you set login? > > Rodrigo Dev > Visite: https://rodrigoluna.net.br > YouTube: GamesBR > >> Em 1 de fev. de 2

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

On Wednesday, February 1, 2023, Ron wrote: > > > 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* ] [, ...] ] > > The square brackets you show are how optional elements are introduce

Re: FATAL: database "xxx" does not exist when it does

Even on the create role you set login? Rodrigo Dev Visite: https://rodrigoluna.net.br YouTube: GamesBR > Em 1 de fev. de 2023, à(s) 17:13, Siddharth Jain > escreveu: > >  >> >> >>> using Postgres 14.4, I created a database as the postgres user: >>> >>> create database xxx; >>> >>> postgre

Re: FATAL: database "xxx" does not exist when it does

> > > > using Postgres 14.4, I created a database as the postgres user: >> >> create database xxx; >> >> postgres=> \c xxx >> psql (13.9 (Debian 13.9-0+deb11u1), server 14.4) >> WARNING: psql major version 13, server major version 14. >> Some psql features might not work. >> SSL connection

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

Ron writes: > 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|/ ] [, ...] ] Really? I don't see anything to that effect in either the syntax synopsis or the text, plus th

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

On Wednesday, February 1, 2023, Ron wrote: > > test=# select from sales_detail; > -- > (11 rows) > It returned 11 rows as per the psql output text, if it acted like count(*), an aggregate, it would have only returned one row. You have produced an 11-row, 0-column, output table. It isn’t “supp

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

SELECT COUNT(*) FROM table_name WHERE condition; Rodrigo Dev Visite: https://rodrigoluna.net.br YouTube: YouTube.com/@gamesbrs > Em 1 de fev. de 2023, à(s) 17:00, Ron escreveu: > >  v12.13 > > https://www.postgresql.org/docs/12/sql-select.html > > The docs say that one of thes

"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;  cus

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

Christophe Pettus writes: >> On Feb 1, 2023, at 10:45, David G. Johnston >> wrote: >> The system just isn't that intelligent for "sequential scan", instead it >> does literally what the label says, goes through the table one page at a >> time and returns any live rows it finds. > Although th

Re: Best Open Source OS for Postgresql

On 2/1/23 9:22 AM, Bob Jolliffe wrote: I have generally favoured ubuntu LTS editions over the years, more out of familiarity than any particular good technical reason.  In the past, postgresql on FreeBSD would have been my first goto, but it's harder to get freebsd skills out on the market than

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

> On Feb 1, 2023, at 10:45, David G. Johnston > wrote: > The system just isn't that intelligent for "sequential scan", instead it does > literally what the label says, goes through the table one page at a time and > returns any live rows it finds. Although this does raise a question: Could

Re: Sequence vs UUID

On Wed, Feb 1, 2023 at 1:34 PM veem v wrote: > I tried to test quickly below on dbfiddle, below with the UUID as data > type and in each of the below cases the UUID performance seems > drastically reduced as compared to sequence performance. Let me know if > anything is wrong in my testing here?

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

On Wed, Feb 1, 2023 at 11:27 AM Dimitrios Apostolou wrote: > I have now run simple VACUUM but it didn't change anything, the simple > SELECT is still slow. > > My understanding by reading the docs is that it should reclaim all unused > space, just not return it to the OS. Which is fine by me. Any

Re: Sequence vs UUID

I tried to test quickly below on dbfiddle, below with the UUID as data type and in each of the below cases the UUID performance seems drastically reduced as compared to sequence performance. Let me know if anything is wrong in my testing here? 1) sequence generation vs UUID generation, execution t

VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

Thanks everyone for the help. I have now run simple VACUUM but it didn't change anything, the simple SELECT is still slow. My understanding by reading the docs is that it should reclaim all unused space, just not return it to the OS. Which is fine by me. Any idea why it failed to reclaim the spa

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

On Wed, Feb 1, 2023 at 11:15 AM Dimitrios Apostolou wrote: > On Tue, 31 Jan 2023, David G. Johnston wrote: > > > > It feels like there is room for improvement here using table statistics > and the visibility map to significantly reduce the number of pages > retrieved that turn out to be all dead.

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

On Tue, 31 Jan 2023, David G. Johnston wrote: It feels like there is room for improvement here using table statistics and the visibility map to significantly reduce the number of pages retrieved that turn out to be all dead.  Yes, I too wish postgres was more clever for a simple unordered qu

Re: Best Open Source OS for Postgresql

I have generally favoured ubuntu LTS editions over the years, more out of familiarity than any particular good technical reason. In the past, postgresql on FreeBSD would have been my first goto, but it's harder to get freebsd skills out on the market than ubuntu/debian linux. I do have one gripe

Re: New to PostgreSQL - looking for query writing tools

There's a bunch listed here: https://wiki.postgresql.org/wiki/PostgreSQL_Clients I personally use Postico, but it's only available on Mac. On Wed, Feb 1, 2023 at 8:43 AM Troy Sherven wrote: > Good morning, > > > > First of all, I am not a database professional. I work for a small > company, an

New to PostgreSQL - looking for query writing tools

Good morning, First of all, I am not a database professional. I work for a small company, and a portion of my job includes report writing (crystal, i-Net, etc), Excel analysis, etc. We recently migrated to a new ERP that runs on a PostgreSQL database. Our previous system was on a MSSQL datab

Re: Best Open Source OS for Postgresql

On 2/1/23 07:54, Marc Millas wrote: If I remember  well, I did try all repo I ever heard off, and basic Google search Till I ask someone thru a postgis mailing list. If you find something, like how to install a postgres 12 with a postgis 3.0 on any debian based distro.. Pls tell, I will read wi

Re: Best Open Source OS for Postgresql

On Tue, Jan 31, 2023 at 3:03 AM Giovanni Biscontini wrote: > We're looking for a Open Source alternative to Rhel for our VM server > dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky, > and Oracle distributions as they're compatible with Rhel package systems. > Can you sha

Re: A Small psql Suggestion

On Tue, Jan 31, 2023 at 11:17:16AM -0500, Raymond Brinzer wrote: > Greetings, > > There is (for me) a small speed bump in psql. I think it's worth > mentioning, minor though it is, because psql is such a polished tool > generally, and because it's something which affects me many, many times a > d

Re: Best Open Source OS for Postgresql

If I remember well, I did try all repo I ever heard off, and basic Google search Till I ask someone thru a postgis mailing list. If you find something, like how to install a postgres 12 with a postgis 3.0 on any debian based distro.. Pls tell, I will read with interest. Also, pls, not recompiling

Re: Best Open Source OS for Postgresql

Hello, What about postgis : 3.0, 3.3, 3.4 ?? Le mer. 1 févr. 2023 à 07:20, Tony Shelver a écrit : > > Copied to the list > On Wed, 1 Feb 2023 at 08:18, Tony Shelver wrote: > >> >> >> On Wed, 1 Feb 2023 at 08:04, Tony Shelver wrote: >> >>> >>> On Tue, 31 Jan 2023 at 15:10, Marc Millas >>> wrot

Re: A Small psql Suggestion

At the risk of starting a +1 snowball or a divergent argument... On Tue, Jan 31, 2023 at 10:16 AM Raymond Brinzer wrote: > Greetings, > > There is (for me) a small speed bump in psql. I think it's worth > mentioning, minor though it is, because psql is such a polished tool > generally, and bec

Re: Best Open Source OS for Postgresql

Hello Giovanni, it depends, do you know a truly open source os without proprietary blobs for firmware or device drivers? Debian used to try to make a clear separation in this with 'main' being the default section when installing, unfortunately this practically no longer applies to proprietary f

Re: 38.10.6. Composite-Type Arguments C-language function code demo works for int, but not for numeric.

jian he writes: > column "salary" int data type works fine. But it does not work if the > "salary" column data type is numeric. Your problem is that numeric is not a primitive C type: > PG_RETURN_BOOL(DatumGetNumericCopy(salary) > limit); That is comparing two pointers-to-numerics, not the

Re: pg_rewind and replication user

Hey, If you would look into docs https://www.postgresql.org/docs/current/app-pgrewind.html on the "Notes" section you will find a list of permissions that user needs to have to be able to run pg_rewind. Cheers, Mateusz śr., 1 lut 2023, 15:09 użytkownik Wiwwo Staff napisał: > Hi! > Provided my r

pg_rewind and replication user

Hi! Provided my replication user created with CREATE USER repl_user REPLICATION LOGIN ENCRYPTED PASSWORD''; If I run pg_rewing referring to this user postgres@host1:~: pg_rewind -D $PGDATA --source-server="host=nre_primary port=5432 user=repl_user passfile='/var/lib/postgresql/.pgpass' dbname=

Re: moving a database to a new 15.1 server

> On 01/02/2023 13:24 CET Matthias Apitz wrote: > > The source database in the 12.11 server shows this: > > $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u $DATABASE > psql (12.11) > Type "help" for help. > > lbs_lbsoclc01_dev_r1=> > lbs_lbsoclc01_dev_r1=> \d > List of relations

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 >

moving a database to a new 15.1 server

Hello, The source database in the 12.11 server shows this: $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u $DATABASE psql (12.11) Type "help" for help. lbs_lbsoclc01_dev_r1=> lbs_lbsoclc01_dev_r1=> \d List of relations Schema | Name | Type |

Re: database postgres not found

> On 01/02/2023 11:33 CET Matthias Apitz wrote: > > Hello, > > I've a problem with a PostgreSQL 12.x server not setup or managed by me. that > the database 'postgres' is not found: > > $ psql -Upostgres postgres > psql: error: ERROR: no such database: postgres > > but the database is there as a S

38.10.6. Composite-Type Arguments C-language function code demo works for int, but not for numeric.

Hi. source: https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-BASETYPE (38.10.6. Composite-Type Arguments) column "salary" int data type works fine. But it does not work if the "salary" column data type is numeric. /* rm funcs.so && rm funcs.o gcc -I/home/jian/postgres/pg16/include/pos

database postgres not found

Hello, I've a problem with a PostgreSQL 12.x server not setup or managed by me. that the database 'postgres' is not found: $ psql -Upostgres postgres psql: error: ERROR: no such database: postgres but the database is there as a SELECT shows: $ psql -Upostgres lbs_lbsoclc01_dev_r1 psql (12.1

Re: invisible commit question for sync replication

In the code it will write a warning to postgresql log. Why not also write the detailed sql? with the exact sql, DBA might do something to fix the issue. if (ProcDiePending) { ereport(WARNING, (errcode(ERRCODE_ADMIN_SHUTDOWN), errmsg("canceling the wait for synchronous replication and term

Re: Best Open Source OS for Postgresql

I would suggest any major Ubuntu based distro, I have a personal preference for Mint. I'd also suggest you set up the official Postgres repo, rather than using a distro repo, so your installs come directly from there. https://wiki.postgresql.org/wiki/Apt Brent Wood Principal Technician, Fish

Re: invisible commit question for sync replication

On Wed, 2023-02-01 at 14:52 +0800, qihua wu wrote: > When run a cluster with sync replication, if DML is done on primary, but > primary is > isolated from all slave, then the DML will hang, if cancel it DML, it will > say: > WARNING:  canceling wait for synchronous replication due to user request

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

At Wed, 1 Feb 2023 00:12:44 +0800, "jack...@gmail.com" wrote in > I use the debugtup to print, and I find out there are "printf", it doesn't > print anything > to the terminal. I need to know how to use this debugtup func. I think I use > it as a mistake It's not clear to me what the terminal

Re: invisible commit question for sync replication

==》Can the second session see the inserted row before you cancel the insert that is waiting for sync ack? The second session can NOT see the inserted row if the first session is still waiting for sync ACK. I checked the source code, it makes sense to me now: The waiting for sync ACK is called in E