Re: Read write performance check

On Thu, Dec 21, 2023 at 8:31 AM veem v wrote: > Can someone please guide me, if any standard scripting is available for > doing such read/write performance test? Or point me to any available docs? > > > ... Veem, first things first... "Top Posting" is when you reply at the top of

Re: Changing a schema's name with function1 calling function2

On Mon, Dec 25, 2023 at 1:47 PM Christophe Pettus wrote: > > > > On Dec 25, 2023, at 10:44, Adrian Klaver > wrote: > > Functions with same name in different schemas would need to be dealt > with. > > I think that's the primary use-case (at least, it would be for me), and I > don't see a

Re: Indexing fragments of a column's value ?

On Fri, Nov 3, 2023 at 3:34 PM David Gauthier wrote: > I'm asking about the possibility of indexing portions of a column's value > where the column has a static field format. Example, a char(8) which > contains all hex values (basically a hex number that's always 8 chars wide, > leading zeros

Is "WITH RECURSIVE" limited to the first position of CTEs by design?

Team, This was strange. I wanted to set up a CTE that would FEED my recursive query. And also feed the results filtering... These are the most trivial examples. The second one results in a syntax error. Which implies that: 1) There can only be one recursive CTE at the same level? 2) It must

Re: psql and pgpass.conf on Windows

On Sat, Jul 1, 2023 at 2:07 AM Julien Rouhaud wrote: > On Sat, Jul 01, 2023 at 01:40:49AM -0400, Kirk Wolak wrote: > > > > FWIW, I discovered that psql is case sensitive on the dbname, without > > quoting it! > > That's on purpose, since shell quoting behavior is en

Re: psql and pgpass.conf on Windows

On Fri, Jun 30, 2023 at 8:39 AM wrote: > On Fri, 30 Jun 2023 11:16:36 +0800 Julien Rouhaud wrote: > > >Hi, > > > >On Thu, Jun 29, 2023 at 10:42:00PM -0400, p...@pfortin.com wrote: > >> > >> Windows: %APPDATA%\postgresql\pgpass.conf > >> > > echo %APPDATA% > C:\Users\Pierre\AppData\Roaming > >

Re: bug or lacking doc hint

On Mon, Jun 26, 2023 at 4:21 PM Marc Millas wrote: > On Mon, Jun 26, 2023 at 4:05 PM Ron wrote: > >> On 6/26/23 07:22, Marc Millas wrote: >> >> On Mon, Jun 26, 2023 at 5:47 AM Avin Kavish wrote: >> >>> Sounds like the problem you are having is, the server is running out of >>> temporary

Re: pb with join plan

On Wed, Jun 21, 2023 at 12:10 PM Marc Millas wrote: > Marc MILLAS > On Wed, Jun 21, 2023 at 12:43 PM Tomas Vondra < > tomas.von...@enterprisedb.com> wrote: > >> On 6/21/23 00:26, Marc Millas wrote: >> > >> > >> > On Tue, Jun 20, 2023 at 11:19 PM David Rowley > > >

Re: Interconnected views

On Fri, Jun 2, 2023 at 9:36 AM Oliver Kohll wrote: > Hi, > > Just wondering, does anyone else create apps which might not have 'big' > data, but quite complex arrangements of views joining to each other? > > If so, do you have scripts to aid refactoring them e.g. drop/recreate/test > them in the

Re: Adding SHOW CREATE TABLE

On Sun, May 14, 2023 at 2:20 AM Kirk Wolak wrote: > On Sat, May 13, 2023 at 3:34 PM Jeremy Smith > wrote: > >> >> >> On Sat, May 13, 2023, 3:25 AM Kirk Wolak wrote: >> >>> Does this imply SQL SYNTAX like: >>> >>> SHOW

Re: "PANIC: could not open critical system index 2662" - twice

On Tue, May 16, 2023 at 10:20 AM Evgeny Morozov < postgres...@realityexists.net> wrote: > On 9/05/2023 3:32 am, Andres Freund wrote: > > Attached is a rough prototype of that idea (only using datconnlimit == > > -2 for now). > > I guess we need to move this to -hackers. Perhaps I'll post

