Transaction and SQL errors

[Sebastien Flaesch]

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 programmers to trust the DB engine regarding table 
constraints, and let the engine check unique / foreign keys, etc. However, 
these errors cancel the whole transaction and user code can't correct the last 
SQL statement and try a new execution, without losing everything since TX 
started...

Any thoughts?

Any arguments I could give to programmers?   Maybe:
"You better replay all SQL of the whole transaction... (and make them as short 
as possible!)"
?

Seb

Re: Transaction and SQL errors

[Sebastien Flaesch]

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

[Sebastien Flaesch]

Hi David,

I know savepoints are supported by PostgreSQL, but what about existing/legacy 
code that has tons of lines that rely on the behavior of other DB engines?

Would you ask programmers to put a savepoint / release savepoint around each 
SQL statement inside a TX?

Seb

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
 wrote:
> Any arguments I could give to programmers?   Maybe:
> "You better replay all SQL of the whole transaction... (and make them as 
> short as possible!)"
> ?

There are SAVEPOINTs [1].

David

[1] 
https://urldefense.com/v3/__https://www.postgresql.org/docs/current/sql-savepoint.html__;!!I_DbfM1H!SJaCAE7hUHxRte8uMaa9RB8byL3QsmxkBRzk9POp0N8sLvjhpL5AWoiH-7MEuTkZMcrl$

Re: Transaction and SQL errors

[Sebastien Flaesch]

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 engine 
check for SQL constraints.

Imagine the following case:

BEGIN WORK
... (some other SQL) ...
DELETE FROM items WHERE item_id = 12823  -- Can raise foreign key error
if sql-error then
...

To me it's better than:

BEGIN WORK
...
SELECT ... FROM orders WHERE item_id = 12823
if not-found then -- make sure we get no SQL error than cancels TX!
  DELETE FROM items WHERE item_id = 12823
endif
...

... and not even sure it's valid atomic code depending on isolation level...

A good argument for PostgreSQL's behavior would be that it's better to cancel 
the whole transaction and restart all SQL commands.

However, legacy code is often spaghetti code where one function starts the TX, 
then calls other functions doing SQL ... ( yes, good candidate for savepoints 
usage! )

Anyway, thanks for the info, nothing planed short term, and that's what I was 
asking for.

Cheers!
Seb

Re: Transaction and SQL errors

[Sebastien Flaesch]

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

Re: Transaction and SQL errors

[Sebastien Flaesch]

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 SQL statement executed in a 
transaction block, and issues an automatic ROLLBACK TO SAVEPOINT in case of SQL 
error, or RELEASE SAVEPOINT, if not SQL error occurred (I think last one is 
useless, but I prefer to cleanup)

>From a functional point of view, it does the job, and we get now the same 
>behavior as with other DB engines.

However, depending on the SQL statements in the transaction, the execution time 
can increase by x10 to x20

I have tested with PostgreSQL 14.1:

Same code takes 12.6 seconds with PostgreSQL, while it takes 0.5 to 2 seconds 
with other DB engines.

The code (written on our own programming language) is basically doing this:

BEGIN WORK
FOR x=1 TO 1000
   DELETE FROM tab2 WHERE pkey = 1001
   DELETE FROM tab1 WHERE pkey = 102
   INSERT INTO tab1 VALUES ( 102, '' )
   INSERT INTO tab2 VALUES ( 1001, 'xxx1', 101 )
   SAVEPOINT sp101
   UPDATE tab1 SET name = '' WHERE pkey = 102
   ROLLBACK WORK TO SAVEPOINT sp101
END FOR
COMMIT WORK

So, I was wondering if someone can comment on the cost of a ROLLBACK TO 
SAVEPOINT...

Yes, this should not occur often.
But imagine some code that tries to INSERT or UPDATE rows, relies in DB 
constraints like UNIQUE to try other values, or relies on FOREIGN KEY 
constraints to DELETE some rows and in case of SQL error wants to continue the 
TX by deleting other rows...

I just want to anticipate customers complains that it's slower as expected.

Seb

Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

[Sebastien Flaesch]

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 concurrently, using this INSERT 
statement, I get duplicate primary key errors.

The SQL code of my INSERT looks like this:

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)

To me something is wrong with the behavior of PostgreSQL:

The whole INSERT statement (including the code in the RETURNING clause), should 
execute in a ATOMIC manner.

Obviously if several processes execute that code in parallel, and if the select 
last_value / setval() get mixed up, we are in trouble...

Can something confirm this is a PostgreSQL bug, or help me to find the right 
code to avoid the issue?

To reproduce:

  1.  Create tab1 table and stored procedure for testing with insert-tab1.sql
  2.  Run several psql processes in parallel, calling the stored procedure: sh 
./start2.sh (Linux/bash)

Thanks!

Seb


inserts-tab1.sql
Description: inserts-tab1.sql


call-proc.sql
Description: call-proc.sql


start2.sh
Description: start2.sh

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

[Sebastien Flaesch]

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

[Sebastien Flaesch]

Changing a bit the CASE/WHEN logic in the RETURNING clause solves the issue 
when concurrently inserting rows without specifying explicitly a value for the 
serial column (INSERTs specifying values for the serial column are seldom used 
by concurrent programs inserting many rows):

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)


Example (assuming there is no unique constraint!):

  1.  INSERT without value for serial column => sequence=0+1 => last_value = 1
  2.  INSERT without value for serial column => sequence=1+1 => last_value = 2
  3.  INSERT with value 2 for serial column => tab1.pkey(2) > last_value(2) ? 
false => no sequence reset
  4.  INSERT without value for serial column => sequence=2+1 => last_value = 3
  5.  INSERT with value 4 for serial column => tab1.pkey(4) > last_value ? true 
=> setval(seqname,4,true)
  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 19, 2022 5:41 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: Concurrent INSERT statements with RETURNING clause resetting 
SERIAL sequence

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch  writes:
> 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.

It's not too surprising that that doesn't work, if you're coding it
based on this assumption:

> The whole INSERT statement (including the code in the RETURNING clause), 
> should execute in a ATOMIC manner.

Sequence-related actions are always carried out immediately, they do
not participate in any atomicity guarantees about the calling transaction.
Without this, any sequence update would have to block all concurrent
uses of that sequence until they see whether the first update commits.

If that's the behavior you want, you can build it out of standard SQL
facilities (e.g. update a one-row table).  The point of sequence objects
is exactly to provide a feature with better concurrent performance,
at the cost of no rollback guarantees.

So, there's no bug here, and calling it one isn't going to change
anybody's mind about that.

regards, tom lane

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

[Sebastien Flaesch]

Thomas, we already have a similar solution.
The idea is to use the native PostgreSQL SERIAL type.
Seb

From: Thomas Kellerer 
Sent: Wednesday, July 20, 2022 8:56 AM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Concurrent INSERT statements with RETURNING 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. 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?

I assume something like this:

https://urldefense.com/v3/__https://blog.sql-workbench.eu/post/gapless-sequence/__;!!I_DbfM1H!F7_2cNahve0cmwPMP6QBBwwpyP6UAum4ukFj71_21ebcxTKXZFtU0_3O6l1lfG5jYiKjO7wEzRt_E1GbJ9Q$

Possible values of DATESTYLE / PGDATESTYLE

[Sebastien Flaesch]

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 
V14?

BR,
Seb

lippq client library and openssl initialization: PQinitOpenSSL()

[Sebastien Flaesch]

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 OpenSSL 1.1.0+

The APIs to initialize OpenSSL are OPENSSL_init_ssl() or OPENSSL_init_crypto().

According to the OpenSSL doc, version 1.1.0 initializes itself automatically 
when calling other APIs ...

https://www.openssl.org/docs/man1.1.1/man3/OPENSSL_init_ssl.html

As of version 1.1.0 OpenSSL will automatically allocate all resources that it 
needs so no explicit initialisation is required. Similarly it will also 
automatically deinitialise as required.

So, is a call to PQinitOpenSSL(0, 0) still needed?

I did some test with our application, and I could establish a TLS/SSL 
connection using server and client certificates.

What can go wrong in fact?

Can someone give me a hint, so I can prove that we really need to call 
PQinitOpenSSL(0,0)?

Note: Our application is for now single-threaded.

OpenSSL doc also states:

However, there may be situations when explicit initialisation is desirable or 
needed, for example when some nondefault initialisation is required.

If our application would requires nondefault initialization, I assume that 
PostgreSQL openssl usage will implicitly inherit the OpenSSL seetings of our 
application, right?

Can this be an issue for PostgreSQL, or can both just share the same OpenSSL 
settings/config?

