Re: psql and regex not like

[Ron Johnson]

On Thu, Mar 6, 2025 at 4:59 AM Dominique Devienne 
wrote:

> On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson 
> wrote:
> > This statement runs great from the psql prompt.  Does exactly what I
> want.
> > select datname from pg_database WHERE datname !~ 'template|postgres'
> ORDER BY datname;
> >
> > But it doesn't work so well from the bash prompt.  Not escaping the "!"
> generates a bunch of garbage, while escaping throws an sql syntax error.
> >
> > psql -Xc "select datname from pg_database WHERE datname \!~
> 'template|postgres' ORDER BY datname;"
> > ERROR:  syntax error at or near "\"
> >
> > What's the magic syntax?
> >
> > (Yes, I could create a view and then query the view, but I'm going to be
> running this remotely against dozens of servers, so I don't want to have to
> create dozens of views, then need to recreate them every time I want to
> change the query.)
>
> No answer to your question, but I'd argue it's moot, because it's not
> the right query in the first place :)
> It should be instead, IMHO, the one below, which should be OK in BASH
> syntax-wise. --DD
>
> select datname from pg_database WHERE datistemplate = false and
> datname <> 'postgres' order by 1
>

I already do that.  This is part of a long chain of commands so I'm trying
to minimize the length of commands.

Anyway, it would be good to know the answer for any future queries that
need multiple exclusions.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: psql and regex not like

[Dominique Devienne]

On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson  wrote:
> This statement runs great from the psql prompt.  Does exactly what I want.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDER BY 
> datname;
>
> But it doesn't work so well from the bash prompt.  Not escaping the "!" 
> generates a bunch of garbage, while escaping throws an sql syntax error.
>
> psql -Xc "select datname from pg_database WHERE datname \!~ 
> 'template|postgres' ORDER BY datname;"
> ERROR:  syntax error at or near "\"
>
> What's the magic syntax?
>
> (Yes, I could create a view and then query the view, but I'm going to be 
> running this remotely against dozens of servers, so I don't want to have to 
> create dozens of views, then need to recreate them every time I want to 
> change the query.)

No answer to your question, but I'd argue it's moot, because it's not
the right query in the first place :)
It should be instead, IMHO, the one below, which should be OK in BASH
syntax-wise. --DD

select datname from pg_database WHERE datistemplate = false and
datname <> 'postgres' order by 1

Re: Quesion about querying distributed databases

[Igor Korot]

Hi,

On Thu, Mar 6, 2025, 1:44 AM me nefcanto  wrote:

> I appreciate your time guys. Thank you very much.
>
> > Having 1 table per database per server is too ugly.
>
> Our databases are not one table per database. They are mapped to DDD's
> bounded contexts and usually by one table per domain entity.
> For example, we have these databases:
>
>
>- Contacts
>- Courses
>- Seo
>- Payment
>- Forms
>- Geo
>- Sales
>- Media
>- Taxonomy
>- ...
>
> These are the tables we have in the Contacts database:
>
>
>- Addresses
>- AddressTypes
>- Attributes
>- BankAccounts
>- ContactContents
>- Contacts
>- Emails
>- Genders
>- JobTitles
>- JuridicalPersons
>- NaturalPersonRelations
>- NaturalPersons
>- Persons
>- Phones
>- PhoneTypes
>- Relations
>- RelationTypes
>- SocialNetworks
>- SocialProfiles
>- Titles
>
> And, these are the tables we have in the Geo database:
>
>
>- AdministrativeDivisions
>- AdministrativeDivisionTypes
>- Cities
>- CityDivisions
>- Countries
>- Locations
>- SpatialDataItems
>- TelephonePrefixes
>- TimeZones
>
> But we also do have databases that only have one table in them. The number
> of tables is not our criteria to break them. The business semantics is our
> criteria.
>
> > Cross-database on MSSQL is identical to the cross schema on Postgres.
>
> Cross-database query in SQL Server is not equivalent to cross-schema
> queries in Postgres. Because SQL Server also has the concept of schemas. In
> other words, both SQL Server and Postgres let you create databases, create
> schemas inside them, and create tables inside schemas. So SQL Server's
> cross-schema query equals Postgres's cross-schema query.
>
> > If you truly need cross server support (versus say beefier hardware)
> how did you come to choose postgres?
>
> We chose Postgres for these reasons that we did R&D about:
>
>
>- Native array per column support
>- Not having multiple storage engines like MariaDB to be confused about
>- Supporting expressions in unique constraints
>- It's usually considered one of the best when it comes to
>performance, especially in GIS we intend to develop more upon
>- As it claims on its website, it's the most advanced open-source
>database engine (but to be honest, we saw many serious drawbacks to that
>statement)
>
> But here's the deal. We don't have one project only. We don't need 
> *cross-server
> queries* for all of our projects. But we tend to keep our architecture
> the same across projects as much as we can. We chose Postgres because we
> had experience with SQL Server and MariaDB and assumed that cross-database
> query on the same server is something natural. Both of them support that.
> And both are very performant on that. On MariaDB all you have to do is to
> use `db_name.table_name` and on SQL Server all you have to do is to use
> `database_name.schema_name.table_name`. So we thought, for projects that do
> not need more than one server, we keep databases on the same server. When
> it needed more resources, we start by taking heavy databases onto their own
> servers, and we start implementing table partitinong on them.
>

