Transaction and SQL errors

Hello! Is there any plan to have an equivalent of psql's set ON_ERROR_ROLLBACK on in the DB engine? Most other DB engines have this behavior built-in, that makes migration to PostgreSQL more difficult when there are complex long transactions in the application code. I do often suggest progra

Re: Transaction and SQL errors

Hi Karsten, No... see the doc: https://www.postgresql.org/docs/14/app-psql.html ON_ERROR_ROLLBACK When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. Seb

Re: Transaction and SQL errors

From: David Rowley Sent: Monday, April 4, 2022 1:44 PM To: Sebastien Flaesch Cc: pgsql-gene...@postgresql.org Subject: Re: Transaction and SQL errors EXTERNAL: Do not click links or open attachments if you do not recognize the sender. On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch

Re: Transaction and SQL errors

David, Personally, I can see where it has significant value for psql in interactive mode because people make typos. Application code doesn't. That removes a whole class of problems where the feature provides benefit. Sure, application code must not have typos, but I prefer to let the DB engi

Re: Transaction and SQL errors

Hello Gilles and thanks for that link! I will definitively add that info to our issue tracker/db. Seb

Re: Transaction and SQL errors

Hello, Back on this "feature", I did some experiments by changing the code of our PostgreSQL driver (db connector)... We have our own programming language and VM/runtime. With a config setting (I don't want to enable this by default), our driver now automatically adds a SAVEPOINT before any SQ

Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Hello! I try to update the underlying sequence of a SERIAL column, by using a RETURNING clause in my INSERT statement, which is checking that the column value is greater than the last_value of my sequence, and reset the sequence with setval() if needed. When running several client processes in

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Tom, If that's the behavior you want, you can build it out of standard SQL facilities (e.g. update a one-row table). Can you elaborate please? Do you mean the code should use an UPDATE on a one-row table to acquire a lock? Seb

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

6. INSERT without value for serial column => sequence=4+1 => last_value = 5 This will also save setval() calls for each INSERT not specifying a value explicitly for the serial column. Stop me if I am wrong... 🙂 Seb Seb ____ From: Tom Lane Sent: Tuesday, July 1

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

clause resetting SERIAL sequence EXTERNAL: Do not click links or open attachments if you do not recognize the sender. Sebastien Flaesch schrieb am 19.07.2022 um 18:50: > Tom, > > /If that's the behavior you want, you can build it out of standard SQL > facilities (e.g. updat

Possible values of DATESTYLE / PGDATESTYLE

Hi! I can find possible values for the DATESTYLE parameter in an old 7.2 doc page: https://www.postgresql.org/docs/7.2/sql-set.html But when switching to V14 there are no more values listed in the equivalent page... Where can I find the list of possible values for this DATESTYLE parameter, for

lippq client library and openssl initialization: PQinitOpenSSL()

Hello! The PostgreSQL doc says that if the application code is initializing OpenSSL, it should tell PostgreSQL libpq client library that OpenSSL initialization is already done: https://www.postgresql.org/docs/14/libpq-ssl.html#LIBPQ-SSL-INITIALIZE I was wondering if this is still true with Ope

Resolving host to IP address

Hi! I am playing with PostgreSQL TLS/SSL connections using OpenSSL, with server and client certificates. I managed to establish the secure connection, by using DN=root.strasbourg.4js.com for the self-signed root CA, and DN=toro.strasbourg.4js.com for the server certificate, DN=pgsuser for the

Re: Resolving host to IP address

Thank you all for your comments. I think I got it: PostgreSQL should listen to the real, non-loopback network interface. Just for info (local dev config, not prod): sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ grep listen_addresses postgresql.conf listen_addresses = 'localhost,toro.strasbourg.4js.

Re: Resolving host to IP address

toro But that could go away if I understand well, as long as toro[.stras...] host names can be resolved. Note: I did not need to re-create the certificates. Thanks a lot, Seb From: Francisco Olarte Sent: Monday, September 12, 2022 5:30 PM To: Sebastien Flaesc

Creating LATIN9 database with PostgreSQL 15 on Windows

Hi! On my Windows 10 with PostgreSQL 15, I try to create a database with LATIN9 encoding (for testing purpose), but I have only the choice to select the C, POSIX and English_United States.1252 collations... C and POSIX are compatible with LATIN9, but SQL functions like UPPER() fail. English_Un

Re: Creating LATIN9 database with PostgreSQL 15 on Windows

wrong. Seb From: Sebastien Flaesch Sent: Thursday, October 20, 2022 5:32 PM To: pgsql-gene...@postgresql.org Subject: Creating LATIN9 database with PostgreSQL 15 on Windows EXTERNAL: Do not click links or open attachments if you do not recognize the sender. Hi

Intervals and ISO 8601 duration

PostgreSQL has the INTERVAL type, which can be defined with fields such as: INTERVAL YEAR TO MONTH(year-month class) INTERVAL DAY TO SECOND(p) (day-second class) It's not possible to define an INTERVAL YEAR TO SECOND(p), which makes sense, since the number of days in a month can vary. Othe

Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

Hello! Assuming that a sequence is used to implement GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY Is there any built-in function that returns the underlying sequence name used for such column? Otherwise, an SQL query to return the sequence name? I need the sequence name, in order to reset it

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

ponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column EXTERNAL: Do not click links or open attachments if you do not recognize the sender. Sebastien Flaesch schrieb am 06.02.2023 um 18:17: > Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY > DEFAULT } AS ID

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

INSERT: insert into tab1 (name) VALUES ('aaa') returning tab1.pkey, (select case when tab1.pkey > (select last_value from public.tab1_pkey_seq) then setval('public.tab1_pkey_seq',tab1.pkey,true) else 0 end) Seb ____

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

' and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema()) ) select tabname, colname, pg_get_serial_sequence(tabname,colname) from table_sequence; Any better suggestion? Seb From: Sebastien Flaesch Sent: Monday, February 6, 2023 7:11 PM To: Thoma

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

Good point, thanks Peter! Seb From: Peter Eisentraut Sent: Wednesday, February 8, 2023 12:07 PM To: Sebastien Flaesch ; pgsql-general@lists.postgresql.org Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

Multi-column index: Which column order

Hello! When creating an index on multiple columns, does the order of the columns matter? (I guess so) It's mostly for SELECT statements using a condition that include ALL columns of the index (pkey): SELECT * FROM art WHERE etb='L1' and code='ART345' I would naturally put the columns wit

PostgreSQL configuration in a VM

Hi! I was wondering if the is any specific configuration setting that should be used with PostgreSQL, when running in a VM... Is there anything obvious that must be set, to get best performances with such a config? Sorry for this general question... Seb

Using CTID system column as a "temporary" primary key

Hello! We are looking for an equivalent of Informix ROWID or Oracle's ROWNUM. Is the CTID a good choice? I assume it must be used in a specific context, and of course not considered as permanent primary key. I understand that if the row is updated, the CTID may change. Where can we find detai

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

I mean Oracle's ROWID of course, not ROWNUM. ROWNUM is temporary in the context of the SELECT, so it cannot be used in subsequent SQL statements. Seb From: Sebastien Flaesch Sent: Tuesday, March 28, 2023 11:28 AM To: pgsql-general Subject: Using CTID s

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

rom: Sebastien Flaesch Sent: Tuesday, March 28, 2023 11:57 AM To: pgsql-general Subject: Re: Using CTID system column as a "temporary" primary key EXTERNAL: Do not click links or open attachments if you do not recognize the sender. I mean Oracle's ROWID of course, not ROWNUM. ROWNUM i

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

: Sebastien Flaesch Cc: pgsql-general Subject: Re: Using CTID system column as a "temporary" primary key EXTERNAL: Do not click links or open attachments if you do not recognize the sender. On Tue, 28 Mar 2023 at 10:28, Sebastien Flaesch mailto:sebastien.flae...@4js.com>> wrot

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

CAST seems to do the job so that's a good solution here. Seb From: Christophe Pettus Sent: Tuesday, March 28, 2023 2:39 PM To: Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general Subject: Re: Using CTID system column as a "temporary" primary ke

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

pecify all columns except user-def ROWID or you add the rowid field to the program variable structure that receives the row. ... Seb From: Kirk Wolak Sent: Tuesday, March 28, 2023 8:24 PM To: Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general Subject: Re: Using C

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

Laurent, Thanks for the advice about REPEATABLE READ isolation level! Seb From: Laurenz Albe Sent: Wednesday, March 29, 2023 1:08 PM To: Kirk Wolak ; Sebastien Flaesch Cc: Geoff Winkless ; pgsql-general Subject: Re: Using CTID system column as a "temp

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

