Attention PL authors: want to be listed in template table?

Tom Lane wrote:

If you want to be listed, let me know. What I need to know to list you
is values for this table:

typedef struct
{
char *lanname; /* PL name */
bool lantrusted; /* trusted? */
char *lanhandler; /* name of handler function */
char *lanvalidator; /* name of validator function, or NULL */
char *lanlibrary; /* path of shared library */
} PLTemplate;

Hi Tom,

Please include PL/R:

{ "plr", false, "plr_call_handler", NULL, "$libdir/plr" },

Thanks,

Joe

p.s. my (supposedly) static IP changed this summer -- I think my direct
mail might be getting blocked by your spam filters again.

Tom Lane wrote:

Barring further changes, we'll have a hard-wired template list for
8.1 and a real system catalog in 8.2. So there's a choice now for
PLs that are not part of the core distribution: do you want to be
listed in the hard-wired template?

Is there any way to create the language and not use the template (an
override option of some kind)?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:

Is there any way to create the language and not use the template (an
override option of some kind)?

There is deliberately not a way to override (other than using a
different name for the PL). One of the points of this facility is to
fix up broken PL definitions being imported from old dump files, so
believing what the CREATE LANGUAGE command says is exactly what we
don't want.

This will definitely be more palatable with a system catalog that you
could alter, of course. Still, I don't see that there's any big
problem. For instance, if you think you might want a validator later,
you can set up a no-op validator procedure today, and then the template
doesn't need to change when you make the validator do something.
Similarly, you could future-proof yourself against adding a trusted (or
untrusted) variant by putting in some stubs now.

regards, tom lane

Tom Lane wrote:

For instance, if you think you might want a validator later,
you can set up a no-op validator procedure today, and then the template
doesn't need to change when you make the validator do something.
Similarly, you could future-proof yourself against adding a trusted (or
untrusted) variant by putting in some stubs now.

Only necessary for one release, right?

Anyway, clearly there are cases where a validator make no sense or very
little sense (pl/sh and pl/{j,java} spring to mind).

cheers

andrew

Tom Lane wrote:

There is deliberately not a way to override (other than using a
different name for the PL). One of the points of this facility is to
fix up broken PL definitions being imported from old dump files, so
believing what the CREATE LANGUAGE command says is exactly what we
don't want.

I don't doubt that, but I just have a stomach ache with this entire
notion of having a hard-coded exception list of the sort "if the user
requires this, do this instead". Why don't we just put all PLs that we
know of into pg_language to begin with, revoke the permissions, and
just let CREATE LANGUAGE fail when the dump is restored? (The dump
should restore the permissions, shouldn't it?) At least that would let
you alter the "template" using existing means.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Andrew Dunstan wrote:

Anyway, clearly there are cases where a validator make no sense or
very little sense (pl/sh and pl/{j,java} spring to mind).

PL/sh has a validator. :-)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Andrew Dunstan wrote:

Anyway, clearly there are cases where a validator make no sense or
very little sense (pl/sh and pl/{j,java} spring to mind).

PL/sh has a validator. :-)

Really? Curious. I must look more closely.

cheers

andrew

Peter Eisentraut <peter_e@gmx.net> writes:

I don't doubt that, but I just have a stomach ache with this entire
notion of having a hard-coded exception list of the sort "if the user
requires this, do this instead".

I don't see it as an exception list. The direction I see for this is
that the parameters to CREATE LANGUAGE are obsolete and will eventually
be removed altogether, with "CREATE LANGUAGE foo" using an existing
template as the only recommended way to do it.

Why don't we just put all PLs that we
know of into pg_language to begin with, revoke the permissions, and
just let CREATE LANGUAGE fail when the dump is restored?

To do that we'd have to force an initdb, in which case we might as well
add the proposed pltemplate catalog and have done with it.

I'm entirely willing to add the catalog if people are willing to accept
an initdb now. As I said earlier, I don't want to mess with adding a
usage privilege just yet, but we could add the catalog with a
nonfunctional ACL column and write that code later.

regards, tom lane

Tom Lane wrote:

