Re: Guidance on INSERT RETURNING order

here's what would be cool, a new token called "tuple_order" or something INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING table.id, inserted.tuple_order tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate the each row delivered by RETURNING to

Re: pg_basebackup / recovery

Στις 13/4/23 01:31, ο/η Michael Paquier έγραψε: On Wed, Apr 12, 2023 at 01:45:56PM +0300, Achilleas Mantzios - cloud wrote: On 4/12/23 12:32, Fabrice Chapuis wrote: During recovery process of a self contained backup, how postgres know to stop reading wal when consistency is reached? Because

Re: Guidance on INSERT RETURNING order

To clarify, the work around (for SQLAlchemy) does not require the RETURNING values to be ordered; they will be reordered code side. Longer term matching the order up would be an added bonus! On Thu, 13 Apr 2023 at 04:02, John Howroyd wrote: > The ideal solution would be that the RETURNING

Re: Guidance on INSERT RETURNING order

The ideal solution would be that the RETURNING values are in the same order as the declared rows in the table literal "FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num)". But before any of you jump down my throat about about sets and a bar to parallel inserts, I think it's a small matter

Re: [E] Re: parallel aggregation

On Thu, 13 Apr 2023 at 14:31, Alexander Saydakov wrote: > 1. I wonder if there is a clean separation between the phases: once partial > aggregation is done only combining is done after that (state transition > function never receives results of combining). Currently the transfn won't be called

Re: [E] Re: parallel aggregation

(Let's keep this on this list) On Thu, 13 Apr 2023 at 12:08, Alexander Saydakov wrote: > > Yes, I am talking about a custom aggregate in C++ with internal state type. > You did not really answer my first question. Does the state always go through > serialfinc-deserialfunc before reaching a

Re: Guidance on INSERT RETURNING order

We do something different for uuids. These are usually created client side anyway or are very easy to create client side if not and then we also have a separate sentinel column option. Overall the whole thing is an optimization that can be turned off for uncommon cases. We just can't go

Re: Guidance on INSERT RETURNING order

On 4/12/23 17:58, Thorsten Glaser wrote: On Wed, 12 Apr 2023, Mike Bayer wrote: ascending values for "mytable_id_seq" will correspond to the ascending values for "num". But, again, what if it’s uuid or something instead of a sequence? This for an ORM that will be using a sequence. bye,

Re: Guidance on INSERT RETURNING order

On Wed, 12 Apr 2023, Mike Bayer wrote: >ascending values for "mytable_id_seq" will correspond to the ascending >values for "num". But, again, what if it’s uuid or something instead of a sequence? bye, //mirabilos -- 15:41⎜ Somebody write a testsuite for helloworld :-)

Re: FW: Error!

On 4/12/23 14:47, Arquimedes Aguirre wrote: Hello Dear! I attach the errors that the system threw me when installing the Postgresql program. The first screenshot is about pgAdmin which is a Postgres client not Postgres. Also it is from 2023-02-19, so has anything changed since then? The

Re: Guidance on INSERT RETURNING order

On Wed, Apr 12, 2023, at 6:24 PM, Thorsten Glaser wrote: > On Wed, 12 Apr 2023, Kirk Wolak wrote: > > >The solution exists. Pre-fetch the IDs, assign them and insert them with > >the IDs. Then you have 100% control. > >SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, ); > > This would be a

Re: FW: Error!

Just to install the program, I got several errors without any common sense, I asked the Microsoft support team for help and in 5 minutes they solved the problem, it's very weird and odd. Apparently they have control of my personal computer. Are you sure you were talking to Microsoft,

Re: pg_basebackup / recovery

On Wed, Apr 12, 2023 at 01:45:56PM +0300, Achilleas Mantzios - cloud wrote: > On 4/12/23 12:32, Fabrice Chapuis wrote: >> During recovery process of a self contained backup, how postgres know to >> stop reading wal when consistency is reached? > > Because it knows the full packup info. It will

