Thread: Per-database/schema settings
I've done a little work on enabling session-specific default behavior
for transaction isolation level. I'm thinking about how to extend this
to default "database-specific" behaviors which persist between sessions
(such as "DateStyle", character encoding, etc), perhaps using the ALTER
SCHEMA command from SQL99. btw, this capability enables, overlaps or
impacts upcoming work to support general character encodings (which may
also be impacted by the current work on TOAST; we'll see).
Anyway, if these kinds of things can be set via SQL (they are required
to in SQL99) then istm that they could/should be stored in tables just
like everything else. My initial thought was to add columns to
pg_database for each setting, but this is not very extensible. Another
possibility might be to add routines somewhere as "trigger-able events"
which happen when, say, a row is selected from pg_database. I'll guess
that this in particular won't work, since pg_database is not opened from
within a fully functioning Postgres backend.
Any thoughts on how to go about this? I assume that Peter's recent
"options" work does not apply, since it is not directly accessible
though SQL. But I haven't looked to verify this assumption.
- Thomas
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> My initial thought was to add columns to
> pg_database for each setting, but this is not very extensible. Another
> possibility might be to add routines somewhere as "trigger-able events"
> which happen when, say, a row is selected from pg_database. I'll guess
> that this in particular won't work, since pg_database is not opened from
> within a fully functioning Postgres backend.
IIRC, pg_database is rechecked as soon as a new backend is up and
running. So it'd be easy enough to extract additional values from
the pg_database row at that instant. A trigger wouldn't help though,
it'd have to be hardwired code. (Even if we tweaked the backend to
fire a trigger at that point, where would the trigger get the data
from? You'd still need to add columns to pg_database.)
I agree that adding columns to pg_database is a painful way of creating
per-database options, but I'm not sure what would be better.
> Any thoughts on how to go about this? I assume that Peter's recent
> "options" work does not apply, since it is not directly accessible
> though SQL. But I haven't looked to verify this assumption.
AFAIR his options stuff does not support per-database settings.
But perhaps it could be made to do so ... Peter, any thoughts?
regards, tom lane
> > I've done a little work on enabling session-specific default behavior
> > for transaction isolation level.
> Is this the START TRANSACTION [ ISOLATION LEVEL ] command?
afaict, SQL99 calls it
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ...;
> > I'm thinking about how to extend this to default "database-specific"
> > behaviors which persist between sessions (such as "DateStyle",
> > character encoding, etc), perhaps using the ALTER SCHEMA command from
> > SQL99. btw,
> What about something like ALTER DATABASE ... SET DEFAULT foo TO bar; The
> ALTER SCHEMA command should be reserved to schema alerations.
afaik character collation is a "schema" property, but it has been quite
a while since I've looked. If my recollection is true, then there is a
pretty big grey area between "database" and "schema" imho.
> > My initial thought was to add columns to pg_database for each setting,
> > but this is not very extensible.
> If it's an attribute of a database, then it should be a pg_database
> column. Notice how the language I chose virtually forces you to do
> that. :) And what's so non-extensible about that?
Any time a new attribute needs to be set, a new column needs to be
added, requiring a dump/initdb/reload. It would be pretty neat to be
able to execute arbitrary code during database startup, which could
set/unset global variables and ?? I guess that was what I was asking
about.
> What kind of settings are you talking about, besides default character set
> and date style? I would assume that the default charset is the one to be
> used by the NCHAR type?
Transaction isolation level is one. And presumably several other things
we haven't yet thought through.
> About datestyle, I had thought that this setting
> should really be deprecated, with the arrival of the to_char() family. If
> you like a default datestyle, then you can define a view based on
> to_char().
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.
- Thomas
On Wed, 5 Jul 2000, Thomas Lockhart wrote: > I've done a little work on enabling session-specific default behavior > for transaction isolation level. Is this the START TRANSACTION [ ISOLATION LEVEL ] command? > I'm thinking about how to extend this to default "database-specific" > behaviors which persist between sessions (such as "DateStyle", > character encoding, etc), perhaps using the ALTER SCHEMA command from > SQL99. btw, What about something like ALTER DATABASE ... SET DEFAULT foo TO bar; The ALTER SCHEMA command should be reserved to schema alerations. > My initial thought was to add columns to pg_database for each setting, > but this is not very extensible. If it's an attribute of a database, then it should be a pg_database column. Notice how the language I chose virtually forces you to do that. :) And what's so non-extensible about that? > I assume that Peter's recent "options" work does not apply, since it > is not directly accessible though SQL. The SHOW command continues to work like it always has. But most, if not all, of these options are not really per-database material. They are either debugging or developing aids that you turn on temporarily, or choices that the site administrator does only once. These options really don't have a lot to do with the SQL environment. (Btw: http://www.postgresql.org/docs/admin/runtime-config.htm) What kind of settings are you talking about, besides default character set and date style? I would assume that the default charset is the one to be used by the NCHAR type? About datestyle, I had thought that this setting should really be deprecated, with the arrival of the to_char() family. If you like a default datestyle, then you can define a view based on to_char(). -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
>>>>> "eisentrp" == eisentrp <eisentrp@csis.gvsu.edu> writes: eisentrp> About datestyle, I had thought that this setting should eisentrp> really be deprecated, with the arrival ofthe to_char() eisentrp> family. If you like a default datestyle, then you can eisentrp> define a view based on to_char(). As far as datestyle goes, I like Oracle's alter session set nls_date_format='MON DD, YYYY HH24:MI:SS'; This doesn't really deal with default for a database, but I've longed fr this in Postgres.... roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD Unix Software Solutions roberts@panix.com 76-15 113th Street, Apt 3B rbroberts@acm.org Forest Hills, NY 11375
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
> 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".
Perhaps it is confusing because it tries to cover (regional) cases we
aren't all familiar with?
> 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.)
I've been pretty resistant to having a fully-tailorable native output
capability, since it would be possible to generate date strings which
can not be correctly interpreted on input. It might interact badly with
pg_dump, for example. It might be a bit slower than the current
hard-coded technique.
> * day_before_month: true/false
> We can provide backward compatibility by still accepting SET DateStyle,
> but internally parsing it apart into these two.
"German" doesn't have much meaning with a flipped month/day field. So
these aren't entirely decoupled. We could vote quickly to get rid of it
and hope that those Germans aren't paying attention ;)
I guess that my letting go of what *I* think is important could be or is
or will be necessary for continued progress on the date/time handling.
But stability and predictability is pretty important. Eventually,
perhaps we should get rid of all of the options, insist on ISO-8601 as
the input and output format, and insist that people use to_char() if
they want anything more. But that seems a bit extreme.
- Thomas
Thomas Lockhart writes: > I've been pretty resistant to having a fully-tailorable native output > capability, since it would be possible to generate date strings which > can not be correctly interpreted on input. Good point. Let them use to_char(). > "German" doesn't have much meaning with a flipped month/day field. The only DateStyle "major mode" that cares about the month/day is "SQL". German, Postgres, and ISO are not affected, AFAICT. > We could vote quickly to get rid of it and hope that those Germans > aren't paying attention ;) Well, I'm German, but ... :-) No, I'm not proposing to get rid of this, at least not right now. All I'm saying is that there should perhaps be two separate settings: 1. Major output mode 2. Should month-before-day or day-before-month be *preferred* where *applicable*? (It's not "applicable" in any output mode but SQL, and it's not "applicable" on input like '99/12/31'. -- I always thought of it as "tie-breaker".) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> 1. Major output mode
> 2. Should month-before-day or day-before-month be *preferred* where
> *applicable*? (It's not "applicable" in any output mode but SQL, and it's
> not "applicable" on input like '99/12/31'. -- I always thought of it as
> "tie-breaker".)
Yes, for input it is a "tie-breaker"; that's how I've thought of it too.
Sure, let's try to break this into two orthogonal attributes. Can you
think of a good name or keyword? "European" and "US" sure isn't saying
things as clearly as "day-before-month" or "month-before-day", but they
are easier to type and make a better shorthand.
Let's find a good syntax for this setting before we go too far...
- Thomas
> This solution can forever stop all discussion about styles that PG
> must/can support.
This is one thing I'm *not* certain about. The problems with a fully
general, templated formatting function for the backend include
1) It is easy to make a date/time template which *cannot* be used to
read data back in. So, for example, pg_dump could be fundamentally
broken just be this setting. Currently, input and output are always
compatible (more or less ;)
2) There may be a performance hit to *always* use a fully general
template for formatting.
3) If the template is used for output, it should probably be used for
input (to minimize the possibility of (1)). But then we would be able to
accept fewer date/time variations than we do now.
- Thomas
On Thu, 6 Jul 2000, Peter Eisentraut wrote:
> Thomas Lockhart writes:
>
> > I've been pretty resistant to having a fully-tailorable native output
> > capability, since it would be possible to generate date strings which
> > can not be correctly interpreted on input.
>
> Good point. Let them use to_char().
>
Small note, to_char() has good friend to_timestamp() and this second
routine must allow interpret all output from to_char() to PG internal
datetype.
test=# select to_timestamp( to_char( now(), '"perverse date/time: "Y,YYY
FMMonth-DD HH24:MI:SS'), '"perverse date/time: "Y,YYY FMMonth-DD
HH24:MI:SS') = now();?column?
----------t
(1 row)
Karel
On Thu, 6 Jul 2000, Peter Eisentraut wrote:
>
> > 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.)
I not sure, but if I good remember for example Oracle has something like
SET DATESTYLE TO 'arbitrary style', where style is defined via to_char
templates. For example:
SET DATESTYLE TO 'YYYY Month-DD HH:MI:SS'
and all date/time (like now()) outputs will formatted via this setting in
to_char "engine". IMHO create support for this is possible. I will think
about it for 7.2
This solution can forever stop all discussion about styles that PG
must/can support.
Karel
On Fri, 7 Jul 2000, Thomas Lockhart wrote:
> > This solution can forever stop all discussion about styles that PG
> > must/can support.
>
> This is one thing I'm *not* certain about. The problems with a fully
> general, templated formatting function for the backend include
>
> 1) It is easy to make a date/time template which *cannot* be used to
> read data back in. So, for example, pg_dump could be fundamentally
> broken just be this setting. Currently, input and output are always
> compatible (more or less ;)
full support for in/out is expect in this idea, and we can add check
that conterol if defined template is right for timestamp interpretation.
> 2) There may be a performance hit to *always* use a fully general
> template for formatting.
Not sure. The to_char/timestamp is fast and parsed template is cached,
IMHO is not big (speed) differention between to_char/timestamp and standard
date/time formatting.
> 3) If the template is used for output, it should probably be used for
> input (to minimize the possibility of (1)). But then we would be able to
> accept fewer date/time variations than we do now.
With this setting is all in user's hands...
I don't know how much problematic it is, but the oracle has this feature
(NSL_DATE_FORMAT in ALTER SESSION, etc)
But I not say that we must implement this, it is still open thema :-)
Karel
On Mon, 10 Jul 2000, Karel Zak wrote: > > I don't know how much problematic it is, but the oracle has this feature > (NSL_DATE_FORMAT in ALTER SESSION, etc) Note, I see Oracle's docs, and NSL_DATE_FORMAT is probably used as default format template for to_char/to_date, and it allowe SELECT TO_CHAR(sysdate) FROM DUAL; (is possible in Oracle formatting/parsing datetime without to_char(), like postgresql timestamp_in/out?) Hmm, PG and Oracle date/time design is probably more different. I was not total right in the previous letter. Karel