Re: Per-database/schema settings - Mailing list pgsql-hackers

Thomas Lockhart writes:

> afaict, SQL99 calls it
> 
> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ...;

I see. Then we'd need a per-transaction isolation level that goes away
after the transaction, and a default transaction isolation level that each
new transaction starts out with.

> It would be pretty neat to be able to execute arbitrary code during
> database startup, which could set/unset global variables and ??

Didn't you say that the default characters set is a schema-property? I
don't think there's anything like a schema "startup".

Anyway, I could think of a way to hook this into my work (or vice versa).
We already have the SET command. All you need to do is to execute a number
of SET commands for each new connection. It actually does that already,
only that the input comes from the PGOPTIONS variable from the client
side. We'd just have to add one pass that reads the database default
settings from somewhere (to be determined, I guess :) and sets these. In
fact, this should be pretty easy.

I could see a commandSET DATABASE DEFAULT "name" TO value;
or, following your lead,ALTER DATABASE(SCHEMA?) SET DEFAULT "name" TO value;

I guess we could make a global table pg_databasedefaults:dboid oid,optname text,optval text
and when you start up database "dboid", then you loop through all matching
rows and effectively execute SET optname = optval for each.


> Even if we agree that various *output* date styles are not useful, the
> DateStyle setting also affects the interpretation of input date/time
> (e.g. month/day or day/month conventions). istm that a lot of apps do
> need some flexibility in date/time inputs.

I've been meaning to ask about that, might as well do it now. As you say,
the DateStyle setting is overloaded for two separate things: default
output style (ISO, "SQL", Postgres, German), and month/day vs day/month
setting. This has always confused me (and presumably not only me) and it
is quite tricky to integrate this into my options work -- there is no
family of settings for "takes a string input and sets two integer
variables".

Maybe we could split this up:

* datetime_output_style: one of ISO, SQL, Postgres, German

(In fact, if we wanted, we could also make this an arbitrary to_char()
format string. If it's empty we default to ISO, if it's set then we pass
it right on to to_char. I guess then we'd need separate parameters for
date and time though.)

* day_before_month: true/false

We can provide backward compatibility by still accepting SET DateStyle,
but internally parsing it apart into these two.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden