Some advice need after a 20 year gap after Ingres/GUIs
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
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
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
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年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
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
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
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
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
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
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
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
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
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
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
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
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
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?
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
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?
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?
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?
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"