I don't see it as an exception list. The direction I see for this is
that the parameters to CREATE LANGUAGE are obsolete and will
eventually be removed altogether, with "CREATE LANGUAGE foo" using an
existing template as the only recommended way to do it.

So your proposal is to enable a new language by doing:

1. register a template
2. activate template using CREATE LANGUAGE (which would copy it to
pg_language)

How is this different from

1. register language in pg_language without privileges
2. activate language by granting privileges

This already works and uses only well-known concepts.

Why don't we just put all PLs that we
know of into pg_language to begin with, revoke the permissions, and
just let CREATE LANGUAGE fail when the dump is restored?

To do that we'd have to force an initdb, in which case we might as
well add the proposed pltemplate catalog and have done with it.

I don't have a strong opinion on initdb, but a difference would be that
this solution would not *require* an initdb but only offer the
improvement if initdb were done while continuing to work as before
without initdb.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:

So your proposal is to enable a new language by doing:

1. register a template
2. activate template using CREATE LANGUAGE (which would copy it to
pg_language)

That's the long-term idea, yes.

How is this different from

1. register language in pg_language without privileges
2. activate language by granting privileges

Because you can't create a language without first creating the support
procedures, which ordinarily requires having the shared library present.
(This is why I proposed text names for the support procedures in
pltemplate, rather than OID references.) This is perhaps not an issue
for entries wired in by initdb, but it's definitely an issue for manual
addition of template entries.

Also, ISTM your proposal is to cause "CREATE LANGUAGE foo" on an
already-existing language to execute "GRANT USAGE ON LANGUAGE foo TO PUBLIC"
instead, rather than erroring out. That doesn't seem to pass the
least-surprise test at all.

regards, tom lane

i am using postgresql 8.0.3 as a single user by running "postgres"

I want to purge all contents in the bufferpool, and I did this by
calling "InitBufTable(256)" (buf_table.c) after each query. However,
this seems not working for each followup query I still get less disk read
(tracked by "smgrread()" in smgr.c) and increased bufferhitcount.

Could anyone tell me which module shall i modify instead?

huaxin zhang <uwcssa@gmail.com> writes:

I want to purge all contents in the bufferpool,

Why do you think that's a good idea? It certainly won't purge the
kernel's disk caches, so if you're hoping to restore the system to
ground zero this won't do it.

and I did this by
calling "InitBufTable(256)" (buf_table.c) after each query.

Let's see ... I'd expect that to run out of shared memory pretty soon,
but not till after it's thoroughly corrupted your database ;-)

There is no API exported by bufmgr.c that does what you want. Something
like a combination of FlushRelationBuffers and DropRelFileNodeBuffers,
but processing a whole database (see also DropBuffers) or the whole buffer
cache, might work. You can't drop a buffer that some other process has
pinned, however.

regards, tom lane

Tom,
I assume that the path of the shared library will be somehow relative to
the GUC dynamic_library_path? If not, the lanlibrary should be changed
to "$libdir/libpljava". That requires that PL/Java is installed within
the PostgreSQL distribution.

I also assume that the handler name can be prefixed with a schema name?
All PL/Java support functions reside in the sqlj schema.

If my assumptions are correct, then please add:

{ "java", true, "sqlj.java_call_handler", NULL,
"libpljava" },
{ "javaU", false, "sqlj.javau_call_handler", NULL,
"libpljava" },

The validator for PL/Java will have to wait until 8.2.

Regards,
Thomas Hallgren

Tom Lane wrote:

Thomas Hallgren <thhal@mailblocks.com> writes:

I assume that the path of the shared library will be somehow relative to
the GUC dynamic_library_path?

Well, whatever you put in the template is what will be in the probin
field of the support functions. I suppose it does not *have* to use
$libdir, but I would definitely recommend using $libdir rather than
depending on dynamic_library_path.

I also assume that the handler name can be prefixed with a schema name?
All PL/Java support functions reside in the sqlj schema.

Not if you use the template facility, they won't. The handler and
validator are hard-wired to live in pg_catalog under this scheme.

The validator for PL/Java will have to wait until 8.2.

Do you want to drop in a stub? It's only a one-line function.

regards, tom lane

Tom Lane wrote:

