| From: | Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | "Eric B(dot)Ridge" <ebr(at)tcdi(dot)com>, Chris Travers <chris(at)travelamericas(dot)com>, Jan Wieck <JanWieck(at)Yahoo(dot)com>, NTPT <ntpt(at)centrum(dot)cz>, Mike Mascari <mascarm(at)mascari(dot)com>, PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Update Default |
| Date: | 2004-01-29 19:36:25 |
| Message-ID: | 401960B9.7030007@blakjak.sytes.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-announce pgsql-general |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
<br>
Tom Lane wrote:<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap="">Brendan Jurd <a class="moz-txt-link-rfc2396E" href="mailto:blakjak(at)blakjak(dot)sytes(dot)net"><blakjak(at)blakjak(dot)sytes(dot)net></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">I think the idea of the update default has interesting possbilities.
Perhaps what is needed is two classes of defaults.
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">1. "implicit default" -- any updates to a tuple either not specifying a
value for the target column at all, or specifying DEFAULT will set that
column to the default. This would be useful for our "touch row" or
"last modified" scenario, as discussed in the previous thread.
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">2. "explicit default" -- this default can only be actioned if requested
deliberately by the user. e.g. UPDATE foo SET a='x', b='y', c=DEFAULT;
</pre>
</blockquote>
<pre wrap=""><!---->
How is #2 different from your "slightly different approach"?
</pre>
</blockquote>
Ah, sorry if this was unclear. #2 would mean that the column
definition has an explicit "update default", which could potentially be
different from the "insert default", if that was desired. The
"slightly different approach" would mean that explicit SET to DEFAULT
instructions would just use the insert default. I was just unsure
whether it would be useful in practice to have separate values for the
explicit update default and the insert default.<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap="">A slightly different approach would be to not have explicit update
defaults at all, and instead make statements like UPDATE foo SET
c=DEFAULT actually set c to the "insert default" value.
</pre>
</blockquote>
<pre wrap=""><!---->
That exists already (and is SQL-standard), but I'm not convinced that
it does the job conveniently. In the example of a time-of-last-change
column, you do not want the user to have to remember to write
SET modtime = DEFAULT. </pre>
</blockquote>
Agreed, UPDATE SET x = DEFAULT isn't a good solution for the last
modtime column. But that doesn't mean it wouldn't be useful in other
situations.<br>
<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap="">In fact, you really don't want ordinary users to
be able to set the column at all. If we had per-column privilege
controls (which the spec says we should, and I think we will eventually)
then disallowing write of the modtime column to ordinary users, along
with an update default expression, would get the job done very nicely.
</pre>
</blockquote>
Sounds good -- column based privileges would have a lot of handy
applications.<br>
<br>
Cheers<br>
<br>
BJ<br>
<blockquote type="cite" cite="mid5288(dot)1075256131(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap=""> regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
</pre>
</blockquote>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.4 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Robert Treat | 2004-02-03 16:31:32 | == PostgreSQL Weekly News - Februrary 2nd 2004 == |
| Previous Message | Jose Paulo Batista Silva | 2004-01-28 11:38:09 | Re: [GENERAL] Looking for mail relays ... |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Martin Marques | 2004-01-29 19:45:59 | Re: Grant question - More |
| Previous Message | Jack Orenstein | 2004-01-29 19:28:00 | Viewing detailed lock information |