Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Schema variables - new implementation for Postgres 15
Date
Msg-id CAFj8pRC9-MnQHUFwksDe72pjOkk9RBt_yRSProkt2y7jyWwQXA@mail.gmail.com
Whole thread Raw
In response to Re: Schema variables - new implementation for Postgres 15  (Dean Rasheed <dean.a.rasheed@gmail.com>)
List pgsql-hackers


čt 13. 1. 2022 v 13:54 odesílatel Dean Rasheed <dean.a.rasheed@gmail.com> napsal:
On Wed, 3 Nov 2021 at 13:05, Tomas Vondra <tomas.vondra@enterprisedb.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

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: SLRUs in the main buffer pool, redux
Next
From: "Joel Jacobson"
Date:
Subject: Re: Schema variables - new implementation for Postgres 15