But why?
Remember - multiple servers means more traffic which might be performance
wise.
And especially if thise servers are located on different hardware as your
OP implied.

Thank you.


> But we have experienced some amazing improvements too in our initial
> tests. For example, creating all databases and tables and database objects
> on MariaDB takes more than 400 seconds, while the same took 80 seconds on
> Postgres. So amazing performance on DDL.
> Also, 1 million records in bulk insertion take almost one-sixth to
> on-fourth of the time on MariaDB. These are valuable numbers. They warmed
> our hearts to keep digging as much as we can to see if we can perform this
> migration.
>
> Regards
> Saeed
>
> On Thu, Mar 6, 2025 at 7:14 AM Rob Sargent  wrote:
>
>>
>>
>> On Mar 5, 2025, at 8:03 PM, Igor Korot jnit worked great for SQL Server.
>> If you're small, we host them all on one server. If you get bigger, we can
>> put heavy databases on separate machines.
>>
>>
>>> However, I don't have experience working with other types of database
>>> scaling. I have used table partitioning, but I have never used sharding.
>>>
>>> Anyway, that's why I asked you guys. However, encouraging me to go back
>>> to monolith without giving solutions on how to scale, is not helping. To be
>>> honest, I'm somehow disappointed by how the most advanced open source
>>> database does not support cross-database querying just like how SQL Server
>>> does. But if it doesn't, it doesn't. Our team should either drop it as a
>>> choice or find a way (by asking the experts who built it or use it) how to
>>> design based on its features. That's why I'm asking.
>>>
>>>
>> Cross-database on MSSQL is identical to cross schema on po

Re: psql and regex not like

[François Lafont]

Hi,

On 3/6/25 10:37, Ron Johnson wrote:

This statement runs great from the psql prompt.  Does exactly what I want.
select datname from pg_database WHERE datname !~ 'template|postgres' ORDER BY 
datname;

But it doesn't work so well from the bash prompt.  Not escaping the "!" 
generates a bunch of garbage, while escaping throws an sql syntax error.

psql -Xc "select datname from pg_database WHERE datname \!~ 'template|postgres' 
ORDER BY datname;"
ERROR:  syntax error at or near "\"

What's the magic syntax?


Indeed it's a question about bash.

This works well for me:

set +H
psql -Xc "SELECT datname FROM pg_database WHERE datname !~ 'template|postgres' 
ORDER BY datname;"

~$ echo "!~"
-bash: !~: event not found

~$ set +H

~$ echo "!~"
!~

Bye.

--
François Lafont

Re: Quesion about querying distributed databases

[Laurenz Albe]

On Thu, 2025-03-06 at 12:15 +0330, me nefcanto wrote:
> We had problems with cross-server queries on SQL Server and MariaDB too.
> It seems that cross-server queries are not solved by any engine. But we
> had no problem with cross-database queries. That's where it worked well
> both on SQL Server and MariaDB.

But then you always worked with a monolithic system.  Splitting over several
"databases" (we call them schemas) does not allow you to scale horizontally.

