PostgreSQL

04 Jul 2025 22:08

I think that a good DBA is a programmer.
At least, have to program some DBs professionally.
Also, SQL shouldn't be the first programming language, therefore shouldn't be the only one.


Also, while a skilled DB chef could use dark magick of SELECT/WHERE delimiters to loop and filter over data — generally everyone who knows how to write fizzbuzz should be able to handle that somehow.

PostgreSQL

04 Jul 2025 22:06

i heard you can use water to cool off some very spicy silicons and it will spit out stuff at you

PostgreSQL

04 Jul 2025 21:59

Or DBAs, I don't think typical devs would do this efficiently. Usually they end up messing something simple as this one, all he needs to do is fit condition in loop and avoid running unnecessary dml.

PostgreSQL

04 Jul 2025 21:53

https://www.postgresql.org/docs/current/backup-dump.html#BACKUP-DUMP-ALL

PostgreSQL

04 Jul 2025 20:49

Is this always happening when you insert data into a table? If so perhaps a trigger would be suitable? https://www.postgresql.org/docs/current/plpgsql-trigger.html

PostgreSQL

04 Jul 2025 20:29

I had to write a bunch of file classifiers do carve files out of the filesystem, create a mock database with the same schema to have a reference of the catalog files, run postgresql with strace enabled to see what file would make it panic and then manually compare files from the mock db with ones with the real one so that i could move the files in the correct folders with the correct OIDs

PostgreSQL

04 Jul 2025 20:22

How to take entire dump in postgres and move into other lap

PostgreSQL

04 Jul 2025 15:02

If you do lack valid catalogs it may struggle to make sense of any data though.

PostgreSQL

04 Jul 2025 14:11

I need to insert same identity value of source into target

PostgreSQL

04 Jul 2025 14:11

I have generated first ddl schemas and run into target db

PostgreSQL

04 Jul 2025 00:20

I wish there was a way to tell postgresql "please disable checkpoints, open everything read only, just do sequential scans over the relations i tell you"

PostgreSQL

04 Jul 2025 00:18

I've been writing tooling that "guesses" using a reference schema® and heuristics what the files that are all over lost+found refer to

PostgreSQL

03 Jul 2025 22:30

2025-07-03 20:26:31.772 UTC [1180] FATAL:  database "template1" has disappeared from pg_database
2025-07-03 20:26:31.772 UTC [1180] DETAIL:  Database OID 1 now seems to belong to "template0".
2025-07-03 20:27:01.769 UTC [1181] FATAL:  database 18996 does not exist
2025-07-03 20:27:31.971 UTC [1182] WARNING:  some databases have not been vacuumed in over 2 billion transactions
2025-07-03 20:27:31.971 UTC [1182] DETAIL:  You might have already suffered transaction-wraparound data los

PostgreSQL

03 Jul 2025 18:36

Can we use nolock in postgresql query like sql server?

PostgreSQL

03 Jul 2025 10:29

Experts,

PostgreS, which developer tool IDE will give best support for Procedure Debugging. (Paid version also OK)

Pl suggest. Thanks in advance

PostgreSQL

04 Jul 2025 22:07

sometimes it even compiles!

PostgreSQL

04 Jul 2025 22:00

Yes. Considering their salaries — probably only lawers worse on average...

PostgreSQL

04 Jul 2025 21:56

I wanted to suggest commands for this, but this is better 👍

PostgreSQL

04 Jul 2025 21:36

Probably, you'd need a computer programmer. They do such tasks.

PostgreSQL

04 Jul 2025 20:44

Hi

I need to process some data in the loop based on the condition if data is ok i need to insert

If condition falis only rollback for the iteration in the loop

Remaining iteration i have commit.

Kind of a situation where i need partial commit and partial rollback

PostgreSQL

04 Jul 2025 20:27

backend> SELECT count(*) FROM foxo_users;
         1: count       (typeid = 20, len = 8, typmod = -1, byval = t)
        ----
         1: count = "898461"    (typeid = 20, len = 8, typmod = -1, byval = t)
        ----

PostgreSQL

04 Jul 2025 15:02

So far, i've also found https://www.postgresql.org/docs/17/runtime-config-developer.html

ignore_system_indexes
ignore_invalid_pages

PostgreSQL

04 Jul 2025 15:01

Stuff like enable_xxx (set false) and ignore_system_indexes might be useful. Or there is a bit in the catalog that you can set for specific indexes. I had to do that when updating a system library (libc?) led to index corruption due to changing sort order.

PostgreSQL

04 Jul 2025 14:11

But when I'm exporting for data identity column is not coming in the insert statement

PostgreSQL

04 Jul 2025 14:11

I need to copy some tables from db1 to db2

PostgreSQL

04 Jul 2025 00:19

The only thing i miss now is doing the same in global, most OIDs are there but not the ones that make it go "corrupted index xyz"

PostgreSQL

04 Jul 2025 00:16

Client: "Is that bad?"

PostgreSQL

03 Jul 2025 18:49

You can skip locks and not wait for locks when you do SELECT FOR UPDATE, see the locking clause section of: https://www.postgresql.org/docs/current/sql-select.html

But I think PostgreSQL and SQL Server have quite different locking models in places, from my limited knowledge of SQL Sever.

PostgreSQL

03 Jul 2025 15:11

So i suggest me to scrape the database xD

PostgreSQL

03 Jul 2025 10:02

yeah, i checked in production mssql, there is one table called property_corpus, overall size shows 60gb, but the actual usage shows 4.7 gb and rest is all some index etc.