Some advice need after a 20 year gap after Ingres/GUIs

2024-04-10 Thread John Bateson
Good morning,
The end of my working life was taken up with developing and supporting a 
Scientific Research establishment near Warrington in the UK. I had a small team 
of programmer who did an excellent job for me and with me.
The software was Ingres and the main program supporting user administration on 
a Synchrotron was built  under OpenROAD with other developments around the 
on-site stores and finance  using ABF since the stores workers found this much 
faster than a GUI! There was also some web development use .Net practises.
This was a quite complex system in the end with nearly 200 tables.
We ended up using Ingres Replicator with the intention of running it from two 
sites 180 miles apart - at the time it was a pile of notquitegoodenough! This 
was early this century so Actian may have made some improvements since then...

So much for the background to establish that I am not a complete newbie, just 
out of the loop for a while.

Using Postgres and PGAdmin -4.
So, 20 years later I am developing, (unpaid) a new project for some historic 
railways in the UK and linking these to the development and design of a range 
of kits for those interested in model railways. This is getting towards 20 
tables so far.
What I really need is a recommendation for the current and best practice for an 
easy GUI that will allow me to press a button without opening up PGAdmin or a 
black screen, i.e. the traditional .EXE file I can put on a button on the 
screen on the screen on a Windows 11 based system.
While my programming history goes back to MDBS-4 and beyond I know I have some 
catching up to do and while not a complete newbie, need something I can work on 
quickly and intuitively and inexpensively!

So, recommendation and possibly some consensus would be very much appreciated.

And apologies if I have taken up too much of your time or have placed this in 
the wrong forum.

John


mystery with postgresql.auto.conf

2024-04-10 Thread Matthias Apitz


Hello,

I've a Linux development / QA server were three different PostgreSQL
cluster are setup and the corresponding (self built) PostgreSQL
software:

The software is below corresponding directories (always the full tree):

# ls -ld /usr/local/sisis-pap/pgsql-*
drwxr-xr-x 7 bin bin 4096 Mar 21 11:01 /usr/local/sisis-pap/pgsql-13.1
drwxr-xr-x 7 bin bin 4096 Mar 21 11:02 /usr/local/sisis-pap/pgsql-15.1
drwxr-xr-x 7 bin bin 4096 Mar 25 10:54 /usr/local/sisis-pap/pgsql-16.2

The cluster:

# ls -ld /data/pos*
drwxr-xr-x  3 postgres root 4096 May  7  2021 /data/postgresql131
drwxr-xr-x 12 postgres root 4096 Mar 28  2023 /data/postgresql151
drwxr-xr-x  3 postgres postgres 4096 Mar 28 12:32 /data/postgresql162

This is to test our application software for the different Pos versions.

End of March I started to investigate the TDE extension pg_tde within
the 16.2 server. And only this software contains this extension:

# find /usr/local/sisis-pap/pgsql** | grep pg_tde
/usr/local/sisis-pap/pgsql-16.2/lib/pg_tde.so
/usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde--1.0.sql
/usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde.control

Today I wanted to start the 15.1 server and it failed with:

2024-04-10 11:32:32.179 CEST [14017] FATAL:  could not access file "pg_tde": No 
such file or directory
2024-04-10 11:32:32.181 CEST [14017] LOG:  database system is shut down

I investigated the reason and found that the pg_tde extension was
enabled also in the 15.1 server's file postgresql.auto.conf

# ls -l pos*/data/postgresql.auto.conf
-rw--- 1 postgres postgres  88 May  7  2021 
postgresql131/data/postgresql.auto.conf
-rw--- 1 postgres postgres 124 Mar 28 11:35 
postgresql151/data/postgresql.auto.conf
-rw--- 1 postgres postgres 124 Mar 28 12:58 
postgresql162/data/postgresql.auto.conf

# cat postgresql151/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_preload_libraries = 'pg_tde'

How is this possible? I only used in the 16.2 server the SQL commands:

sisis=# CREATE EXTENSION pg_tde;
sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
sisis=# SELECT pg_tde_set_master_key('my-master-key','file');

In the 15.1 server such command would give (correctly) an error, because
the software is not there:

# /usr/local/sisis-pap/pgsql-15.1/bin/psql -Usisis sisis
psql (15.1)
Type "help" for help.

sisis=# CREATE EXTENSION pg_tde;
ERROR:  extension "pg_tde" is not available
DETAIL:  Could not open extension control file 
"/usr/local/sisis-pap/pgsql-15.1/share/extension/pg_tde.control": No such file 
or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is 
running.

How was this option set into the file postgresql151/data/postgresql.auto.conf?
And I did not do this by hand, I wasn't even aware until today that this
file exists at all.

matthias


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub




Re: mystery with postgresql.auto.conf

2024-04-10 Thread Ian Lawrence Barwick
Hi

2024年4月10日(水) 20:10 Matthias Apitz :
(...)
> End of March I started to investigate the TDE extension pg_tde within
> the 16.2 server. And only this software contains this extension:
>
> # find /usr/local/sisis-pap/pgsql** | grep pg_tde
> /usr/local/sisis-pap/pgsql-16.2/lib/pg_tde.so
> /usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde--1.0.sql
> /usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde.control
>
> Today I wanted to start the 15.1 server and it failed with:
>
> 2024-04-10 11:32:32.179 CEST [14017] FATAL:  could not access file "pg_tde": 
> No such file or directory
> 2024-04-10 11:32:32.181 CEST [14017] LOG:  database system is shut down
>
> I investigated the reason and found that the pg_tde extension was
> enabled also in the 15.1 server's file postgresql.auto.conf
>
> # ls -l pos*/data/postgresql.auto.conf
> -rw--- 1 postgres postgres  88 May  7  2021 
> postgresql131/data/postgresql.auto.conf
> -rw--- 1 postgres postgres 124 Mar 28 11:35 
> postgresql151/data/postgresql.auto.conf
> -rw--- 1 postgres postgres 124 Mar 28 12:58 
> postgresql162/data/postgresql.auto.conf
>
> # cat postgresql151/data/postgresql.auto.conf
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> shared_preload_libraries = 'pg_tde'
>
> How is this possible? I only used in the 16.2 server the SQL commands:
>
> sisis=# CREATE EXTENSION pg_tde;
> sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
> sisis=# SELECT pg_tde_set_master_key('my-master-key','file');

The simplest explanation is that you (or someone), when configuring pg_tde,
accidentally executed (as per the instructions [*]):

 ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

in the 15.1 instance, rather than the 16.2 instance. This will have
resulted in the
entry in the 15.1 postgresql.auto.conf.

[*] https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file#installation-steps

Regards

Ian Barwick




Re: mystery with postgresql.auto.conf

2024-04-10 Thread Matthias Apitz
El día miércoles, abril 10, 2024 a las 09:08:56 +0900, Ian Lawrence Barwick 
escribió:

> > # cat postgresql151/data/postgresql.auto.conf
> > # Do not edit this file manually!
> > # It will be overwritten by the ALTER SYSTEM command.
> > shared_preload_libraries = 'pg_tde'
> >
> > How is this possible? I only used in the 16.2 server the SQL commands:
> >
> > sisis=# CREATE EXTENSION pg_tde;
> > sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
> > sisis=# SELECT pg_tde_set_master_key('my-master-key','file');
> 
> The simplest explanation is that you (or someone), when configuring pg_tde,
> accidentally executed (as per the instructions [*]):
> 
>  ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
> 
> in the 15.1 instance, rather than the 16.2 instance. This will have
> resulted in the
> entry in the 15.1 postgresql.auto.conf.

Here are my notes from the testing pg_tde:

Install sisis-pap v73 and create a PostgreSQL 16.2 cluster the
usual way, load a database dump into it (all done on
srap21dxr1.dev.oclc.org)

I followed exactly https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file

psql -Usisis sisis
psql (16.2)
Type "help" for help.

sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

(PostgreSQL restart)
...

The notes have been done by cut&paste into a text file. The psql
was fired up against the 16.2 server as it says above. And we also
have never two servers up at the same time.

Maybe later I did it accidently against the 15.1 server from the psql
history. I just tested it in the 15.1 server: it does not give any
error:

psql -Usisis sisis
psql (15.1)
Type "help" for help.

sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
ALTER SYSTEM
sisis=#

and the file gets modified :-(

Why it does not give an error because the shared lib isn't there?

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub




Re: mystery with postgresql.auto.conf

2024-04-10 Thread Ian Lawrence Barwick
2024年4月10日(水) 21:40 Matthias Apitz :
>
> El día miércoles, abril 10, 2024 a las 09:08:56 +0900, Ian Lawrence Barwick 
> escribió:
>
> > > # cat postgresql151/data/postgresql.auto.conf
> > > # Do not edit this file manually!
> > > # It will be overwritten by the ALTER SYSTEM command.
> > > shared_preload_libraries = 'pg_tde'
> > >
> > > How is this possible? I only used in the 16.2 server the SQL commands:
> > >
> > > sisis=# CREATE EXTENSION pg_tde;
> > > sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
> > > sisis=# SELECT pg_tde_set_master_key('my-master-key','file');
> >
> > The simplest explanation is that you (or someone), when configuring pg_tde,
> > accidentally executed (as per the instructions [*]):
> >
> >  ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
> >
> > in the 15.1 instance, rather than the 16.2 instance. This will have
> > resulted in the
> > entry in the 15.1 postgresql.auto.conf.
>
> Here are my notes from the testing pg_tde:
>
> Install sisis-pap v73 and create a PostgreSQL 16.2 cluster the
> usual way, load a database dump into it (all done on
> srap21dxr1.dev.oclc.org)
>
> I followed exactly https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file
>
> psql -Usisis sisis
> psql (16.2)
> Type "help" for help.
>
> sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
>
> (PostgreSQL restart)
> ...
>
> The notes have been done by cut&paste into a text file. The psql
> was fired up against the 16.2 server as it says above. And we also
> have never two servers up at the same time.
>
> Maybe later I did it accidently against the 15.1 server from the psql
> history. I just tested it in the 15.1 server: it does not give any
> error:
>
> psql -Usisis sisis
> psql (15.1)
> Type "help" for help.
>
> sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
> ALTER SYSTEM
> sisis=#
>
> and the file gets modified :-(
>
> Why it does not give an error because the shared lib isn't there?

ALTER SYSTEM is a way of modifying the PostgreSQL configuration file
via SQL; just as when you modify it manually, changes are not applied
until you actually reload the configuration.

See: https://www.postgresql.org/docs/current/sql-altersystem.html

Regards

Ian Barwick




Re: Some advice need after a 20 year gap after Ingres/GUIs

2024-04-10 Thread Francisco Prado

Hi,

To start I had to find out what MDBS IV is, since it caught my attention 
and I ended up learning a few things about the history of databases ;-).


Regarding your concern:

First you must be clear that there are many alternatives to develop 
graphical interfaces to work with Postgres.


So, first of all:

 * You must choose and master a programming language that you feel
   comfortable with, Python is the one that most developers choose.
 * Use a development environment suitable for the language you have
   chosen, there are several, but the best known is possibly Visual
   Studio Code.
 * Choose which operating system you are going to develop with:
   Windows, Linux, macOS, generally most people choose Windows since
   you can integrate a Linux like Ubuntu using WSL.
 * After you master your favorite programming language, you must select
   a framework that allows you to generate a graphical interface, in
   Python there are several such as Tkinter, wxPython or Qt among others.
 * If the development is via the web, the options are many, the best
   known in Python would be Django for the web and FastAPI for the
   development of APIs via REST
 * You must also have a library that allows you to connect with
   Postgres, in Python there is pyODBC, psycopg2 among others.


Additional resources:

Python Tutorial: 
https://www.youtube.com/watch?v=eWRfhZUzrAc&list=PLWKjhJtqVAbnqBxcdjVGgT3uVR10bzTEB
Python & Tkinter: 
https://www.youtube.com/watch?v=yQSEXcf6s2I&list=PLCC34OHNcOtoC6GglhF3ncJ5rLwQrLGnV

Python & Postgres: https://www.youtube.com/watch?v=miEFm1CyjfM


Greetings and luck!

El 10/04/2024 a las 06:11, John Bateson escribió:


Good morning,

The end of my working life was taken up with developing and supporting 
a Scientific Research establishment near Warrington in the UK. I had a 
small team of programmer who did an excellent job for me and with me.


The software was Ingres and the main program supporting user 
administration on a Synchrotron was built  under OpenROAD with other 
developments around the on-site stores and finance  using ABF since 
the stores workers found this much faster than a GUI! There was also 
some web development use .Net practises.


This was a quite complex system in the end with nearly 200 tables.

We ended up using Ingres Replicator with the intention of running it 
from two sites 180 miles apart – at the time it was a pile of 
*notquitegoodenough*! This was early this century so Actian may have 
made some improvements since then…


So much for the background to establish that I am not a complete 
newbie, just out of the loop for a while.


*Using Postgres and PGAdmin -4*.

So, 20 years later I am developing, (unpaid) a new project for some 
historic railways in the UK and linking these to the development and 
design of a range of kits for those interested in model railways. This 
is getting towards 20 tables so far.


What I really need is a recommendation for the current and best 
practice for an easy GUI that will allow me to press a button without 
opening up PGAdmin or a black screen, i.e. the traditional .EXE file I 
can put on a button on the screen on the screen on a Windows 11 based 
system.


While my programming history goes back to MDBS-4 and beyond I know I 
have some catching up to do and while not a complete newbie, need 
something I can work on quickly and intuitively and inexpensively!


So, recommendation and possibly some consensus would be very much 
appreciated.


And apologies if I have taken up too much of your time or have placed 
this in the wrong forum.


John


--
Saludos,

Francisco Prado


Re: mystery with postgresql.auto.conf

2024-04-10 Thread Tom Lane
Ian Lawrence Barwick  writes:
> 2024年4月10日(水) 21:40 Matthias Apitz :
>> Why it does not give an error because the shared lib isn't there?

> ALTER SYSTEM is a way of modifying the PostgreSQL configuration file
> via SQL; just as when you modify it manually, changes are not applied
> until you actually reload the configuration.
> See: https://www.postgresql.org/docs/current/sql-altersystem.html

Even if you had issued a reload, you would not have noticed the
faulty setting without looking into the postmaster's log for
warning messages.  The system wouldn't get in your face about it
until you did a postmaster restart.

regards, tom lane




Re: mystery with postgresql.auto.conf

2024-04-10 Thread Laurenz Albe
On Wed, 2024-04-10 at 10:26 -0400, Tom Lane wrote:
> Ian Lawrence Barwick  writes:
> > 2024年4月10日(水) 21:40 Matthias Apitz :
> > > Why it does not give an error because the shared lib isn't there?
> 
> > ALTER SYSTEM is a way of modifying the PostgreSQL configuration file
> > via SQL; just as when you modify it manually, changes are not applied
> > until you actually reload the configuration.
> > See: https://www.postgresql.org/docs/current/sql-altersystem.html
> 
> Even if you had issued a reload, you would not have noticed the
> faulty setting without looking into the postmaster's log for
> warning messages.  The system wouldn't get in your face about it
> until you did a postmaster restart.

An alternative to looking at the log file is to

  SELECT * FROM pg_file_settings WHERE error IS NOT NULL;

after you reload.

Yours,
Laurenz Albe




Re: Some advice need after a 20 year gap after Ingres/GUIs

2024-04-10 Thread Dominique Devienne
On Wed, Apr 10, 2024 at 12:11 PM John Bateson 
wrote:

> *Using Postgres and PGAdmin -4*.
>
> [...]. This is getting towards 20 tables so far.
>
> [...] i.e. the traditional .EXE file I can put on a button on the screen
> on a Windows 11 based system.
>

John,

Are you aware of SQLite or DuckDB?
Unlike PostgreSQL, which is an (excellent) client-server RDBMS,
the former two are "embedded" disk-based databases, which are quite capable.
"Deployment" is trivial. And their SQL engine are more sophisticated than
most people realize.
Not that I want to turn you away from PostgreSQL, but they do have
advantages, FWIW.
Having DBs be a single file on disk easily shared is quite convenient.

The reason I mention it here, is that SQLite for example has tons of
ready-made GUIs,
with some of the Windows one being just 1 .exe indeed. Most allow to view
the tables
in "Grid UI controls", some allow modifications too. They are not MS Access
like, in the
sense that they don't allow writing custom UIs on top of the DB, just view
and edit DB
tables. But maybe that's enough for your hobby project?

My $0.02. --DD


Re: Some advice need after a 20 year gap after Ingres/GUIs

2024-04-10 Thread Adrian Klaver

On 4/10/24 03:11, John Bateson wrote:

Good morning,

The end of my working life was taken up with developing and supporting a 
Scientific Research establishment near Warrington in the UK. I had a 
small team of programmer who did an excellent job for me and with me.


The software was Ingres and the main program supporting user 
administration on a Synchrotron was built  under OpenROAD with other 
developments around the on-site stores and finance  using ABF since the 
stores workers found this much faster than a GUI! There was also some 
web development use .Net practises.


This was a quite complex system in the end with nearly 200 tables.

We ended up using Ingres Replicator with the intention of running it 
from two sites 180 miles apart – at the time it was a pile of 
*notquitegoodenough*! This was early this century so Actian may have 
made some improvements since then…


So much for the background to establish that I am not a complete newbie, 
just out of the loop for a while.


*Using Postgres and PGAdmin -4*.

So, 20 years later I am developing, (unpaid) a new project for some 
historic railways in the UK and linking these to the development and 
design of a range of kits for those interested in model railways. This 
is getting towards 20 tables so far.


What I really need is a recommendation for the current and best practice 
for an easy GUI that will allow me to press a button without opening up 
PGAdmin or a black screen, i.e. the traditional .EXE file I can put on a 
button on the screen on the screen on a Windows 11 based system.


With Postgres or other similar client-server database there will be a 
need to set up the database server somewhere first, before you get to 
the GUI interface part.


Are you planning on a single instance of Postgres that folks log in to 
from multiple locations?


Or do want a stand alone setup that each user has on their machine?



While my programming history goes back to MDBS-4 and beyond I know I 
have some catching up to do and while not a complete newbie, need 
something I can work on quickly and intuitively and inexpensively!


So, recommendation and possibly some consensus would be very much 
appreciated.


And apologies if I have taken up too much of your time or have placed 
this in the wrong forum.


John



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





Re: mystery with postgresql.auto.conf

2024-04-10 Thread Greg Sabino Mullane
On Wed, Apr 10, 2024 at 8:40 AM Matthias Apitz  wrote:

> Maybe later I did it accidently against the 15.1 server from the psql
> history.


Yes, as shown by the timestamps from your first post:

-rw--- 1 postgres postgres 124 Mar 28 11:35
> postgresql151/data/postgresql.auto.conf
>

This is also a good reason to set your log_statement to 'ddl', which will
put the ALTER SYSTEM change into your database logs.

Cheers,
Greg


Re: Failure of postgres_fdw because of TimeZone setting

2024-04-10 Thread Adnan Dautovic

Hi,

On 05. Apr 2024, at 16:13, Tom Lane  wrote:

Adnan Dautovic  writes:

SELECT * FROM pg_timezone_names ORDER BY name;



"name""abbrev"  "utc_offset"  "is_dst"
"Turkey"  "+03" "03:00:00"false
"UCT" "UCT" "00:00:00"false
"Universal"   "UTC" "00:00:00"false
"W-SU""MSK" "03:00:00"false


Wow.  To clarify, is that the *whole* result?


I apologize for the confusion, this is an excerpt where I cut out
everything before "Turkey" and after "W-SU". Between those, the
output is complete.


Out of curiosity, does
SET timezone to 'GMT';
work?


Yes, it yields:

SET

Query returned successfully in 84 msec.


The corresponding excerpt from pg_timezone_names is:

"name""abbrev"  "utc_offset"  "is_dst"
[snip]
"Europe/Zurich"   "CEST""02:00:00"true
"GB-Eire" "BST" "01:00:00"true
"Greenwich"   "GMT" "00:00:00"false
"HST" "HST" "-10:00:00"   false
"Hongkong""HKT" "08:00:00"false
"Iceland" "GMT" "00:00:00"false
[snip]


By the way, the row count of pg_timezone_names is 385, but I do
not know how that compares to a more standard installation.

Kind regards,

Adnan Dautovic






Re: Failure of postgres_fdw because of TimeZone setting

2024-04-10 Thread Adnan Dautovic

Dear Adrian,

On 05. Apr 2024, at 17:05, Adrian Klaver  wrote:

The below is cut down from the actual output as there should be at least:

Europe/Berlin  CEST  02:00:00 t

present also?


Correct! That entry also exists. I only included the snippet
where I would have expected the "UTC" entry to be.

1) For the long term contact whomever is in charge of the remote 
server and ask them what they have done with the timezones, why and 
can they fix it?