> > Well, if you split the data into several databases, that *was* sharding.
> 
> The way I understood it, sharding is when you split the database by rows,
> not by tables. Examples choose a column like Tenant or User or Date as the
> base of sharding. Never have I seen an example that stores Orders on one
> database and Customers on another database and call it sharding.

Right.  And I don't think that your data model is good.  It won't scale
well, because you don't get more tables as you get more data.


> But to choose a technology, we do have enough literacy and experience.
> It's just some simple questions and answers. If I know that FDW works
> differently for same-server databases, then I know that we will migrate.

It doesn't work any differently.

> > Don't ever store arrays in the database.  It will be a nightmare.
> 
> This is a very interesting claim. May I ask you to share its problems
> and your experience?

If you store foreign keys in arrays, you have no referential integrity,
and any join over the array will be complicated and slow.

If you store many values in an array and you want to update one of these
values, you have to retrieve the whole array, construct a new array and
store that.  You cannot modify individual array elements, only the whole
thing.

Of course there are ways to use arrays that are not harmful.  It's just
that almost always when I see someone use an array as table column, it
is an abuse of technology.

Yours,
Laurenz Albe

Re: psql and regex not like

[hubert depesz lubaczewski]

On Thu, Mar 06, 2025 at 04:37:56AM -0500, Ron Johnson wrote:
> This statement runs great from the psql prompt.  Does exactly what I want.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDER
> BY datname;
> But it doesn't work so well from the bash prompt.  Not escaping the "!"
> generates a bunch of garbage, while escaping throws an sql syntax error.

The problem is that ! is magical in bash.

The solution is to not use it. Instead you can easily do:

psql -Xc "select datname from pg_database WHERE not datname ~ 
'template|postgres' ORDER BY datname;"

Best regards,

depesz

Re: Review my steps for rollback to restore point

[chandan Kumar]

Dear Team,
Thank you for all the responses I have received in this matter.  I would
like to send my final steps that I am going to follow during PITR.
kindly take some out to see if these steps are correct or need any
correction or advise.

*Performing Database rollback using PITR*

*Steps: *

*Pre requisites*

1)  Ensure WAL Archiving is Enabled

2)  Ensure postgres have access to write WAL Files on archive location

3)  Check WAL files are being generated in default directory

4)  Check WAL files are being archived in archive directory

5)  Ensure Replication is Running fine, check if any lag

6)  Backup PostgreSQL config files (postgres.conf, hba, repmgr config
file)

*Implementation steps:*

1.Take a Base Backup (Before Making Any Changes)

2.   Create a restore point

*SELECT pg_create_restore_point('before_database_update');*

3.Execute DDL statements

4.Validate changes

If the changes are *not* as expected, proceed to rollback (PITR).

5.   Unregister the standby first

*repmgr -f /etc/postgresql/14/main/repmgr.conf standby unregister*

6.   Stop both servers (Primary & Standby)

*sudo systemctl stop postgresql@14-main*

7.Move the Old Data Directory (Backup Just in Case)

* mv /var/lib/postgresql/14/main
/var/lib/postgresql/14/main_old_$(date +%F)*

8.   Extract the Base Backup to the Data Directory

9.   make sure Correct Ownership is granted to user postgres to
data directory

10.Create the recovery.signal File

*touch /var/lib/postgresql/14/main/recovery.signal*

11.Update postgresql.auto.conf

*echo "restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'" >>
/var/lib/postgresql/14/main/postgresql.auto.conf*

*echo "recovery_target_name = 'before_database_update'" >>
/var/lib/postgresql/14/main/postgresql.auto.conf*

*echo "recovery_target_action = 'promote'" >>
/var/lib/postgresql/14/main/postgresql.auto.conf*



8Start PostgreSQL on primary (rollback is done)

*sudo systemctl start postgresql@14-main*

9Verify recovery status

*psql -U postgres -c "SELECT pg_is_in_recovery();"*

10  Reestablish replication- Standby needs to rebuilt to match
primary after PITR.

11  Create the replication slot on primary, it might gets deleted
during PITR

*  select * from
pg_create_physical_replication_slot('node_a_repslot');*

12. Move/rename standby signal , because standby signal will be
created in next step

*mv standby.signal standbyold.signal*

13.Start Standby

14.Register the standby

