Обсуждение: Read-only attributes

I have a table where one of the attributes is the time a row was inserted:
'updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP'.

Is there a way to define this attribute so that a user cannot override the
default value?  I have tried this constraint, but it fails for any INSERT:
'updated ... CHECK (updated = CURRENT_TIMESTAMP)'.

___________________________________
Michael Klatt

University of Oklahoma
Environmental Verification and Analysis Center
710 Asp Avenue, Suite 8
Norman, OK  73069

405.447.8412
405.447.8455  FAX
http://www.evac.ou.edu/

"I'm a great motivator.  Everyone says they have to work twice as hard when
I'm around."

Michael Klatt <mdklatt@ou.edu> writes:
> I have a table where one of the attributes is the time a row was inserted:
> 'updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP'.

> Is there a way to define this attribute so that a user cannot override the
> default value?  I have tried this constraint, but it fails for any INSERT:
> 'updated ... CHECK (updated = CURRENT_TIMESTAMP)'.

No, but if you used a trigger rather than a default value, you could
force the field value to be always the time of insert.

You should, however, consider carefully whether that is *really* what
you want: there's no way to bypass a trigger.  Among other things,
dumping and reloading such a table would cause all the rows to acquire
the time of load.

A probably cleaner solution is not to give the untrusted users direct
write access on the table at all.  Give them write access on a view, and
let the INSERT rewrite rule for the view enforce the desired behavior.

            regards, tom lane

hi all,

i'm pretty new to postgres, and thus the post to novice.

i tried to perform an upgrade to 7.2 today and have run into some
problems at the very end of the process.

after making 2 pg_dumpalls (one for globals and one for the data),
stopping postgresql, installing the appropriate 7.2 rpms, and then
starting postgresql again, i tried to put the data dumps back in and had
an error thrown back at me.

here's what i did pre install:
sudo -u postgres pg_dumpall -g -h myDB > /u/tchatter/globals_dbdump.txt

sudo -u postgres pg_dumpall -o -h myDB > /u/tchatter/all_dbdump.txt

post install:
sudo -u postgres /usr/bin/psql -d template1 -f
/u/tchatter/globals_dbdump.txt
sudo -u postgres /usr/bin/psql -d template1 -f
/u/tchatter/all_dbdump.txt

at which point i got:
psql:/u/tchatter/all_dbdump.txt:323: \connect: FATAL 1:  IDENT
authentication failed for user "karl"

so i figured that something was wrong with the pg_hba.conf file
but i'm not quite sure what i'm looking for in order to correct it.

i'd appreciate any pointers for where to look next.
thanks!
tuna

Tuna,

> so i figured that something was wrong with the pg_hba.conf file
> but i'm not quite sure what i'm looking for in order to correct it.
>
> i'd appreciate any pointers for where to look next.
> thanks!

Check to make sure that the authentication for localhost is set to
 "TRUST". Anything else will interfere with restoring a pg_dumpall.
  You can re-secure your authentication after you are done restoring.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

thanks a lot josh!  after changing the authentication for localhost i
forgot that i had to restart postgres... but after much hairpulling i
remembered to restart and it works beautifully:o)

take care,
tuna

On Wed, 2002-02-20 at 17:18, Josh Berkus wrote:
> Tuna,
>
> > so i figured that something was wrong with the pg_hba.conf file
> > but i'm not quite sure what i'm looking for in order to correct it.
> >
> > i'd appreciate any pointers for where to look next.
> > thanks!
>
> Check to make sure that the authentication for localhost is set to
>  "TRUST". Anything else will interfere with restoring a pg_dumpall.
>   You can re-secure your authentication after you are done restoring.
>
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco