Re: DELETING then INSERTING record with same PK in the same TRANSACTION

[Andrew Hardy]

As requested here is a full script and the issue does not arise.

My sincere apologies it appears the issue is most likely with my client
programming code.
My first ever post here.  I shall definitely be providing a full working
SQL script from the very start on any future post.
Many thanks and apologies again.

CREATE TABLE public.itineraryx (
dk varchar(10) NULL,
"locator" varchar(20) NOT NULL,
CONSTRAINT itinerary_pkeyx PRIMARY KEY (locator)
);

CREATE TABLE public.hotel_bookingx (
itinerary varchar(20) NULL,
"hotelName" varchar(50) NULL,
id bigserial NOT NULL,
CONSTRAINT hotel_booking_pkeyx PRIMARY KEY (id)
);

ALTER TABLE public.hotel_bookingx ADD CONSTRAINT
hotel_booking_itinerary_foreignx FOREIGN KEY (itinerary) REFERENCES
itineraryx(locator) ON DELETE CASCADE;

INSERT INTO public.itineraryx
(dk, "locator")
VALUES('tba', 'TEST0001');

INSERT INTO public.hotel_bookingx
(itinerary, "hotelName")
VALUES('TEST0001', 'Crown');


BEGIN TRANSACTION;
delete from itineraryx i where i."locator" = 'TEST0001';

INSERT INTO public.itineraryx
(dk, "locator")
VALUES('tba2', 'TEST0001');

INSERT INTO public.hotel_bookingx
(itinerary, "hotelName")
VALUES('TEST0001', 'Crown2');

COMMIT TRANSACTION;


On Wed, 9 Feb 2022 at 20:01, Andrew Hardy  wrote:

> Really appreciate the input thank you.
>
> I shall try to provide a full script, I guess if the cascades are relevant
> in going to have to also provide all of the indirect table creations and
> all of the earlier inserts to those various tables.
>
> At the very least I'll try to provide a fuller error message and a minimum
> structure that will cause it to occur.
>
> Andrew
>
>
> On Wed, 9 Feb 2022, 19:26 David G. Johnston, 
> wrote:
>
>> On Wed, Feb 9, 2022 at 11:12 AM Andrew Hardy 
>> wrote:
>>
>>> Do I need some particular kind of settings on my transaction to be able
>>> to delete and insert afresh in the same transaction?
>>>
>>
>> No.  I cannot reproduce your claim with a trivial example on stock 13.5.
>>
>> You will need to be less vague and at least provide an exact reproducer
>> script.
>>
>> In case it is relevant - the first delete will lead to cascaded deletes
>>> on children.
>>>
>>
>> This may indeed be relevant.  Again, you need to provide an exact
>> reproducer, not expect others to reverse-engineer one for you.
>>
>> David J.
>>
>>

Re: Can we go beyond the standard to make Postgres radically better?

[Karsten Hilbert]

Am Wed, Feb 09, 2022 at 09:14:39PM -0800 schrieb Guyren Howe:

> There are huge developer benefits available to focusing
> more on making a great relational programming environment,
> well outside the SQL standard.

There's a seemingly small but conceptually rather significant
difference between going _beyond_ a standard and being _well
outside_ said standard.

Which do you propose ?

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Proposed German Translation of Code of Conduct Policy

[Umair Shahid]

Hi Karsten

On Tue, Jan 25, 2022 at 4:48 AM Karsten Hilbert 
wrote:

> Ein Vorschlag:
>
> > Inklusivität und angemessenes Verhalten
> >
> > Das PostgreSQL-Projekt steht jedem offen, der Interesse an
> > der Arbeit mit PostgreSQL hat, unabhängig von seinem
> > Erfahrungsstand mit der Software oder mit Technologie im
> > Allgemeinen. Wir fördern die Entwicklung und Beiträge aller
> > Benutzer, unabhängig ihres Hintergrunds.
>
> Entweder "unabhängig von ihrem" oder besser "ungeachtet ihres".
>
> > Im Falle einer Beschwerde von oder gegen ein Mitglied des
> > CoC-Komitee oder des Core-Teams erfolgt die
> > Beschwerdebearbeitung wie gewohnt, mit der Ausnahme, dass die
> > Person, die in die Beschwerde involviert ist, in ihrer
> > Funktion im Komitee oder dem Core-Team vom Verfahren
> > ausgeschlossen wird.
>
> What happens if all members of the committee are impeached at once ?
>

That is one reason to strive for diversity in the CoC Committee - the
chances of this happening are reduced to near-zero.

In the unlikely event that it does happen, however, one option could be for
the Core team to ask for volunteers from the committers group (senior &
respected members of the community) to pitch in.

If you feel this point needs to be addressed in the policy, I would
recommend starting off a new thread with your suggested amendments to the
CoC policy.


>
> > Die Komitee kann feststellen, dass ein Verstoß gegen diese
>
> "*Das* Komitee"
>

Thank you for your suggestions. These have been incorporated in the text -
updated markdown file is attached.