Re: Guidance on INSERT RETURNING order

This would be a nice solution… but the PK could be always generated, or not even sequential (UUIDs for example). If you’re developing schema-first the application would not even (need to) know about how the IDs are generated as it’s the DB that generates them. AIUI the OP’s an SQLAlchemy

Re: Guidance on INSERT RETURNING order

On Wed, 12 Apr 2023, Kirk Wolak wrote: >The solution exists. Pre-fetch the IDs, assign them and insert them with >the IDs. Then you have 100% control. >SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, ); This would be a nice solution… but the PK could be always generated, or not even

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, this solves the > > problem

Re: Guidance on INSERT RETURNING order

On Wed, 12 Apr 2023 at 23:49, 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, this solves the > > problem

Re: Guidance on INSERT RETURNING order

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, this solves the problem in a very generic way. From what I gather from the conversation

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: TEXT column > 1Gb

On 4/12/23 15:03, Joe Carlson wrote: On Apr 12, 2023, at 12:21 PM, Rob Sargent wrote: On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? The assumption in the schema I’m using is 1 chromosome per record. Chromosomes are

Re: TEXT column > 1Gb

> On Apr 12, 2023, at 12:21 PM, Rob Sargent wrote: > > On 4/12/23 13:02, Ron wrote: >> Must the genome all be in one big file, or can you store them one line per >> table row? The assumption in the schema I’m using is 1 chromosome per record. Chromosomes are typically strings of continuous

Re: TEXT column > 1Gb

On 4/12/23 14:21, Rob Sargent wrote: On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? Not sure what OP is doing with plant genomes (other than some genomics) but the tools all use files and pipeline of sub-tools. In and out

Re: TEXT column > 1Gb

Yea. For ease of use, out of the box solutions that will just work, large objects. You might know them as BLOBS in other SQL varieties. If you are dealing with that much data, I'm going to assume that storage isn't really your concern. I wouldn't even waste time compressing. I use them frequently

Re: TEXT column > 1Gb

On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? Not sure what OP is doing with plant genomes (other than some genomics) but the tools all use files and pipeline of sub-tools.  In and out of tuples would be expensive. 

Re: TEXT column > 1Gb

/Must/ the genome all be in one big file, or can you store them one line per table row? On 4/12/23 12:19, Joe Carlson wrote: I’ve certainly thought about using a different representation. A factor of 2x would be good, for a while anyway. For nucleotide sequence, we’d need to consider a 10

Re: TEXT column > 1Gb

On 4/12/23 11:24, Benedict Holland wrote: For documents that long I would seriously consider using large objects and refencing them with their OIDs. Text fields get put in a special location within the database. It's similar (possibly exactly) to using large objects. Also, you can potentially

Re: TEXT column > 1Gb

For documents that long I would seriously consider using large objects and refencing them with their OIDs. Text fields get put in a special location within the database. It's similar (possibly exactly) to using large objects. Also, you can potentially compress them to save space on write and read.

Re: TEXT column > 1Gb

I’ve certainly thought about using a different representation. A factor of 2x would be good, for a while anyway. For nucleotide sequence, we’d need to consider a 10 character alphabet (A, C, G, T, N and the lower case forms when representing ’soft masked’ sequence*). So it would be 2

Re: TEXT column > 1Gb

> On Apr 12, 2023, at 7:59 AM, Joe Carlson wrote: > > The use case is genomics. Extracting substrings is common. So going to > chunked storage makes sense. Are you storing nucleotide sequences as text strings? If using the simple 4-character (A,C,G,T) alphabet, you can store four bases

Re: TEXT column > 1Gb

On 4/12/23 08:59, Joe Carlson wrote: I’m curious what you learned. I’ve been tripping over the buffer allocation issue when either splitting input text into chunks or aggregating chunks in selects. I’ve decided that I need to move this to client side. The use case is genomics. Extracting

Re: FW: Error!

