2830
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
banks use select..for update to prevent race condition and read committed in most rdbms. It's not some new problem.
Читать полностью…
First, if you had the defaults and did nothing special. You should only see the data AS IT WAS the INSTANT your query ran. This is what XMIN/XMAX help provide. The OPPOSITE of this IMNSHO is "Dirty Read" which MSFT SQL allows. You can NEVER trust a query that allows Dirty Reads (for banking or important data).
Second, we (or I) would need more detail on what exactly you are trying to accomplish.
If you are writing a routine that will "Update the Account Balance (a single field off of the main account record).
And assuming you are the only process allowed to do it. AND it's allowed to be "OFF" in between runs of your routine. Then you are probably fine. You SUM() all of the transactions and calculate the value, write it.
But if they transact WHILE your code is running, you won't see those. And you could be overstating or understanding your field. (And that's okay, IMO, because they should queue up a request for you to recalculate).
I can debug this and say "This feels safe". Mostly because we control the one place that updates this balance.
Now, if the logic is balance = balance + "RECENT" transactions. Then I start getting scared.
And if anyone else can update the balance and the above line is true. Then I am panicked.
Errors can be carried forward.
REPEATABLE READ doesn't guarantee that no data will change! It just presents the data to your session like it was at the beginning of the transaction.
Читать полностью…
Some thoughts (not an answer! I've worked in banking, but never in products related to actual accounts):
a) there's also REPEATABLE READ
b) you'll experience serialization errors (albeit less of them) in the lower isolation levels
c) you'll definitely want to make sure you've got your locking properly done (SELECT ... FOR [NO KEY] UPDATE etc.)
d) if a banking system could only run on _proper_ SERIALIZABLE, Oracle wouldn't be where it is... 🤪
Both are proprietary products derived from Open Source Postgres.
There is no such thing as an "Enterprise License of PostgreSQL", as PostgreSQL is Free as in Free Beer and as in Free Speech.
You can buy _support_ for the Open Source Version, and you can buy the aforementioned products (and some more), but those are Closed Source Products, or at the very least you lose the freedom of choosing your support provider.
Hi all,
Any opening for Powerbi developer role I am a Microsoft Certified Powerbi developer have total 5 years of experience
Can join in 3-4 days willing to Relocate PAN india
Anybody seen something like this before?
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp2450094.0.fileset/i2of16.p0.0", size 14909440Читать полностью…
STATEMENT: -- +----------------------------
The documentation is actually quite good. If you like reading, you can start there: https://www.postgresql.org/docs/current/index.html
If you prefer videos, there are many available, look for some on topics you are interested in.
If you need an automatic timeout then https://dba.stackexchange.com/a/164450/99326 may help, however I would suggest looking at what is actually happening and maybe use a connection pooler like pgbouncer to protect the DB connection pool.
Читать полностью…
Anybody can get to knw why connection leakage occurs?
Читать полностью…
Yes, the surrogate key might be more sensible size-wise than a generated text version of the box, and doesn't involve any app changes - I considered one in the original design but removed it because it served no purpose then, when we had no replication (removing it helped some tables size-wise).
(Of course we have one for the cache server as it was a better foreign key for the associated areas, plus you get a consistent order.)
Yes, if I did what I proposed it'd be because it might be an interesting project and seems the 'ideal' solution (and perhaps useful to others). Either a generated column or manually replicating the [rarely-changed] cache areas seem the simplest solutions. 😼
A point is insufficient because the nearest cache may not be the most suitable, either due to connectivity to the user (see the South America example) or due to varying cache capacities (some have limited monthly transfer; we want to avoid using them if the latency benefit is small but they would incur overages).
Caches may also be disabled and in that case we might want traffic to fall back to a larger cache defined by a large area with a lower (well, technically higher value) priority level.
Polygons from PostGIS might work, assuming https://postgis.net/docs/ST_Geometry_EQ.html acts as an equality operator for index purposes, which seems to be the case based on the description. It was not used initially as box seemed sufficient and we do not have PostGIS installed for any other reason.
There is a database company with marketing based solely on poor understanding of isolation levels.
https://x.com/being_mudit/status/1973957092697337868
READ COMMITTED isn't a problem.
Poor understanding of isolation levels and their glitches, however, is a problem.
It looks like you don't understand what's going on at all — so yes, better don't fall from SERIALIZABLE for any access of the database. In particular, don't do any reads on replicas. Also, always use a transaction for logically connected statements.
Then the most striking feature should be that any transaction may fail and you have to check failures on every step and make appropriate actions. Not a much of a trouble.
(Also, don't rely on a sequence order — they are not transactionally ordered, don't use DDL on a working database — they are sometimes also non-transactional — and don't use system fields like xmin/ctid/etc — for the same reason).
I have a service that is separate from our main project logic and I want to completely separate the payment from this logic. Then imagine that the transaction table is separate and the balance is also separate, meaning we have a balance record for each user. Do you think it's right that if I turn on repqtbe red, which guarantees that no more data will change, I should use this, okay, instead of the serial numbers.
Читать полностью…
Friends, in your opinion, in a banking system that is concrete, if I use read commit in the database instead of serializable, is there a problem? I mean, I want to have a situation where each record is separate, for example, the balance of each person, so I don't run into the phantom problem.
Читать полностью…
How to purchase Enterprise license of PostgreSQL and what is difference between PostgreSQL Pro & EDB
Читать полностью…
Ok, update; seems like I've been looking at a log that had already been through a grep. There is (obviously) some "real" query on the following lines, which were filtered. In other words: NVM
Nope.
But it is deleted at this point, so this seems plausible.
The query is really just that comment, but inside a SAVEPOINT apparently
Читать полностью…
Hi,
We are looking for a Hyderabad based PostgreSQL expert/trainer who can provide offline training in Hyderabad.
If interested, please DM me for details
whether killing connections is the right solution if occurs than 30 min
Читать полностью…
Thanks for all your suggestions, it's a better problem to have more ways to do it than none. 😸
Читать полностью…
OK, understood. Then point and radius, probably?
Well, postgis also would work, even have better extendability — byt installing it.
PS Surrogate box pk is also always an option.
Text representation of a box and functional indexes on text::box seems much more sraightforward.
Well, let's begin with: why do you use box anyway?
Why not a point and not a postgis?