Dealing with readonly column

[Lele Gaifax]

Hi,

I wonder if there is a way to declare a particular column of a table as
"readonly", either for the purpose of documenting the model, or to get
early error should someone try to update it.

Some context: I have to maintain an old application, based on
PostgreSQL, with several surrounding tools (mainly a PHP (bleach :-!)
frontend and set of "services" built with Python+SQLObject). At the same
time, I'm slowly developing a "future" replacement, rewriting the whole
stack, starting from the DB layer using SQLAlchemy.

Implementing a new "feature", where I have to rewrite the logic that
keeps up-to-date a "counter" column, I thought of replacing that column
with a "computed value" (a.k.a. "virtual column"), using a PostgreSQL
SQL function that compute that counter on-the-fly [1]: this allowed me
to avoid touching dozens of places where that column is read in the PHP
code (what a relief!).

To illustrate, where I had a table such as

CREATE TABLE something (id SERIAL, name TEXT, counter INTEGER)

I now have

CREATE TABLE something (id SERIAL, name TEXT)

CREATE FUNCTION counter(something)
RETURNS INTEGER AS $$
SELECT count(*) FROM something_else se
WHERE se.something_id = $1.id
$$ STABLE SQL

and thus existing queries such as

SELECT s.name, s.counter
FROM something AS s

work as before.

For now, I just left the SA Table definition (I'm using classic mappings)
untouched, that is something like

from sqlalchemy import Column, Integer, MetaData, Table, Text

metadata = MetaData()

something = Table("something", metadata,
Column("id", Integer, primary_key=True),
Column("name", Text),
Column("counter", Integer))

but as said, I looked around to see if there is a way to inform SA that
that "counter" column cannot be updated, just read. I tried to use the
Computed() thing like

from sqlalchemy import Computed

something = Table("something", metadata,
Column("id", Integer, primary_key=True),
Column("name", Text),
Column("counter", Integer, Computed("counter")))

but accordingly to the documentation [2] "SA behavior ... is currently
that the value [assigned to the column] will be ignored", so that does
not bring me any advantage.

On the ORM side, I could "hide" the concrete column and expose it thru a
readonly property, but I'd like to have a similar safety-belt also at
the Core layer, because many services/APIs will be written at that
level.

Do you have any suggestion?

Thanks in advance,
bye, lele.

[1] https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS
[2] https://docs.sqlalchemy.org/en/20/core/defaults.html#computed-ddl
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Mike Bayer]

On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
> Hi,
>
> I wonder if there is a way to declare a particular column of a table as
> "readonly", either for the purpose of documenting the model, or to get
> early error should someone try to update it.

"readonly" can mean a few different things....

>
> Implementing a new "feature", where I have to rewrite the logic that
> keeps up-to-date a "counter" column, I thought of replacing that column
> with a "computed value" (a.k.a. "virtual column"), using a PostgreSQL
> SQL function that compute that counter on-the-fly [1]: this allowed me
> to avoid touching dozens of places where that column is read in the PHP
> code (what a relief!).
>
> To illustrate, where I had a table such as
>
> CREATE TABLE something (id SERIAL, name TEXT, counter INTEGER)
>
> I now have
>
> CREATE TABLE something (id SERIAL, name TEXT)
>
> CREATE FUNCTION counter(something)
> RETURNS INTEGER AS $$
> SELECT count(*) FROM something_else se
> WHERE se.something_id = $1.id
> $$ STABLE SQL
>
> and thus existing queries such as
>
> SELECT s.name, s.counter
> FROM something AS s
>
> work as before.

how does "SELECT s.counter" work if the table does not have an actual "counter" column?

