Re: Timing out A Blocker Based on Time or Count of Waiters
On Fri, Mar 22, 2024 at 11:25 AM Fred Habash wrote: > Facing an issue where sometimes humans login to a database and run DDL > statements causing a long locking tree of over 1000 waiters. As a > workaround, we asked developers to always start their DDL sessions > with 'SET lock_timeout = 'Xs'. > > I reviewed the native lock timeout parameter in Postgres and found 7. None > seem to be related to blocker timeouts directly. > > idle_in_transaction_session_timeout > idle_session_timeout > lock_timeout: How long a session waits for a lock > statement_timeout > authentication_timeout > deadlock_timeout > log_lock_waits > > Instead, I put together a quick procedure that counts waiter sessions for > a given blocker and terminates it if waiter count exceeds a threshold. > > Is there not a native way to ... > 1. Automatically time out a blocker > 2. A metric that shows how many waiters for a blocker? > I guess this probably does not belong in the native codebase because in most real world scenarios with contention you would end up with priority inversion or a situation where no work gets done. With current locking rules, theoretically the work queue would always clear (assuming the locker doesn't hold the transaction indefinitely), where with your setting enabled it might not always assume the locker retries. In your case, a hand written 'unblocker' script might be the way to go, or (probably better) encourage patterns where critical tables are not blocked, say by building up a scratch table and swapping in on a separate transaction. Reducing contention rather than mitigating the symptoms of it, is *always* a good thing. merlin
Re: PostgreSQL as advanced job queuing system
On 2024-03-25 23:44, Merlin Moncure wrote: On Mon, Mar 25, 2024 at 4:43 AM Dominique Devienne wrote: Hi. Anything you can share? OSS? Doesn't look like it... If it's not, a more details higher level architecture overview would be nice. let me float that, I would love to project-ize this. Stay tuned Hopefully it get approved. More battle tested queue systems are welcome, especially those that have been used at non-trivial scales. :D + Justin
Re: Is this a buggy behavior?
On Mon, Mar 25, 2024 at 9:49 AM Christophe Pettus wrote: > > > > On Mar 25, 2024, at 02:50, Thiemo Kellner > wrote: > > My bad. I was under the impression that the create table statement was > an atomic process/transaction with all its bells and whistles for > constraints and keys, instead of a succession of alter statements. > > That may be a bit judgmental. :-) It's not a series of ALTER statements, > really. The basic issue is that the parser throws away a bare NULL very > early in the process, so it is not available to consult at the point that > PostgreSQL is creating the constraint. The underlying implementation of > the actual table creation isn't the issue here. > > There seems to be general consensus that: > > 1. It would be handy to have a warning in the particular case that NULL is > specified, however, > 2. It would be undesirable to have a warning in the case where no NULL at > all is specified, which means, > 3. The presence of an existing bare NULL would have to be retained through > the whole process, which is, > 4. Not trivial. > > The reason the SQL standard is relevant here is that if bare NULL were > part of the standard, that would be an argument for taking the pains. > Since it's not, it's not clear that doing the work to add the warning is > worth the effort. > Such a warning *could* be put in psql*, but is the effort worth the benefit? I don't really think OP's scenario is very common. *People using pgAdmin, pgcli, etc wouldn't see the warning.
Re: Query on Postgres SQL transaction
On 3/25/24 00:18, Bandi, Venkataramana - Dell Team wrote: Hi, Please find my inline comments for your questions. Regards, Venkat Internal Use - Confidential -Original Message- From: Adrian Klaver Sent: Tuesday, March 19, 2024 9:33 PM To: Bandi, Venkataramana - Dell Team ; Greg Sabino Mullane Cc: pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team ; Alampalli, Kishore Subject: Re: Query on Postgres SQL transaction [EXTERNAL EMAIL] On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote: Hi Greg, We are using hibernate framework to persist the data into Postgres SQL DB and data is persisting and committing for all the clients but one of the client data is not inserted into DB. What is different about that client? Ans: In our application data is getting from different nodes(systems) and persisting into Postgres SQL DB but for one of the nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out the transaction why data is not persisting sometimes. That we knew already. What I was after was whether that particular node(system) is different in some important way from the others. For instance different OS or OS version, different encoding, different location on the network, different data it is working, etc. Define what you have done to trace the path of the transaction. Are all the clients passing data through the same instance of the framework? Ans: Since it is a monolithic architecture application, it is running on same instance. Are you sure that the client is pointed at the correct database? Ans: Yes, its pointed to correct database and with same database connection, data is persisting for other nodes. Is the log entry below from that client? Ans: Yes -- Adrian Klaver adrian.kla...@aklaver.com
Re: No warning for a no-op REVOKE
> On Mar 25, 2024, at 07:20, Daniel Gustafsson wrote: > >> On 25 Mar 2024, at 15:09, Tom Lane wrote: > >> My initial reaction is that we should warn only when the command >> is a complete no-op, that is none of the mentioned privileges >> matched. > > That's my gut reaction too, I think that's fine. The all-singing-all-dancing solution would be to warn if the role retains any of the mentioned privileges for some other reason, as in: WARNING: role "lowpriv" still has EXECUTE permission on "f()" via a grant to role "PUBLIC" by role "owner" ... but I suspect the implementation complexity there isn't trivial.
Re: No warning for a no-op REVOKE
> On 25 Mar 2024, at 15:09, Tom Lane wrote: > My initial reaction is that we should warn only when the command > is a complete no-op, that is none of the mentioned privileges > matched. That's my gut reaction too, -- Daniel Gustafsson
Re: No warning for a no-op REVOKE
Christophe Pettus writes: > Right now, if you do a REVOKE that doesn't actually revoke anything, it works > silently. This can be a bit of a foot-gun. For example: > CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; > REVOKE EXECUTE ON FUNCTION f() FROM lowpriv; > Naively, it might be expected that `lowpriv` can't execute the function, but > unless default privileges have been changed, `lowpriv` still can under the > default grant of EXECUTE to PUBLIC. Since there was no previous grant to > `lowpriv`, nothing actually changes in the ACL. This bit a client recently. > Is it worth generating a warning in this case? To be clear, even if there had been a grant of EXECUTE to lowpriv, that role would still be able to call the function thanks to the public EXECUTE permission. I'm not sure that warning about whether anything got revoked is going to help people who fundamentally misunderstand where the privilege is coming from. Still, I take your point that maybe the command should mention that nothing happened. To me the best argument for producing a warning is that we already do so for grants of roles: regression=# create user alice; CREATE ROLE regression=# create role bob; CREATE ROLE regression=# revoke bob from alice; WARNING: role "alice" has not been granted membership in role "bob" by role "postgres" REVOKE ROLE However, ordinary privileges are a bit more complicated since multiple privilege bits can be specified: regression=# revoke select,update on table t from alice; REVOKE or even regression=# revoke select(x),update(y) on table t from alice; REVOKE Should we generate a warning when only some of the named privileges exist to be revoked? My initial reaction is that we should warn only when the command is a complete no-op, that is none of the mentioned privileges matched. But I've not thought about it very hard. regards, tom lane
Re: No warning for a no-op REVOKE
> On 25 Mar 2024, at 14:54, Christophe Pettus wrote: > > Right now, if you do a REVOKE that doesn't actually revoke anything, it works > silently. This can be a bit of a foot-gun. For example: > > CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; > REVOKE EXECUTE ON FUNCTION f() FROM lowpriv; > > Naively, it might be expected that `lowpriv` can't execute the function, but > unless default privileges have been changed, `lowpriv` still can under the > default grant of EXECUTE to PUBLIC. Since there was no previous grant to > `lowpriv`, nothing actually changes in the ACL. This bit a client recently. That's indeed a potential foot-gun. > Is it worth generating a warning in this case? Or maybe a NOTICE? -- Daniel Gustafsson
No warning for a no-op REVOKE
Right now, if you do a REVOKE that doesn't actually revoke anything, it works silently. This can be a bit of a foot-gun. For example: CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; REVOKE EXECUTE ON FUNCTION f() FROM lowpriv; Naively, it might be expected that `lowpriv` can't execute the function, but unless default privileges have been changed, `lowpriv` still can under the default grant of EXECUTE to PUBLIC. Since there was no previous grant to `lowpriv`, nothing actually changes in the ACL. This bit a client recently. Is it worth generating a warning in this case?
Re: Is this a buggy behavior?
> On Mar 25, 2024, at 02:50, Thiemo Kellner wrote: > My bad. I was under the impression that the create table statement was an > atomic process/transaction with all its bells and whistles for constraints > and keys, instead of a succession of alter statements. That may be a bit judgmental. :-) It's not a series of ALTER statements, really. The basic issue is that the parser throws away a bare NULL very early in the process, so it is not available to consult at the point that PostgreSQL is creating the constraint. The underlying implementation of the actual table creation isn't the issue here. There seems to be general consensus that: 1. It would be handy to have a warning in the particular case that NULL is specified, however, 2. It would be undesirable to have a warning in the case where no NULL at all is specified, which means, 3. The presence of an existing bare NULL would have to be retained through the whole process, which is, 4. Not trivial. The reason the SQL standard is relevant here is that if bare NULL were part of the standard, that would be an argument for taking the pains. Since it's not, it's not clear that doing the work to add the warning is worth the effort.
RE: Query on Postgres SQL transaction
Hi, Please find my inline comments for your questions. Regards, Venkat Internal Use - Confidential -Original Message- From: Adrian Klaver Sent: Tuesday, March 19, 2024 9:33 PM To: Bandi, Venkataramana - Dell Team ; Greg Sabino Mullane Cc: pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team ; Alampalli, Kishore Subject: Re: Query on Postgres SQL transaction [EXTERNAL EMAIL] On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote: > Hi Greg, > > We are using hibernate framework to persist the data into Postgres SQL > DB and data is persisting and committing for all the clients but one > of the client data is not inserted into DB. What is different about that client? Ans: In our application data is getting from different nodes(systems) and persisting into Postgres SQL DB but for one of the nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out the transaction why data is not persisting sometimes. Are all the clients passing data through the same instance of the framework? Ans: Since it is a monolithic architecture application, it is running on same instance. Are you sure that the client is pointed at the correct database? Ans: Yes, its pointed to correct database and with same database connection, data is persisting for other nodes. Is the log entry below from that client? Ans: Yes > > Not getting any error/exception for this case. Could you please let us > know how we can trace out this scenario on transaction level whether > transaction is committing or not? > > We have enabled below properties in postgresql.conf file and verified > but didn't get any findings about the transaction and below log > statements are writing in our data store logs. > > log_statement = 'all' > > logging_collector = on > > log_min_messages = debug5 > > log_min_error_statement = debug5 > > 2024-02-19 15:21:54.850 +08 [1876] LOG: execute S_48: insert into > xxx > (f_schedule_name,f_id,f_totaldataredtn,f_invalidationtime,f_statuscode > ,f_module,f_app_type,f_dbbackuptype,f_is_compressed,f_proxy,f_size,f_s > izeprotected,f_groupjobid,f_status,f_bytesmodifiednotsent,f_sizetransf > erredboffset,f_bytesmodifiedsent,f_errcode,f_jobid2,f_media_server,f_s > tarttime,f_storageid,f_pool,f_queuestart,f_sizescannedboffset,f_errorc > odesummary,f_ncopies,f_sizeprotectedboffset,f_snap_target_platform,f_b > ackup_servername,f_nfiles,f_expiry,f_owner,f_policy_id,f_parentjobid,f > _sub_name,f_completion_status,f_endtime,f_filesscanned,f_idle_wait,f_s > torage_unit,f_group_id,f_backup_set,f_ntries,f_job_name,f_level,f_agen > t_name,f_failed_copies,f_restarted_job,f_success_copies,f_domain_id,f_ > snap_target,f_jobid,f_request_id,f_pluginname,f_sizetransferred,f_is_s > nap,f_node_id,f_workflow_id,f_action_name,f_agent_id,f_instancename,f_ > session,f_totalobjdedup,f_changedbytes,f_sizeboffset,f_dedupredtn,f_st > atuscodesummary,f_workflow_jobid,f_snap_policy,f_size_copies,f_sizesca > nned,f_sub_id,f_archive_flag,f_nfilesnot,f_media_wait,f_snap_creation, > f_effective_path) values > ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$2 > 0,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37, > $38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$5 > 5,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72, > $73,$74,$75,$76,$77,$78) > > 2024-02-19 15:21:54.851 +08 [10928] DEBUG: bind to > > > 2024-02-19 15:21:54.852 +08 [10928] DEBUG: CommitTransaction(1) name: > unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0 > > Regards, > > Venkat > > * > * > > * > > Internal Use - Confidential > > From:*Greg Sabino Mullane > *Sent:* Saturday, March 16, 2024 12:07 AM > *To:* Bandi, Venkataramana - Dell Team > > *Cc:* pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team > ; Alampalli, Kishore > > *Subject:* Re: Query on Postgres SQL transaction > > [EXTERNAL EMAIL] > > That's a very vague question, but you can trace exactly what is > happening by issuing > > SET log_statement = 'all'; > > Ideally at the session level by your application, but can also set it > at the database and user level. If all else fails, set it globally (i.e. > postgresql.conf). Turn it off again as soon as possible, it will make > your logs extremely verbose. But you can track exactly what your > application is doing. > > Cheers, > > Greg > -- Adrian Klaver adrian.kla...@aklaver.com
Re: PostgreSQL as advanced job queuing system
On Mon, Mar 25, 2024 at 4:43 AM Dominique Devienne wrote: > On Sat, Mar 23, 2024 at 3:13 AM Merlin Moncure wrote: > >> On Fri, Mar 22, 2024 at 6:58 AM ushi wrote: >> >>> the idea to implement a job queuing system using PostgreSQL. >>> >> >> I wrote an enterprise scheduler, called pgtask, which ochestates a very >> large amount of work [...] >> > > Hi. Anything you can share? OSS? Doesn't look like it... > If it's not, a more details higher level architecture overview would be > nice. > let me float that, I would love to project-ize this. Stay tuned merlin >
Re: Is this a buggy behavior?
Am 25.03.2024 um 07:59 schrieb Laurenz Albe: On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote: How can that be forgotten? This information ends up in the data catalogue eventually! It *is* stored in the catalog. But if you add a primary key, that is tantamount to saying ALTER TABLE tab ALTER col SET NOT NULL; > > So it overrides the definition you made before. > My bad. I was under the impression that the create table statement was an atomic process/transaction with all its bells and whistles for constraints and keys, instead of a succession of alter statements. Or would you want the above statement to cause an error just because somebody had defined the column nullable before? When getting contradictory information, I just would ask back what really was meant. But as a succession of alter statements, there is no contradiction. In that case, no. Kind regards and thanks for the insights. Thiemo
Re: PostgreSQL as advanced job queuing system
On Sat, Mar 23, 2024 at 3:13 AM Merlin Moncure wrote: > On Fri, Mar 22, 2024 at 6:58 AM ushi wrote: > >> the idea to implement a job queuing system using PostgreSQL. >> > > I wrote an enterprise scheduler, called pgtask, which ochestates a very > large amount of work [...] > Hi. Anything you can share? OSS? Doesn't look like it... If it's not, a more details higher level architecture overview would be nice. > I'm running at a pretty high scale and it's reliable. [...] > Sounds great. > Being able to manage the state explicitly in the database is wonderful, if > you know what you're doing. > Indeed. I want to reduce the complexity of the stack as much as possible, and rely only on PostgreSQL, if possible. We don't need a super duper queue or full distributed system. So a PostgreSQL-based solution aught to be enough. --DD
Re: Empty materialized view
My bad as always. Having the refresh after the filling of the tables does the trick. Thanks for your help.
Re: Is this a buggy behavior?
On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote: > > The reason it doesn't give you a warning is that by the time it would > > be in a position to, it's forgotten that you explicitly said NULL. > > How can that be forgotten? This information ends up in the data > catalogue eventually! It *is* stored in the catalog. But if you add a primary key, that is tantamount to saying ALTER TABLE tab ALTER col SET NOT NULL; So it overrides the definition you made before. Or would you want the above statement to cause an error just because somebody had defined the column nullable before? Perhaps you'd want that, but you are one of the few who do. You'll have to get used to the way it is. Yours, Laurenz Albe
Re: Not able to purge partition
On Mon, 2024-03-25 at 02:54 +0530, veem v wrote: > Can you please suggest some docs which shows the way we should > do the partition maintenance (if not using pg_partman)? man cron > Actually , I am not able to visualize the steps here. Do you mean > to say , we will just create the partition tables without any foreign > key constraints first. Then create parent table future partitions first > (say 10 days partitions) and then child table future 10 days partitions > manually and during that define the foreign key constraints , indexes > etc. Here , how to exactly find the exact matching parent partitions > so as to create the foreign keys one to one I don't see what's so hard about it. When the time is there to create a new partition, create a partition for both the parent and the child table and define a foreign key between them. Yours, Laurenz Albe