Ok... sounds not good all in all. Appreciate your help! Thanks! From: Laurenz Albe Sent: Wednesday, March 29, 2023 5:53 PM To: Sebastien Flaesch ; Kirk Wolak Cc: Geoff Winkless ; pgsql-general Subject: Re: Using CTID system column as a "temporary" p

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

say : no, using zero was not considered. 😉 Seb From: Adrian Klaver Sent: Wednesday, March 29, 2023 4:59 PM To: Sebastien Flaesch ; Kirk Wolak Cc: Geoff Winkless ; pgsql-general Subject: Re: Using CTID system column as a "temporary" primary key EXTERNAL: Do n

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

I understand and agree. Anyway, we suggest our customers to use sequences instead of serials. Seb From: Alban Hertroys Sent: Wednesday, March 29, 2023 10:15 PM To: Sebastien Flaesch Cc: Adrian Klaver ; Kirk Wolak ; Geoff Winkless ; pgsql-general Subject: Re

JSON / ASP.NET AJAX Dates support in PostgreSQL

Hello, Is there an easy way to convert JSON data containing ASP.NET AJAX Dates into PostgreSQL timestamp? I have this kind of JSON data: { "PurchaseOrder" : "45", "CreationDate" : "\/Date(167235840)\/", "LastChangeDateTime" : "\/Date(1672692813062+0100)\/" } Warning: No

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

___ From: Ron Sent: Thursday, April 13, 2023 5:07 PM To: pgsql-general@lists.postgresql.org Subject: Re: JSON / ASP.NET AJAX Dates support in PostgreSQL EXTERNAL: Do not click links or open attachments if you do not recognize the sender. On 4/13/23 09:44, Sebastien Flaesch wro

OpenSSL version 3

Hello, OpenSSL V1 goes end of support soon (see https://www.openssl.org/policies/releasestrat.html) with replacement should be OpenSSL V3. What is the strategy with PostgreSQL (regarding sources, and binary packages)? On the various supported platforms? Seb

Build fails on RHEL 8.8 about libxml2 version

Hello, Trying to build PostgreSQL 15 and 16 on RHEL 8.8: [comp@havok postgresql-15.1]$ cat /etc/redhat-release Red Hat Enterprise Linux release 8.8 (Ootpa) [comp@havok postgresql-15.1]$ uname -a Linux havok.strasbourg.4js.com 4.18.0-477.27.1.el8_8.x86_64 #1 SMP Thu Aug 31 10:29:22 EDT 2023 x86_

Re: Build fails on RHEL 8.8 about libxml2 version

Answering to myself: Of course I must install the "*-devel" packages Seb ____ From: Sebastien Flaesch Sent: Thursday, October 19, 2023 10:36 AM To: pgsql-general@lists.postgresql.org Subject: Build fails on RHEL 8.8 about libxml2 version EXTERNAL: Do

prepared statement "cu1" already exists (but it does not)

Hello, In a specific case, I get the error   prepared statement "cu1" already exists I understand when this can happen, but in fact I do de-allocate prepared statements when I should. I am investigating on this for several hours now, I thought I could share my problem to see if this ring a be

Re: prepared statement "cu1" already exists (but it does not)

Here a first PQtrace() file... assuming it can help. Seb From: Sebastien Flaesch Sent: Monday, April 8, 2024 5:31 PM To: pgsql-general Cc: Sebastien Flaesch Subject: prepared statement "cu1" already exists (but it does not) Hello, In a specific case,

Re: prepared statement "cu1" already exists (but it does not)

Sent: Monday, April 8, 2024 7:36 PM To: Sebastien Flaesch Cc: pgsql-general Subject: Re: prepared statement "cu1" already exists (but it does not) EXTERNAL: Do not click links or open attachments if you do not recognize the sender. Sebastien Flaesch writes: > I understand that th

Tracing libpq client: Only with PQtrace()?

Hello Sorry if I missed that in the doc: Is the PQtrace() API the only way to enable libpq client tracing? I thought about some environment variable of client configuration setting... Seb

Re: prepared statement "cu1" already exists (but it does not)

;cu1", "declare cu1 cursor for ... ", ... ) PQexecPrepared(pgConn, "cu1", ... ) ? So far this has always worked. Seb From: Tom Lane Sent: Monday, April 8, 2024 7:36 PM To: Sebastien Flaesch Cc: pgsql-general Subject: Re: prepared statement "cu1&quo

Re: Tracing libpq client: Only with PQtrace()?

Yep I got it, thanks for the suggestion! Seb From: Greg Sabino Mullane Sent: Tuesday, April 9, 2024 4:50 PM To: Sebastien Flaesch Cc: pgsql-general Subject: Re: Tracing libpq client: Only with PQtrace()? EXTERNAL: Do not click links or open attachments if you

Re: prepared statement "cu1" already exists (but it does not)

: Tom Lane Sent: Tuesday, April 9, 2024 6:36 PM To: Sebastien Flaesch Cc: pgsql-general Subject: Re: prepared statement "cu1" already exists (but it does not) EXTERNAL: Do not click links or open attachments if you do not recognize the sender. Sebastien Flaesch writes: >>>

SUM() of INTERVAL type produces INTERVAL with no precision

Hello! When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the resulting type loses the interval type qualifiers... Obviously one can cast the SUM() to get the original type back, but I was wondering if there is a simpler way to handle this, to not force programmers to add a

Re: SUM() of INTERVAL type produces INTERVAL with no precision

12434:12:11 9 days 12:11:10 years 10 mons 12:11:10 years 10 mons 55 days 12:11:10 (4 rows) How much days is is years 10 months 55 days? A month can have 30, 31, 28 or 29 days ... I must carefully read the doc again. Thanks for you answer(s) Seb ______

Re: SUM() of INTERVAL type produces INTERVAL with no precision

ays 11:22:00 (1 row) Expected? Qualifiers "hour to minute" seem just to be some decoration... Seb ________ From: Sebastien Flaesch Sent: Friday, December 3, 2021 5:08 PM To: Tom Lane Cc: pgsql-general Subject: Re: SUM() of INTERVAL type produces INTERVAL with no

Re: SUM() of INTERVAL type produces INTERVAL with no precision

My bad, sorry. RTFM Seb. Seb From: Adrian Klaver Sent: Friday, December 3, 2021 5:30 PM To: Sebastien Flaesch ; Tom Lane Cc: pgsql-general Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision EXTERNAL: Do not click links or open attachments

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

Hello, I think the column type identification with PQftype() needs some review/clarification. It's a pity that libpq does not have an API to return directly the actual type name of a column. The doc says: You can query the sys

pgvector as standard PostgreSQL feature?

Hello, I am looking at pgvector, pgvectorscale, pgai extensions. Other DB engines support built-in vector types. Is there a plan to get pgvector's types (vector, halfvec, sparsevec, bit) implemented as native built-in data types like json/jsonb ? Side note: I have some doubts about these type

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

API doc would. Seb From: Tom Lane Sent: Thursday, March 20, 2025 3:31 PM To: Sebastien Flaesch Cc: Adrian Klaver ; M Tarkeshwar Rao ; pgsql-gene...@postgresql.org Subject: Re: After upgrading libpq, the same function(PQftype) call returns a different OID EXTERNAL: Do not

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

this is maybe not natural for PostgreSQL implementors, because of the flexible data type system and the fact that even built-in type ids are generated. Seb From: David G. Johnston Sent: Thursday, March 20, 2025 5:25 PM To: Sebastien Flaesch Cc: Tom Lane ; Adr

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

29 ... I don't care if this list is generated when building PostgreSQL from sources. I expect however that the type oids for built-in types remain the same forever. Seb From: Tom Lane Sent: Wednesday, March 19, 2025 6:22 PM To: Adrian Klaver Cc: Sebast

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

erver" folder, when it's to be used for client apps. And still, I do not trust the content. Seb From: Adrian Klaver Sent: Tuesday, March 18, 2025 7:41 PM To: Sebastien Flaesch ; Tom Lane ; M Tarkeshwar Rao Cc: pgsql-gene...@postgresql.org Subject: Re:

Re: pgvector as standard PostgreSQL feature?

stien Flaesch Cc: pgsql-gene...@postgresql.org Subject: Re: pgvector as standard PostgreSQL feature? EXTERNAL: Do not click links or open attachments if you do not recognize the sender. > On Mar 19, 2025, at 07:47, Sebastien Flaesch > wrote: > > Is there a plan to get pgvector's

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

Dominique, That's what Tom already replied, yes. --DD My bad! I missed that answer from Tom. Thanks. Seb From: Dominique Devienne Sent: Thursday, March 20, 2025 4:49 PM To: Sebastien Flaesch Cc: Tom Lane ; Adrian Klaver ; M Tarkeshwar Rao ; pgsql