Re: pg_stat_statements and "IN" conditions
Hi, I'm interested in this feature. It looks like these patches have some conflicts. http://cfbot.cputube.org/patch_47_2837.log Would you rebase these patches? Thanks, -- Yasuo Honda On Sat, Mar 23, 2024 at 4:11 PM Dmitry Dolgov <9erthali...@gmail.com> wrote: > > Oh, I see, thanks. Give me a moment, will fix this. > > Here is it.
Re: pg_stat_statements and "IN" conditions
Thanks for the information. I can apply these 4 patches from 0eb23285a2 . I tested this branch from Ruby on Rails and it gets some unexpected behavior from my point of view. Setting pg_stat_statements.query_id_const_merge_threshold = 5 does not normalize sql queries whose number of in clauses exceeds 5. Here are test steps. https://gist.github.com/yahonda/825ffccc4dcb58aa60e12ce33d25cd45#expected-behavior It would be appreciated if I can get my understanding correct. -- Yasuo Honda On Sun, Mar 24, 2024 at 3:20 AM Dmitry Dolgov <9erthali...@gmail.com> wrote: > Sure, I can rebase, give me a moment. If you don't want to wait, there > is a base commit in the patch, against which it should be applied > without issues, 0eb23285a2.
Re: pg_stat_statements and "IN" conditions
Yes. The script uses prepared statements because Ruby on Rails enables prepared statements by default for PostgreSQL databases. Then I tested this branch https://github.com/yahonda/postgres/tree/pg_stat_statements without using prepared statements as follows and all of them do not normalize in clause values. - Disabled prepared statements by setting `prepared_statements: false` https://gist.github.com/yahonda/2c2d6ac7a955886a305750eecfd07c5e - Use ruby-pg https://gist.github.com/yahonda/2f0efb11ae888d8f6b27a07e0b833fdf - Use psql https://gist.github.com/yahonda/c830379b33d66a743aef159aa03d7e49 I do not know why even if I use psql, the query column at pg_stat_sql_statement shows it is like a prepared statement "IN ($1, $2)". On Tue, Mar 26, 2024 at 1:35 AM Dmitry Dolgov <9erthali...@gmail.com> wrote: > From what I understand out of the description this ruby script uses > prepared statements, passing values as parameters, right? Unfortunately > the current version of the patch doesn't handle that, it works with > constants only [1]. The original incarnation of this feature was able to > handle that, but the implementation was considered to be not suitable -- > thus, to make some progress, it was left outside.
Re: pg_stat_statements and "IN" conditions
Thanks for the useful info. Ruby on Rails uses bigint as a default data type for the primary key and prepared statements have been enabled by default for PostgreSQL. I'm looking forward to these current patches being merged as a first step and future versions of pg_stat_statements will support normalizing bigint and prepared statements. On Wed, Mar 27, 2024 at 6:00 AM Dmitry Dolgov <9erthali...@gmail.com> wrote: > It's a similar case: the column is defined as bigint, thus PostgreSQL > has to wrap every constant expression in a function expression that > converts its type to bigint. The current patch version doesn't try to > reduce a FuncExpr into Const (event if the wrapped value is a Const), > thus this array is not getting merged. If you replace bigint with an > int, no type conversion would be required and merging logic will kick > in. > > Again, the original version of the patch was able to handle this case, > but it was stripped away to make the patch smaller in hope of moving > forward. Anyway, thanks for reminding about how annoying the current > handling of constant arrays can look like in practice!
Re: pg_stat_statements and "IN" conditions
Hi, this is my first email to the pgsql hackers. I came across this email thread while looking at https://github.com/rails/rails/pull/49388 for Ruby on Rails one of the popular web application framework by replacing every query `in` clause with `any` to reduce similar entries in `pg_stat_statements`. I want this to be solved on the PostgreSQL side, mainly because I want to avoid replacing every in clause with any to reduce similar entries in pg_stat_statements. It would be nice to have this patch reviewed. As I'm not familiar with C and PostgreSQL source code, I'm not reviewing this patch myself, I applied this patch to my local PostgreSQL and the Active Record unit tests ran successfully. -- Yasuo Honda
Re: CREATE TABLE NOT VALID for check and foreign key
Hi, On Mon, Dec 23, 2024 at 10:10 AM Alvaro Herrera wrote: > Maybe it would have been wise to forbid NOT VALID when used with CREATE > TABLE. But we didn't. Should we do that now? Maybe we can just > document that you can specify it but it doesn't do anything. I'd like PostgreSQL to raise errors and/or warnings for the NOT VALID check constraint for CREATE TABLE. Ruby on Rails supports creating check constraints with the NOT VALID option and I was not aware that it is just ignored until https://github.com/rails/rails/issues/53732 issue is reported. Rails has implemented a kind of workaround by not dumping the NOT VALID option, but it does not help for the first execution. https://github.com/rails/rails/pull/53735 Thanks, -- Yasuo Honda