This will probably be long term indeed. But I am curious and want
to see if I can get some information from the responsible
person(s).


2) In short term per the link from your first post and with no guarantees:

https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677

In the source code change

do_sql_command(conn, "SET timezone = 'UTC'");

to

do_sql_command(conn, "SET timezone = 'Universal'");

As from the link: "Set remote timezone; this is basically just cosmetic"

Then recompile the extension.


Thank you, I got around to trying this route and it worked! Now I
just have to tinker around a bit to see how I can best include
the modified extension into the Docker image, but that is a task
I can grapple with outside of this mailing list. :-)

I am happy to have learned a few things and thank you for your
help tom and Adrian.

Kind regards,

Adnan Dautovic






Re: Failure of postgres_fdw because of TimeZone setting

2024-04-10 Thread Adrian Klaver

On 4/10/24 12:38, Adnan Dautovic wrote:

Hi,

On 05. Apr 2024, at 16:13, Tom Lane  wrote:

Adnan Dautovic  writes:



By the way, the row count of pg_timezone_names is 385, but I do
not know how that compares to a more standard installation.


On my instance of Postgres 16.2, 1196.



Kind regards,

Adnan Dautovic




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





Re: Failure of postgres_fdw because of TimeZone setting

2024-04-10 Thread Tom Lane
Adnan Dautovic  writes:
> On 05. Apr 2024, at 16:13, Tom Lane  wrote:
>> Out of curiosity, does
>> SET timezone to 'GMT';
>> work?

