Re: Dumped SQL failed to execute with ERROR "GROUP BY position -1 is not in select list"

> The thing to do here is modify pg_operator.dat and give both of these > operators an "oid_symbol". Perhaps Int4NegOperator is ok. Thanks for suggestions, I replaced hacking oids with Int4NegOperator and NumericNegOperator. And I also updated some comments and commit message. Please feel free to

Re: Should REINDEX be listed under DDL?

On Mon, 2023-12-04 at 14:26 +0900, Michael Paquier wrote: > On a recent thread about adding support for event triggers with > REINDEX, a change has been proposed to make REINDEX queries reflect in > the logs under the DDL category: > https://www.postgresql.org/message-id/ZW0ltJXJ2Aigvizl%40paquier.

cost_incremental_sort() and limit_tuples

I think that cost_incremental_sort() does not account for the limit_tuples argument properly. Attached is my proposal to fix the problem. -- Antonin Houska Web: https://www.cybertec-postgresql.com diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index d6

RE: CRC32C Parallel Computation Optimization on ARM

On Date: Thu, 30 Nov 2023 14:54:26PM -0600, Nathan Bossart wrote: >>pg_crc32c_armv8.o: CFLAGS += ${CFLAGS_CRC} ${CFLAGS_CRYPTO} >> >> It does not work correctly. CFLAGS ='-march=armv8-a+crc, >> -march=armv8-a+crypto', what actually works is '-march=armv8-a+crypto'. >> >> We set a new variable CLAGS

Re: Report planning memory in EXPLAIN ANALYZE

On Fri, Dec 1, 2023 at 8:27 AM Andrei Lepikhov wrote: > > On 30/11/2023 18:40, Alvaro Herrera wrote: > > Well, SUMMARY is enabled by default with ANALYZE, and I'd rather not > > have planner memory consumption displayed by default with all EXPLAIN > > ANALYZEs. So yeah, I still think this deserve

Avoid detoast overhead when possible

Currently our code can do lazily detoast by design, for example: SELECT toast_col FROM t; SELECT toast_col FROM t ORDER BY b; SELECT toast_col FROM t join t2 using(c); it is only detoast at {type}_out function. The benefits includes: 1. The life time of detoast datum is pretty short which is g

Re: POC, WIP: OR-clause support for indexes

Hi, Here is the next version of the patch where, I think, all of Roberts's claims related to the code have been fixed. For example, expression 'x < 1 OR x < 2' is transformed to 'x < ANY (1,2)'. Here, we still need to deal with the architectural issues. I like the approach mentioned by Peter:

Re: [PATCH] ltree hash functions

On Fri, Dec 1, 2023 at 8:44 AM Tommy Pavlicek wrote: > > > Patch updated for those comments (and a touch of cleanup in the tests) > attached. it would be a better name as hash_ltree than ltree_hash, similar logic applies to ltree_hash_extended. that would be the convention. see: https://stackove

proposal: plpgsql - OPEN LOCAL statement

Hi The lifecycle of cursors in plpgsql is not strictly joined with the life cycle of related cursor's variables. Without breaking compatibility it is not possible to change this behaviour. Usually it doesn't cause problems, but in some cases very big numbers or unclosed cursors can force memory is

Make COPY format extendable: Extract COPY TO format implementations

Hi, I want to work on making COPY format extendable. I attach the first patch for it. I'll send more patches after this is merged. Background: Currently, COPY TO/FROM supports only "text", "csv" and "binary" formats. There are some requests to support more COPY formats. For example: * 2023-11:

Re: XID formatting and SLRU refactorings (was: Add 64-bit XIDs into PostgreSQL 15)

On Thu, Nov 30, 2023 at 4:37 PM Alexander Korotkov wrote: > > On Thu, Nov 30, 2023 at 10:29 AM Pavel Borisov wrote: > > Agree. The fix is attached. > > What an oversight. > Thank you, pushed! With that, is there any more work pending, or can we close the CF entry?

RE: [PoC] pg_upgrade: allow to upgrade publisher node

Dear hackers, I found another failure related with the commit [1]. This is caused by missing wait on the test code. Amit helped me for this analysis and fix. # Analysis of the failure The failure is that restored slot is two_phase = false, whereas the slot is created as two_phase = true. This is

Which parts of src/backend/nodes/print.c are used?

src/backend/nodes/print.c contains a number of functions that print node types, mostly to stdout. Most of these are not actually used anywhere in the code. Are they meant to be inserted into the code ad hoc for debugging? Is anyone using these? This file has clearly not been updated substan

Re: Use PGAlignedBlock instead of "char buf[BLCKSZ]" in more places

On Mon, Dec 04, 2023 at 06:59:13AM +0530, Bharath Rupireddy wrote: > The commit 44cac934 replaced "char buf[BLCKSZ]" with PGAlignedBlock to > avoid issues on alignment-picky hardware. While it replaced most of the > instances, there are still some more left. How about we use PGAlignedBlock > there

Re: Remove unnecessary includes of system headers in header files

On 01.12.23 11:51, shubham.kha...@fujitsu.com wrote: Hi Peter, I have reviewed the patches and also RUN the command, 'make check-world'. It is working fine. All test cases are passed successfully. committed Thanks and Regards, Shubham Khanna. -Original Message- From: Peter Eisentr

Should REINDEX be listed under DDL?

Hi all, On a recent thread about adding support for event triggers with REINDEX, a change has been proposed to make REINDEX queries reflect in the logs under the DDL category: https://www.postgresql.org/message-id/ZW0ltJXJ2Aigvizl%40paquier.xyz REINDEX being classified as LOGSTMT_ALL comes from 8

Re: Synchronizing slots from primary to standby

On Mon, Dec 4, 2023 at 10:40 AM shveta malik wrote: > > On Fri, Dec 1, 2023 at 5:40 PM Nisha Moond wrote: > > > > Review for v41 patch. > > Thanks for the feedback. > > > > > 1. > > == > > src/backend/utils/misc/postgresql.conf.sample > > > > +#enable_syncslot = on # enables slot synchronizat

Re: Change GUC hashtable to use simplehash?

On Mon, Dec 4, 2023 at 4:16 AM Jeff Davis wrote: > I'm trying to follow the distinctions you're making between dynahash > and simplehash -- are you saying it's easier to do incremental hashing > with dynahash, and if so, why? That's a good thing to clear up. This thread has taken simplehash as a

Re: Synchronizing slots from primary to standby

On Fri, Dec 1, 2023 at 5:40 PM Nisha Moond wrote: > > Review for v41 patch. Thanks for the feedback. > > 1. > == > src/backend/utils/misc/postgresql.conf.sample > > +#enable_syncslot = on # enables slot synchronization on the physical > standby from the primary > > enable_syncslot is disable

Re: Bug in pgbench prepared statements

On Fri, Dec 01, 2023 at 07:06:40PM -0800, Lev Kokotov wrote: > Attached. PR against master also here > , just to make sure it's > mergeable . Thanks for the updated patch. It looks sensible seen fr

Re: Separate memory contexts for relcache and catcache

Hi, I also think this change would be helpful. I imagine you're working on the Andres's comments and you already notice this, but v1 patch cannot be applied to HEAD. For the convenience of other reviewers, I marked it 'Waiting on Author'. -- Regards, -- Atsushi Torikoshi NTT DATA Group Corpo

Re: Parent/child context relation in pg_get_backend_memory_contexts()

Thanks for working on this improvement! On 2023-10-23 21:02, Melih Mutlu wrote: Hi, Thanks for reviewing. Attached the updated patch v3. I reviewed v3 patch and here are some minor comments: + + role="column_definition"> + path int4 Should 'int4' be 'int4[]'? Other system

RE: Synchronizing slots from primary to standby