>
> For now, I just left the SA Table definition (I'm using classic mappings)
> untouched, that is something like
>
> from sqlalchemy import Column, Integer, MetaData, Table, Text
>
> metadata = MetaData()
>
> something = Table("something", metadata,
> Column("id", Integer, primary_key=True),
> Column("name", Text),
> Column("counter", Integer))
>
> but as said, I looked around to see if there is a way to inform SA that
> that "counter" column cannot be updated, just read. I tried to use the
> Computed() thing like

so here's where "readonly" means different things. if you want to omit "counter" from your application entirely, you can remove it from the Table() definition.

Also with some irony I am actually working on a whole different feature right now where there's a new parameter, which I'm not going to make public yet, called "_omit_from_statements" for an individual column. From an ORM mapping perspective, it's essentially the same as if you added this column to the Mapper exclude_properties list: https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper.params.exclude_properties which will also make that column not present within the mapping or any of the statements used.

neither of these two things make the column "read only", they just make the column not really present in the application.

In general, whenever a column has a server side default of this nature, you want to use FetchedValue or some subclass of this in the column definition, so the ORM as well as Core knows that something in the server will be generating a value for this column: https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns / https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults

>
> from sqlalchemy import Computed
>
> something = Table("something", metadata,
> Column("id", Integer, primary_key=True),
> Column("name", Text),
> Column("counter", Integer, Computed("counter")))
>
> but accordingly to the documentation [2] "SA behavior ... is currently
> that the value [assigned to the column] will be ignored", so that does
> not bring me any advantage.

Using Computed in this way is equivalent to using FetchedValue. Why is that a problem?


So far note all of these techniques have to do with "read only", but they are the usual approaches taken when there's a server generated column that you'd prefer the application not deal with explicitly.

>
> On the ORM side, I could "hide" the concrete column and expose it thru a
> readonly property, but I'd like to have a similar safety-belt also at
> the Core layer, because many services/APIs will be written at that
> level.
>
> Do you have any suggestion?

true "readonly" at the ORM level, meaning the column is present, it's mapped, and you want to disallow user code from saying myclass.col = "foo" or some error is raised, you can only do with attribute events or with a validator: https://docs.sqlalchemy.org/en/20/orm/mapped_attributes.html#sqlalchemy.orm.validates or other Python-side object technique.

true "readonly" at the Core level, where any attempt to use connection.execute() in such a way that data is being sent to this column, you'd need to use event hooks such as before_cursor_execute() https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute and then do heuristics to figure out what the statement is, and if any of the parameters point to this column. I would not go this route since it's overkill and not that reliable for said overkill.

If you want to absolutely disallow client side changes to this column, since you are setting up PG defaults anyway, using a trigger or a true COMPUTED column would be the easiest route.

Without using server-side constructs, in a practical sense, simply omitting the column from the Table or using mapper.exclude_properties is in my experience sufficient.

>
> Thanks in advance,
> bye, lele.
>
> [1]
> https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS
> [2] https://docs.sqlalchemy.org/en/20/core/defaults.html#computed-ddl
> --
> nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
> real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
> le...@metapensiero.it | -- Fortunato Depero, 1929.
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/87y1mullgf.fsf%40metapensiero.it.

[Lele Gaifax]

"Mike Bayer" <mike_not_...@zzzcomputing.com> writes:

> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
>> I now have
>>
>> CREATE TABLE something (id SERIAL, name TEXT)
>>
>> CREATE FUNCTION counter(something)
>> RETURNS INTEGER AS $$
>> SELECT count(*) FROM something_else se
>> WHERE se.something_id = $1.id
>> $$ STABLE SQL
>>
>> and thus existing queries such as
>>
>> SELECT s.name, s.counter
>> FROM something AS s
>>
>> work as before.
>
> how does "SELECT s.counter" work if the table does not have an actual "counter" column?

As explained here [1], "the field notation and functional notation are
equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM
t AS a" means the same, when "foo" is a function accepting the "implicit
composite type corresponding to a row in the table t".

Consider:

foo=# \d artists
Table "public.artists"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"artists_pkey" PRIMARY KEY, btree (id)

foo=# select a.name, name(a) from artists as a;
name | name
---------------+---------------
peter gabriel | peter gabriel
(1 row)

[1] https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

> In general, whenever a column has a server side default of this
> nature, you want to use FetchedValue or some subclass of this in the
> column definition, so the ORM as well as Core knows that something in
> the server will be generating a value for this column:
> https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns
> /
> https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults
>
>>
>> from sqlalchemy import Computed
>>
>> something = Table("something", metadata,
>> Column("id", Integer, primary_key=True),
>> Column("name", Text),
>> Column("counter", Integer, Computed("counter")))
>>
>> but accordingly to the documentation [2] "SA behavior ... is currently
>> that the value [assigned to the column] will be ignored", so that does
>> not bring me any advantage.
>
> Using Computed in this way is equivalent to using FetchedValue. Why is that a problem?

No, it's not a problem, but the following

stmt = something.update().values(counter=42).where(something.c.id=1)
connection.execute(stmt)

will raise an error at execution time, as will, assuming
"instance_of_something" is an instance of the ORM class mapped to the
table "something"

instance_of_something.counter = 42
session.flush()

I was just imagining something that could raise an error earlier.

> true "readonly" at the Core level, where any attempt to use
> connection.execute() in such a way that data is being sent to this
> column, you'd need to use event hooks such as before_cursor_execute()
> https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
> and then do heuristics to figure out what the statement is, and if any
> of the parameters point to this column. I would not go this route
> since it's overkill and not that reliable for said overkill.

Right, not worth the effort indeed.

> If you want to absolutely disallow client side changes to this column,
> since you are setting up PG defaults anyway, using a trigger or a true
> COMPUTED column would be the easiest route.

Unfortunately in this case PG does not help: the closest thing is a
"GENERATED" column [2], but (up to version 15 at least) it "is a special
column that is always computed from other columns", it cannot be an
arbitrary subquery.

[2] https://www.postgresql.org/docs/15/ddl-generated-columns.html

> Without using server-side constructs, in a practical sense, simply
> omitting the column from the Table or using mapper.exclude_properties
> is in my experience sufficient.

Uhm, how could I then select that value, to be returned by an API that
queries the "something" table?

Anyway, as said, it was just to be sure I didn't miss some SA magic (it
wouldn't be the first time you've managed to surprise me, and I bet
you'll keep doing that :-). My coworkers will surely be able to cope
with an error coming back from the database at flush time!

Thanks&bye, lele.

[Mike Bayer]

On Fri, Apr 14, 2023, at 3:02 PM, Lele Gaifax wrote:
> "Mike Bayer" <mike_not_...@zzzcomputing.com> writes:
>
>> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
>>> I now have
>>>
>>> CREATE TABLE something (id SERIAL, name TEXT)
>>>
>>> CREATE FUNCTION counter(something)
>>> RETURNS INTEGER AS $$
>>> SELECT count(*) FROM something_else se
>>> WHERE se.something_id = $1.id
>>> $$ STABLE SQL
>>>
>>> and thus existing queries such as
>>>
>>> SELECT s.name, s.counter
>>> FROM something AS s
>>>
>>> work as before.
>>
>> how does "SELECT s.counter" work if the table does not have an actual "counter" column?
>
> As explained here [1], "the field notation and functional notation are
> equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM
> t AS a" means the same, when "foo" is a function accepting the "implicit
> composite type corresponding to a row in the table t".

oh geez it's that silly PostgreSQL syntax. not a fan. you can use that but you'd be on your own....

>
>>
>> Using Computed in this way is equivalent to using FetchedValue. Why is that a problem?
>
> No, it's not a problem, but the following
>
> stmt = something.update().values(counter=42).where(something.c.id=1)
> connection.execute(stmt)
>
> will raise an error at execution time, as will, assuming
> "instance_of_something" is an instance of the ORM class mapped to the
> table "something"

So what then is a "read only column" if not one that raises an error if someone tries to write to it explicitly?

> instance_of_something.counter = 42
> session.flush()
>
> I was just imagining something that could raise an error earlier.

well you have the ORM validates and the before_cursor_execute approaches, but I would think if this is simple developer level programming guards, the PG exception is perfect

>
> Unfortunately in this case PG does not help: the closest thing is a
> "GENERATED" column [2], but (up to version 15 at least) it "is a special
> column that is always computed from other columns", it cannot be an
> arbitrary subquery.

trigger, then. im sure this is a thing PG can do

>
>> Without using server-side constructs, in a practical sense, simply
>> omitting the column from the Table or using mapper.exclude_properties
>> is in my experience sufficient.
>
> Uhm, how could I then select that value, to be returned by an API that
> queries the "something" table?

OK so the "readonly" you are looking for includes one where your application actually needs to load it, then fine, dont exclude it from your mappings.

[Jonathan Vanasco]

FWIW, I often use the events to ensure an object or column is "read only".  

Sometimes I also will use two different attributes, where `Object.foo` is a getter for `Object._foo` in the database.  The database will raise an error (hopefully) if I try to write to a 'protected column', and SQLAlchemy code is somewhat easy to audit to ensure I am only reading and never accessing the protected column.  I generally like to integrate that approach because I know something special is going on with the columns that have a leading _ and will immediately check the table's schema to remind myself.