15. Check Replication Status











On Tue, Mar 4, 2025 at 9:15 PM chandan Kumar 
wrote:

> Hi David,
> You catched my word "revert". Thats so encouraging to see how this
> community helps. Your answer has cleared my 99% doubt. Thanks again.
> I wish I also contribute one day .  Have a good time!
>
> On Tue, Mar 4, 2025 at 9:08 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tuesday, March 4, 2025, chandan Kumar 
>> wrote:
>>
>>> Thank you for your time and clarification.
>>> Does PITR recreate database internally ?  can i say it is not the same
>>> as pg_restore  or it is same as pg_restore plus applying WAL on top of it.
>>> I am asking because can we revern DDL operations without PITR in streaming
>>> replication
>>>
>>
>> PostgreSQL doesn’t have a concept of “revert”.
>>
>> PITR just deals with raw bytes on disk for an entire cluster.  If a new
>> file appears in the WAL that file is created.  That file can be a directory
>> for a database.
>>
>> You cannot mix physical and logical images of the database so applying
>> WAL on top of pg_restore is technically invalid - but it does effective
>> convey the idea.  It’s like saying pg_dump and pg_basebackup are similar.
>> Sure, in some ways that is true - but the logical vs. physical distinction
>> cannot be ignored fully.
>>
>> David J.
>>
>>
>
> --
> *With warm regards*
> * Chandan*
>


-- 
*With warm regards*
* Chandan*

Re: Quesion about querying distributed databases

[Greg Sabino Mullane]

On Wed, Mar 5, 2025 at 9:44 PM me nefcanto  wrote:

> Anyway, that's why I asked you guys. However, encouraging me to go back to
> monolith without giving solutions on how to scale, is not helping.
>

We did. In addition to the ongoing FDW discussion, I mentioned read-only
replicas and Citus. As far as *how* to scale vertically, we can offer
general advice (more hardware resources, ramdisks for temp stuff, OS-level
tuning, separate disk mounts). But a lot of it is tuning Postgres for your
specific situation and your specific bottlenecks. Which we are happy to
help with. Once we convince you to not throw the baby out with the
bathwater. :)

8 Terabytes of data. A single backup took us more than 21 days


Something was fundamentally wrong there.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: Quesion about querying distributed databases

[Achilleas Mantzios - cloud]

On 3/5/25 11:55, Laurenz Albe wrote:

On Wed, 2025-03-05 at 12:57 +0330, me nefcanto wrote:

Right now this data is in MariaDB, on separate databases (schema) but on one
server. The solution in this situation is to have a cross-database query.
(this is the status quo of our application)

Now our team has decided to migrate to Postgres. However, we realized that
Postgres does not support cross-database queries. And if we want to do so,
we should use FDW. So, we thought we might as well put databases on separate
servers for scalability if we have to write more code. That's the reason
behind this question.

In MySQL, the terms "database" and "schema" are used for the same thing.
Not so in PostgreSQL.  I think you should migrate the data into different
schemas in a single database, pretty much like you had it in MySQL.
Then you don't need a foreign data wrapper, and I bet the query can
perform as well as it did on MySQL.

Same thing in MS SQL, as of version 2017 or so.


Yours,
Laurenz Albe

Re: Quesion about querying distributed databases

[Igor Korot]

Hi,

On Thu, Mar 6, 2025, 7:32 AM Greg Sabino Mullane  wrote:

> On Wed, Mar 5, 2025 at 9:44 PM me nefcanto  wrote:
>
>> Anyway, that's why I asked you guys. However, encouraging me to go back
>> to monolith without giving solutions on how to scale, is not helping.
>>
>
> We did. In addition to the ongoing FDW discussion, I mentioned read-only
> replicas and Citus. As far as *how* to scale vertically, we can offer
> general advice (more hardware resources, ramdisks for temp stuff, OS-level
> tuning, separate disk mounts). But a lot of it is tuning Postgres for your
> specific situation and your specific bottlenecks. Which we are happy to
> help with. Once we convince you to not throw the baby out with the
> bathwater. :)
>
> 8 Terabytes of data. A single backup took us more than 21 days
>
>
> Something was fundamentally wrong there.
>

It could happen on an old and drained hardware... 😀

Thank you.