> Yes, it yields:
>> SET
>> 
>> Query returned successfully in 84 msec.

I expected that, because the name "GMT" is hard-wired in our code.
Doesn't help for postgres_fdw though, because it has "UTC" hardwired.
(I have a todo item to rationalize that...)

> By the way, the row count of pg_timezone_names is 385, but I do
> not know how that compares to a more standard installation.

Using current PG HEAD (with tzdata release 2024a):

=# select count(*) from pg_timezone_names;
 count 
---
   597
(1 row)

I can believe older tzdata releases varying from that a little,
but they haven't exactly been adding zone names at a rapid clip.
Either the one you're dealing with is VERY old or it lost some
files sometime.

regards, tom lane




Re: Failure of postgres_fdw because of TimeZone setting

2024-04-10 Thread Tom Lane
Adrian Klaver  writes:
> On 4/10/24 12:38, Adnan Dautovic wrote:
>> By the way, the row count of pg_timezone_names is 385, but I do
>> not know how that compares to a more standard installation.

> On my instance of Postgres 16.2, 1196.

You're probably using a build with --with-system-tzdata pointing
at a system tzdata tree that includes leap-second-aware zones.
These tend to have duplicative entries like "America/New_York"
and "posix/America/New_York".  (There's also a subtree like
"right/America/New_York", but we reject those because we don't
do leap seconds.)  The real number of distinct zones in a
standard tzdata file set these days is a shade under 600.