On Wednesday, November 29, 2023 5:55 PM Drouvot, Bertrand wrote: Hi, > On 11/29/23 6:58 AM, Zhijie Hou (Fujitsu) wrote: > > On Tuesday, November 28, 2023 8:07 PM Drouvot, Bertrand > wrote: > > > > Hi, > > > >> On 11/27/23 9:57 AM, Zhijie Hou (Fujitsu) wrote: > >>> On Monday, November 27, 2023

gai_strerror() is not thread-safe on Windows

Hi, Commit 5579388d removed a bunch of dead code, formerly needed for old systems that lacked getaddrinfo() in the early days of IPv6. We already used the system getaddrinfo() via either configure-time tests (Unix) or runtime tests (Windows using attempt-to-find-with-dlsym that always succeeded o

Re: connection timeout hint

Jeff Janes writes: > When one tries to connect to a server and port which is protected by a > firewall, ones get messages like this: > Unix: > psql: error: connection to server at "192.168.0.26", port 5432 failed: > Connection timed out > Is the server running on that host and accepting T

Re: [RFC] Clang plugin for catching suspicious typedef casting

On Sun, Dec 3, 2023 at 6:31 AM Dmitry Dolgov <9erthali...@gmail.com> wrote: > > Only advantage I see to using libclang is that you can run programs built > > with libclang regardless of what your C compiler is. I typically use GCC. > > > > I think your idea of automating this kind of thing is great

connection timeout hint

When one tries to connect to a server and port which is protected by a firewall, ones get messages like this: Unix: psql: error: connection to server at "192.168.0.26", port 5432 failed: Connection timed out Is the server running on that host and accepting TCP/IP connections? Windows: psq

Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)