> Cheers,
> Greg
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>

Re: Quesion about querying distributed databases

[Laurenz Albe]

On Thu, 2025-03-06 at 06:13 +0330, me nefcanto wrote:
> I once worked with a monolithic SQL Server database with more than 10 billion
> records and about 8 Terabytes of data. A single backup took us more than 21 
> days.
> It was a nightmare. Almost everybody knows that scaling up has a ceiling, but
> scaling out has no boundaries.

I hear you, and I agree with that.


> We initially chose to break the database into smaller databases, because it
> seemed natural for our modularized monolith architecture. And it worked great
> for SQL Server. If you're small, we host them all on one server. If you get
> bigger, we can put heavy databases on separate machines.

So you mean that you had those databases on different servers?
How would a cross-database query work in that case?  It must be something
akin to foreign data in PostgreSQL.

If that worked well, then it should also work well with PostgreSQL and
foreign data wrappers.  Look at the execution plan you got on SQL Server
and see where PostgreSQL chooses a different plan.  Then try to improve that.
We can try to help if we see actual plans.

> However, I don't have experience working with other types of database
> scaling. I have used table partitioning, but I have never used sharding.

Well, if you split the data into several databases, that *was* sharding.

> Anyway, that's why I asked you guys. However, encouraging me to go back to
> monolith without giving solutions on how to scale, is not helping. To be
> honest, I'm somehow disappointed by how the most advanced open source
> database does not support cross-database querying just like how SQL Server
> does. But if it doesn't, it doesn't. Our team should either drop it as a
> choice or find a way (by asking the experts who built it or use it) how
> to design based on its features. That's why I'm asking.

Excluding options from the start is limiting yourself.  Consider using
other, better databases than PostgreSQL (if you can find them).

It is difficult to come up with a concrete design based on the information
you provided.  Perhaps you should get a consultant; the mailing list does
not seem to be the right format for that request.

Typically, you split the data in a ways that they have few interconnections,
for example per customer, so that you don't regularly end up joining data
from different databases (shards).

> One thing that comes to my mind, is to use custom types. Instead of storing
> data in ItemCategories and ItemAttributes, store them as arrays in the
> relevant tables in the same database.

Don't ever store arrays in the database.  It will be a nightmare.
You seem to be drawn to questionable data design...

Yours,
Laurenz Albe

Re: Duplicate Key Values

[Adrian Klaver]

On 3/6/25 10:36, mark bradley wrote:
Reply to list also.
Ccing list.

1) Did you not see duplicates with the old version of pgAdmin4?
I did see it in my last update but have done a couple, so it should have 
happened then.


2) What do you see if you use psql?
Here is the PSQL output:


Supply information as copy and paste text.




3) Did you upgrade/move the Postgres server or the underlying OS?
No

4) Have you tried reindexing the node_id field?
No, but I'll try that.



Mark Brady,
_amazon.com/author/markjbrady _

*From:* Adrian Klaver 
*Sent:* Thursday, March 6, 2025 1:22 PM
*To:* mark bradley ; Ron Johnson 
; pgsql-general 

*Subject:* Re: Duplicate Key Values
On 3/6/25 10:11, mark bradley wrote:

Here is the table definition:


And here is the error message I get when I try to delete a duplicate:


Please answer the following:

1) Did you not see duplicates with the old version of pgAdmin4?

2) What do you see if you use psql?

3) Did you upgrade/move the Postgres server or the underlying OS?

4) Have you tried reindexing the node_id field?




Mark Brady,
_amazon.com/author/markjbrady >_




--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[Adrian Klaver]

On 3/6/25 12:06, mark bradley wrote:

My mistake I forgot to Cc list on my previous post, which was:

That would be an issue and also would mean it is not a PK.

In psql  do:

\d dataset

and show the results as text in your reply.

Ccing list


The below shows there is an index("dataset_pkey") on node_id.

Note, reindexing will take a lock on the table that prevents changing 
data while the operation is running. See the below for more information:


https://www.postgresql.org/docs/current/sql-reindex.html

If the table is not to big and you can interrupt access to it then the 
simplest command to run would be:


REINDEX  TABLE dataset;


Universal Metadata Schema=# \d dataset
                                  Table "public.dataset"
           Column           |           Type            | Collation | 