regards, tom lane




Two server instances on one server

2024-04-10 Thread Andreas Wagner
Hello everyone,

I consider using two instances of the PG server on one node. The node
has two processors and each processor has 64GB memory assigned to it.
I'd like to
* restrict instance one to processor 0 and its memory and
* instance two to processor 1 and its memory
using numactl (or similar commands regarding cpuset) and partitioning.
I expect >30% speedup on some queries, because collecting data can be
done in parallel.
Some background information on the system: It's a regular 2 Xeon (each
4x2 cores due to hyperthreading) system with a lot of memory. Memory
assigned to the processor can be accessed quickly, memory assigned to
the OTHER processor is slow.
Does anyone here know a way to accomplish that? I think openstreetmap
map of germany (4GB) is a nice opportunity to test performance.

Looking forward to constructive answers
Andreas Wagner




Re: Two server instances on one server

2024-04-10 Thread Tom Lane
Andreas Wagner  writes:
> I consider using two instances of the PG server on one node. The node
> has two processors and each processor has 64GB memory assigned to it.
> I'd like to
> * restrict instance one to processor 0 and its memory and
> * instance two to processor 1 and its memory
> using numactl (or similar commands regarding cpuset) and partitioning.

Seems straightforward enough to me, as long as you put the instances
onto distinct port numbers.  Are you encountering problems?