Thomas Hallgren <thhal@mailblocks.com> writes:

I assume that the path of the shared library will be somehow relative to
the GUC dynamic_library_path?

Well, whatever you put in the template is what will be in the probin
field of the support functions. I suppose it does not *have* to use
$libdir, but I would definitely recommend using $libdir rather than
depending on dynamic_library_path.

I'm not I understand this. The default setting for the
dynamic_library_path is $libdir, isn't it? So why have another hardwired
setting here? Wouldn't it be better if all PL's used the
dynamic_library_path setting at all times?

I also assume that the handler name can be prefixed with a schema name?
All PL/Java support functions reside in the sqlj schema.

Not if you use the template facility, they won't. The handler and
validator are hard-wired to live in pg_catalog under this scheme.

Ok. That's fine. They're not covered by the SQL standard anyway. I have
a lot of other "support functions" for managing jar files, classpath,
etc. in the database. They all live in the sqlj schema but they will not
be affected by this.

The validator for PL/Java will have to wait until 8.2.

Do you want to drop in a stub? It's only a one-line function.

Yes, that's a good idea. I'll call them "java_validator" and
"javau_validator" respectively.

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:

Wouldn't it be better if all PL's used the
dynamic_library_path setting at all times?

I wouldn't think so at all. That's just another way to shoot yourself
in the foot; $libdir is the correct place by definition, and any other
directory is not the correct place, by definition. This is certainly
true for the PLs that are part of the distribution, but I don't see that
it would be any less true for add-on PLs.

As an example of how to shoot yourself in the foot, consider someone
setting dynamic_library_path to point to a version-specific directory:
/home/postgres/version74/lib
and then updating to a newer release without changing that.

If there were some upside to letting people load alternate versions of
PLs by changing dynamic_library_path then you might have a point ...
but I really don't see any value there, just risk of breakage.

Do you want to drop in a stub? It's only a one-line function.

Yes, that's a good idea. I'll call them "java_validator" and
"javau_validator" respectively.

OK, I'll add that to the template info.

regards, tom lane

Thomas Hallgren wrote:

If my assumptions are correct, then please add:

{ "java", true, "sqlj.java_call_handler", NULL,
"libpljava" },
{ "javaU", false, "sqlj.javau_call_handler", NULL,
"libpljava" },

In the interest of uniformity, please rename your libraries to omit the
"lib" prefix.

The other problem I see emerging here is that in certain environments,
the "java" language may not be trusted, such as when it is compiled
with GCJ. Then, this built-in template will override the CREATE
LANGUAGE specification and introduce a security hole.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Tom Lane wrote:

How is this different from

1. register language in pg_language without privileges
2. activate language by granting privileges

Because you can't create a language without first creating the support
procedures, which ordinarily requires having the shared library
present.

We are only talking about well-known procedural languages. The issue of
completely new languages added by the user is addressed by neither
proposal.

Also, ISTM your proposal is to cause "CREATE LANGUAGE foo" on an
already-existing language to execute "GRANT USAGE ON LANGUAGE foo TO
PUBLIC" instead, rather than erroring out. That doesn't seem to pass
the least-surprise test at all.

Clearly, there's going to be some surprise element. The surprise
element proposed by you is that the command does something completely
different than specified (which possibly introduces security holes, see
other mail). My proposal is that the command does only a subset of
what it would normally do, which amounts to some sort of implicit "OR
REPLACE", which people are familiar with.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Tom Lane wrote:

Barring further changes, we'll have a hard-wired template list for
8.1 and a real system catalog in 8.2. So there's a choice now for
PLs that are not part of the core distribution: do you want to be
listed in the hard-wired template?

Another note: PLs that are not in the core distribution are not created
with createlang in the first place, so the issues that led up to this
proposal don't apply AFAICT, that is:

- "pg_dump dumps them at a pretty low semantic level" --> pg_dump dumps
them at exactly the level they were created at.

- "problem with adding a validator" --> An update of PostgreSQL does not
change the properties of external modules

- "issues with hardwired paths to the shared libraries" --> This is an
issue with all loadable modules and needs a more general solution.
Basically, on a major version upgrade, we need to make the user
recompile all shared libraries.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/