Nullable | Defau

lt
---+---+---+--+--
---
  node_id                   | integer                   |           | 
not null |
  dataset_name              | character varying(25)     |           | 
not null |
  notes                     | text                      |           |   
        |
  dataset_type              | database_type             |           | 
not null |
  dataset_maturity          | database_maturity_type    |           | 
not null |
  disposition               | disposition_type          |           | 
not null |
  start_date                | date                      |           |   
        |
  end_date                  | date                      |           |   
        |
  most_recent_update        | date                      |           |   
        |
  update_periodicity        | interval                  |           |   
        |
  system_of_record          | text                      |           |   
        |
  point_of_contact          | integer                   |           | 
not null |
  dataset_url               | text                      |           |   
        |
  classification_level      | classification_level_type |           | 
not null |
  physical_location         | text                      |           |   
        |
  quality_control           | yes_no_type               |           | 
not null |
  dataset_documentation_url | text                      |           | 
not null |
  description               | text                      |           |   
        |
  node_type                 | node_type                 |           |   
        |
  dummy                     | integer                   |           |   
        |

Indexes:
     "dataset_pkey" PRIMARY KEY, btree (node_id)
Foreign-key constraints:
     "node_id" FOREIGN KEY (node_id) REFERENCES node(node_id) NOT VALID
     "poc" FOREIGN KEY (point_of_contact) REFERENCES poc(poc_id) NOT VALID
Referenced by:
     TABLE "dataset_table" CONSTRAINT "dataset" FOREIGN KEY (node_id) 
REFERENCES datas

et(node_id) NOT VALID
     TABLE "dataset_subject" CONSTRAINT "dataset_subject_node_id_fkey" 
FOREIGN KEY (no

de_id) REFERENCES dataset(node_id)
     TABLE "system_dataset" CONSTRAINT "system_dataset_node_id_fkey" 
FOREIGN KEY (node

_id) REFERENCES dataset(node_id) NOT VALID
Inherits: node


Best regards,
Mark Brady
_amazon.com/author/markjbrady _

*From:* Adrian Klaver 
*Sent:* Thursday, March 6, 2025 3:03 PM
*To:* mark bradley 
*Subject:* Re: Duplicate Key Values
On 3/6/25 10:51, mark bradley wrote:
Reply to list alos.
Ccing list.


Looks like there is no index on node_id at the moment


That would be an issue and also would mean it is not a PK.

In psql  do:

\d dataset

and show the results as text in your reply.




Mark Brady, Ph.D.
Deputy Chief Data Officer, TRMC
_amazon.com/author/markjbrady >_


*From:* Adrian Klaver 
*Sent:* Thursday, March 6, 2025 1:22 PM
*To:* mark bradley ; Ron Johnson 
; pgsql-general 

*Subject:* Re: Duplicate Key Values
On 3/6/25 10:11, mark bradley wrote:

Here is the table definition:


And here is the error message I get when I try to delete a duplicate:


Please answer the following:

1) Did you not see duplicates with the old version of pgAdmin4?

2) What do you see if you use psql?

3) Did you upgrade/move the Postgres server or the underlying OS?

4) Have you tried reindexing the node_id field?




Mark Brady,
_amazon.com/author/markjbrady >>_




--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate Key Values

[Adrian Klaver]

On 3/6/25 10:11, mark bradley wrote:

Here is the table definition:


And here is the error message I get when I try to delete a duplicate:


Please answer the following:

1) Did you not see duplicates with the old version of pgAdmin4?

2) What do you see if you use psql?

3) Did you upgrade/move the Postgres server or the underlying OS?

4) Have you tried reindexing the node_id field?




Mark Brady,
_amazon.com/author/markjbrady _



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Error on query execution

[Igor Korot]

H,

On Tue, Mar 4, 2025 at 8:37 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > binaryIntVal = htonl( (uint32_t) type );
> > paramValues[0] = (char *) &binaryIntVal;
> > paramLengths[0] = sizeof( binaryIntVal );
> > paramFormats[0] = 1;
>
> You're apparently trying to pass this parameter as an int4 ...
>
> > res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
> > $1", 1, nullptr, paramValues, paramLengths, paramFormats, 1 );
>
> ... but given that you didn't specify any data type, I think the
> parser will fall back to assuming that $1 is the same type as
> "abf_type", whatever that is.  Passing data in binary is not at all
> forgiving about getting the data type right.