regards, tom lane




(When) can a single SQL statement return multiple result sets?

2024-04-10 Thread Jan Behrens
Hello,

While writing a PostgreSQL client library for Lua supporting
Pipelining (using PQsendQueryParams), I have been wondering if there
are any single SQL commands that return multiple result sets. It is
indeed possible to create such a case by using the RULE system:

db=> CREATE VIEW magic AS SELECT;
CREATE VIEW
db=> CREATE RULE r1 AS ON DELETE TO magic
db-> DO INSTEAD SELECT 42 AS "answer";
CREATE RULE
db=> CREATE RULE r2 AS ON DELETE TO magic
db-> DO ALSO SELECT 'Hello' AS "col1", 'World!' AS "col2";
CREATE RULE
db=> DELETE FROM magic; -- single SQL statement!
 answer 

 42
(1 row)

 col1  |  col2  
---+
 Hello | World!
(1 row)

DELETE 0

Here, "DELETE FROM magic" returns multiple result sets, even though it
is only a single SQL statement.

(Note that this isn't possible with rules ON SELECT because it is only
allowed to create a single SELECT rule on a view.)

The case outlined above seems to be a somewhat special case. I haven't
found any other way to return multiple results (other than sending
several semicolon-separated statements, which is not supported by
PQsendQueryParams). So is there any (other) case where I reasonably
should expect several result sets returned by PQgetResult (before
PQgetResult returns NULL)? Wouldn't it make sense to disallow such
behavior altogether? And if not, why can't I write a stored procedure
or function that returns multiple result sets?

These questions are relevant to me because it may have an effect on the
API design if a statement can return several result sets.

Kind regards,
Jan Behrens




Re: Failure of postgres_fdw because of TimeZone setting

2024-04-10 Thread Adrian Klaver




On 4/10/24 1:31 PM, Tom Lane wrote:

Adrian Klaver  writes:

On 4/10/24 12:38, Adnan Dautovic wrote:

By the way, the row count of pg_timezone_names is 385, but I do
not know how that compares to a more standard installation.



On my instance of Postgres 16.2, 1196.


You're probably using a build with --with-system-tzdata pointing
at a system tzdata tree that includes leap-second-aware zones.
These tend to have duplicative entries like "America/New_York"
and "posix/America/New_York".  (There's also a subtree like
"right/America/New_York", but we reject those because we don't
do leap seconds.)  The real number of distinct zones in a
standard tzdata file set these days is a shade under 600.


