pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pasi Oja-Nisula <pon@iki.fi>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Date: Fri, 02 Dec 2022 11:02:18 -0500
Message-ID: <1937424.1669996938@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJvus-O6qvF_3jNHZdfX5f-5s3FLSHhUHVyPYtgCzftYH+CYXg@mail.gmail.com>
References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS>
	<CAKFQuwbYCk0oA3VT+ngEebBTBjdtGsVLEFOdPX2qqQJobA7rtg@mail.gmail.com>
	<1579912.1669908194@sss.pgh.pa.us>
	<CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com>
	<Y4lFsaF3xz24K0mr@raf.org>
	<CAJvus-MKW1tFhnotbQXbvCdXbfgH6WpvmExsMrYi1Yfov85KOA@mail.gmail.com>
	<Y4oB5RLrbTFLGWsW@raf.org>
	<CAJvus-O6qvF_3jNHZdfX5f-5s3FLSHhUHVyPYtgCzftYH+CYXg@mail.gmail.com>

Pasi Oja-Nisula <pon@iki.fi> writes:
> I would be perfectly satisfied, if the sql that produced the procedure
> would be stored "as is" read-only copy when it was compiled.

As I mentioned upthread, we used to have that behavior (store both
compiled form and original text) for column default expressions and
CHECK constraint expressions.  We got rid of that because it caused
far more confusion, and bugs, than it was worth.  Not bugs in the
database, but faulty behavior in applications that thought they
could trust the text form to be accurate.  I don't intend to
fail to learn from that history.

Also, we've *never* had storage of the original text for views.
I'm a little mystified by people claiming they use original
text for vetting functions when they clearly have no equivalent
ability for views ... or are your applications completely
view-free?

If you want an audit comparison point, I'd suggest capturing
the result of pg_get_functiondef or one of its sibling functions
just after creating your function.  "pg_dump -s" is another
pretty credible mechanism for capturing schema details.

> If an object
> rename makes it invalid, tweak a bit telling so, but don't change the text
> until next alter procedure is run.

I'm astonished at the number of people who think that poorly-implemented
Oracle behavior is something we should emulate.

			regards, tom lane





view thread (37+ messages)  latest in thread

Message-ID: <1937424.1669996938@sss.pgh.pa.us>
Permalink:  ../1937424.1669996938@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1937424.1669996938@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pon@iki.fi
  Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
  In-Reply-To: <1937424.1669996938@sss.pgh.pa.us>
  References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <CAKFQuwbYCk0oA3VT+ngEebBTBjdtGsVLEFOdPX2qqQJobA7rtg@mail.gmail.com> <1579912.1669908194@sss.pgh.pa.us> <CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com> <Y4lFsaF3xz24K0mr@raf.org> <CAJvus-MKW1tFhnotbQXbvCdXbfgH6WpvmExsMrYi1Yfov85KOA@mail.gmail.com> <Y4oB5RLrbTFLGWsW@raf.org> <CAJvus-O6qvF_3jNHZdfX5f-5s3FLSHhUHVyPYtgCzftYH+CYXg@mail.gmail.com> <1937424.1669996938@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox