Thread: [HACKERS] Create language syntax is not proper in pg_dumpall and not workingusing pg_upgrade

v9.6

postgres=# CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler;
CREATE LANGUAGE
postgres=# \q

v10 , run pg_upgrade - failing with this error -

pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
pg_restore: creating COMMENT "postgres"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating PROCEDURAL LANGUAGE "alt_lang1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 560; 2612 16384 
PROCEDURAL LANGUAGE alt_lang1 edb
pg_restore: [archiver (db)] could not execute query: ERROR: unsupported 
language "alt_lang1"
HINT:  The supported languages are listed in the pg_pltemplate system 
catalog.    Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE "alt_lang1";


take the cluster dump using pg_dumpall
"
--
-- Name: alt_lang1; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: edb
--

CREATE OR REPLACE PROCEDURAL LANGUAGE alt_lang1;


ALTER PROCEDURAL LANGUAGE alt_lang1 OWNER TO edb;
"

Handler part is missing and due to that  it is throwing an error ,if we 
try to execute on psql terminal.

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

On 25 July 2017 at 12:09, tushar <tushar.ahuja@enterprisedb.com> wrote:
> v9.6
>
> postgres=# CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler;
> CREATE LANGUAGE
> postgres=# \q
>
> v10 , run pg_upgrade - failing with this error -
>
> pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
> pg_restore: creating COMMENT "postgres"
> pg_restore: creating SCHEMA "public"
> pg_restore: creating COMMENT "SCHEMA "public""
> pg_restore: creating PROCEDURAL LANGUAGE "alt_lang1"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 560; 2612 16384 PROCEDURAL
> LANGUAGE alt_lang1 edb
> pg_restore: [archiver (db)] could not execute query: ERROR: unsupported
> language "alt_lang1"
> HINT:  The supported languages are listed in the pg_pltemplate system
> catalog.
>     Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE "alt_lang1";
>
>
> take the cluster dump using pg_dumpall
> "
> --
> -- Name: alt_lang1; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: edb
> --
>
> CREATE OR REPLACE PROCEDURAL LANGUAGE alt_lang1;
>
>
> ALTER PROCEDURAL LANGUAGE alt_lang1 OWNER TO edb;
> "
>
> Handler part is missing and due to that  it is throwing an error ,if we try
> to execute on psql terminal.

I think this is because the handler function is in the pg_catalog
schema, which aren't dumped.  However, it seems they need to be if a
non-pg_catalog language is created that uses them.

This seems odd though:

/* Skip if not to be dumped */
if (!plang->dobj.dump || dopt->dataOnly)       return;

...

funcInfo = findFuncByOid(plang->lanplcallfoid);
if (funcInfo != NULL && !funcInfo->dobj.dump && plang->dobj.dump)       funcInfo = NULL;                /* treat
not-dumpedsame as not-found */
 


The reason I think it's odd is because, if it finds that the language
needs to be dumped, it checks whether the functions referenced for the
handler, inline and validator are in the pg_catalog schema too.  If
they are, it doesn't output them, but if we know the language isn't in
pg_catalog, and the functions for these options are, then surely we
*have* to output them?  Should there be any checks for these functions
at all?

Thom

tushar <tushar.ahuja@enterprisedb.com> writes:
> postgres=# CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler;
> CREATE LANGUAGE

pg_dump doesn't really support that scenario, and I don't feel any
great need to make it do so.  Per the comment in dumpProcLang:
 * Try to find the support function(s).  It is not an error if we don't * find them --- if the functions are in the
pg_catalogschema, as is * standard in 8.1 and up, then we won't have loaded them. (In this case * we will emit a
parameterlessCREATE LANGUAGE command, which will * require PL template knowledge in the backend to reload.)
 

So the assumption is basically that PLs that don't have pg_pltemplate
entries will not keep their support functions in pg_catalog.  I think
there are exceptions to handle languages+support functions that are
wrapped into extensions ... but you didn't do that either.
        regards, tom lane

On Tue, Jul 25, 2017 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> tushar <tushar.ahuja@enterprisedb.com> writes:
>> postgres=# CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler;
>> CREATE LANGUAGE
>
> pg_dump doesn't really support that scenario, and I don't feel any
> great need to make it do so.  Per the comment in dumpProcLang:
>
>          * Try to find the support function(s).  It is not an error if we don't
>          * find them --- if the functions are in the pg_catalog schema, as is
>          * standard in 8.1 and up, then we won't have loaded them. (In this case
>          * we will emit a parameterless CREATE LANGUAGE command, which will
>          * require PL template knowledge in the backend to reload.)
>
> So the assumption is basically that PLs that don't have pg_pltemplate
> entries will not keep their support functions in pg_catalog.

Is this assumption, like, documented someplace?

I tend to think it's pretty bad when users can execute SQL and then
pg_dump doesn't work.  I mean, if you play with the contents of
pg_catalog, then I'm not sure how much we can guarantee, but I don't
see how a user would know that there's anything wrong with Tushar's
SQL command.

I would be on board with the idea that you (or anyone, really) doesn't
want to fix this because it's a fairly unimportant issue, but I balk
at the notion that nothing is wrong here, because to me that looks
busted.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jul 25, 2017 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> pg_dump doesn't really support that scenario, and I don't feel any
>> great need to make it do so.  Per the comment in dumpProcLang:

> Is this assumption, like, documented someplace?

Uh, right there?

> I would be on board with the idea that you (or anyone, really) doesn't
> want to fix this because it's a fairly unimportant issue, but I balk
> at the notion that nothing is wrong here, because to me that looks
> busted.

Well, it's not just unimportant but smack in the middle of code that
is treading a very narrow path to avoid assorted version dependencies.
I don't want to risk breaking cases that are actually important in the
field to support something that's obviously a toy test case.

We might be able to make some simplification/rationalization here
whenever we desupport pg_dump from < 8.1 servers (ie pre pg_pltemplate).
But right now I'm afraid to touch it.
        regards, tom lane

On Tue, Jul 25, 2017 at 1:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Is this assumption, like, documented someplace?
>
> Uh, right there?

I don't think we can expect end-users to read the code comments to
determine whether their apparently-legal SQL is fully supported.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2017-07-25 13:10:11 -0400, Robert Haas wrote:
> On Tue, Jul 25, 2017 at 1:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Is this assumption, like, documented someplace?
> >
> > Uh, right there?
> 
> I don't think we can expect end-users to read the code comments to
> determine whether their apparently-legal SQL is fully supported.

I don't think plain end-users are going to create differently named PLs
using builtin handlers. There's plenty special casing of system object
in pg_dump and elsewhere. Dependency tracking doesn't quite work right
if you refer to system objects either, etc.  This is superuser only
stuff, for a reason.

- Andres

On Tue, Jul 25, 2017 at 1:13 PM, Andres Freund <andres@anarazel.de> wrote:
> On 2017-07-25 13:10:11 -0400, Robert Haas wrote:
>> On Tue, Jul 25, 2017 at 1:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >> Is this assumption, like, documented someplace?
>> >
>> > Uh, right there?
>>
>> I don't think we can expect end-users to read the code comments to
>> determine whether their apparently-legal SQL is fully supported.
>
> I don't think plain end-users are going to create differently named PLs
> using builtin handlers. There's plenty special casing of system object
> in pg_dump and elsewhere. Dependency tracking doesn't quite work right
> if you refer to system objects either, etc.  This is superuser only
> stuff, for a reason.

But superuser != developer.  Superusers aren't obliged to read the
code comments any more than any other user.

I think the only reason we don't get people whining about stuff like
this more than we do is that it's pretty obscure.  But I bet if we
look through the pgsql-bugs archives we can find people complaining
about various cases where they did assorted seemingly-legal things
that turned out not to be supported by pg_dump.  Whether this
particular thing has been discovered by anyone before, I dunno.  But
there's certainly a whole category of bug reports along the line of
"pg_dump works mostly, except when I do X".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2017-07-25 13:18:25 -0400, Robert Haas wrote:
> On Tue, Jul 25, 2017 at 1:13 PM, Andres Freund <andres@anarazel.de> wrote:
> > On 2017-07-25 13:10:11 -0400, Robert Haas wrote:
> >> On Tue, Jul 25, 2017 at 1:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> >> Is this assumption, like, documented someplace?
> >> >
> >> > Uh, right there?
> >>
> >> I don't think we can expect end-users to read the code comments to
> >> determine whether their apparently-legal SQL is fully supported.
> >
> > I don't think plain end-users are going to create differently named PLs
> > using builtin handlers. There's plenty special casing of system object
> > in pg_dump and elsewhere. Dependency tracking doesn't quite work right
> > if you refer to system objects either, etc.  This is superuser only
> > stuff, for a reason.
> 
> But superuser != developer.  Superusers aren't obliged to read the
> code comments any more than any other user.