Thanks!
Seb

Resolving host to IP address

[Sebastien Flaesch]

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 
client certificate.

I have created my client certificate by using the root CA.

pg_hba.conf:

hostssl all pgsuser toro.strasbourg.4js.com  md5 
clientcert=verify-ca


Server and client are on the same Debian 11 machine.

It works, if I comment out the /etc/hosts line set by Debian Linux for my host 
name:

# 127.0.1.1   toro.strasbourg.4js.com toro

The name "toro" is then resolved to the IP address provided by my DHCP server:

root@toro:~# host toro
toro.strasbourg.4js.com has address 10.0.40.61

root@toro:~# host toro.strasbourg.4js.com
toro.strasbourg.4js.com has address 10.0.40.61

However, if I put the 127.0.1.1 line back in /etc/hosts, re-create all 
certificates (is this actually needed? I guess no), restart the PostgreSQL 
server, I get this error:

$ psql 
'postgresql://toro.strasbourg.4js.com:5437/test1?user=pgsuser&sslmode=verify-ca&sslrootcert=./root.crt&sslcert=./client.crt&sslkey=./client.key'
psql: error: connection to server at "toro.strasbourg.4js.com" (127.0.1.1), 
port 5437 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user 
"pgsuser", database "test1", SSL encryption


What looks strange to me in this error is that first it mentions 127.0.1.1 (ok) 
but then, 127.0.0.1

What am I missing here?

Thanks!
Seb

Re: Resolving host to IP address

[Sebastien Flaesch]

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.com'


sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W | grep 5437
tcp0  0 localhost:5437  0.0.0.0:*   LISTEN
tcp0  0 toro.strasbourg.4js.com:5437 0.0.0.0:*   LISTEN
tcp6   0  0 localhost:5437  [::]:*  LISTEN


sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W --numeric-hosts | grep 
5437
tcp0  0 127.0.0.1:5437  0.0.0.0:*   LISTEN
tcp0  0 127.0.1.1:5437  0.0.0.0:*   LISTEN
tcp6   0  0 ::1:5437:::*LISTEN


sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ ip route get 127.0.1.1
local 127.0.1.1 dev lo src 127.0.0.1 uid 1000
cache 


Seb

Re: Resolving host to IP address

[Sebastien Flaesch]

Hi!

That works for my case now:

postgresql.conf:

listen_addresses = '0.0.0.0'(only IPv4 is ok for me)

pg_hba.conf:

hostssl all pgsuser samenet  md5 
clientcert=verify-ca


In /etc/hosts, I still have:

127.0.1.1   toro.strasbourg.4js.com 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 Flaesch 
Cc: Tom Lane ; pgsql-general@lists.postgresql.org 

Subject: Re: Resolving host to IP address

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Hi  Sebastien:

On Mon, 12 Sept 2022 at 16:40, Sebastien Flaesch
 wrote:

> I think I got it: PostgreSQL should listen to the real, non-loopback network 
> interface.

Not really. Your problem is not where postgres listens, but where your
host line resolves, the 127.0.1.1 stuff, plus your rules.

In many OS you can bind a listening TCP socket to IN_ADDR_ANY plus a
port. The good thing of doing it is it will work even if you add /
delete new IP addresses. Postgres does this and, IMO, is a good thing.
You seem to have it configured that way-

> 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.com'

No, you do not, you list your interfaces.. Unless you have a
complex setup, which does not seem to be the case, I would recommend
just using the default "*", specially if you intend to run your
servers firewalled ( which 10.xxx hints to ). This would lead to
something like ..

tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN
tcp6   0  0 :::5432 :::*LISTEN

( not done with pg, done with nc -l, but it worked the same last time
I checked it )

> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W | grep 5437
> tcp0  0 localhost:5437  0.0.0.0:*   LISTEN
> tcp0  0 toro.strasbourg.4js.com:5437 0.0.0.0:*   
> LISTEN
> tcp6   0  0 localhost:5437  [::]:*  LISTEN

There is a piece of info missing here, where does your localhost resolve to.

> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W --numeric-hosts | 
> grep 5437
> tcp0  0 127.0.0.1:5437  0.0.0.0:*   LISTEN
> tcp0  0 127.0.1.1:5437  0.0.0.0:*   LISTEN
> tcp6   0  0 ::1:5437:::*LISTEN

But from this it seems, if you have restored the 127.0.1.1 hosts line,
to 127.0.0.1+::1

> sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ ip route get 127.0.1.1
> local 127.0.1.1 dev lo src 127.0.0.1 uid 1000
> cache 
This is expected.

IMO your problem arises from your pg_hba.conf, and your bizarre ( IMO,
I use debian too and one of the first things I do after installing is
dropping the 127.0.1.1 line, but I used fixed IP on a desktop, so YMMV
).
If you just lists localhost as origin ( which means you have to
duplicate the pg_hba.conf lines, listen_address=* will work for both )
it will work right, whatever your listen_adresses is ). You can also
RTFM a bit and notice the line "You can also write all to match any IP
address, samehost to match any of the server's own IP addresses, or
samenet to match any address in any subnet that the server is directly
connected to." ( it is easy to miss, I had to search for it, even if I
knew it existed ).

Note: localhost is a name, like toro, and is resolved,
samehost/samenet/all are magic.


Sumarizing, I would:

- Use listen_adresses=*
- Use samehost in your rules.

Which will lead to a lean config which probably does what you want.

Francisco Olarte.

Creating LATIN9 database with PostgreSQL 15 on Windows

[Sebastien Flaesch]

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_United States.1252 is not compatible with LATIN9.

I do not want to use WIN1252 encoding, I must use LATIN9 because it's for QA 
tests and my programs need LATIN9 (comes from Linux)

Do I have to create/install more locales on this PostgreSQL 15 / Windows 
installation?

I wonder... I while ago with PostgreSQL 13 I have created a DB with LATIN9 on 
this machine.

I must be missing something, can't remember how I did that.

Can someone point me to the doc page where I can find related info?

Thanks!
Seb

Re: Creating LATIN9 database with PostgreSQL 15 on Windows

[Sebastien Flaesch]

Answering to myself:

I was using pgAdmin to create the database, and this tool does not show much 
collations.

I could create my database from the command line:

createdb --username=postgres --port=5436 --template=template0 --encoding=latin9 
--locale=English_US.28605 test2

Stop me if I am 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!

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_United States.1252 is not compatible with LATIN9.

I do not want to use WIN1252 encoding, I must use LATIN9 because it's for QA 
tests and my programs need LATIN9 (comes from Linux)

Do I have to create/install more locales on this PostgreSQL 15 / Windows 
installation?

I wonder... I while ago with PostgreSQL 13 I have created a DB with LATIN9 on 
this machine.

I must be missing something, can't remember how I did that.

Can someone point me to the doc page where I can find related info?

Thanks!
Seb

Intervals and ISO 8601 duration

[Sebastien Flaesch]

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. Other SQL engines like Oracle and 
Informix also have 2 classes of interval types.

However, the ISO-8601 standard format for durations allows to specify 
year/month with day to second parts, for example:

P2Y10M15DT10H30M20S

Seems PostgreSQL accepts this format in input.
But what does it mean exactly?
What is the actual INTERVAL value and INTERVAL class of this?

Testing with V15.1:

What is the interval class in this case:

test1=> select cast('P2Y10M15DT10H30M20S' as interval);
 interval
--
 2 years 10 mons 15 days 10:30:20
(1 row)

Should the following convert to a day-second interval?

test1=> select cast('P2Y10M15DT10H30M20S' as interval day to second);
 interval
--
 2 years 10 mons 15 days 10:30:20
(1 row)

Should PostgreSQL not raise an SQL error in above cases?


When using invalid INTERVAL fields, error is raised as expected:

test1=> select cast('P2Y10M15DT10H30M20S' as interval year to second);
ERROR:  syntax error at or near "second"
LINE 1: ...lect cast('P2Y10M15DT10H30M20S' as interval year to second);


Does PostgreSQL assume that a month is ~30 days?

I did not find details about this in the documentation.

Thanks in advance!
Seb

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

[Sebastien Flaesch]

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 (setval) or to get the last 
generated value (currval) ...

The query must work with all PostgreSQL versions 10 to 15 ...

Seb

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

[Sebastien Flaesch]

I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from 
pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 
'nextval.''([^'']*)') seqname, c.attname
  from pg_class p
   join pg_attrdef a on (p.oid=a.adrelid)
   join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and pg_get_expr(a.adbin,0) like 'nextval%'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb


From: Thomas Kellerer 
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Get the sequence name corresponding 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 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 (setval) or to get the last 
> generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

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

[Sebastien Flaesch]

... and I would like to automatically return the last generated identity, and 
also reset the sequence, if a value was specified explicitly for the identity 
column and this value is greater than the last generated value.

With serial/bigserial/smallserial I am adding a returning clause to each 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
____
From: Sebastien Flaesch 
Sent: Monday, February 6, 2023 7:03 PM
To: Thomas Kellerer ; pgsql-general@lists.postgresql.org 

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


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from 
pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 
'nextval.''([^'']*)') seqname, c.attname
  from pg_class p
   join pg_attrdef a on (p.oid=a.adrelid)
   join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and pg_get_expr(a.adbin,0) like 'nextval%'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb


From: Thomas Kellerer 
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Get the sequence name corresponding 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 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 (setval) or to get the last 
> generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

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

[Sebastien Flaesch]

Just sharing my conclusion so far, maybe this could do the job?

Check the usage of double quotes, to manage upper/lower case in schema and 
table name.
This is not needed for the column name, according to the doc of 
pg_get_serial_sequence().


with table_sequence as (
select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname
  from pg_class p
   join pg_attribute c on p.oid=c.attrelid
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and c.attidentity = 'd'
   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: Thomas Kellerer ; pgsql-general@lists.postgresql.org 

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


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

... and I would like to automatically return the last generated identity, and 
also reset the sequence, if a value was specified explicitly for the identity 
column and this value is greater than the last generated value.

With serial/bigserial/smallserial I am adding a returning clause to each 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

From: Sebastien Flaesch 
Sent: Monday, February 6, 2023 7:03 PM
To: Thomas Kellerer ; pgsql-general@lists.postgresql.org 

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


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from 
pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 
'nextval.''([^'']*)') seqname, c.attname
  from pg_class p
   join pg_attrdef a on (p.oid=a.adrelid)
   join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and pg_get_expr(a.adbin,0) like 'nextval%'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb


From: Thomas Kellerer 
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Get the sequence name corresponding 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 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 (setval) or to get the last 
> generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

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

[Sebastien Flaesch]

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

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On 07.02.23 11:43, Sebastien Flaesch wrote:
> select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname

Just a side note: You might find the quote_ident() function useful.

Multi-column index: Which column order

[Sebastien Flaesch]

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 with the most various values first, and

For example, if the "code" column contains thousands of various item ids like 
'SXZ874', 'ERF345', ... while the "etb" column contains a dozen of values like 
"L1", "LT" and "BX".

Which one is best?

CREATE UNIQUE INDEX ix1 ON art (code, etb)
or
CREATE UNIQUE INDEX ix1 ON art (etb, code)

(or its PRIMARY KEY equivalent)

Does it depend on the type of index (Btree, GiST, etc) ?

I could not find that information in the doc.

Seb

PostgreSQL configuration in a VM

[Sebastien Flaesch]

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

[Sebastien Flaesch]

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 details about the validity and lifetime of the value such 
column?

Will CTID be supported long term or is there any plan to remove it or hide it 
some day?

Of course, one should use a real primary key definition. However, we have 
legacy code to adapt to PostgreSQL, and in some cases, tables have a composite 
primary key. A first SELECT uses that primary key, but it also fetches the 
ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead 
of carrying the composite pkey values.

Seb

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

[Sebastien Flaesch]

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 system column as a "temporary" primary key


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

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 details about the validity and lifetime of the value such 
column?

Will CTID be supported long term or is there any plan to remove it or hide it 
some day?

Of course, one should use a real primary key definition. However, we have 
legacy code to adapt to PostgreSQL, and in some cases, tables have a composite 
primary key. A first SELECT uses that primary key, but it also fetches the 
ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead 
of carrying the composite pkey values.

Seb

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

[Sebastien Flaesch]

How large can the string representation of a CTID (TID type) be?

This page mentions 6 bytes for t_ctid / ItemPointerData...
=> how can I deduce the max size of its string representation?

https://www.postgresql.org/docs/14/storage-page-layout.html

Seb

From: 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 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 system column as a "temporary" primary key


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

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 details about the validity and lifetime of the value such 
column?

Will CTID be supported long term or is there any plan to remove it or hide it 
some day?

Of course, one should use a real primary key definition. However, we have 
legacy code to adapt to PostgreSQL, and in some cases, tables have a composite 
primary key. A first SELECT uses that primary key, but it also fetches the 
ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead 
of carrying the composite pkey values.

Seb

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

[Sebastien Flaesch]

Hi Geoff,

Your remark makes total sense, and this is what should be done.

However, we have to deal with quite complex legacy 4GL code that we prefer to 
not touch, and we can adapt the SQL statements on the fly with our solution 
(kind of Java compiler/runtime system).

Next question:

How can I UPDATE or DELETE a row, with the CTID column?

When I bind a string parameter, I get this error:

SQLSTATE = 42883
MESSAGE: operator does not exist: tid = character varying

Do I have to cast() ?

Seb


From: Geoff Winkless 
Sent: Tuesday, March 28, 2023 12:20 PM
To: 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>> wrote:
Is the CTID a good choice?

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 
your own indexed BIGSERIAL value called "ROWID" to the rows and use that 
instead (assuming it will be large enough).

Geoff

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

[Sebastien Flaesch]

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 key

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

> On Mar 28, 2023, at 03:39, Sebastien Flaesch  
> wrote:
> Do I have to cast() ?

Yes:

select * from t where ctid='(0,1)'::tid;

The string representation can be up to 17 characters: 10 for the page number, 4 
for the tuple number, and three for the delimiters.

Remember that updating a row changes its CTID.

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

[Sebastien Flaesch]

Hello Kirk,

We are pushing our customers to use only "pure" SQL without writing triggers or 
stored procedures, to not be stuck with a specific DB vendor.

We have a quite good vision of what is SQL portable and what is not SQL 
portable.

Concurrent data access is one these topic, especially when using old-style 
Informix pessimistic locking where you declare a cursor FOR UPDATE, fetch the 
row to set an exclusive lock, until the end user has finished to modify the 
record in the form, then do the UPDATE and close the cursor or commit the TX to 
release the lock. Involves all concepts of concurrent data access (isolation 
level, lock wait mode, locks and locking granularity, transactions) - best 
solution I found so far is: Committed read isolation level, wait for locks to 
the released (with timeout like 10 seconds), do short transaction to hold locks 
only for a fraction of seconds.

For sure the application code needs to be modified.

Adding a ROWID BIGSERIAL is an option we consider, but then it has other 
constraints.

INSERT statements must not use the serial column, so you have to list all 
columns of the table and provide only the values of the non-serial columns. 
With Informix you could just specific a zero to get a new generated serial, but 
seems this has never been considered with PostgreSQL.

SELECT * FROM table will return all column, user-defined ROWID included...
This is not the case with Informix or Oracle ROWID columns.
So, either you specify 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 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, Mar 28, 2023 at 6:40 AM Sebastien Flaesch 
mailto:sebastien.flae...@4js.com>> 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 
your own indexed BIGSERIAL value called "ROWID" to the rows and use that 
instead (assuming it will be large enough).

Geoff

I have to second this...  Why not, during conversion, create a ROWID BIGSERIAL 
column in the PG only version.  (And if not large enough, it's easy enough to 
use a NUMERIC field, and a sequence)
Then the code would have access to this field, and when it uses it to 
delete/update it should work.

I cringe at the thought of using CTID.  And while it's probably "safe enough" 
inside a single transaction.  I doubt that there is much "testing" of this 
concept.

Having been through this process (Oracle to PG), I wonder how far you are into 
the process...  Because Packages/Package Variables, Global Temp Tables, and 
Autonomous Transactions all consumed significant time in our process, as well 
as variable/field naming problems...  If you pull off converting this to PG 
without changing the source.  Let me know...

Regards, Kirk

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

[Sebastien Flaesch]

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 "temporary" primary key

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Tue, 2023-03-28 at 14:24 -0400, Kirk Wolak wrote:
>
> I cringe at the thought of using CTID.  And while it's probably "safe enough"
> inside a single transaction.  I doubt that there is much "testing" of this 
> concept.

It is safe to assume that the CTID is stable within a single transaction
only if you use REPEATABLE READ or better transaction isolation level.

With READ COMMITTED, you see updated rows (and consequently changed CTID)
within a single transaction.  And if you use SELECT ... FOR UPDATE, you
could even see a changed CTID within a single statement.

So don't use CTID to identify rows unless you use REPEATABLE READ or better.

Yours,
Laurenz Albe

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

[Sebastien Flaesch]

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" primary key

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Wed, 2023-03-29 at 14:23 +, Sebastien Flaesch wrote:
> From: Laurenz Albe 
> > It is safe to assume that the CTID is stable within a single transaction
> > only if you use REPEATABLE READ or better transaction isolation level.
> >
> > With READ COMMITTED, you see updated rows (and consequently changed CTID)
> > within a single transaction.  And if you use SELECT ... FOR UPDATE, you
> > could even see a changed CTID within a single statement.
> >
> > So don't use CTID to identify rows unless you use REPEATABLE READ or better.
>
> Thanks for the advice about REPEATABLE READ isolation level!


... but that is only useful in a read-only scenario.

If you try to UPDATE the row in a REPEATABLE READ transaction, you
will get a serialization error if there was a concurrent update.

In short: don't use the CTID to identify a row.

Yours,
Laurenz Albe

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

[Sebastien Flaesch]

Oh the use of default keyword is new to me, thanks for that.

But to make PostgreSQL more Informix-compatible, zero should have been 
considered as well.

Informix:

sf@toro:/tmp$ dbaccess test1 -
Database selected.
> create table mytable ( pkey serial not null primary key, name varchar(50) );
Table created.

> insert into mytable values ( 0, 'a' );
1 row(s) inserted.

> select * from mytable;
   pkey name
  1 a
1 row(s) retrieved.


PostgreSQL:

sf@toro:/tmp$ psql test1 --host=localhost --port=5436 --user=pgsuser
psql (14.1)
Type "help" for help.

test1=> create table mytable ( pkey serial not null primary key, name 
varchar(50) );
CREATE TABLE

test1=> insert into mytable values ( 0, 'a' );
INSERT 0 1

test1=> select * from mytable;
 pkey | name
--+---
0 | a
(1 row)



So, I would rather 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 not click links or open attachments if you do not recognize the 
sender.

On 3/29/23 07:19, Sebastien Flaesch wrote:
> Hello Kirk,
>

> INSERT statements must not use the serial column, so you have to list
> all columns of the table and provide only the values of the non-serial
> columns. With Informix you could just specific a zero to get a new
> generated serial, but seems this has never been considered with PostgreSQL.

Yes it has:

  \d seq_test
  Table "public.seq_test"
  Column |   Type| Collation | Nullable |
Default
+---+---+--+--
  id | integer   |   | not null |
nextval('seq_test_id_seq'::regclass)
  fld_1  | character varying |   |  |
Indexes:
 "seq_test_pkey" PRIMARY KEY, btree (id)

insert into seq_test values(default, 'test');

select * from seq_test;
  id | fld_1
+---
   1 | test


>
> SELECT * FROM table will return all column, user-defined ROWID included...
> This is not the case with Informix or Oracle ROWID columns.
> So, either you specify all columns except user-def ROWID or you add the
> rowid field to the program variable structure that receives the row.
>
> ...
>
> Seb
> 

--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Sebastien Flaesch]

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: 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 29 Mar 2023, at 21:11, Sebastien Flaesch  wrote:
>
> Oh the use of default keyword is new to me, thanks for that.
>
> But to make PostgreSQL more Informix-compatible, zero should have been 
> considered as well.

…No, I’m not going to be humble about this opinion… Postgres does a sane thing 
here.
It’s Informix that you should be complaining about. Zero is not a sane value to 
specify special behaviour, it could mean zero and be just as valid. By 
consequence, Informix probably forbids zero as a sequence value, but that is an 
artefact (and a limitation) of its implementation, not a feature.

The core of your problem however, is that you’re trying to get 
database-agnostic behaviour by relying on database-specific features. That is 
not going to work, you’ve just been lucky enough to get away with it until now.

There’s really only one realistic answer here: Fix your design.

Regards,
Alban Hertroys
--
There is always an exception to always.

JSON / ASP.NET AJAX Dates support in PostgreSQL

[Sebastien Flaesch]

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: Note the backslash before the slashes!
According to JSON spec this is valid JSON and used by AJAX Date format.


When loading that data in Firefox, the JSON visualizer shows AJAX Date values as

"/Date(167235840)/"

and

"/Date(1672692813062+0100)/"

(without the backslash)

My understanding is that backslash + slash = slash in JSON strings.


How can I load this record into a table defined as:

CREATE TABLE custorder (
"PurchaseOrder" BIGINT NOT NULL PRIMARY KEY,
"CreationDate" TIMESTAMP NOT NULL,
"LastChangeDateTime" TIMESTAMP NOT NULL
)

?


Seb

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

[Sebastien Flaesch]

>From the investigation we did here, I can confirm that the number in AJAX Date 
>is the milliseconds since Epoch (1970-01-01 00:00:00), and it's always in UTC, 
>even if there is a timezone offset provided. I mention this because it's 
>different from ISO 8601 datetimes, where the datetime part is in local time 
>corresponding to the offset, when one is specified (check 
>https://momentjs.com/docs/#/parsing/asp-net-json-date/)

So, is there some built-in JSON API in PostgreSQL to convert an AJAX Date from 
JSON or do I have to do this by hand?

Seb

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 wrote:
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: Note the backslash before the slashes!
According to JSON spec this is valid JSON and used by AJAX Date format.


When loading that data in Firefox, the JSON visualizer shows AJAX Date values as

"/Date(167235840)/"

and

"/Date(1672692813062+0100)/"

(without the backslash)

My understanding is that backslash + slash = slash in JSON strings.

This looks like "milliseconds since the Unix epoch:

$ date -d @1672692813.062
Mon 02 Jan 2023 02:53:33 PM CST

Thus:
select to_timestamp(cast(1672692813062 as bigint))::timestamp;

--
Born in Arizona, moved to Babylonia.

OpenSSL version 3

[Sebastien Flaesch]

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

[Sebastien Flaesch]

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_64 x86_64 x86_64 GNU/Linux

I got an error about libxml2 invalid version.


checking for inflate in -lz... yes
checking for xmlSaveToBuffer in -lxml2... no
configure: error: library 'xml2' (version >= 2.6.23) is required for XML support

But I see a 2.9.7:

[comp@havok postgresql-15.1]$ find /lib64/ -name "libxml2*"
/lib64/libxml2.so.2
/lib64/libxml2.so.2.9.7
/lib64/girepository-1.0/libxml2-2.0.typelib
/lib64/python3.6/site-packages/__pycache__/libxml2.cpython-36.opt-1.pyc
/lib64/python3.6/site-packages/__pycache__/libxml2.cpython-36.pyc
/lib64/python3.6/site-packages/libxml2.py
/lib64/python3.6/site-packages/libxml2mod.so

What am I missing here?

Seb

Re: Build fails on RHEL 8.8 about libxml2 version

[Sebastien Flaesch]

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 not click links or open attachments if you do not recognize the 
sender.

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_64 x86_64 x86_64 GNU/Linux

I got an error about libxml2 invalid version.


checking for inflate in -lz... yes
checking for xmlSaveToBuffer in -lxml2... no
configure: error: library 'xml2' (version >= 2.6.23) is required for XML support

But I see a 2.9.7:

[comp@havok postgresql-15.1]$ find /lib64/ -name "libxml2*"
/lib64/libxml2.so.2
/lib64/libxml2.so.2.9.7
/lib64/girepository-1.0/libxml2-2.0.typelib
/lib64/python3.6/site-packages/__pycache__/libxml2.cpython-36.opt-1.pyc
/lib64/python3.6/site-packages/__pycache__/libxml2.cpython-36.pyc
/lib64/python3.6/site-packages/libxml2.py
/lib64/python3.6/site-packages/libxml2mod.so

What am I missing here?

Seb

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

[Sebastien Flaesch]

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 bell.

I am using PostgreSQL 16.2 or 15.1 (I can try older versions if needed)

Platform is Debian 11

I am using the libpq C API to execute SQL statements, mixing:


  *
PQprepare
  *
PQexecPrepared
  *
PQexecParams
  *
PQexec

When a prepared statement handle is no longer needed, I do execute

   deallocate cu1

(seems there is no C API to de-allocate a prepared statement, right?)

For result sets (SELECT), I do PQprepare / PQexecPrepared of "server cursors" 
with:

  declare cu1 cursor for ...

When a server cursor is no longer needed, I do PQexec(conn, "close curs-name')

The problem occurs when doing a first transaction, where an INSERT fails 
because of a table UNIQUE constraint.

After the rollback, I restart a new TX with begin, several PQexec() of SELECT 
and UPDATE succeed, but on a subsequent PQprepare/PQexecPrepared using a 
"declare cu1 cursor for select ...", I the error "cu1 statement already 
exists"... and I don't see how cu1 can exist.

Before doing the rollback, I try to deallocate the prepared statement with 
deallocate cu1, but this fails with error:

   current transaction is aborted, commands ignored until end of transaction 
block

I understand that the whole TX is aborted with PostgreSQL, and probably the 
deallocate is useless since stmt was prepared inside the TX?

I just want to make sure that doing a deallocate in this context does not 
confuse PostgreSQL.

I have tried to skip the deallocate in case of SQL error, but on the subsequent 
PQprepare/PQexecPrepared, I still get the error that the cu1 statement already 
exists.

Is it an issue if I use the same name for a prepared statement and the server 
cursor? I mean:

   PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
   PQexecPrepared(pgConn, "cu1", ... )

My understanding is that a prepared stmt and server cursor are 2 distinct 
objects.

Anyway: I tried to use distinct names but that did not help.

Note that when the INSERT succeeds, I do not get the error prepared statement 
"cu1" already exists


Any suggestion is welcome!

Seb

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

[Sebastien Flaesch]

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, 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 bell.

I am using PostgreSQL 16.2 or 15.1 (I can try older versions if needed)

Platform is Debian 11

I am using the libpq C API to execute SQL statements, mixing:


  *
PQprepare
  *
PQexecPrepared
  *
PQexecParams
  *
PQexec

When a prepared statement handle is no longer needed, I do execute

   deallocate cu1

(seems there is no C API to de-allocate a prepared statement, right?)

For result sets (SELECT), I do PQprepare / PQexecPrepared of "server cursors" 
with:

  declare cu1 cursor for ...

When a server cursor is no longer needed, I do PQexec(conn, "close curs-name')

The problem occurs when doing a first transaction, where an INSERT fails 
because of a table UNIQUE constraint.

After the rollback, I restart a new TX with begin, several PQexec() of SELECT 
and UPDATE succeed, but on a subsequent PQprepare/PQexecPrepared using a 
"declare cu1 cursor for select ...", I the error "cu1 statement already 
exists"... and I don't see how cu1 can exist.

Before doing the rollback, I try to deallocate the prepared statement with 
deallocate cu1, but this fails with error:

   current transaction is aborted, commands ignored until end of transaction 
block

I understand that the whole TX is aborted with PostgreSQL, and probably the 
deallocate is useless since stmt was prepared inside the TX?

I just want to make sure that doing a deallocate in this context does not 
confuse PostgreSQL.

I have tried to skip the deallocate in case of SQL error, but on the subsequent 
PQprepare/PQexecPrepared, I still get the error that the cu1 statement already 
exists.

Is it an issue if I use the same name for a prepared statement and the server 
cursor? I mean:

   PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
   PQexecPrepared(pgConn, "cu1", ... )

My understanding is that a prepared stmt and server cursor are 2 distinct 
objects.

Anyway: I tried to use distinct names but that did not help.

Note that when the INSERT succeeds, I do not get the error prepared statement 
"cu1" already exists


Any suggestion is welcome!

Seb

2024-04-08 18:23:11.280594  F   33  Query"set 
intervalstyle='iso_8601'"
2024-04-08 18:23:11.280740  B   8   CommandComplete  "SET"
2024-04-08 18:23:11.280743  B   27  ParameterStatus  
"IntervalStyle" "iso_8601"
2024-04-08 18:23:11.280745  B   5   ReadyForQueryI
2024-04-08 18:23:11.280781  F   67  Query"update pg_settings 
set setting=1 where name='lock_timeout'"
2024-04-08 18:23:11.281610  B   35  RowDescription   1 "set_config" 
0 0 25 65535 -1 0
2024-04-08 18:23:11.281620  B   13  DataRow  1 3 '10s'
2024-04-08 18:23:11.281622  B   13  CommandComplete  "UPDATE 0"
2024-04-08 18:23:11.281623  B   5   ReadyForQueryI
2024-04-08 18:23:11.281698  F   10  Query"begin"
2024-04-08 18:23:11.281720  B   10  CommandComplete  "BEGIN"
2024-04-08 18:23:11.281722  B   5   ReadyForQueryT
2024-04-08 18:23:11.281854  F   250 Parse"cu0" "declare srv_cu0 
cursor for select udsi_table_name, udsi_last_mtime, udsi_temp_mtime, 
udsi_can_insert, udsi_can_update, udsi_can_delete, udsi_where_part, 
udsi_position from   dbsync_udsinfo where udsi_user_id = $1 order by 
udsi_position" 1 1043
2024-04-08 18:23:11.281855  F   4   Sync
2024-04-08 18:23:11.282200  B   4   ParseComplete
2024-04-08 18:23:11.282206  B   5   ReadyForQueryT
2024-04-08 18:23:11.282210  F   27  Bind "" "cu0" 1 0 1 4 
'mike' 1 0
2024-04-08 18:23:11.282211  F   6   Describe P ""
2024-04-08 18:23:11.282213  F   9   Execute  "" 0
2024-04-08 18:23:11.282214  F   4   Sync
2024-04-08 18:23:11.282512  B   4   BindComplete
2024-04-08 18:23:11.282517  B   4   NoData
2024-04-08 18:23:11.282519  B   19  CommandComplete  "DECLARE 
CURSOR"
2024-04-08 18:23:11.282520  B   5   ReadyForQueryT
2024-04-08 18:23:11.282523  F   34  Query"fetch absolut

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

[Sebastien Flaesch]

Thanks all for your help.

I have discovered another fact: When surrounding the execution of the failing 
INSERT with a savepoint + rollback to savepoint, the problem disappears: The 
DEALLOCATE commands do then succeed.

See attached trace...

Seb


From: Tom Lane 
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 the whole TX is aborted with PostgreSQL, and probably the 
> deallocate is useless since stmt was prepared inside the TX?

As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back.  This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.

> Is it an issue if I use the same name for a prepared statement and the server 
> cursor? I mean:

>From memory, I think those share the same "portal" namespace.

regards, tom lane
2024-04-08 19:39:22.679893  F   33  Query"set 
intervalstyle='iso_8601'"
2024-04-08 19:39:22.679989  B   8   CommandComplete  "SET"
2024-04-08 19:39:22.679993  B   27  ParameterStatus  
"IntervalStyle" "iso_8601"
2024-04-08 19:39:22.679994  B   5   ReadyForQueryI
2024-04-08 19:39:22.680029  F   67  Query"update pg_settings 
set setting=1 where name='lock_timeout'"
2024-04-08 19:39:22.680939  B   35  RowDescription   1 "set_config" 
0 0 25 65535 -1 0
2024-04-08 19:39:22.680945  B   13  DataRow  1 3 '10s'
2024-04-08 19:39:22.680947  B   13  CommandComplete  "UPDATE 0"
2024-04-08 19:39:22.680948  B   5   ReadyForQueryI
2024-04-08 19:39:22.681035  F   10  Query"begin"
2024-04-08 19:39:22.681150  B   10  CommandComplete  "BEGIN"
2024-04-08 19:39:22.681155  B   5   ReadyForQueryT
2024-04-08 19:39:22.681306  F   250 Parse"cu0" "declare srv_cu0 
cursor for select udsi_table_name, udsi_last_mtime, udsi_temp_mtime, 
udsi_can_insert, udsi_can_update, udsi_can_delete, udsi_where_part, 
udsi_position from   dbsync_udsinfo where udsi_user_id = $1 order by 
udsi_position" 1 1043
2024-04-08 19:39:22.681308  F   4   Sync
2024-04-08 19:39:22.681739  B   4   ParseComplete
2024-04-08 19:39:22.681744  B   5   ReadyForQueryT
2024-04-08 19:39:22.681748  F   27  Bind "" "cu0" 1 0 1 4 
'mike' 1 0
2024-04-08 19:39:22.681750  F   6   Describe P ""
2024-04-08 19:39:22.681751  F   9   Execute  "" 0
2024-04-08 19:39:22.681752  F   4   Sync
2024-04-08 19:39:22.682027  B   4   BindComplete
2024-04-08 19:39:22.682033  B   4   NoData
2024-04-08 19:39:22.682034  B   19  CommandComplete  "DECLARE 
CURSOR"
2024-04-08 19:39:22.682036  B   5   ReadyForQueryT
2024-04-08 19:39:22.682038  F   34  Query"fetch absolute 0 from 
srv_cu0"
2024-04-08 19:39:22.682126  B   276 RowDescription   8 
"udsi_table_name" 25071 2 1043 65535 54 0 "udsi_last_mtime" 25071 8 1114 8 3 0 
"udsi_temp_mtime" 25071 9 1114 8 3 0 "udsi_can_insert" 25071 4 1042 65535 5 0 
"udsi_can_update" 25071 5 1042 65535 5 0 "udsi_can_delete" 25071 6 1042 65535 5 
0 "udsi_where_part" 25071 7 1043 65535 2004 0 "udsi_position" 25071 3 23 4 -1 0
2024-04-08 19:39:22.682131  B   12  CommandComplete  "FETCH 0"
2024-04-08 19:39:22.682132  B   5   ReadyForQueryT
2024-04-08 19:39:22.682175  F   34  Query"fetch forward 50 from 
srv_cu0"
2024-04-08 19:39:22.682277  B   276 RowDescription   8 
"udsi_table_name" 25071 2 1043 65535 54 0 "udsi_last_mtime" 25071 8 1114 8 3 0 
"udsi_temp_mtime" 25071 9 1114 8 3 0 "udsi_can_insert" 25071 4 1042 65535 5 0 
"udsi_can_update" 25071 5 1042 65535 5 0 "udsi_can_delete" 25071 6 1042 65535 5 
0 "udsi_where_part" 25071 7 1043 65535 2004 0 "udsi_position" 25071 3 23 4 -1 0
2

Tracing libpq client: Only with PQtrace()?

[Sebastien Flaesch]

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)

[Sebastien Flaesch]

Tom,

> > Is it an issue if I use the same name for a prepared statement and the 
> > server cursor? I mean:
> From memory, I think those share the same "portal" namespace.

Can you please elaborate?

Is it supported to do:

   PQprepare(pgConn, "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" 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 the whole TX is aborted with PostgreSQL, and probably the 
> deallocate is useless since stmt was prepared inside the TX?

As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back.  This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.

> Is it an issue if I use the same name for a prepared statement and the server 
> cursor? I mean:

>From memory, I think those share the same "portal" namespace.

regards, tom lane

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

[Sebastien Flaesch]

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 do not recognize the 
sender.

On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch 
mailto:sebastien.flae...@4js.com>> wrote:
Is the PQtrace() API the only way to enable libpq client tracing?

I thought about some environment variable of client configuration setting...

That's generally the job of the client, or more specifically, the driver 
providing the interface between your program and the libpq API. Unless you are 
writing at the low level yourself, in which case, you would need to have your 
program detect an env and call PQtrace() as needed.

See for example 
https://metacpan.org/pod/DBD::Pg#parse_trace_flag-and-parse_trace_flags<https://urldefense.com/v3/__https://metacpan.org/pod/DBD::Pg*parse_trace_flag-and-parse_trace_flags__;Iw!!I_DbfM1H!CShCRGSMCXDnZkZhChWyuMkn2offRsd-E0IeowBRQDEXbGIcpIs0rTpnx_2a79rrfeQkcbMbt2QCBlKrmHtlFw$>

Cheers,
Greg

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

[Sebastien Flaesch]

Ok thanks Tom,

My understanding from your last comment:

Since prepared statements and server cursors use different name spaces, it's 
save to use the same name as in

PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )

Seb

From: 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:
>>> Is it an issue if I use the same name for a prepared statement and the 
>>> server cursor? I mean:

>> From memory, I think those share the same "portal" namespace.

> Can you please elaborate?

> Is it supported to do:
>PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
>PQexecPrepared(pgConn, "cu1", ... )
> ?

> So far this has always worked.

Ah, sorry, what I should have said is that the SQL PREPARE command
works with the same namespace as PQprepare (which is a thin wrapper
over the wire-protocol Parse command).  There is a different namespace
for cursors.  Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.

regards, tom lane

SUM() of INTERVAL type produces INTERVAL with no precision

[Sebastien Flaesch]

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 CAST() or :: all over the place.

The type of a SELECT item is important to us because we have generic C code 
that identifies the PQfmod() type, to properly interpret the string value 
returned by PQgetvalue().

With SUM(interval-column), PQfmod() returns -1 ...

Here some SQL sample to illustrate our concern by describing column types:

===

create table tab1 ( pkey int, dur interval hour to second(0));
\d tab1

insert into tab1 values ( 101, interval '999:12:45');
insert into tab1 values ( 102, interval '100:00:00');

create view v1 as select sum(dur) from tab1;
select * from v1;
\d v1

create view v2 as select sum(dur)::interval hour to second(0) from tab1;
select * from v2;
\d v2

drop view v1;
drop view v2;
drop table tab1;

===

Output:

CREATE TABLE
 Table "public.tab1"
 Column |Type| Collation | Nullable | Default
++---+--+-
 pkey   | integer|   |  |
 dur| interval hour to second(0) |   |  |

INSERT 0 1
INSERT 0 1
CREATE VIEW
sum

 1099:12:45
(1 row)

  View "public.v1"
 Column |   Type   | Collation | Nullable | Default
+--+---+--+-
 sum| interval |   |  |

CREATE VIEW
sum

 1099:12:45
(1 row)

   View "public.v2"
 Column |Type| Collation | Nullable | Default
++---+--+-
 sum| interval hour to second(0) |   |  |

DROP VIEW
DROP VIEW
DROP TABLE



Why is PostgreSQL "losing" the original type when doing aggregates like SUM()?

Seems the result can fit into an INTERVAL HOUR TO SECOND(0) since we can cast...

With other database engines like Informix, one defines a dimension for the 
largest interval qualifier:

INTERVAL HOUR(9) TO SECOND  => can store 123456789:00:00 for ex

Is this is handled differently in PostgreSQL, right?

Sorry to ask if this is obvious to experts, but the documentation should be a 
bit more detailed for the interval type and its storage possibilities.
https://www.postgresql.org/docs/14/datatype-datetime.html

Seb

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

[Sebastien Flaesch]

Hi Tom,

I do care because I wrote a database driver/interface that needs to do 
automatic conversions from native PostgreSQL types, to our programming language 
types (we sell a compile/runtime system).

I need introspection of the fetched data. The more exact type info I get, the 
better.

As our software is a programming language runtime system, I don't know in 
advance the SQL statement and column types.

It's all generic code.

We have features/APIs that return the type of a SELECT item, like ODBC's 
SQLDescribeCol() ...

Our programming language supports Informix-style INTERVALs where you have 2 
classes:

INTERVAL year-to-month
INTERVAL day-to-second[.fractions]

It's not easy to explain all the constraints here in a few lines but basically, 
I can better handle the values returned from PQgetvalue(), when I know exactly 
when is the original type from PQftype() and PQfmode()

We could help our customer by using a CAST():

SELECT CAST( SUM(col) AS INTERVAL HOUR TO MINUTE) ...

After more tests:

I have some doubts about the values that can be inserted into a PostgreSQL 
INTERVAL.

Sorry if I am missing something, but it's quite strange to me that one can 
insert various "classes" of interval values:

test1=> create table tab1 ( i interval );
CREATE TABLE

test1=> insert into tab1 values ( interval '12434:12:11' );
INSERT 0 1

test1=> select * from tab1;
  i
-
 12434:12:11
(1 row)

test1=> insert into tab1 values ( interval '9 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
  i
-
 12434:12:11
 9 days 12:11:10
(2 rows)

test1=> insert into tab1 values ( interval '-10 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
  i
-
 12434:12:11
 9 days 12:11:10
  years 10 mons 12:11:10
(3 rows)

test1=> insert into tab1 values ( interval '-10 55 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
  i
-
 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



____
From: Tom Lane 
Sent: Friday, December 3, 2021 4:15 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch  writes:
> When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the 
> resulting type loses the interval type qualifiers...

We don't generally attribute a typmod (which is what interval field
specs are) to the output of any function, other than the ones that are
specifically identified in the catalogs as cast-to-a-particular-typmod
infrastructure.  Why do you care?  The value is the same regardless.

> The type of a SELECT item is important to us because we have generic C code 
> that identifies the PQfmod() type, to properly interpret the string value 
> returned by PQgetvalue().

I think you have vastly overestimated the value of PQfmod.  Moreover,
if you think you need it to interpret the output data, you are mistaken.
There is no situation in which Postgres output formats are
typmod-specific; in fact, the output function API doesn't even pass
the typmod.

regards, tom lane

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

[Sebastien Flaesch]

Hum

test1=> create table tab2 ( i interval hour to minute );
CREATE TABLE
test1=> insert into tab2 values ( interval '-10 555 11:22:33' );
INSERT 0 1
test1=> select * from tab2;
  i
--
  years 10 mons 555 days 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 precision


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Hi Tom,

I do care because I wrote a database driver/interface that needs to do 
automatic conversions from native PostgreSQL types, to our programming language 
types (we sell a compile/runtime system).

I need introspection of the fetched data. The more exact type info I get, the 
better.

As our software is a programming language runtime system, I don't know in 
advance the SQL statement and column types.

It's all generic code.

We have features/APIs that return the type of a SELECT item, like ODBC's 
SQLDescribeCol() ...

Our programming language supports Informix-style INTERVALs where you have 2 
classes:

INTERVAL year-to-month
INTERVAL day-to-second[.fractions]

It's not easy to explain all the constraints here in a few lines but basically, 
I can better handle the values returned from PQgetvalue(), when I know exactly 
when is the original type from PQftype() and PQfmode()

We could help our customer by using a CAST():

SELECT CAST( SUM(col) AS INTERVAL HOUR TO MINUTE) ...

After more tests:

I have some doubts about the values that can be inserted into a PostgreSQL 
INTERVAL.

Sorry if I am missing something, but it's quite strange to me that one can 
insert various "classes" of interval values:

test1=> create table tab1 ( i interval );
CREATE TABLE

test1=> insert into tab1 values ( interval '12434:12:11' );
INSERT 0 1

test1=> select * from tab1;
  i
-
 12434:12:11
(1 row)

test1=> insert into tab1 values ( interval '9 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
  i
-
 12434:12:11
 9 days 12:11:10
(2 rows)

test1=> insert into tab1 values ( interval '-10 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
  i
-
 12434:12:11
 9 days 12:11:10
  years 10 mons 12:11:10
(3 rows)

test1=> insert into tab1 values ( interval '-10 55 12:11:10' );
INSERT 0 1
test1=> select * from tab1;
  i
-
 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




From: Tom Lane 
Sent: Friday, December 3, 2021 4:15 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: SUM() of INTERVAL type produces INTERVAL with no precision

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch  writes:
> When doing a SUM( ) aggregate on an INTERVAL HOUR TO SECOND(0) column, the 
> resulting type loses the interval type qualifiers...

We don't generally attribute a typmod (which is what interval field
specs are) to the output of any function, other than the ones that are
specifically identified in the catalogs as cast-to-a-particular-typmod
infrastructure.  Why do you care?  The value is the same regardless.

> The type of a SELECT item is important to us because we have generic C code 
> that identifies the PQfmod() type, to properly interpret the string value 
> returned by PQgetvalue().

I think you have vastly overestimated the value of PQfmod.  Moreover,
if you think you need it to interpret the output data, you are mistaken.
There is no situation in which Postgres output formats are
typmod-specific; in fact, the output function API doesn't even pass
the typmod.

regards, tom lane

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

[Sebastien Flaesch]

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 if you do not recognize the 
sender.

On 12/3/21 08:12, Sebastien Flaesch wrote:
>
> Hum
>
> test1=> create table tab2 ( i interval hour to minute );
> CREATE TABLE
> test1=> insert into tab2 values ( interval '-10 555 11:22:33' );
> INSERT 0 1
> test1=> select * from tab2;
>i
> --
>    years 10 mons 555 days 11:22:00
> (1 row)
>
> Expected?
>
> Qualifiers "hour to minute" seem just to be some decoration...

https://urldefense.com/v3/__https://www.postgresql.org/docs/current/datatype-datetime.html*DATATYPE-INTERVAL-INPUT__;Iw!!I_DbfM1H!RExSz7qwDTY70m7inqoIFBso3O-e15X0Aza2CJ7tHhCV27fI70fJtnuDTTLb7EIGJcpI$

"... Also, field values “to the right” of the least significant field
allowed by the fields specification are silently discarded. For example,
writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the
seconds field, but not the day field."

>
> Seb
> 



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Sebastien Flaesch]

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 system table pg_type to obtain the names and properties of 
the various data types. The OIDs of the built-in data types are defined in the 
file catalog/pg_type_d.h in the PostgreSQL installation's include directory.

After building PostgreSQL 17.4 from the sources, I cannot find this pg_type_d.h 
header file in the installation directory. Maybe I am missing some configure 
option?

Anyway, I found the file in the sources:

   src/include/catalog/pg_type_d.h

And I can read this comment:

/*
 * Backwards compatibility for ancient random spellings of pg_type OID macros.
 * Don't use these names in new code.
 */
#define CASHOID MONEYOID
#define LSNOID  PG_LSNOID

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
...


I am expecting something like:

#define PG_TYPE_BOOL  16
#define PG_TYPE_BYTEA 17
#define PG_TYPE_CHAR  18
#define PG_TYPE_NAME  19
#define PG_TYPE_INT8  20
#define PG_TYPE_INT2  21
#define PG_TYPE_INT2VECTOR22
...

Seb


From: Tom Lane 
Sent: Saturday, March 15, 2025 8:02 PM
To: M Tarkeshwar Rao 
Cc: pgsql-gene...@postgresql.org 
Subject: Re: After upgrading libpq, the same function(PQftype) call returns a 
different OID

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

M Tarkeshwar Rao  writes:
> We are using PostgreSQL libpq in our application. The code worked fine for 
> the past four years, but after upgrading the library, the function PQftype is 
> returning unexpected values for some columns.
> Specifically, the issue occurs with a column of type timestamp(3) without 
> time zone.

The OID of type timestamp has not changed.  Perhaps you are now
querying some other table.  I'd suggest looking into pg_type to
find out what type is represented by the OID you're now getting,
and then searching pg_attribute to see what's using that.

select typname from pg_type where oid = 123456;

select attrelid::regclass, attname from pg_attribute where atttypid = 123456;

Also, if you really do mean that you changed only libpq and
nothing about the server side, I'd have to guess that you're
connecting to some other database than before.  That would be
surprising, but with zero details it's hard to debug.

regards, tom lane

pgvector as standard PostgreSQL feature?

[Sebastien Flaesch]

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 names, especially "bit" ... why 
not "bitvec"?

Seb

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

[Sebastien Flaesch]

Tom,
They are.  You were already pointed to it. The fact that you don't
like how that file's name is spelled is not really going to
impress anyone.
I don't care about the .h file name or location, what scares me is this:

/*
 * Backwards compatibility for ancient random spellings of pg_type OID macros.
 * Don't use these names in new code.
 */
#define CASHOID MONEYOID
#define LSNOID  PG_LSNOID

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
#define INT2VECTOROID 22
#define INT4OID 23
#define REGPROCOID 24

If I am missing something, then please point me to the correct .h file that 
contains #define constants without this scary comment.

OR ( I guess I start to understand the code... ) it this comment only for:

#define CASHOID MONEYOID
#define LSNOID  PG_LSNOID

???

And sorry if I consider constant names like these (without any prefix such as 
PG_TYPE_)

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
...

... are looking more like names not to be used!

Arrogance does not help here, clarity and better 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 click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch  writes:
> Native PostgreSQL built-in SQL types should be listed in a .h header of the C 
> client API

They are.  You were already pointed to it. The fact that you don't
like how that file's name is spelled is not really going to
impress anyone.

regards, tom lane

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

[Sebastien Flaesch]

David,
That said, I am curious as to the education flow a developer, not linking in 
this specific header to their code, would go through in order to learn about 
type OIDs and make use of them in their project.  Maybe that flow is good, 
maybe not.  It's a rare flow and there are many things to do in the project.  
So my curiosity may not get satiated.  As you brought this up and are invested 
in the outcome you have more motivation than probably anyone else to dive into 
it and make concrete suggestions for change.

Any project using the PostgreSQL C API to implement an interface/module for 
another programming language will need to implement basic SQL handling 
functions to prepare, execute, fetch rows, AND introspect SQL statements parts 
like column information (name, type).

We have for ex a SQL handler class in Genero BDL, providing following methods:

  DEFINE h base.SqlHandle
  LET h = base.SqlHandle.create()
  CALL h.prepare("SELECT ")
  CALL h.open()
  DISPLAY h.getResultType(1) : type name of column #1
  DISPLAY h.getResultType(2) : type name of column #2
  ...


About the PQftype() doc, the header file catalog/pg_type_d.h is mentioned, but 
I think it is missing the fact that type OID constants can be recognized in 
that header file, with the "OID" suffix.

While I understand that it's additional work on doc maintenance, I would in 
fact expect an exhaustive list of built-in data type ids just like in:

https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver16

https://dev.mysql.com/doc/c-api/8.4/en/c-api-prepared-statement-type-codes.html

https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=constants-sql-data-type#ids_esqlc_0123__sii-03-97666

But I can understand that 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 ; 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 click links or open attachments if you do not recognize the 
sender.

On Thu, Mar 20, 2025 at 8:42 AM Sebastien Flaesch 
mailto:sebastien.flae...@4js.com>> wrote:

/*
 * Backwards compatibility for ancient random spellings of pg_type OID macros.
 * Don't use these names in new code.
 */
#define CASHOID MONEYOID
#define LSNOID  PG_LSNOID

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
#define INT2VECTOROID 22
#define INT4OID 23
#define REGPROCOID 24

If I am missing something, then please point me to the correct .h file that 
contains #define constants without this scary comment.

OR ( I guess I start to understand the code... ) it this comment only for:

Yes, that blank line separating LSNOID and BOOLOID blocks the comment from 
applying to the items after the blank line.  That is a fairly common 
convention, using whitespace to break things up.  Also, assigning one macro to 
another is quite distinct from assigning a constant to a name; making the 
"backward compatibility" aspect of this comment only meaningfully apply to 
those two items.


And sorry if I consider constant names like these (without any prefix such as 
PG_TYPE_)

We spelled PG_TYPE_  as OID and put it on the end.  A boolean as an object is 
by definition a type.  Context clues are important, not every pattern gets 
spelled out in prose.


#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21

Arrogance does not help here, clarity and better API doc would.


To my knowledge the current API doc for this hasn't had any comments of this 
sort for a very long time.  All documentation can be improved because every 
reader comes at it from a different starting point.  Do you have a concrete 
suggestion for what you think should be changed, and why?  My take away from 
this thread is that a single report isn't usually enough to go searching hard 
for ways to tweak the documentation for readability; nor has this one pointed 
out any outright errors to be fixed.  In short, we expect that some subset of 
readers will ask us questions on the mailing list because that is the reality 
of things.

That said, I am curious as to the education flow a developer, not linking in 
this specific header to their code, would go through in order to learn about 
type OIDs and make use of them in their project.  Maybe that flow is good, 
maybe not.  It's a rare flow and there are many things to do in the project.  
So my curiosity may not get satiated.  As you brought this up and are invested 
in the outcome you have more motivation than probably anyone else to dive in

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

[Sebastien Flaesch]

Let's not deviate from my request:

I have implemented various DB client modules using the C client APIs, for 
Oracle DB, SQL Server, IBM DB2, MySQL/MariaDB, SQLite and PostgreSQL.

While I like PostgreSQL a lot, this is the only case where I have to define 
myself the column type ids, to implement a "describe column" feature.

ODBC has SQLDescribeCol() / SQLDescribeColW() and predefined SQL_* constants 
like SQL_INTEGER, SQL_VARCHAR ...

Native PostgreSQL built-in SQL types should be listed in a .h header of the C 
client API

I do not want to execute SQL to identify a column type returned by PQftype().
This is not efficient, even if I would cache the mapping of the type oid to a 
type name.

I just want to do this:

static int prepareField(SqlStatement *st, int i)
{
int pgftype = PQftype(st->pgResult, i);
int pgfmod = PQfmod(st->pgResult, i);
...
switch (pgftype) {
case PG_TYPE_BOOL:
...
break;
case PG_TYPE_CHAR:
case PG_TYPE_BPCHAR:
case PG_TYPE_VARCHAR:
...
break;

And today I have to define all these type ids:

/* ! Should be provided by a PostgreSQL header file! */
#define PG_TYPE_BOOL  16
#define PG_TYPE_BYTEA 17
#define PG_TYPE_CHAR  18
#define PG_TYPE_NAME  19
#define PG_TYPE_INT8  20
#define PG_TYPE_INT2  21
#define PG_TYPE_INT2VECTOR22
#define PG_TYPE_INT4  23
#define PG_TYPE_REGPROC   24
#define PG_TYPE_TEXT  25
#define PG_TYPE_OID   26
#define PG_TYPE_TID   27
#define PG_TYPE_XID   28
#define PG_TYPE_CID   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: Sebastien Flaesch ; M Tarkeshwar Rao 
; pgsql-gene...@postgresql.org 

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

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Adrian Klaver  writes:
> On 3/18/25 23:41, Sebastien Flaesch wrote:
>> I was not expecting this file to be in a "server" folder, when it's to
>> be used for client apps.

> Not surprising. As I understand it this is the code used to build the
> type entries in the system catalog pg_type.

More the other way around: pg_type_d.h is built from the authoritative
source files pg_type.h and pg_type.dat, according to the process
described here:

https://urldefense.com/v3/__https://www.postgresql.org/docs/devel/bki.html__;!!I_DbfM1H!GM5pJKRPNVArTRiyYGhyIZrVAgLo7RZl1FSS5kG4IZvWLW75bP4zu1P7yVuLucHd3_FbuKym1-W3Wv0iEs6X$

>> And still, I do not trust the content.

Why not?  If it's the "Backwards compatibility" comment that's
bothering you, a look at pg_type.h will show you that that's
only intended to apply to the CASHOID and LSNOID symbols.
Everything below that in pg_type_d.h is machine-generated.

regards, tom lane

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

[Sebastien Flaesch]

You are right Adrian, I did not search properly I found the header file here:

sf@toro:/opt3/dbs/pgs/17.4$ ls -l include/postgresql/server/catalog/pg_type_d.h
-rw-r--r-- 1 sf sf 9672 Mar 13 17:05 
include/postgresql/server/catalog/pg_type_d.h

I was not expecting this file to be in a "server" 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: After upgrading libpq, the same function(PQftype) call returns a 
different OID

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On 3/16/25 02:30, Sebastien Flaesch wrote:
>
> 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
> <https://urldefense.com/v3/__https://www.postgresql.org/docs/17/libpq-exec.html*LIBPQ-PQFTYPE__;Iw!!I_DbfM1H!BrhvYoUAjZeszdg-ZMusy9M6WcTcHnTVpF22U_PzygM-UxQsQ0oa34TMFyp3Asr6-8L3nJQDSkUR9533wp_t-V3RsEXlNg$
>  > says:
>
> You can query the system table |pg_type| to obtain the names and
> properties of the various data types. *The OIDs of the built-in data
> types are defined in the file |catalog/pg_type_d.h| in the
> PostgreSQL installation's |include| directory.*
>
> After building PostgreSQL *17.4* from the sources, I cannot find this
> pg_type_d.h header file in the installation directory. Maybe I am
> missing some configure option?

Went I built from source in ended up in:

/usr/local/pgsql/include/server/catalog/


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pgvector as standard PostgreSQL feature?

[Sebastien Flaesch]

Go it, makes total sense.

So pgvector etc will probably remain an extension for a while.

Thanks for the note about BIT type.
I have missed that it's a standard built-in type.

Seb

From: Christophe Pettus 
Sent: Wednesday, March 19, 2025 9:19 AM
To: Sebastien 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 types (vector, halfvec, sparsevec, bit) 
> implemented as native built-in data types like json/jsonb ?

(I'm speaking just for myself here.)  I would not base any plans on this 
functionality being available in the PostgreSQL core in the near future (and by 
"near future," I mean the next five years).

1. You list three different extensions with overlapping functionality, and 
that's a good sign that there isn't consensus on what the features that would 
be offered in core should be.

2. Adding a type to the core distribution (or even to contrib/) creates a 
maintenance burden on the core developers, and that's not something assumed 
lightly.  Once a type is in core, it (almost) never can be removed, and the 
more specialized the type and detailed the implementation, the greater the risk 
that the developers who know and care about it won't be available in the 
future.  Search the archives for a discussion of the "money" type for what 
happens when a type added to core starts becoming ill-supported... and "money" 
isn't anywhere near as complex as vector functionality.

3. PostgreSQL is designed to have a rich ecosystem of extensions.  The ability 
to add this kind of functionality in an extension is exactly what distinguishes 
PostgreSQL from many other RDBMS systems.  There's no burning need to add 
functionality like this to core.

It is true that hosted environments take time to adopt new extensions (although 
AWS RDS has supported pgvector for nearly two years now), but that's not in 
itself a reason to move things into core.

> Side note: I have some doubts about these type names, especially "bit" ... 
> why not "bitvec"?

BIT and BIT VARYING are the SQL standard names for these types.

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

[Sebastien Flaesch]

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-gene...@postgresql.org 

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

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Thu, Mar 20, 2025 at 4:43 PM Sebastien Flaesch
 wrote:
> OR ( I guess I start to understand the code... ) it this comment only for:
> #define CASHOID MONEYOID
> #define LSNOID  PG_LSNOID

That's what Tom already replied, yes. --DD