It's the PGDG package running on Ubuntu 22.04.



regards, tom lane


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




Re: (When) can a single SQL statement return multiple result sets?

2024-04-10 Thread Merlin Moncure
On Wed, Apr 10, 2024 at 4:22 PM Jan Behrens  wrote:

> Hello,
>
> While writing a PostgreSQL client library for Lua supporting
> Pipelining (using PQsendQueryParams), I have been wondering if there
> are any single SQL commands that return multiple result sets. It is
> indeed possible to create such a case by using the RULE system:
>
> db=> CREATE VIEW magic AS SELECT;
> CREATE VIEW
> db=> CREATE RULE r1 AS ON DELETE TO magic
> db-> DO INSTEAD SELECT 42 AS "answer";
> CREATE RULE
> db=> CREATE RULE r2 AS ON DELETE TO magic
> db-> DO ALSO SELECT 'Hello' AS "col1", 'World!' AS "col2";
> CREATE RULE
> db=> DELETE FROM magic; -- single SQL statement!
>  answer
> 
>  42
> (1 row)
>
>  col1  |  col2
> ---+
>  Hello | World!
> (1 row)
>
> DELETE 0
>
> Here, "DELETE FROM magic" returns multiple result sets, even though it
> is only a single SQL statement.
>


 I guess you should have named your table, "sorcery", because that's
what this is.  In the corporate world, we might regard the 'CREATE RULE'
feature as a 'solution opportunity'  :-).  You might be able to overlook
this on your end IMO as the view triggers feature has standardized and
fixed the feature.

> why can't I write a stored procedure or function that returns multiple
result sets?

Functions arguably should not be able to do this, doesn't the standard
allow for procedures (top level statements invoked with CALL) to return
multiple results?

merlin


Re: (When) can a single SQL statement return multiple result sets?

2024-04-10 Thread Tom Lane
Jan Behrens  writes:
> While writing a PostgreSQL client library for Lua supporting
> Pipelining (using PQsendQueryParams), I have been wondering if there
> are any single SQL commands that return multiple result sets.

Right now, I don't think so.  I believe the current protocol design
intends to support that, and I think this may trace back to some
ancient idea at Berkeley that if you select from an inheritance
hierarchy where the child tables aren't all alike, you should be
able to see all the child data, which'd require changing tuple
descriptors midstream.  But our current interpretation of SQL
SELECT forbids that.

> Here, "DELETE FROM magic" returns multiple result sets, even though it
> is only a single SQL statement.

Right, so it's kind of a case that you have to support.  We're not
likely to rip out rules anytime soon, even if they're a bit
deprecated.

> The case outlined above seems to be a somewhat special case. I haven't
> found any other way to return multiple results (other than sending
> several semicolon-separated statements, which is not supported by
> PQsendQueryParams). So is there any (other) case where I reasonably
> should expect several result sets returned by PQgetResult (before
> PQgetResult returns NULL)? Wouldn't it make sense to disallow such
> behavior altogether?

No.  For one thing, there's too much overlap between what you're
suggesting and pipelined queries.

> And if not, why can't I write a stored procedure
> or function that returns multiple result sets?

[ shrug... ] Lack of round tuits, perhaps.  We don't have any
mechanism today whereby a stored procedure could say "please ship
this resultset off to the client, but I want to continue afterwards".
But you can do that in other RDBMSes and probably somebody will be
motivated to make it possible in Postgres.

regards, tom lane




Re: (When) can a single SQL statement return multiple result sets?

2024-04-10 Thread Thomas Kellerer
Tom Lane schrieb am 11.04.2024 um 01:02:
> Jan Behrens  writes:
>> While writing a PostgreSQL client library for Lua supporting
>> Pipelining (using PQsendQueryParams), I have been wondering if there
>> are any single SQL commands that return multiple result sets.
>
> Right now, I don't think so.

Hmm, what about functions returning multiple refcursors?

From a client library point of view, I think that would qualify as
"multiple result sets"