And yet we tell them that they're to blame if they do a CREATE FUNCTION
with the wrong signature, or a DELETE FROM pg_class; or ...


> I think the only reason we don't get people whining about stuff like
> this more than we do is that it's pretty obscure.  But I bet if we
> look through the pgsql-bugs archives we can find people complaining
> about various cases where they did assorted seemingly-legal things
> that turned out not to be supported by pg_dump.  Whether this
> particular thing has been discovered by anyone before, I dunno.  But
> there's certainly a whole category of bug reports along the line of
> "pg_dump works mostly, except when I do X".

Yes, and?  We can try to address countless intentionally unsupported
edge-cases, but it's going to cost code, complexity and time. And very
likely it's going to add hard to find, test and address bugs. pg_dump is
complicated as is, I don't think trying to address every conceivable
weirdness is a good idea. There's plenty more fundamental things wrong
(e.g. DDL concurrent with a dump sometimes breaking that dump).

I'm not sure what you're arguing for here.

- Andres

On 07/25/2017 10:24 AM, Andres Freund wrote:
> On 2017-07-25 13:18:25 -0400, Robert Haas wrote:
>> On Tue, Jul 25, 2017 at 1:13 PM, Andres Freund <andres@anarazel.de> wrote:
>>> On 2017-07-25 13:10:11 -0400, Robert Haas wrote:
>>>> On Tue, Jul 25, 2017 at 1:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>>>> Is this assumption, like, documented someplace?

> Yes, and?  We can try to address countless intentionally unsupported
> edge-cases, but it's going to cost code, complexity and time. And very
> likely it's going to add hard to find, test and address bugs. pg_dump is
> complicated as is, I don't think trying to address every conceivable
> weirdness is a good idea. There's plenty more fundamental things wrong
> (e.g. DDL concurrent with a dump sometimes breaking that dump).
> 
> I'm not sure what you're arguing for here.

Isn't the simplest solution just to actually document this? Code is not 
documentation except for those reading code. End users, sql developers, 
DBAs etc, should never have to open doxygen.postgresql.org to figure 
this stuff out.

JD


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****     Unless otherwise stated, opinions are my own.   *****

Andres Freund <andres@anarazel.de> writes:
> I'm not sure what you're arguing for here.

Robert wants perfection, of course ;-).  As do we all.  But there are
only so many hours in the day, and rejiggering pg_dump's rules about
how to dump PLs is just way down the to-do list.  I'm going to go do
something with more tangible benefit, like see if we can make its
REFRESH MATERIALIZED VIEW commands come out at the right time.
        regards, tom lane

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Isn't the simplest solution just to actually document this?

Given that it's behaved this way since 8.1 (maybe earlier, I'm not sure),
and nobody has complained before, I'm not sure it's worth documenting.
There are lots of undocumented behaviors in PG; if we tried to explain
every one of them, the docs would become unusably bloated.
        regards, tom lane

On Tue, Jul 25, 2017 at 1:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andres Freund <andres@anarazel.de> writes:
>> I'm not sure what you're arguing for here.
>
> Robert wants perfection, of course ;-).  As do we all.  But there are
> only so many hours in the day, and rejiggering pg_dump's rules about
> how to dump PLs is just way down the to-do list.  I'm going to go do
> something with more tangible benefit, like see if we can make its
> REFRESH MATERIALIZED VIEW commands come out at the right time.

+1 to all of that.  I'm only arguing that there's a difference between
the things that are worth fixing and the things that are formally
bugs.  This may not be worth fixing, but I think it's formally a bug,
because you could easily expect it to work and there's no user-facing
documentation anywhere that says it doesn't.  However, I'm no doubt
about the relative priority of this vs. the other issue you mention.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company