help with a query
i have data bast that represents basketball teams. and i'm trying to find one team that has not won yet but has gain that most points during their games from the other team who have not won yet. i wrote a query that is checking if a team has not won at all. but i'm trying to figure out how to count the points because every team play either at home or away. thank you for you help. here is the query the i have wrote so far: select team.id, nickname,color from team,points,game where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = team.tid and (hscore - vscore )< 0 ) having
Managing major PostgreSQL upgrades
Hi, Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences? Thanks. Tiff
Re: help with a query
Due to time constraints, I do not directly answer general PostgreSQL questions. For assistance, please join the appropriate mailing list and post your question: https://www.postgresql.org/community You can also try the #postgresql IRC channel on irc.freenode.net. See the PostgreSQL FAQ for more information. --- On Sat, Nov 13, 2021 at 11:13:25AM +0200, yarden tamam wrote: > i have data bast that represents basketball teams. and i'm trying to find one > team that has not won yet but has gain that most points during their games > from > the other team who have not won yet. > > i wrote a query that is checking if a team has not won at all. but i'm trying > to figure out how to count the points because every team play either at home > or > away. > > thank you for you help. > > here is the query the i have wrote so far: > select team.id, nickname,color > from team,points,game > where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = > team.tid and (hscore - vscore )< 0 ) > having -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: help with a query
On Mon, Nov 15, 2021 at 09:30:03AM -0500, Bruce Momjian wrote: > > Due to time constraints, I do not directly answer general PostgreSQL > questions. For assistance, please join the appropriate mailing list and > post your question: > > https://www.postgresql.org/community > > You can also try the #postgresql IRC channel on irc.freenode.net. See > the PostgreSQL FAQ for more information. Sorry, my apologies for saying the above. You are asking in the right place, though the question is so unclear and with no specific Postgres aspect, you might be better in an SQL forum. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response)
"Chen, Yan-Jack (NSB - CN/Hangzhou)" writes: > We recently encounter one issue about PostgreSQL ODBC client doesn¡¯t > receive response from PostgreSQL server in time (client set 5 seconds > timeout) occasionally (1 or 2 times per 24 hours). Both PostgreSQL and its > client are deployed in VM against. It took us days to debug where cause the > timeout. We enable PostgreSQL server debug log via below configuration. We can see from this that the server spent 10 seconds in CommitTransaction, so the question is what took so long. I'd wonder first about end-of-transaction triggers (have you got foreign keys on that table? maybe an event trigger?), and then second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replication turned on?). > This mail is to ask why PostgreSQL debug log doesn¡¯t really include the > response message delay which may cause misleading why troubleshooting. It > looks to me the debug log doesn¡¯t record the whole procedure. If there are > some developer options include the missing part but we didn¡¯t enable? [ shrug... ] Sure, we could put an elog(DEBUG) after every line of code in the server, and then high-level debugging logs would be even more impossibly voluminous than they are now. I'd say the existing logging gave you plenty of clue where to look. regards, tom lane
Re: help with a query
Yarden: On Mon, 15 Nov 2021 at 10:30, yarden tamam wrote: > i have data bast that represents basketball teams. and i'm trying to find one > team that has not won yet but has gain that most points during their games > from the other team who have not won yet. > i wrote a query that is checking if a team has not won at all. but i'm trying > to figure out how to count the points because every team play either at home > or away. > thank you for you help. > here is the query the i have wrote so far: > select team.id, nickname,color > from team,points,game > where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = > team.tid and (hscore - vscore )< 0 ) > having This has the faint smell of homework assignment, but anyway, decompose the problem a bit and build from it, if I understand your logic you can do more or less... with home_games_total as ( select htid as tid, sum(hscore) as tscore, sum (vscore as oscore), max(hscore>vscore) as won_any ) This captures total result for home games for the team, for the other team, and wether it has won any match ( using the fact that true>false in max, there are better ways but this is easy to type for an example ). Then you can add with visitor_games_total as ( select vtid as tid, sum(vscore) as tscore, sum (hscore as oscore), max(vscore>hscore) as won_any ) to capture visitor teams stats, then you just have to combine them with with all_games_totals as (select * from home games total union all select * from visitor_games_total ) note each team will have play twice, one for the home team, one for the visitor in the first two sum, and the last subquery will have two rows per team, one for home games, one for visitor. Now just add them all to get one row per team with team_result as (select tid, sum(tscore), sum(oscore), max(won_any) from all_games_total group by tid) and there you just have to search for wat you want: select * from team_result where not won_any order by tscore desc limit 1 -- ( or use tscore-oscore, if you want point diffs ) >From this you can probably optimize, but if your tables are not too big doing it that way is easy for maintenance. And even if you have all playing history for NBA if you limit first two queries to a season they will not spit too many results. Also the first two queries plus the third can be done in a single scan by capturing stats for home and visitor in a single round, then sum it with itself with columns swapped. Also note, this is totally untested code and probably chock full of typos and errors. Francisco Olarte.
Re: pg_restore depending on user functions
Thanks for the feedback! Is it ok to attach a 5MB data schema or is it not possible? Copy one by one to a letter? It would be nice if in the end I could rebuild the base without hindrance. Thank you in advance. Restore Windows 10 PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit -> Debian 11 PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit sudo /usr/lib/postgresql/14/bin/pg_dump --file "/home/dismay/uchet/Uchet.backup" --host "server" --port "5999" --username "back" --no-password --verbose --format=c --quote-all-identifiers --blobs --column-inserts --inserts --create --disable-triggers --encoding="UTF8" "Uchet" no error/ Drop base CREATE DATABASE "Uchet" WITH OWNER = funcowner TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8' CONNECTION LIMIT = -1; SET check_function_bodies = false; sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --format=c --create --verbose "/home/dismay/uchet/Uchet.backup" error: "bpd"."class_prop_user_smal_val" error syntax money "bpd"."class_prop_user_small_val_snapshot" error syntax money "bpd"."object error insert column is_inside" ignored error 26603 visible differences base sourcebase receiver count function 711 649 count views 125 117 count rows bpd.object 25769 0 count rows bpd.class_prop_user_small_val 28295 28182 count rows bpd.class_prop_user_small_val_snapshot 33550 33491 lost views: "int_class_ext" "int_doc_category_ext" "int_doc_file_ext" "int_doc_link_ext" "int_object_ext" "vclass_ext" "vdocument_ext" "vobject_general_ext" lost function: "class_act_base_ext_allowed_by_id_group" "class_act_ext_allowed_rl1_by_id_position" "class_act_ext_by_id" "class_act_ext_by_id_conception_msk_name" "class_act_ext_by_id_global_prop" "class_act_ext_by_id_group" "class_act_ext_by_id_group_msk_name" "class_act_ext_by_id_parent" "class_act_ext_by_id_parent_msk_name" "class_act_ext_by_id_parent_strict_name" "class_act_ext_by_msk_global_prop" "class_act_ext_by_msk_global_prop_from_class" "class_act_ext_by_msk_global_prop_from_group" "class_act_real_ext_allowed_by_id_group" "class_act_real_ext_by_id_group" "class_snapshot_base_ext_by_id_position" "class_snapshot_ext_by_id" "class_snapshot_ext_by_id_class" "class_snapshot_ext_by_id_parent_snapshot" "class_snapshot_ext_on_object_by_id_parent_snapshot_parent_pos" "document_ext_by_id" "document_ext_by_id_category" "document_ext_by_id_class_prop" "document_ext_by_id_conception" "document_ext_by_id_group" "document_ext_by_id_object" "document_ext_by_id_object_prop" "document_ext_by_id_parent" "document_ext_by_id_pos_temp" "document_ext_by_id_pos_temp_prop" "document_ext_by_id_position" "document_ext_by_id_position_prop" "document_ext_by_id_user" "document_ext_by_msk_name_from_category" "document_ext_by_msk_name_from_conception" "int_class_ext_prop_by_id_class_array" "int_object_ext_prop_by_id_object_array" "object_carrier_ext_by_object_class_full" "object_ext_by_id" "object_ext_by_id_class_act" "object_ext_by_id_class_full" "object_ext_by_id_class_id_pos" "object_ext_by_id_class_root" "object_ext_by_id_class_snapshot" "object_ext_by_id_class_snapshot_id_pos" "object_ext_by_id_group" "object_ext_by_id_group_root" "object_ext_by_id_object_carrier" "object_ext_by_id_position" "object_ext_by_id_position_full" "object_ext_by_id_prop_data_type" "object_ext_by_id_prop_enum" "object_ext_by_id_prop_enum_val" "object_ext_by_id_unit_conversion_rule" "object_ext_by_link_object" "object_ext_by_msk_global_prop" "object_ext_by_msk_global_prop_from_pos" "object_ext_by_name" "object_ext_by_name_id_pos" "object_ext_carrier_by_msk_global_prop" "object_object_prop_by_id_object_carrier_ext" "object_object_prop_by_id_position_carrier_ext" пн, 15 нояб. 2021 г. в 05:06, Tom Lane : > =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= > writes: > > вс, 14 нояб. 2021 г. в 23:46, Tom Lane : > >> This is not "details", this is an evidence-free assertion. Please show > >> a concrete example of problematic functions. > > > Ok, I'll do it. > > [ example ] > > This is pretty far from being a self-contained example; I had to guess > at the definitions of several types that you didn't provide. Having > done so, though, the set of functions and types seem to dump and restore > just fine. So I still don't see any problem here. > > Possibly worth noting is that pg_dump is careful to include this in > its output script: > > SET check_function_bodies = false; > > Without that, I can believe that you'd have some trouble restoring > these functions, since pg_dump doesn't know anything about the > cross-references appearing in the function bodies. But with that, > there don't appear to be any troublesome cross-references here. > > regards, tom lane >
Re: pg_restore depending on user functions
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes: > Is it ok to attach a 5MB data schema or is it not possible? Copy one by one > to a letter? Is it smaller if you omit the data (-s switch)? Shouldn't be relevant here. regards, tom lane
Re: pg_restore depending on user functions
Yes, it is. I did so (--schema-only). Removing unnecessary definitions. That is, you do not need to attach files? пн, 15 нояб. 2021 г. в 20:49, Tom Lane : > =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= > writes: > > Is it ok to attach a 5MB data schema or is it not possible? Copy one by > one > > to a letter? > > Is it smaller if you omit the data (-s switch)? Shouldn't be relevant > here. > > regards, tom lane >
Re: pg_restore depending on user functions
Yes, it is. I'll leave behind the lost features, views, table definitions, and dependent objects. It will take some time. thanks, for the help. пн, 15 нояб. 2021 г. в 20:49, Tom Lane : > =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= > writes: > > Is it ok to attach a 5MB data schema or is it not possible? Copy one by > one > > to a letter? > > Is it smaller if you omit the data (-s switch)? Shouldn't be relevant > here. > > regards, tom lane >
Re: pg_restore depending on user functions
Yes, it is. I continue to extract data as promised, but I think I see some pattern. "chicken or egg" To work with the NPGSQL library, I created a cast. They are created after the views in which I use them. Here is the order 279: CREATE TYPE bpd.cclass_prop 4646: CREATE VIEW bpd.vclass_prop 4784: CREATE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop) RETURNS bpd.cclass_prop 4803: CREATE VIEW bpd.int_class_ext AS SELECT cp.id_class AS id, array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list FROM bpd.vclass_prop cp GROUP BY cp.id_class; 89428: CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop); lost views: "int_class_ext" "int_doc_category_ext" "int_doc_file_ext" "int_doc_link_ext" "int_object_ext" "vclass_ext" "vdocument_ext" "vobject_general_ext" пн, 15 нояб. 2021 г. в 20:49, Tom Lane : > =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= > writes: > > Is it ok to attach a 5MB data schema or is it not possible? Copy one by > one > > to a letter? > > Is it smaller if you omit the data (-s switch)? Shouldn't be relevant > here. > > regards, tom lane >
Re: Managing major PostgreSQL upgrades
Hey Tiff, We're in a similar boat. We currently lean on (mostly custom) ansible scripting to automate all the repeatable tasks we can, but automation of major PG version upgrades is something we are yet to tackle -- although we plan to start this effort in the short term. Would you mind sharing a bit more about your own current upgrade process? What's your standard, a dump+restore, a pg_upgrade, or replication-based? Also if you are able to share any lessons learned (e.g. common pitfalls you've run into) will all be useful information to identify ahead of time when drafting an automation strategy. On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, wrote: > Hi, > Every year we spent a lot of time planning and manually performing major > PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering > if there is a better way of managing these upgrades through automation. Can > anyone share their experiences? > > Thanks. > > Tiff > > > >
RE: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response)
"Tom Lane" writes: > We can see from this that the server spent 10 seconds in CommitTransaction, > so the question is what took so long. I'd wonder first about end-of-transaction triggers (have you got foreign keys on that table? maybe an event trigger?), and then second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replication turned on?). We have the same as your second suspicion. Delays in fsyncing WAL as the backend ceph cluster storage and we see there was significant latency increased during that time. Yes. the synchronous_commit is on. fsync | on | Forces synchronization of updates to disk. synchronous_commit | on | Sets the current transaction's synchronization level. > [ shrug... ] Sure, we could put an elog(DEBUG) after every line of code in > the server, and then high-level debugging logs would be even more impossibly > voluminous than they are now. I'd say the existing logging gave you plenty > of clue where to look. Actually, do not need to write log for every line code. The last debug log we can see is for CommitTransaction which state is INPROGRESS. But we can't see when CommitTransaction state is DONE/COMPLETE from the debug log which I think this kind of log is valuable which can help to identify where the delay comes, from server or client from the postgres server debug log only shall be enough without to monitoring and analyzing the TCP message. [2701833-618d1b70.293a09-173273] 2021-11-13 22:25:58.051 GMT <169.254.0.21 UPDATE> DEBUG: 0: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 23280/1/1 [2701833-618d1b70.293a09-173274] 2021-11-13 22:25:58.051 GMT <169.254.0.21 UPDATE> LOCATION: ShowTransactionStateRec, xact.c:5333 Best Regards It always takes longer than you expect, even when you take into account -- Yan-Jack Chen (陈雁) Tel: +8613957141340 Addr: No.567 XinCheng Rd, Binjiang District, Hangzhou, China, 310053 -Original Message- From: Tom Lane Sent: 2021年11月15日 23:09 To: Chen, Yan-Jack (NSB - CN/Hangzhou) Cc: pgsql-gene...@postgresql.org Subject: Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response) "Chen, Yan-Jack (NSB - CN/Hangzhou)" writes: > We recently encounter one issue about PostgreSQL ODBC client doesn¡¯t > receive response from PostgreSQL server in time (client set 5 seconds > timeout) occasionally (1 or 2 times per 24 hours). Both PostgreSQL and its > client are deployed in VM against. It took us days to debug where cause the > timeout. We enable PostgreSQL server debug log via below configuration. We can see from this that the server spent 10 seconds in CommitTransaction, so the question is what took so long. I'd wonder first about end-of-transaction triggers (have you got foreign keys on that table? maybe an event trigger?), and then second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replication turned on?). > This mail is to ask why PostgreSQL debug log doesn¡¯t really include the > response message delay which may cause misleading why troubleshooting. It > looks to me the debug log doesn¡¯t record the whole procedure. If there are > some developer options include the missing part but we didn¡¯t enable? [ shrug... ] Sure, we could put an elog(DEBUG) after every line of code in the server, and then high-level debugging logs would be even more impossibly voluminous than they are now. I'd say the existing logging gave you plenty of clue where to look. regards, tom lane