Changing the line above to read:

res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
$1::smallint", 1, nullptr, paramValues, paramLengths, paramFormats, 1
);


results in:

$1 = L"ERROR:  incorrect binary data format in bind parameter 1\n"

I am now at a complete loss.

How do I fix the code so it will run?

Thank you.

>
> regards, tom lane

Re: Error on query execution

[Tom Lane]

Igor Korot  writes:
> On Tue, Mar 4, 2025 at 8:37 PM Tom Lane  wrote:
>> ... but given that you didn't specify any data type, I think the
>> parser will fall back to assuming that $1 is the same type as
>> "abf_type", whatever that is.  Passing data in binary is not at all
>> forgiving about getting the data type right.

> Changing the line above to read:

> res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
> $1::smallint", 1, nullptr, paramValues, paramLengths, paramFormats, 1
> );

That just makes it explicit that the query is expecting an int16.
You're still passing an int32.  You need to either change the
parameter setup code to pass an int16, or make the query look
more like

res = PQexecParams( m_db, "SELECT * FROM abcatfmt WHERE abf_type =
$1::integer", 1, nullptr, paramValues, paramLengths, paramFormats, 1);

regards, tom lane

Re: Duplicate Key Values

[mark bradley]

Here is the table definition:

[cid:ecac8e92-826b-45c8-95a8-aaf0e55c4f9c]

And here is the error message I get when I try to delete a duplicate:

[cid:a0f5f298-984d-4f89-abd2-475c02e65b9d]

Mark Brady,
amazon.com/author/markjbrady

From: Adrian Klaver 
Sent: Wednesday, March 5, 2025 6:49 PM
To: Ron Johnson ; pgsql-general 

Subject: Re: Duplicate Key Values

On 3/5/25 15:37, Ron Johnson wrote:
> What does "and unique identifier" mean?
>
> (Creating a Primary Key constraint automatically creates a unique
> no-nulls-allowed index on the column(s), so no other index is needed.)
>
> Can you show the table definition?
>
> Anyway, you /should/ be able to delete one of the duplicate node_id
> records by specifying another column with a different value;
>
> On Wed, Mar 5, 2025 at 6:14 PM mark bradley  > wrote:
>
> Hi Ron,
>
> The key is an integer.  I'm using pGAdmin4 and recently updated to
> the latest version.

1) Did you not see duplicates with the old version of pgAdmin4?

2) What do you see if you use psql?

3) Did you upgrade/move the Postgres server or the underlying OS?

4) Have you tried reindexing the node_id field?

>
> The records are not all identical, some have NULL values of the
> non-key for some unknown reason.  Here is a screenshot.  The key is
> node_id, which I have specified as a key and unique identifier.
>
>
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Quesion about querying distributed databases

[Ron Johnson]

On Thu, Mar 6, 2025 at 10:47 AM Igor Korot  wrote:

> Hi,
>
> On Thu, Mar 6, 2025, 7:32 AM Greg Sabino Mullane 
> wrote:
>
>> On Wed, Mar 5, 2025 at 9:44 PM me nefcanto  wrote:
>>
>>> Anyway, that's why I asked you guys. However, encouraging me to go back
>>> to monolith without giving solutions on how to scale, is not helping.
>>>
>>
>> We did. In addition to the ongoing FDW discussion, I mentioned read-only
>> replicas and Citus. As far as *how* to scale vertically, we can offer
>> general advice (more hardware resources, ramdisks for temp stuff, OS-level
>> tuning, separate disk mounts). But a lot of it is tuning Postgres for your
>> specific situation and your specific bottlenecks. Which we are happy to
>> help with. Once we convince you to not throw the baby out with the
>> bathwater. :)
>>
>> 8 Terabytes of data. A single backup took us more than 21 days
>>
>>
>> Something was fundamentally wrong there.
>>
>
> It could happen on an old and drained hardware... 😀
>

8TB databases existed 20+ years ago.  Like always, the hardware must fit
the application.

21 days to backup a database absolutely means *many* things were improperly
sized and configured.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!