>
> Regards,
> Karsten Hilbert
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
*Zuletzt aktualisiert: 18. August 2020. [Änderungsverlauf anzeigen](https://git.postgresql.org/gitweb/?p=pgweb.git;a=history;f=templates/pages/about/policies/coc/de.html).*
##Einführung

Das PostgreSQL-Projekt ist stolz auf die Qualität unseres Codes und unserer Arbeit sowie auf
die technischen und professionellen Leistungen unserer Community. Wir erwarten von jedem
Mitglied der Community, dass es sich professionell verhält, mit Höflichkeit und im gemeinsamen
Interesse mit Respekt gegenüber allen Benutzern und Entwicklern handelt.

Zu diesem Zweck haben wir für die Interaktion mit und innerhalb der Gemeinschaft, die
Beteiligung an der Arbeit des Projekts und die Gemeinschaft insgesamt diesen Verhaltenskodex
erstellt. Dieser Kodex soll alle Interaktionen zwischen Community-Mitgliedern abdecken,
unabhängig davon, ob sie innerhalb oder außerhalb der postgresql.org-Infrastruktur stattfinden,
solange kein anderer Verhaltenskodex (wie z.B. der Verhaltenskodex einer Konferenz) Vorrang
hat.

##Inklusivität und angemessenes Verhalten

Das PostgreSQL-Projekt steht jedem offen, der Interesse an der Arbeit mit PostgreSQL hat,
unabhängig von seinem Erfahrungsstand mit der Software oder mit Technologie im
Allgemeinen. Wir fördern die Entwicklung und Beiträge aller Benutzer, ungeachtet ihres
Hintergrunds.

Wir fördern eine durchdachte und konstruktive Diskussion über die Software und diese
Community, ihren aktuellen Stand und mögliche Entwicklungsrichtungen. Der Schwerpunkt
unserer Diskussionen sollte der Code und die damit verbundene Technologie,
Community-Projekte sowie die Infrastruktur sein.

Persönliche Angriffe und negative Kommentare über persönliche Eigenschaften sind
inakzeptabel und nicht gestattet. Beispiele für persönliche Eigenschaften sind unter anderem
Alter, Herkunft oder Abstammung, Religion, Geschlecht und sexuelle Orientierung.

Weitere Verhaltensweisen, die ebenfalls gegen diesen Verhaltenskodex verstoßen, sind unter
anderem die Androhung von Gewalt gegen eine Einzelperson oder eine Gruppe, Androhung
von Berufs-, Gemeinschafts- oder Projektsabotage, jegliche Form von unerwünschter sexueller
Annäherung, Verhaltensweisen, die das PostgreSQL-Projekt in Verruf bringen können, sowie
die Weigerung, unangemessenes Verhalten einzustellen, wenn dazu aufgefordert wird.

##Vergeltung

Es ist ebenfalls ausdrücklich verboten, gegenüber einer Person, die eine Beschwerde nach
diesem Verhaltenskodex einreicht oder bei der Untersuchung einer solchen Beschwerde hilft,


Vergeltungsmaßnahmen zu verüben. Vergeltungsmaßnahmen können unter anderem folgende
Formen annehmen:

* weitere persönliche Angriffe (öffentlich oder privat);
* Handlungen, die den beruflichen Status einer Person und/oder ihren Status am Arbeitsplatz, bei Kunden oder in der Community untergraben;
* Handlungen, die die Privatsphäre, die körperliche Unversehrtheit, das Wohlbefinden, das Zuhause und/oder die Familie der betroffenen Person bedrohen. 

Vergeltungsmaßnahmen werden genauso behandelt wie jeder andere Verstoß gegen diesen Verhaltenskodex.

##Code of Conduct Committee

Das Core-Team ernennt ein Komitee für den Verhaltenskodex, welches alle Beschwerden
entgegennimmt und untersucht, sowie eine(n) Vorsitzende(n) für dieses Komitee. Jedes Mitglied
der Community kann sich freiwillig als Mitglied des Komitees melden, mit A

Re: Proposed German Translation of Code of Conduct Policy

[Karsten Hilbert]

Am Thu, Feb 10, 2022 at 03:24:54PM +0500 schrieb Umair Shahid:

> > What happens if all members of the committee are impeached at once ?
> >
>
> That is one reason to strive for diversity in the CoC Committee - the
> chances of this happening are reduced to near-zero.

It may be near-zero for impeachment based on sound reasons
but it may still happen malevolently. However, not all cases
can, or need, be accounted for explicitely.

> In the unlikely event that it does happen, however, one option could be for
> the Core team to ask for volunteers from the committers group (senior &
> respected members of the community) to pitch in.
>
> If you feel this point needs to be addressed in the policy, I would
> recommend starting off a new thread with your suggested amendments to the
> CoC policy.

Perhaps such might be contained in an ancillary document
"Suggestions for execution of the Policy" containing such
advice as you gave above. Other than that one might leave
things as they are.

> Thank you for your suggestions. These have been incorporated in the text

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Invalid operation order while producing DB dump

[Sergey Belyashov]

Hi,
I have tried to upgrade my cluster from version 13 to 14 using the
command: "pg_upgradecluster -m links --no-start 13 main". But upgrade
failed with messages:
-
pg_restore: creating INDEX "public.closed_sessions_closed_id_idx"
pg_restore: while processing header:
pg_restore: from header entry 3195; 1259 29484835 INDEX
closed_sessions_closed_id_idx postgres
pg_restore: error: could not execute query: ERROR:  cannot use invalid
index "closed_sessions_closed_id_idx" as replica identity
While executing command:
-- For binary upgrade, must preserve pg_class oids
SELECT 
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('29484835'::pg_catalog.oid);

CREATE UNIQUE INDEX "closed_sessions_closed_id_idx" ON ONLY
"public"."closed_sessions" USING "btree" ("closed", "id");

ALTER TABLE ONLY "public"."closed_sessions" REPLICA IDENTITY USING
INDEX "closed_sessions_closed_id_idx";
---
closed_sessions is partitioned table and column closed is used for
replica identity index.
Cluster version 13 works fine...

I think pg_dump generates invalid order of SQL commands causing it to
create a replica identity index for partition tables without connected
partitions. Also it is possible that there is an error in the very
strict checking for invalid index.

Sergey Belyashov

Re: Network Card Not Listening at Startup

[Ludwig Isaac Lim]

> On Thursday, February 10, 2022, 01:49:10 AM GMT+8, Tom Lane 
>  wrote: 




>> Ludwig Isaac Lim  writes:

>> How do make sure that NIC will be listening every time I started/restarted 
>> the server?


> You need to tell systemd not to start the postgresql service
> until the network is up.

> In basic cases, you can edit the postgresql.service file,
> or maybe better create an add-on config file something like this:

> $ cat /etc/systemd/system/postgresql.service.d/delay_start.conf 
> [Unit]
> After=network-online.target
> Wants=network-online.target

> (The add-on file name doesn't matter, the directory name does.)

> I'm not certain that this will wait for custom NICs if you are
> doing something nonstandard to configure those, but for me
> it works for alternate IPs configured in
> /etc/sysconfig/network-scripts/ifcfg-whatever.

>            regards, tom lane

Thanks Tom. It works.

Regards,
Ludwig

Re: Can we go beyond the standard to make Postgres radically better?

[Merlin Moncure]

On Wed, Feb 9, 2022 at 11:15 PM Guyren Howe  wrote:

> Postgres has since the outset gone beyond the SQL standard in many ways :
> types, inheritance, programmability, generality are all well beyond what
> SQL used to mandate and still well beyond the current standard.
>
> There are huge developer benefits available to focusing more on making a
> great relational programming environment, well outside the SQL standard.
>
> Examples of small things Postgres could have:
>
>- *SELECT * - b.a_id from a natural join b*
>   - *let me describe a select list by removing fields from a
>   relation. In the example, I get all fields in the join of  a  and b 
> other
>   than the shared key, which I only get once.*
>
> I could see this as being extremely useful, I would support non standard
ways of subtracting from '*',not necessarily in that syntax.


>
>- *note how this simplifies maintaining views wrt  changes in tables*
>- *Let me put the FROM clause first*
>   - *if I can write FROM a join b SELECT a.height, a.name
>   , b.email then an editor can give me autocomplete when 
> I’m
>   writing the select clause.*
>
> I see the benefit, but it's not big enough to support standard deviation
IMO.  SQL grammar is already very complex and this wouldn't help, even if
it's deterministically parsable.

>
>- *Hierarchical schemas*
>
> This would violate the standard, and would be unclear.  Rules are set,
identifier is [database.].[schema.][table.].column. and your change would
introduce ambiguity.


> *Examples of larger things Postgres might have:*
>
>- *First-class functions.*
>   - *A global-only namespace is dumb. Schemas are only slightly less
>   dumb. The obvious way to store and call functions is as values of 
> fields.
>   Let me organize them how I choose*
>
> Not following this, can you elaborate specific uses?


>
>- *Also let me pass around functions as values, let me make new ones
>   and so on. Postgres is the best relational programming environment 
> already
>   because it has a decent type system and more general features. Let’s 
> expand
>   and also simplify that.*
>
> Ditto above. Mostly we can work around this with dynamic SQL today without
too much effort.

>
>- *Also nested function definitions, so top-level functions can be
>   built out of local auxiliary functions.*
>- *Other languages*
>   - *Tutorial D, Datalog, Quell, let’s open this puppy up!*
>   - *SQL is a terrible, no good, very bad language*
>
> Postgres used to suport QUEL way back in the day.  Being blunt, there is
zero chance of this happening in core.  A query language translator out of
core, preprocessing the language into SQL, might work.   SQL has evolved
far beyond what those languages could practically offer.   Main benefit
here would be to better support relational division, which bizarrely has
yet to arrive in SQL.
 *A portable, low-level API*

>
>- *An alternative to SQLite that provides CRUD operations on a
>   Postgres database.*
>
> This has been frequently discussed in the archives. Short version, won't
happen; it would put too many constraints on the architecture.

merlin

>

Passing XML column in an array

[Garfield Lewis]

Hi All,

I have the following code:


141 if ( 0 != iXmlArrDim ) {

142 Datum*pXmlData  = NULL;

143 bool *pXmlDataNulls = NULL;

144 uint32_t  iXmlCount = 0;

145 bool  bFirstTime= true;

146

147 Assert( XMLOID == ARR_ELEMENTS( pXmlDataArr ) );

148

149 deconstruct_array( pXmlDataArr,

150XMLOID,

151-1,

152false,

153'i',

154&pXmlData,

155&pXmlDataNulls,

156(int32_t*)&iXmlCount );

157

158 initStringInfo( &xmlStr );

159

160 for ( size_t ix = 0; ix < iXmlCount; ++ix ) {

161 xmltype  *pX= DatumGetXmlP( pXmlData[ix] );

162 char *pData = VARDATA( pX );

163 uint32_t  iData = VARSIZE( pX ) - VARHDRSZ;

164

165 if ( !bFirstTime )

166 appendStringInfoString( &xmlStr, ", " );

167 else

168 bFirstTime = false;

169

170 appendStringInfoString( &xmlStr,

171 (const char*)lzXmlCharStrndup( pData,

172iData ) 
);

173 }

174

175 pfree( pXmlData );

176 pfree( pXmlDataNulls );

177 }

I am trying to pass an array into a new function I am creating called 
lzxmlexists via the following statement:

wdbs=# SELECT "XT"."ROWKEY" , "XT"."XMLT1C1" FROM "LZQAAS"."T642_XML_T1" "XT" 
WHERE "ROWKEY"=64201 AND lzxmlexists( ' $XTX//DeptName[ . = $Dn]', 
'DEFAULT', ARRAY["XT"."XMLT1C1"] ) ORDER BY "ROWKEY";

The column XT.XMLT1C1 exists because I can do a simple select to see the 
contents. The problem is that in the code above I hit the following error at 
line 161.

160 for ( size_t ix = 0; ix < iXmlCount; ++ix ) {
(gdb)
163 xmltype  *pX=  DatumGetXmlP( pXmlData[ix] );
(gdb)

Program received signal SIGSEGV, Segmentation fault.
0x008b3514 in pg_detoast_datum ()

Obviously, I’ve done something wrong, or I am misunderstanding something.

BTW, if I change the array input to something like this 
ARRAY[‘something-variable’::xml] it works no problem, but it seems as though 
getting the input from the column results in garbage that traps.

Any suggestion/help would be greatly appreciated.

Regards,
Garfield

Re: Can we go beyond the standard to make Postgres radically better?

[Peter J. Holzer]

On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> Postgres has since the outset gone beyond the SQL standard in many ways :
> types, inheritance, programmability, generality are all well beyond what SQL
> used to mandate and still well beyond the current standard.
> 
> There are huge developer benefits available to focusing more on making a great
> relational programming environment, well outside the SQL standard.
> 
> Examples of small things Postgres could have:
> 
>   • SELECT * - b.a_id from a natural join b
>   □ let me describe a select list by removing fields from a relation. In
> the example, I get all fields in the join of  a  and b other than the
> shared key, which I only get once.

Natural join already does this.

My use case for such a feature are tables which contain one column (or a
small number of columns) which you usually don't want to select: A bytea
column or a very wide text column. In a program I don't mind (in fact I
prefer) listing all the columns explicitely, but exploring a database
interactively with psql typing lots of column names is tedious
(especially since autocomplete doesn't work here).

>   □ note how this simplifies maintaining views wrt  changes in tables

Maybe. I'm not sure whether views that change automatically with their
underlying tables wouldn't do more harm than good.

>   • Let me put the FROM clause first
>   □ if I can write FROM a join b SELECT a.height, a.name, b.email then an
> editor can give me autocomplete when I’m writing the select clause.

Logically from should be first and select should be last, I agree. That
would make life easier for editors, but it shouldn't be impossible for
an editor to look forward.

>   • Hierarchical schemas

I thought I would miss that when I learned SQL 25 years ago, but in
practice I didn't. Plus it's already not always obvious how names are
resolved and hierarchical schemas would almost certainly make that
worse.


> Examples of larger things Postgres might have:
> 
>   • First-class functions.

I prefer to have as much application logic as feasible in the
application, so I'm rather indifferent to server-side programming
features.

>   • Other languages
>   □ Tutorial D, Datalog, Quell, let’s open this puppy up!
>   □ SQL is a terrible, no good, very bad language

I suspect that lots of the internals (especially in the optimizer) are quite
specific to how SQL works. So it's probably not that easy to provide a
different query language - at least not one which works efficiently.

But you are welcome to try.

>   • A portable, low-level API
>   □ An alternative to SQLite that provides CRUD operations on a Postgres
> database.

I'm not familiar with the low level SQLite interface. I've only ever
used it with SQL. I did use dBase back in the 1980s, though ;-).

Are you really interested in a lower-level interface or do you just want
it in-process? I suspect that just adding in-process capability would
require a major overhaul.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Can we go beyond the standard to make Postgres radically better?

[Peter J. Holzer]

On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:
> On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> >   • SELECT * - b.a_id from a natural join b
> >   □ let me describe a select list by removing fields from a relation. In
> > the example, I get all fields in the join of  a  and b other than 
> > the
> > shared key, which I only get once.
> 
> Natural join already does this.
> 
> My use case for such a feature are tables which contain one column (or a
> small number of columns) which you usually don't want to select: A bytea
> column or a very wide text column. In a program I don't mind (in fact I
> prefer) listing all the columns explicitely, but exploring a database
> interactively with psql typing lots of column names is tedious
> (especially since autocomplete doesn't work here).

Forgot to add: I think that the syntax would have to be more explicit.
It's too easy to mix up
SELECT * - b.a_id FROM ...
and
SELECT *, - b.a_id FROM ...

Maybe
SELECT * EXCEPT b.a_id FROM ...
?

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Can we go beyond the standard to make Postgres radically better?

[Peter J. Holzer]

On 2022-02-10 10:13:16 +0100, Karsten Hilbert wrote:
> Am Wed, Feb 09, 2022 at 09:14:39PM -0800 schrieb Guyren Howe:
> > There are huge developer benefits available to focusing
> > more on making a great relational programming environment,
> > well outside the SQL standard.
> 
> There's a seemingly small but conceptually rather significant
> difference between going _beyond_ a standard and being _well
> outside_ said standard.

In my opinion:

A change which doesn't alter the semantics of any
standard-conforming query (but only makes some queries valid which
are invalid according to the standard) is an extension.

Not sure if this is true for all of Guyren's proposals, although no
counter-examples immediatly pop into mind.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Passing XML column in an array

[Tom Lane]

Garfield Lewis  writes:
> I am trying to pass an array into a new function I am creating called 
> lzxmlexists via the following statement:

> wdbs=# SELECT "XT"."ROWKEY" , "XT"."XMLT1C1" FROM "LZQAAS"."T642_XML_T1" "XT" 
> WHERE "ROWKEY"=64201 AND lzxmlexists( ' $XTX//DeptName[ . = $Dn]', 
> 'DEFAULT', ARRAY["XT"."XMLT1C1"] ) ORDER BY "ROWKEY";

> The column XT.XMLT1C1 exists because I can do a simple select to see the 
> contents. The problem is that in the code above I hit the following error at 
> line 161.

> 160 for ( size_t ix = 0; ix < iXmlCount; ++ix ) {
> (gdb)
> 163 xmltype  *pX=  DatumGetXmlP( pXmlData[ix] );
> (gdb)

> Program received signal SIGSEGV, Segmentation fault.
> 0x008b3514 in pg_detoast_datum ()

The most obvious theory is that there are some nulls in the XT.XMLT1C1
column.  Since your code isn't bothering to check pXmlDataNulls[ix],
it would hit a null-pointer dereference when accessing pXmlData[ix].

regards, tom lane

Re: [EXT] Re: Passing XML column in an array

[Garfield Lewis]

>The most obvious theory is that there are some nulls in the XT.XMLT1C1
>column.  Since your code isn't bothering to check pXmlDataNulls[ix],
>it would hit a null-pointer dereference when accessing pXmlData[ix].
>
>   regards, tom lane

Thx, Tom... my bad...

Regards,
Garfield

Re: Can we go beyond the standard to make Postgres radically better?

[Merlin Moncure]

On Thu, Feb 10, 2022 at 10:54 AM Merlin Moncure  wrote:

> On Wed, Feb 9, 2022 at 11:15 PM Guyren Howe  wrote:
>
>>
>>
>
>>- *Also nested function definitions, so top-level functions can be
>>   built out of local auxiliary functions.*
>>- *Other languages*
>>   - *Tutorial D, Datalog, Quell, let’s open this puppy up!*
>>   - *SQL is a terrible, no good, very bad language*
>>
>> Postgres used to suport QUEL way back in the day.  Being blunt, there is
> zero chance of this happening in core.  A query language translator out of
> core, preprocessing the language into SQL, might work.   SQL has evolved
> far beyond what those languages could practically offer.   Main benefit
> here would be to better support relational division, which bizarrely has
> yet to arrive in SQL.
>  *A portable, low-level API*
>

FYI, there was a semi-serious commercial attempt to do this back in 2001,
Dataphor. It's been opensourced.  Wikipedia has a pretty good write up on
it:
https://en.wikipedia.org/wiki/Dataphor

IMNSHO suggestions like these should travel this technical path; take the
data language you want and translate it into SQL.  Some of these
translations will end up being complicated (read: slow).  Iterating this
kind of thing outside of core would allow for faster development.

merlin

>

Re: Can we go beyond the standard to make Postgres radically better?

[Guyren Howe]

I’d like to point out that sum types would be great.

(Sum types let you have any of two or more different types in one value)

For example, I could work around the issues with NULL by defining an 
enumeration type with values like MISSING, UNKNOWN, INVALID, … and then I can 
have a column that is a sum of that type with the type we normally keep in the 
column.

We would need some machinery to declare how aggregates work, but I think that’s 
very much in the spirit of how types work in Postgres now.

In general, I would think we could make SQL a better functional programming 
language by just implementing good fp features like this.
On Feb 10, 2022, 11:09 -0800, Merlin Moncure , wrote:
> On Thu, Feb 10, 2022 at 10:54 AM Merlin Moncure  wrote:
> > > On Wed, Feb 9, 2022 at 11:15 PM Guyren Howe  wrote:
> > > > >
> > > > >
> > > > > • Also nested function definitions, so top-level functions can be 
> > > > > built out of local auxiliary functions.
> > > > > • Other languages
> > > > > • Tutorial D, Datalog, Quell, let’s open this puppy up!
> > > > > • SQL is a terrible, no good, very bad language
> > > > >
> > > > Postgres used to suport QUEL way back in the day.  Being blunt, there 
> > > > is zero chance of this happening in core.  A query language translator 
> > > > out of core, preprocessing the language into SQL, might work.   SQL has 
> > > > evolved far beyond what those languages could practically offer.   Main 
> > > > benefit here would be to better support relational division, which 
> > > > bizarrely has yet to arrive in SQL.
> > > >  A portable, low-level API
> >
> > FYI, there was a semi-serious commercial attempt to do this back in 2001, 
> > Dataphor. It's been opensourced.  Wikipedia has a pretty good write up on 
> > it:
> > https://en.wikipedia.org/wiki/Dataphor
> >
> > IMNSHO suggestions like these should travel this technical path; take the 
> > data language you want and translate it into SQL.  Some of these 
> > translations will end up being complicated (read: slow).  Iterating this 
> > kind of thing outside of core would allow for faster development.
> >
> > merlin

How to determine whether I'm running on a standby?

[Mladen Gogala]

Hi!

I am running PostgreSQL 13.5, one primary cluster and two standby 
clusters, managed by PgBouncer which will automatically promote one of 
the  standbys to the primary in case of failure. I have a job that is 
cleaning "old data",  with "old data" being whatever business side of 
the company deems as "old data". The cleanup is a PgPL/SQL procedure 
which uses a bunch of DELETE commands. The procedure works fine on the 
primary but reports a bunch of errors on the read-only standbys. In 
another database system, I have the table called V$DATABASE and the 
column named DATABASE_ROLE which can be either primary or some kind of 
standby (physical or logical). Is there anything like that in the world 
of Postgres? I would like to do something like this:


DECLARE

DB_ROLE VARCHAR2(64);

BEGIN

SELECT DATABASE_ROLE INTO DB_ROLE FROM V$DATABASE;

IF DB_ROLE = 'PRIMARY' THEN

   

ELSE

    exit;

END IF;

END;

What would a similar idiom in the PostgreSQL world look like? I have the 
job in crontab on the primary and I don't really know which of the 3 
clusters will be my primary at any given moment. So far, I am using 
manual labor to do that, which is silly. Knowing Postgres idioms, there 
will probably be a function for this but I was unable to figure out 
which one.


Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: How to determine whether I'm running on a standby?

[David G. Johnston]

On Thu, Feb 10, 2022 at 12:54 PM Mladen Gogala 
wrote:

> The procedure works fine on the primary but reports a bunch of errors on
> the read-only standbys.
>
A standby is in perpetual recovery mode. There is a function that returns a
boolean true if you are in recovery.

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL

David J.

Re: Can we go beyond the standard to make Postgres radically better?

[Bruce Momjian]

On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote:
> On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:
> > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> > >   • SELECT * - b.a_id from a natural join b
> > >   □ let me describe a select list by removing fields from a relation. 
> > > In
> > > the example, I get all fields in the join of  a  and b other than 
> > > the
> > > shared key, which I only get once.
> > 
> > Natural join already does this.
> > 
> > My use case for such a feature are tables which contain one column (or a
> > small number of columns) which you usually don't want to select: A bytea
> > column or a very wide text column. In a program I don't mind (in fact I
> > prefer) listing all the columns explicitely, but exploring a database
> > interactively with psql typing lots of column names is tedious
> > (especially since autocomplete doesn't work here).
> 
> Forgot to add: I think that the syntax would have to be more explicit.
> It's too easy to mix up
> SELECT * - b.a_id FROM ...
> and
> SELECT *, - b.a_id FROM ...
> 
> Maybe
> SELECT * EXCEPT b.a_id FROM ...

Yes, this was proposed on hackers a few months ago and a patch was
proposed:


https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9

The last post was from October of 2021 so you can email the author to
ask about its status.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: Can we go beyond the standard to make Postgres radically better?

[Benedict Holland]

This is a strange post. Why is SQL bad and how do your reconcile that with
managing 99%+ of all data? It's so bad that we have systems that plug into
sql to query data outside of tables like Athena or Excel.

Why are you not using pgadmin4? Yes. Psql as a command line isn't great for
humans. It's spectacular for computers though. So we have pgadmin4, which I
would take over any other database ui.

Do you not want your views to change with underlying base tables changing?
Do a fully specified select. It's better programming anyway. Do you want an
api? That seems like a bad idea (i would never implement it) but you also
have a postgres socket, flask, and sqlalchemy or psycopg2. It would take a
few hours to write your own. Again, please don't do that. You will almost
surely lose user information like who decided to delete your client data
and your api would likely require user privileges to get passed by token
(sso would be a nightmare to authenticate) or simply give root privileges
to an api. Both are honestly really bad.

Now if postgres had the ability to do schema change tracking with
rollback... now that would be a victory. But there are sort of 3rd party
solutions that sort of work some of the time. It's a hard problem and
automated sql generation, particularly automated schema migrations, are
really hard to build in general and there are specific things that are damn
hard to not break.

Thanks,
Ben


On Thu, Feb 10, 2022, 4:13 PM Bruce Momjian  wrote:

> On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote:
> > On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:
> > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> > > >   • SELECT * - b.a_id from a natural join b
> > > >   □ let me describe a select list by removing fields from a
> relation. In
> > > > the example, I get all fields in the join of  a  and b other
> than the
> > > > shared key, which I only get once.
> > >
> > > Natural join already does this.
> > >
> > > My use case for such a feature are tables which contain one column (or
> a
> > > small number of columns) which you usually don't want to select: A
> bytea
> > > column or a very wide text column. In a program I don't mind (in fact I
> > > prefer) listing all the columns explicitely, but exploring a database
> > > interactively with psql typing lots of column names is tedious
> > > (especially since autocomplete doesn't work here).
> >
> > Forgot to add: I think that the syntax would have to be more explicit.
> > It's too easy to mix up
> > SELECT * - b.a_id FROM ...
> > and
> > SELECT *, - b.a_id FROM ...
> >
> > Maybe
> > SELECT * EXCEPT b.a_id FROM ...
>
> Yes, this was proposed on hackers a few months ago and a patch was
> proposed:
>
>
> https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9
>
> The last post was from October of 2021 so you can email the author to
> ask about its status.
>
> --
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
>
>   If only the physical world exists, free will is an illusion.
>
>
>
>

Re: Can we go beyond the standard to make Postgres radically better?

[Guyren Howe]

When you dig into it, the powerful idea here is the relational algebra, and its 
equivalence to a first-orderish logic.

I put up with SQL so I can use relations, and I love Postgres because it has 
the least bad SQL (by a mile!)

But SQL is a terrible, no good, very bad language.

I don’t really understand why folks who love the relational model aren’t 
perpetually up in arms about SQL being their only option. Much better query 
languages are known and well studied.

https://www.scattered-thoughts.net/writing/against-sql 


I’d love to see Postgres offer say Datalog. But until that Halcyon day, we 
could at least aggressively extend SQL to make it less awful.

> On Feb 10, 2022, at 14:38 , Benedict Holland  
> wrote:
> 
> This is a strange post. Why is SQL bad and how do your reconcile that with 
> managing 99%+ of all data? It's so bad that we have systems that plug into 
> sql to query data outside of tables like Athena or Excel. 
> 
> Why are you not using pgadmin4? Yes. Psql as a command line isn't great for 
> humans. It's spectacular for computers though. So we have pgadmin4, which I 
> would take over any other database ui. 
> 
> Do you not want your views to change with underlying base tables changing? Do 
> a fully specified select. It's better programming anyway. Do you want an api? 
> That seems like a bad idea (i would never implement it) but you also have a 
> postgres socket, flask, and sqlalchemy or psycopg2. It would take a few hours 
> to write your own. Again, please don't do that. You will almost surely lose 
> user information like who decided to delete your client data and your api 
> would likely require user privileges to get passed by token (sso would be a 
> nightmare to authenticate) or simply give root privileges to an api. Both are 
> honestly really bad. 
> 
> Now if postgres had the ability to do schema change tracking with rollback... 
> now that would be a victory. But there are sort of 3rd party solutions that 
> sort of work some of the time. It's a hard problem and automated sql 
> generation, particularly automated schema migrations, are really hard to 
> build in general and there are specific things that are damn hard to not 
> break. 
> 
> Thanks,
> Ben
> 
> 
> On Thu, Feb 10, 2022, 4:13 PM Bruce Momjian  > wrote:
> On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote:
> > On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:
> > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> > > >   • SELECT * - b.a_id from a natural join b
> > > >   □ let me describe a select list by removing fields from a 
> > > > relation. In
> > > > the example, I get all fields in the join of  a  and b other 
> > > > than the
> > > > shared key, which I only get once.
> > > 
> > > Natural join already does this.
> > > 
> > > My use case for such a feature are tables which contain one column (or a
> > > small number of columns) which you usually don't want to select: A bytea
> > > column or a very wide text column. In a program I don't mind (in fact I
> > > prefer) listing all the columns explicitely, but exploring a database
> > > interactively with psql typing lots of column names is tedious
> > > (especially since autocomplete doesn't work here).
> > 
> > Forgot to add: I think that the syntax would have to be more explicit.
> > It's too easy to mix up
> > SELECT * - b.a_id FROM ...
> > and
> > SELECT *, - b.a_id FROM ...
> > 
> > Maybe
> > SELECT * EXCEPT b.a_id FROM ...
> 
> Yes, this was proposed on hackers a few months ago and a patch was
> proposed:
> 
> 
> https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9
>  
> 
> 
> The last post was from October of 2021 so you can email the author to
> ask about its status.
> 
> -- 
>   Bruce Momjian  mailto:br...@momjian.us>>
> https://momjian.us 
>   EDB  https://enterprisedb.com 
> 
> 
>   If only the physical world exists, free will is an illusion.
> 
> 
> 

Re: Can we go beyond the standard to make Postgres radically better?

[David G. Johnston]

On Thu, Feb 10, 2022 at 3:51 PM Guyren Howe  wrote:

> But SQL is a terrible, no good, very bad language.
>

No, it's not.  It is also not perfect.

I don’t really understand why folks who love the relational model aren’t
> perpetually up in arms about SQL being their only option. Much better query
> languages are known and well studied.
>

Because it isn't worth the stress.

That, and it's difficult to justify such behavior when the product you are
using is basically free.

David J.

P.S. How did you ever survive the Betamax vs. VHS period of human
civilization? ;)

Re: Can we go beyond the standard to make Postgres radically better?

[Mladen Gogala]

Please, don't top-post.

On 2/10/22 17:51, Guyren Howe wrote:
When you dig into it, the powerful idea here is the relational 
algebra, and its equivalence to a first-orderish logic.


I put up with SQL so I can use relations, and I love Postgres because 
it has the least bad SQL (by a mile!)


As you've said, the basic motivation behind SQL is the relational 
algebra from the branch of mathematics called "naive set theory". SQL 
does that really well. Late Dr. Ted Codd was a mathematician, so he was 
also aware of non-naive set theory so the "selection language" of 
Zermelo's axiom of choice was also included. That is why we are 
selecting elements of subsets and not defining subsets. Codd was also 
working for IBM, once great company, whose name stands for "It's Better 
Manually". IBM was doing lots of business with the banks (figures) so 
the transaction handling part was modeled after the banking business. 
When I've had my first course in SQL, back in 1988, I was given analogy 
with writing checks: I A writes a $100 check to B, one of the following 
two things can happen: either A has enough money to cover the check, in 
which case B will have $100 more in his account and A will have $100 
less or, if the transaction doesn't go through, nothing will happen. Any 
mixed outcome in which B is $100 richer but A doesn't have $100 less 
money in his account or A has $100 less money but B is not $100 richer 
is strictly prohibited. That transaction must happen atomically, as a 
whole or not happen at all. So, that's SQL for you: relational algebra 
plus banking business. Relational algebra implementation is rather good, 
although a tad too verbose. Transactional part is excellent and still 
reflects the needs of the business community today.





But SQL is a terrible, no good, very bad language.



I cannot accept such a religious persecution of SQL without a detailed 
explanation.





I don’t really understand why folks who love the relational model 
aren’t perpetually up in arms about SQL being their only option. Much 
better query languages are known and well studied.


https://www.scattered-thoughts.net/writing/against-sql

I’d love to see Postgres offer say Datalog. But until that Halcyon 
day, we could at least aggressively extend SQL to make it less awful.


It will take much more than a blog post to eradicate decades of 
investment into SQL by both computer scientists and business community. 
I've seen the same crusade against SQL on Slashdot and I assume you were 
behind that as well. So, if I were you, I would establish a company to 
implement Datalog language in a database and try to sell that to the 
people. That happened in 1979: Larry Ellison, Bob Miner and Ed Oates 
established a company and offered a SQL product, competing with 
hierarchical databases like IMS and CICS/DL1, to the world. The rest is 
history. I am sure that if you do the same and if you are right about 
the Datalog, a brilliant future is ahead of you. That is the way of the 
Force. Anything can happen. A young student can come from Africa, 
convinced that he can sell electric cars and be very successful while 
doing that. So successful that he became the richest man in the world. I 
am sure that your endeavor with Datalog will have similar success. 
Market decides what is the standard. May the Force be with you. In the 
mean time, I will still be using SQL.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: How to determine whether I'm running on a standby?

[Ron]

On 2/10/22 1:56 PM, David G. Johnston wrote:
On Thu, Feb 10, 2022 at 12:54 PM Mladen Gogala > wrote:


The procedure works fine on the primary but reports a bunch of errors
on the read-only standbys.

A standby is in perpetual recovery mode.


(Just like in SQL Server.)

--
Angular momentum makes the world go 'round.

Re: Can we go beyond the standard to make Postgres radically better?

[Ron]

On 2/10/22 4:51 PM, Guyren Howe wrote:
[snip]
I don’t really understand why folks who love the relational model aren’t 
perpetually up in arms about SQL being their only option. Much better 
query languages are known and well studied.


Because it's Good Enough, and everyone with the wisdom of age knows that 
perfect is the enemy of good enough.


--
Angular momentum makes the world go 'round.

Re: Can we go beyond the standard to make Postgres radically better?

[Raymond Brinzer]

On Thu, Feb 10, 2022 at 5:51 PM Guyren Howe  wrote:

> When you dig into it, the powerful idea here is the relational algebra,
> and its equivalence to a first-orderish logic.
>
> I put up with SQL so I can use relations, and I love Postgres because it
> has the least bad SQL (by a mile!)
>
> But SQL is a terrible, no good, very bad language.
>
> I don’t really understand why folks who love the relational model aren’t
> perpetually up in arms about SQL being their only option. Much better query
> languages are known and well studied.
>

I, for one, quite agree.  The advances in languages have been marvelous,
and it's hard to imagine anyone today choosing to code in COBOL, or any
other English-like language.  SQL holds its place because of the tools it
allows us to use, not on its own virtues... rather like Javascript with
browsers.

And the problem seems very serious to me.  In the words  of Alfred North
Whitehead, "By relieving the brain of all unnecessary work, a good notation
sets it free to concentrate on more advanced problems, and in effect
increases the mental power of the race."  Conversely, a tool which imposes
needless overhead is an IQ tax we can ill-afford.

So far, though, that's just my unsupported opinion, and one can't expect to
convince people with generalities.  But what a sprawling topic!  You could
make dozens of suggestions for improvements, any one of which would need an
extensive conversation.

Here's one that I think is simple:  why would we want a language where the
clauses must come in a particular order?  `FROM mytable SELECT column` is
as clear an expression as `SELECT column FROM mytable`, and probably
better, in that it starts with the source and winnows from there.
Likewise, the order of WHERE, ORDER BY, and so on don't change what is
being said.

I believe the "why" is,  "because parsing SQL is hard enough already", but
that's a problem unto itself.  A language with a more regular syntax is
easier to work with and improve.

Now, while I'm not at all saying this is ideal, consider something as
simple as a shell:

cat mytable | cols x y z | where x > 2 | join table_2.y = mytable.y | sort z

The parts are atomic, and the structure is easy to see.  If you wanted to
add a "command", you aren't going to run into questions of how to shoehorn
it into the existing language.  Even if the example solution isn't to one's
taste, I hope the general point stands apart from it.

Also, I think it only fair to say:  PostgreSQL has done quite a remarkable
job of polishing SQL into the best thing which can be made of it.  I may
not be a fan of the language itself, but I'm truly grateful when I'm able
to use PG's dialect rather than the others I need to work with.

-- 
Ray Brinzer

Re: Can we go beyond the standard to make Postgres radically better?

[Guyren Howe]

On Feb 10, 2022, at 17:06 , Mladen Gogala  wrote:
> 
>> But SQL is a terrible, no good, very bad language.
> 
> I cannot accept such a religious persecution of SQL without a detailed 
> explanation.
> 

I feel like anyone who is defending SQL here isn’t aware of how much better the 
alternatives are, and how bad SQL really is.

I mean: it’s hard to write, hard to read. It’s hard to generate. But that’s 
just the starting point.

One of the worst things about it that I don’t see much discussed is that it 
imposes assumptions about the storage model that aren’t part of the relational 
model. Like heavyweight, hard to change tables with transactional guarantees 
and such. Don’t get me wrong, those things are great to have available, but I 
don’t need them all the time.

The whole NoSQL movement has been such a tragedy. Having diagnosed a problem 
with SQL databases, they threw out the relational model and very often 
reimplemented a form of SQL when they should have done the opposite. There is 
no reason you can’t have a relational database with an on-demand schema, with 
eventual consistency, with all those fun things that various NoSQL databases 
provide. Those storage models have their place, but the SQL standard says you 
can’t use them.

But the biggest issue is the verbose, terrible, very bad query language. In the 
web development community where I spend my time, it is almost holy writ to 
treat the database as a dumb data bucket, and do everything in the application 
layer (even things like validations, even when that is a provably incorrect 
approach). Why? I think it’s because they’re used to working in a pleasant 
language like Ruby or Python, and they want to do everything there. And who can 
blame them?

But this is bad. Proper relational design can take over much (most!) of the 
design of a typical business app, with significant efficiency gains the result. 
But no *community* is going to choose that when most of the members of the 
community don’t want to learn SQL and who can blame them?

Another issue: everyone thinks “relational” is the same thing as “SQL”. If we 
could get folks to break that association, then relations should be a standard 
feature of high-level programming languages, just as arrays and hashes are.

Heck, give me a functional programming language with a good relational model, 
and I can implement OOP in that relational language without breaking a sweat.

Software *should* be designed around a logical/relational layer with minimal 
occasional forays into Turing completeness where necessary. But no-one is even 
thinking about software like that because relational is SQL and SQL is awful.

Re: Can we go beyond the standard to make Postgres radically better?

[Raymond Brinzer]

On Thu, Feb 10, 2022 at 11:56 PM Guyren Howe  wrote:

> I feel like anyone who is defending SQL here isn’t aware of how much
> better the alternatives are, and how bad SQL really is.
>

Have you written a language description we can read and talk about?

-- 
Ray Brinzer

Re: Can we go beyond the standard to make Postgres radically better?

[Guyren Howe]

I’m not proposing some crackpot half-baked idea here. There are well-defined 
and researched alternatives to SQL.

The most fully-developed you-can-use-today offering is Datomic, which uses 
Datalog as its query language. If you know Prolog, and how that is kind of 
database-like, Datomic is pretty much a variant of Prolog.

https://www.datomic.com

I don’t use it because it’s closed source.
On Feb 10, 2022, 21:15 -0800, Raymond Brinzer , wrote:
> > On Thu, Feb 10, 2022 at 11:56 PM Guyren Howe  wrote:
> > > I feel like anyone who is defending SQL here isn’t aware of how much 
> > > better the alternatives are, and how bad SQL really is.
> >
> > Have you written a language description we can read and talk about?
> >
> --
> Ray Brinzer

Re: Can we go beyond the standard to make Postgres radically better?

[David G. Johnston]

On Thursday, February 10, 2022, Guyren Howe  wrote:

> On Feb 10, 2022, at 17:06 , Mladen Gogala  wrote:
>
>
> But SQL is a terrible, no good, very bad language.
>
>
> I cannot accept such a religious persecution of SQL without a detailed
> explanation.
>
>
> I feel like anyone who is defending SQL here isn’t aware of how much
> better the alternatives are, and how bad SQL really is.
>
>
>
I’m very comfortable in my ignorance and apathy here…since even you feel
resigned to use PostgreSQL presently.  When you find an alternative you
will substitute for PostgreSQL let me know.  I don’t see that PostgreSQL is
best served trying to make you happy on this front.

David J.

Re: Can we go beyond the standard to make Postgres radically better?

[Raymond Brinzer]

On Fri, Feb 11, 2022 at 12:26 AM Guyren Howe  wrote:

> I’m not proposing some crackpot half-baked idea here. There are
> well-defined and researched alternatives to SQL.
>

I didn't suggest that you were.  Anything which was written, someone had to
actually write.


> The most fully-developed you-can-use-today offering is Datomic, which uses
> Datalog as its query language. If you know Prolog, and how that is kind of
> database-like, Datomic is pretty much a variant of Prolog.
>
> https://www.datomic.com
>
> I don’t use it because it’s closed source.
>

And being closed-source, it's not useful here.  A concrete spec for what
you'd like to see happen at least has potential.  A parser that someone has
actually written, more so.

Will it be accepted here?  I don't know; I'm not an insider, or in a
position to say.  But it'd be a much better pitch than a pep talk, or
speaking in generalities about SQL.  And that's coming from someone who
actually agrees with you.  I'm 100% on board with the idea that something
better is (badly) needed.  But is the idea, here, really to talk a highly
successful project into doing a 180 based on this sort of argument?  If
only the people writing the code saw the light, they'd go read the Datomic
site, and start overhauling PostgreSQL?

I've floated a few modest, concrete ideas here, and while the response to
them was conservative, I wouldn't call it closed-minded. The message I've
gotten from Tom Lane was basically, "here are the problems; show me how
this would actually work."  I'd have to call that fair; ball's in my
court.  Being more ambitious, I'd be pleased with a query language which
used S-expressions.  But I think the road ahead for that would be to say,
"Hey, guys, look at this thing I've written.  Would you please consider it?"

-- 
Ray Brinzer

Re: Can we go beyond the standard to make Postgres radically better?

[Tom Lane]

Raymond Brinzer  writes:
> Will it be accepted here?  I don't know; I'm not an insider, or in a
> position to say.  But it'd be a much better pitch than a pep talk, or
> speaking in generalities about SQL.  And that's coming from someone who
> actually agrees with you.  I'm 100% on board with the idea that something
> better is (badly) needed.  But is the idea, here, really to talk a highly
> successful project into doing a 180 based on this sort of argument?  If
> only the people writing the code saw the light, they'd go read the Datomic
> site, and start overhauling PostgreSQL?

Nah, probably not.  I mean, not only are we implementing SQL, but
we're implementing it in C.  I used better languages than C back
in the seventies ... but here we are.  Practical acceptance is
all about infrastructure and compatible tooling, which SQL and C
both have in spades, while academic designs really don't.

Also, I fear this discussion underestimates the difficulty of
putting some other query language on top of Postgres.  I know
you'll say "but the Berkeley guys pasted SQL onto a QUEL engine
back when, so how hard can it be?"  In the first place, that
was done on top of maybe ten years worth of work, but now there's
another twenty-five years of development agglomerated on top of
that.  So moving things would be more than 3X harder, even if
you make the very-naive assumption that the difficulty is merely
linear.  In the second place, QUEL and SQL aren't that far apart
conceptually, and yet we've still had lots of problems that can
be traced to their incompatibilities.  Something that was really
different from SQL would be a nightmare to embed into PG.  I'll
just point out one example: if you don't like SQL's semantics for
NULL (which no I don't much like either), changing that would
probably require touching tens of thousands of lines of code just
in the PG core, never mind breaking every API used by extensions.

So for better or worse, Postgres is a SQL engine now.  If you
want Datalog or $other_language, you'd be better off starting
or contributing to some other project.

That's not to say that we can't do stuff around the margins.
The idea of "select all columns except these", for instance,
has been discussed quite a bit, and would probably happen if
we could get consensus on the syntax.  But we're not going to
throw away thirty-five years' worth of work to chase some
blue-sky ideas.

regards, tom lane

Re: Can we go beyond the standard to make Postgres radically better?

[Guyren Howe]

I get all this. Give me a couple million bucks, and I’ll hire some of the 
Postgres devs to build a new database. We could crib some of the low-level code 
from Postgres, but everything above the low level would need to be rewritten.

I was proposing more that we at least provide higher-level, more general, 
orthogonal etc features in the SQL we have now. eg first-class functions could 
be added to SQL reasonably easily.
On Feb 10, 2022, 22:32 -0800, Tom Lane , wrote:
> Raymond Brinzer  writes:
> > Will it be accepted here? I don't know; I'm not an insider, or in a
> > position to say. But it'd be a much better pitch than a pep talk, or
> > speaking in generalities about SQL. And that's coming from someone who
> > actually agrees with you. I'm 100% on board with the idea that something
> > better is (badly) needed. But is the idea, here, really to talk a highly
> > successful project into doing a 180 based on this sort of argument? If
> > only the people writing the code saw the light, they'd go read the Datomic
> > site, and start overhauling PostgreSQL?
>
> Nah, probably not. I mean, not only are we implementing SQL, but
> we're implementing it in C. I used better languages than C back
> in the seventies ... but here we are. Practical acceptance is
> all about infrastructure and compatible tooling, which SQL and C
> both have in spades, while academic designs really don't.
>
> Also, I fear this discussion underestimates the difficulty of
> putting some other query language on top of Postgres. I know
> you'll say "but the Berkeley guys pasted SQL onto a QUEL engine
> back when, so how hard can it be?" In the first place, that
> was done on top of maybe ten years worth of work, but now there's
> another twenty-five years of development agglomerated on top of
> that. So moving things would be more than 3X harder, even if
> you make the very-naive assumption that the difficulty is merely
> linear. In the second place, QUEL and SQL aren't that far apart
> conceptually, and yet we've still had lots of problems that can
> be traced to their incompatibilities. Something that was really
> different from SQL would be a nightmare to embed into PG. I'll
> just point out one example: if you don't like SQL's semantics for
> NULL (which no I don't much like either), changing that would
> probably require touching tens of thousands of lines of code just
> in the PG core, never mind breaking every API used by extensions.
>
> So for better or worse, Postgres is a SQL engine now. If you
> want Datalog or $other_language, you'd be better off starting
> or contributing to some other project.
>
> That's not to say that we can't do stuff around the margins.
> The idea of "select all columns except these", for instance,
> has been discussed quite a bit, and would probably happen if
> we could get consensus on the syntax. But we're not going to
> throw away thirty-five years' worth of work to chase some
> blue-sky ideas.
>
> regards, tom lane