On 4/10/23 17:55, Arquimedes Aguirre wrote: Sent from Mail for Windows *From: *Arquimedes Aguirre *Sent: *Sunday, April 9, 2023 5:59 PM *To: *pgsql-advoc...@postgresql.org

Re: TEXT column > 1Gb

I’m curious what you learned. I’ve been tripping over the buffer allocation issue when either splitting input text into chunks or aggregating chunks in selects. I’ve decided that I need to move this to client side. The use case is genomics. Extracting substrings is common. So going to chunked

Re: Performance issue after migration from 9.4 to 15

On 4/11/23 07:41, Christian Schröder wrote: Hi all, Thanks for all your tips! I have meanwhile run "ANALYZE" on all tables. Either that was as important as everybody tells me  or it is just a coincidence (e.g., fewer other queries on the database). It is:

Re: subscribe

On 4/12/23 07:00, Mike Bayer wrote: What is it you are trying to do? -- Adrian Klaver adrian.kla...@aklaver.com

Re: FW: Error!

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, April 9, 2023 5:59 PM *To:

subscribe

Re: lippq client library and openssl initialization: PQinitOpenSSL()

Daniel Gustafsson writes: > Reviving an old thread that got buried, I propose to apply the attached and > backpatch it as OpenSSL of such vintage is equally likely to be used in old > versions of postgres as current. +1 regards, tom lane

Re: lippq client library and openssl initialization: PQinitOpenSSL()

> On 12 Sep 2022, at 15:47, Tom Lane wrote: > Personally I'd put this up front, more like > > have been initialized by your application, so that > libpq will not also initialize those libraries. > + However, this is unnecessary when using OpenSSL 1.1.0 or later, > + as duplicate

Re: Guidance on INSERT RETURNING order

Hi All, The fundamental question here is: What does "insert into object(col1, ... , coln) select sel1, ... , seln from ... order by ord1, ... ordm returning val1, ..., valk" mean? In particular, what does the "order by" do in "insert ... select ... order by"? Will this be honoured with parallel

Re: pg_basebackup / recovery

On 4/12/23 12:32, Fabrice Chapuis wrote: During recovery process of a self contained backup, how postgres know to stop reading wal when consistency is reached? Because it knows the full packup info. It will observe the STOP WAL LOCATION: 3BC7/4B000130 (file 00023BC7004B) inside

Re: parallel aggregation

On Wed, 12 Apr 2023 at 22:14, Alexander Saydakov wrote: > > I have a few questions regarding aggregate functions that would be parallel > safe. > 1. do the inputs of combinefunc always go through serialfunc-deserialfunc or > they can come directly from sfunc (worker on the same machine,

RE: Performance issue after migration from 9.4 to 15

Hi, I will definitely give it a try; however, I do not understand how this could explain the observed performance changes. As far as I understand, an outdated/non-optimized index would not be relevant in a sequential scan, and outdated statistics could cause a non-optimal plan to be chosen, but

RE: Performance issue after migration from 9.4 to 15

Hi all, Thanks for all your tips! I have meanwhile run "ANALYZE" on all tables. Either that was as important as everybody tells me  or it is just a coincidence (e.g., fewer other queries on the database). In any case, the performance has indeed improved considerably. I will observe the

parallel aggregation

I have a few questions regarding aggregate functions that would be parallel safe. 1. do the inputs of combinefunc always go through serialfunc-deserialfunc or they can come directly from sfunc (worker on the same machine, perhaps)? 2. can the result of combinefunc ever be fed to sfunc as opposed

Re: Guidance on INSERT RETURNING order

On Wed, 12 Apr 2023 at 11:46, Thorsten Glaser wrote: > > On Tue, 11 Apr 2023, Federico wrote: > > >My understanding was that they are generated in select order > > But are they? (I don’t know, but I’d not assume that.) That's kind of the point for this question, to see if that's correct or not.

pg_basebackup / recovery

During recovery process of a self contained backup, how postgres know to stop reading wal when consistency is reached?