Re: Schema variables - new implementation for Postgres 15

čt 13. 1. 2022 v 13:54 odesílatel Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
napsal:

> On Wed, 3 Nov 2021 at 13:05, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
> wrote:
> >
> > 2) I find this a bit confusing:
> >
> > SELECT non_existent_variable;
> > test=# select s;
> > ERROR: column "non_existent_variable" does not exist
> > LINE 1: select non_existent_variable;
> >
> > I wonder if this means using SELECT to read variables is a bad idea, and
> > we should have a separate command, just like we have LET (instead of
> > just using UPDATE in some way).
> >
>
> Hmm. This way of reading variables worries me for a different reason
> -- I think it makes it all too easy to break existing applications by
> inadvertently (or deliberately) defining variables that conflict with
> column names referred to in existing queries.
>
> For example, if I define a variable called "relkind", then psql's \sv
> meta-command is broken because the query it performs can't distinguish
> between the column and the variable.
>
> Similarly, there's ambiguity between alias.colname and
> schema.variablename. So, for example, if I do the following:
>
> CREATE SCHEMA n;
> CREATE VARIABLE n.nspname AS int;
>
> then lots of things are broken, including pg_dump and a number of psql
> meta-commands. I don't think it's acceptable to make it so easy for a
> user to break the system in this way.
>
> Those are examples that a malicious user might use, but even without
> such examples, I think it would be far too easy to inadvertently break
> a large application by defining a variable that conflicted with a
> column name you didn't know about.
>

This is a valid issue, and it should be solved, or reduce a risk

I see two possibilities

a) easy solution can be implementation of other conflict strategy -
variables have lower priority than tables with possibility to raise
warnings if some identifiers are ambiguous. This is easy to implement, and
with warning I think there should not be some unwanted surprises for
developers. This is safe in meaning - no variable can break any query.

b) harder implementation (but I long think about it) can be implementation
of schema scope access. It can be used for implementation of schema private
objects. It doesn't solve the described issue, but it can reduce the risk
of collision just for one schema.

Both possibilities can be implemented together - but the @b solution should
be implemented from zero - and it is more generic concept, and then I
prefer @a

Dean, can @a work for you?

Regards

Pavel

> Regards,
> Dean
>