Re:

On Mon, May 15, 2023 at 11:42 AM Fabrice Chapuis wrote: > I am using postgres v.14 on rhel8 > I enabled the track_commit_timestamp parameter. > > postgres [379418]=# show track_commit_timestamp ; > ┌┐ > │ track_commit_timestamp │ > ├┤ > │ on

Re: Adding SHOW CREATE TABLE

On Fri, May 12, 2023 at 8:37 PM Stephen Frost wrote: > Greetings, > ... > Yes, it's a large effort, no doubt. > > Stephen, I started looking at the code. And I have the queries from \set SHOW_HIDDEN that psql uses. And also the pg_dump output. My first table was an ID bigint NOT NULL PRIMARY

Re: Adding SHOW CREATE TABLE

On Sat, May 13, 2023 at 3:34 PM Jeremy Smith wrote: > > > On Sat, May 13, 2023, 3:25 AM Kirk Wolak wrote: > >> Does this imply SQL SYNTAX like: >> >> SHOW CREATE TABLE >> [ INCLUDING { ALL | INDEXES | SEQUENCES | ??? }] >> [EXCLUDING { PK | FK

Re: Window function for get the last value to extend missing rows

On Sat, May 13, 2023 at 2:18 AM Andrew Gierth wrote: > > "Durumdara" == Durumdara writes: > > create table tmp_test_table(mmin,val) >as select o, v > from unnest(array[1,5,NULL,3,NULL,NULL,10,7,NULL,NULL,NULL,4]) >with ordinality as u(v,o); > select * from

Re: Adding SHOW CREATE TABLE

On Fri, May 12, 2023 at 8:37 PM Stephen Frost wrote: > Greetings, > .. > I mean ... it's already in postgres_fdw, just not in a way that can be > returned to the user. I don't think I'd want this functionality to > depend on postgres_fdw or generally on an extension though, it should > be part

Re: Adding SHOW CREATE TABLE

On Sat, May 13, 2023 at 1:03 AM Ron wrote: > On 5/12/23 18:00, Kirk Wolak wrote: > > [snip] > > Where do we draw the lines? > > > At other tables. > > Does Table DDL include all indexes? > > > Absolutely! > > It should include constraints, clearly.

Re: Adding SHOW CREATE TABLE

On Fri, May 12, 2023 at 4:37 PM Stephen Frost wrote: > Greetings, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > Stephen Frost writes: > > > Again, would be great to see someone actually work on this. There's > > > already a good chunk of code in core in pg_dump and in the postgres_fdw > > >

Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)

On Mon, May 8, 2023 at 8:29 AM Kent Tong wrote: > Hi, > > I have a complex query involving over 15 joins and a CTE query and it > takes over 17s to complete. The output of EXPLAIN ANALYZE includes > (somewhere deep inside): > > Index Scan using document_pkey on document document0_

Re: "PANIC: could not open critical system index 2662" - twice

On Wed, May 10, 2023 at 9:32 AM Evgeny Morozov < postgres...@realityexists.net> wrote: > On 10/05/2023 6:39 am, Kirk Wolak wrote: > > It could be as simple as creating temp tables in the other database (since > I believe pg_class was hit). > > We do indeed create te

Re: order by

On Thu, May 11, 2023 at 11:30 AM Marc Millas wrote: > On Thu, May 11, 2023 at 5:23 PM Adrian Klaver > wrote: > >> On 5/11/23 08:00, Marc Millas wrote: >> > >> > On Thu, May 11, 2023 at 4:43 PM Adrian Klaver < >> adrian.kla...@aklaver.com >> > > wrote: >> > >> >

Re: ICU, locale and collation question

On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 09.05.23 08:54, Oscar Carlberg wrote: > > Our initdb setup would then look like this for compatibility; > > -E 'UTF-8' > > --locale-provider=icu > > --icu-locale=sv-SE-x-icu > >

Re: Return rows in input array's order?

On Tue, May 9, 2023 at 6:36 AM David Wheeler wrote: > > >> It was only used for small arrays but never noticed any performance > issues > > > > Hmmm, sounds like this would be quadratic though... > > True, but it’s cpu time not io, which tends to be orders of magnitude > slower > > > I wonder

Re: "PANIC: could not open critical system index 2662" - twice

On Sun, May 7, 2023 at 10:18 PM Thomas Munro wrote: > On Mon, May 8, 2023 at 4:10 AM Evgeny Morozov > wrote: > > On 6/05/2023 11:13 pm, Thomas Munro wrote: > > > Would you like to try requesting FILE_COPY for a while and see if it > eventually happens like that too? > > Sure, we can try that. >

Re: Guidance on INSERT RETURNING order

On Wed, Apr 12, 2023 at 5:49 PM Adrian Klaver wrote: > On 4/12/23 2:35 PM, Kirk Wolak wrote: > > On Tue, Apr 11, 2023 at 4:38 PM Federico > > > > A couple of comments. For the more generic, I prefer RETURNING * > > you get back all the columns for matching. To me,

Re: FW: Error!

On Wed, Apr 12, 2023 at 10:14 AM Ron wrote: > Are you sure that you're entering the correct password? > > On 4/10/23 19:55, Arquimedes Aguirre wrote: > > > > > > Sent from Mail for > Windows > > > > *From: *Arquimedes Aguirre > *Sent: *Sunday,

Re: Guidance on INSERT RETURNING order

On Tue, Apr 11, 2023 at 4:38 PM Federico wrote: > Thanks for the ansers > > > 2) What would you order by, id or data or both? > > by values order, (that incidentally seems to be what PG does) > > > with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3) > > returning id) > > select