hi. here is my implementation based on previous discussions add a new COPY FROM flag save_error. save_error only works with non-BINARY flags. save_error is easier for me to implement, if using "save error" I worry, 2 words, gram.y will not work. save_error also works other flag like {csv mode, for

Re: Proposal: Filter irrelevant change before reassemble transactions during logical decoding

> This may be helpful for the case you have mentioned but how about > cases where there is nothing to filter by relation? You can see the complete antecedent in the email [1]. Relation that has not been published will also generate changes and put them into the entire transaction group, which will

Re: Proposal: Filter irrelevant change before reassemble transactions during logical decoding

> > This may be helpful for the case you have mentioned but how about > cases where there is nothing to filter by relation? You can see the complete antecedent in the email [1]. Relation that has not been published will also generate changes and put them into the entire transaction group, which wi

Re: May be BUG. Periodic burst growth of the checkpoint_req counter on replica.

Thanks for remarks! On 28.11.2023 21:34, Alexander Korotkov wrote: After examining the second patch ("v2-0001-Add-restartpoint-stats.patch"), it appears that adding additional statistics as outlined in the patch is the most suitable approach to address the concerns raised. This solution provides

Re: Syncrep and improving latency due to WAL throttling

Hi, Since the last patch version I've done a number of experiments with this throttling idea, so let me share some of the ideas and results, and see where that gets us. The patch versions so far tied everything to syncrep - commit latency with sync replica was the original motivation, so this mak

Use PGAlignedBlock instead of "char buf[BLCKSZ]" in more places

Hi, The commit 44cac934 replaced "char buf[BLCKSZ]" with PGAlignedBlock to avoid issues on alignment-picky hardware. While it replaced most of the instances, there are still some more left. How about we use PGAlignedBlock there too, something like the attached patch? A note [2] in the commit 44cac

Re: Emitting JSON to file using COPY TO

I worked around it by using select json_agg(t)... and redirecting it to file via psql on the command line. COPY TO was working until we ran into broken JSON and discovered the double quoting issue due to some values containing " in them.

Re: Is WAL_DEBUG related code still relevant today?

On Sun, Dec 03, 2023 at 11:07:05AM -0800, Noah Misch wrote: > Can be, but the WAL_DEBUG model is mighty convenient: > - Cooperates with backtrace_functions > - Change log_line_prefix to correlate any log_line_prefix fact with WAL > records > - See WAL records interleaved with non-WAL log messages

Re: Emitting JSON to file using COPY TO

(please don't top quote on the Postgres lists) On 12/3/23 17:38, Davin Shearer wrote: " being quoted as \\" breaks the JSON. It needs to be \".  This has been my whole problem with COPY TO for JSON. Please validate that the output is in proper format with correct quoting for special character

Re: PATCH: Add REINDEX tag to event triggers

On Tue, Nov 28, 2023 at 12:28:50AM +0800, jian he wrote: > On Mon, Nov 27, 2023 at 6:58 PM Ajin Cherian wrote: >> Ajin Cherian Dammit. I've just noticed that I missed mentioning you in the commit log as a reviewer. Sorry about that. > The above 2 suggestions are good, now the code is more neat

Re: collect_corrupt_items_vacuum.patch

Hi, Alexander! On Tue, Nov 7, 2023 at 3:00 PM Alexander Lakhin wrote: > > 07.11.2023 14:38, Alexander Korotkov wrote: > > Hi, Alexander. > > > > On Tue, Nov 7, 2023 at 1:00 PM Alexander Lakhin wrote: > >> It looks like the v2 patch doesn't fix the original issue. Maybe I miss > >> something, but

Re: Emitting JSON to file using COPY TO

" being quoted as \\" breaks the JSON. It needs to be \". This has been my whole problem with COPY TO for JSON. Please validate that the output is in proper format with correct quoting for special characters. I use `jq` on the command line to validate and format the output. On Sun, Dec 3, 2023,

Re: Dumped SQL failed to execute with ERROR "GROUP BY position -1 is not in select list"

On Mon, 4 Dec 2023 at 02:38, Haotian Chen wrote: > Yes, I updated my patch and just used oid numbers 558 and 1751 stand for > int4um and numeric_uminus. Maybe we could define a macro for them, > but seems unnecessary. The thing to do here is modify pg_operator.dat and give both of these operators

Re: Change GUC hashtable to use simplehash?

On Wed, 2023-11-29 at 20:31 +0700, John Naylor wrote: > v5-0001 puts fash-hash as-is into a new header, named in a way to > convey in-memory use e.g. hash tables. > > v5-0002 does the minimal to allow dynash to use this for string_hash, > inlined but still calling strlen. > > v5-0003 shows one wa

Re: Add semi-join pushdown to postgres_fdw

Hi, Alexander! On Mon, Nov 27, 2023 at 5:11 PM Alexander Pyhalov wrote: > Alexander Korotkov писал(а) 2023-11-27 03:49: > > > Thank you for the revision. > > > > I've revised the patch myself. I've replaced StringInfo with > > additional conds into a list of strings as I proposed before. I thin

Re: Emitting JSON to file using COPY TO

On 12/3/23 14:52, Andrew Dunstan wrote: On 2023-12-03 Su 14:24, Joe Conway wrote: On 12/3/23 11:03, Joe Conway wrote: On 12/3/23 10:10, Andrew Dunstan wrote: I  realize this is just a POC, but I'd prefer to see composite_to_json() not exposed. You could use the already public datum_to_json()

Re: Emitting JSON to file using COPY TO

On 2023-12-03 Su 14:24, Joe Conway wrote: On 12/3/23 11:03, Joe Conway wrote: On 12/3/23 10:10, Andrew Dunstan wrote: I  realize this is just a POC, but I'd prefer to see composite_to_json() not exposed. You could use the already public datum_to_json() instead, passing JSONTYPE_COMPOSITE and

Re: Emitting JSON to file using COPY TO

On 2023-12-03 Su 12:11, Joe Conway wrote: On 12/3/23 11:03, Joe Conway wrote:   From your earlier post, regarding constructing the aggregate -- not extensive testing but one data point: 8<-- test=# copy foo to '/tmp/buf' (format json, force_array); COPY 1000 Time: 3

Re: Emitting JSON to file using COPY TO

On 12/3/23 11:03, Joe Conway wrote: On 12/3/23 10:10, Andrew Dunstan wrote: I  realize this is just a POC, but I'd prefer to see composite_to_json() not exposed. You could use the already public datum_to_json() instead, passing JSONTYPE_COMPOSITE and F_RECORD_OUT as the second and third argument

Re: Is WAL_DEBUG related code still relevant today?

On Sun, Dec 03, 2023 at 08:30:24PM +0530, Bharath Rupireddy wrote: > On Sun, Dec 3, 2023 at 4:00 AM Nathan Bossart > wrote: > > On Sat, Dec 02, 2023 at 07:36:29PM +0530, Bharath Rupireddy wrote: > > > b) Remove both the WAL_DEBUG macro and the wal_debug GUC. I don't > > > think (2) is needed to b

Re: logical decoding and replication of sequences, take 2

On 12/3/23 18:52, Tomas Vondra wrote: > ... > > Some time ago I floated the idea of maybe "queuing" the sequence changes > and only replay them on the next commit, somehow. But we did ran into > problems with which snapshot to use, that I didn't know how to solve. > Maybe we should try again. The

Re: Emitting JSON to file using COPY TO

On 12/3/23 11:03, Joe Conway wrote: From your earlier post, regarding constructing the aggregate -- not extensive testing but one data point: 8<-- test=# copy foo to '/tmp/buf' (format json, force_array); COPY 1000 Time: 36353.153 ms (00:36.353) test=# copy (select j

Re: Parallel CREATE INDEX for BRIN indexes

On 11/30/23 18:47, Matthias van de Meent wrote: > ... > > I just ran some more tests in less favorable environments, and it > looks like I hit a bug: > > % SET max_parallel_workers = 0; > % CREATE INDEX ... USING brin (...); > ERROR: cannot update tuples during a parallel operation > > Fix attac

Re: More new SQL/JSON item methods

On 2023-11-06 Mo 08:23, Jeevan Chalke wrote: On Wed, Nov 1, 2023 at 3:49 PM Andrew Dunstan wrote: On 2023-11-01 We 03:00, Jeevan Chalke wrote: Hello, On Tue, Oct 24, 2023 at 6:41 PM Andrew Dunstan wrote: On 2023-10-19 Th 02:06, Jeevan Chalke wrote: Than

Re: Emitting JSON to file using COPY TO

On 12/3/23 10:10, Andrew Dunstan wrote: On 2023-12-01 Fr 14:28, Joe Conway wrote: On 11/29/23 10:32, Davin Shearer wrote: Thanks for the responses everyone. I worked around the issue using the `psql -tc` method as Filip described. I think it would be great to support writing JSON using COP

Re: Emitting JSON to file using COPY TO

On 12/3/23 10:31, Davin Shearer wrote: Please be sure to include single and double quotes in the test values since that was the original problem (double quoting in COPY TO breaking the JSON syntax). test=# copy (select * from foo limit 4) to stdout (format json); {"id":2456092,"f1":"line with

Re: Emitting JSON to file using COPY TO

Please be sure to include single and double quotes in the test values since that was the original problem (double quoting in COPY TO breaking the JSON syntax). On Sun, Dec 3, 2023, 10:11 Andrew Dunstan wrote: > > On 2023-12-01 Fr 14:28, Joe Conway wrote: > > On 11/29/23 10:32, Davin Shearer wrot

Make PostgreSQL work with newer libxml2.

Hi hackers, There is a breaking change of API since the v2.12.0 of libxml2[1][2]. My compiler complains about incompatible function signatures: /usr/bin/clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -W

Re: Emitting JSON to file using COPY TO

On 2023-12-01 Fr 14:28, Joe Conway wrote: On 11/29/23 10:32, Davin Shearer wrote: Thanks for the responses everyone. I worked around the issue using the `psql -tc` method as Filip described. I think it would be great to support writing JSON using COPY TO at some point so I can emit JSON t

Re: Is WAL_DEBUG related code still relevant today?

On Sun, Dec 3, 2023 at 4:00 AM Nathan Bossart wrote: > > On Sat, Dec 02, 2023 at 07:36:29PM +0530, Bharath Rupireddy wrote: > > I started to think if this code is needed at all in production. How > > about we do either of the following? > > Well, the fact that this code is hidden behind an off-by-

Re: Is WAL_DEBUG related code still relevant today?

On Sun, Dec 3, 2023 at 4:16 AM Tom Lane wrote: > > Nathan Bossart writes: > > On Sat, Dec 02, 2023 at 07:36:29PM +0530, Bharath Rupireddy wrote: > >> I started to think if this code is needed at all in production. How > >> about we do either of the following? > > > Well, the fact that this code i

Re: Emitting JSON to file using COPY TO

On 12/2/23 17:37, Joe Conway wrote: On 12/2/23 16:53, Nathan Bossart wrote: On Sat, Dec 02, 2023 at 10:11:20AM -0500, Tom Lane wrote: So if you are writing a production that might need to match FORMAT followed by JSON, you need to match FORMAT_LA too. Thanks for the pointer. That does seem t

Re: Refactoring backend fork+exec code

On 02/12/2023 05:00, Alexander Lakhin wrote: What bothered me additionally, is an error detected after server start. I couldn't see it without the patches applied. I mean, on HEAD I now see `make check` passing, but with the patches it fails: ... # parallel group (20 tests):  interval date numero

Re: [RFC] Clang plugin for catching suspicious typedef casting

> On Fri, Dec 01, 2023 at 04:01:05PM -0600, Tristan Partin wrote: > On Fri Aug 4, 2023 at 5:47 AM CDT, Dmitry Dolgov wrote: > > > On Thu, Aug 03, 2023 at 12:23:52PM -0500, Tristan Partin wrote: > > > > > > This is the first I am learning about clang plugins. Interesting concept. > > > Did you give

Re: Schema variables - new implementation for Postgres 15

> On Sun, Dec 03, 2023 at 06:04:12AM +0100, Pavel Stehule wrote: > > Thank you for your review. Next two weeks I'll not too much time to work > on this patch - I have to work on some commercial work, and the week is > Prague PgConf, so my reply will be slow. But after these events I'll > concentra

Re: Emitting JSON to file using COPY TO

On 2023-12-02 Sa 17:43, Joe Conway wrote: On 12/2/23 13:50, Maciek Sakrejda wrote: On Fri, Dec 1, 2023 at 11:32 AM Joe Conway wrote: 1. Is supporting JSON array format sufficient, or does it need to support some other options? How flexible does the support scheme need to be? "JSON Lines"

答复: Dumped SQL failed to execute with ERROR "GROUP BY position -1 is not in select list"

> Hmm, surely that is a contrived case? Sorry for forgetting to paste the reproduction. It should be as follows. ### psql -d postgres -c 'create table t1(a int, b int, c int)' psql -d postgres -c 'create view v1 as select a,b,c, -1::int from t1 group by 1,2,3,4' pg_dumpall > /tmp/ddl.sql psql -d

RE: logical decoding and replication of sequences, take 2

Dear Tomas, > > I did also performance tests (especially case 3). First of all, there are > > some > > variants from yours. > > > > 1. patch 0002 was reverted because it has an issue. So this test checks > > whether > >refactoring around ReorderBufferSequenceIsTransactional seems really > ne

Re: SQL:2011 application time

On 12/2/23 19:11, Paul Jungwirth wrote: Thank you again for such thorough reviews! On Thu, Nov 16, 2023 at 11:12 PM jian he wrote: > UPDATE FOR PORTION OF, may need insert privilege. We also need to document this. > Similarly, we also need to apply the above logic to DELETE FOR PORTION OF.