Re: Using CTID system column as a "temporary" primary key

On Tue, Mar 28, 2023 at 6:40 AM Sebastien Flaesch wrote: > ... > > > I think if you're honest with yourself you already know the answer to this > question. The only real solution is to update the legacy code to use the > primary key, or (if that's not possible) change the table definition to add

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On Thu, Mar 2, 2023 at 8:20 AM cen wrote: > On 16/02/2023 17:15, Ron wrote: > > On 2/16/23 09:47, cen wrote: > >> Hi, > >> > >> I am running the same application (identical codebase) as two > >> separate instances to index (save) different sets of data. Both run > >> PostgreSQL 13. > >> > >> The

Re: pg_get_functiondef(), trailing spaces and + sign

On Mon, Feb 27, 2023 at 2:40 PM Adrian Klaver wrote: > On 2/27/23 11:34 AM, Ron wrote: > > Is there any direct way in Postgresql to get rid of the frankly > > anti-useful junk at the end of each line (which also infects > > pg_stat_activity.query), or must I resort to sed post-processing? > > >

Re: A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

On Sun, Feb 19, 2023 at 4:18 PM Tom Lane wrote: > Kirk Wolak writes: > > I have some converted code that uses this syntax. > > Seems kinda dumb, but ... > > > The solution is to remove the ORDER BY NULL. [since that is not > > sortable, should it be ignored

A performance issue in ROW_NUMBER() OVER(ORDER BY NULL) [27 times slow than OVER()] V14.5

Okay, I have some converted code that uses this syntax. For 20 Million rows it was taking 15-20 minutes! (versus 3 minutes) on live data. See here: https://explain.depesz.com/s/VQFJ [There are 2 optimizations, removing the ORDER BY NULL, and just using a sequence] (The above is a live

Re: Sequence vs UUID

On Wed, Feb 8, 2023 at 4:18 AM veem v wrote: > Thank you So much all for such valuable feedback. > .. > So wanted to know from experts here, is there really exists any scenario > in which UUID really cant be avoided? > > Funny you are asking about this. My recent experience is that UUIDs really

Re: Sequence vs UUID

On Wed, Feb 1, 2023 at 1:34 PM veem v wrote: > I tried to test quickly below on dbfiddle, below with the UUID as data > type and in each of the below cases the UUID performance seems > drastically reduced as compared to sequence performance. Let me know if > anything is wrong in my testing here?

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Wed, Dec 7, 2022 at 2:18 PM Andres Freund wrote: > Hi, > > On 2022-11-28 17:22:19 -0500, Kirk Wolak wrote: > > Thank you for this. We have the build working with meson (w/o > readline). > > I was not able to get "readline" to be recognized by mes

Re: delete statement returning too many results

On Mon, Nov 28, 2022 at 9:18 AM Ron wrote: > On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: > > Hello everyone, > > > > I am seeing weird behaviour of a delete statement that is returning more > results than I am expecting. > > > > This is the query: > > > > DELETE FROM queue > > WHERE > >

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Tue, Nov 22, 2022 at 5:51 PM Thomas Munro wrote: > On Tue, Nov 22, 2022 at 4:25 AM Kirk Wolak wrote: > > In researching this problem, it appears that the decision was made > like 17yrs ago, when windows did not have a realistic "terminal" type > interfac

Re: About row locking ordering

My first question is why are you not using "WHERE CURRENT OF" cursor_name? The link to the page follows. But effectively, you are locking the row and that is the row you want to update (the current row of the cursor). I wonder if that addresses the problem...

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Wed, Nov 23, 2022 at 9:57 PM Tom Lane wrote: > Andres Freund writes: > > I think it'd be easier to deal with this if COMPLETE_WITH_* caused the > the > > containing function to return. Then the completions wouldn't need to be > in one > > huge if-else if. Leaving msvc aside, that also seems

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Wed, Nov 23, 2022 at 9:32 PM Andres Freund wrote: > Hi, > > On 2022-11-23 20:55:18 -0500, Kirk Wolak wrote: > > Currently I have a lot of it working, but I need to partner up with some > of > > the meson guys... > > if it is already close to working, a couple

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Wed, Nov 23, 2022 at 7:41 PM Andres Freund wrote: > Hi, > > On 2022-11-23 18:11:22 -0500, Tom Lane wrote: > > Andres Freund writes: > > > On 2022-11-21 10:58:06 -0500, Tom Lane wrote: > > >> It'd certainly be nice if we could use Readline on Windows. > > So It might just be that we need to

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Wed, Nov 23, 2022 at 6:11 PM Tom Lane wrote: > Andres Freund writes: > > On 2022-11-21 10:58:06 -0500, Tom Lane wrote: > >> It'd certainly be nice if we could use Readline on Windows. > > > 2) The last time I checked, msvc couldn't preprocess tab-complete.c with > >USE_READLINE defined,

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Wed, Nov 23, 2022 at 6:56 AM Karsten Hilbert wrote: > Am Tue, Nov 22, 2022 at 11:59:59PM -0500 schrieb Kirk Wolak: > > > > It's OK to post a work-in-progress patch to pgsql-hackers, even if it > > > src/bin/psql/t/010_tab_completion.pl pass on Windows, but if that

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Tue, Nov 22, 2022 at 5:51 PM Thomas Munro wrote: > On Tue, Nov 22, 2022 at 4:25 AM Kirk Wolak wrote: > > In researching this problem, it appears that the decision was made > like 17yrs ago, when windows did not have a realistic "terminal" type > interface. Assum

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Mon, Nov 21, 2022 at 1:10 PM Dominique Devienne wrote: > On Mon, Nov 21, 2022 at 6:12 PM Kirk Wolak wrote: > > On Mon, Nov 21, 2022 at 11:01 AM Dominique Devienne > wrote: > > > FWIW, I've been using https://github.com/arangodb/linenoise-ng for > Linux and Wi

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

On Mon, Nov 21, 2022 at 10:58 AM Tom Lane wrote: > It'd certainly be nice if we could use Readline on Windows. I do not > think we want to buy into maintaining our own fork of Readline, if that's > what you're trying to suggest. If it "just works" now, that'd be great. > Okay, we should know

Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

First, I wanted to send this to the developers (hackers) list. But the instructions are clear that initial posts don't go there (try elsewhere first). So playing by the rules here... *Background:* We have our team of Windows developers, leveraging PSQL. But honestly, it feels crippled