pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVery strange 'now' behaviour in nested triggers.
17+ messages / 6 participants
[nested] [flat]
* Very strange 'now' behaviour in nested triggers.
@ 2003-07-26 13:39 Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:14 ` Re: Very strange 'now' behaviour in nested triggers. Richard Huxton <dev@archonet.com>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 17+ messages in thread
From: Denis Zaitsev @ 2003-07-26 13:39 UTC (permalink / raw)
To: pgsql-sql
In short, the idea this example is to test for is to split a
comma-separated value of some text attribute (given to the INSERT
operator) and then insert a row for each of the parts of that text
value. I've tried to do this thru a nested triggers approach.
create
table xxx (
s text,
t timestamp
default 'now'
);
create
function xxx () returns trigger
language plpgsql
as '
declare
tail text;
head integer;
begin
tail:= substring(new.s, \'[^,]+$\');
head:= length(new.s)-
length(tail) -1;
if head > 0 then
insert into xxx values (
substring(new.s for head)
--,new.t
);
end if;
new.s:= trim(tail);
raise notice \'"%"\', new.s;
raise notice \'"%"\', new.t;
return new;
end;
';
create
trigger xxx
before insert on xxx
for each row execute procedure
xxx ();
Then:
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:26.514217"
INSERT 223886 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:28.300914"
INSERT 223891 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:30.948737"
INSERT 223896 1
zzz=> SELECT * from xxx;
s | t
---+----------------------------
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:26.514217
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:28.300914
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:30.948737
(15 rows)
So, all the timestamps except those for the last 'x' field are the
same! These "the same" timestamps are really the timestamp of the
first top-level INSERT. And the timestamps for the last field of the
comma-separated string are the correct things. This last field is
cultivated by the top-level trigger's call.
If to set new.t for nested triggers explicitly (commented in the
trigger code above), then all will be ok. But this is not a cure, of
course.
So, what does it mean? Is this a bug (PostgreSQL 7.3.2)? Or do I
misunderstand something?
Thanks in advance.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
@ 2003-07-26 14:14 ` Richard Huxton <dev@archonet.com>
2003-07-26 19:58 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
1 sibling, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2003-07-26 14:14 UTC (permalink / raw)
To: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
On Saturday 26 July 2003 14:39, Denis Zaitsev wrote:
> In short, the idea this example is to test for is to split a
> comma-separated value of some text attribute (given to the INSERT
> operator) and then insert a row for each of the parts of that text
> value. I've tried to do this thru a nested triggers approach.
I'm not sure I'd use this approach for very long strings, but we can sort out
your timestamp problem.
> create
> table xxx (
> s text,
> t timestamp
> default 'now'
^^^
Note the quoted 'now'.
[snip recursive before trigger - final element gets inserted by the actual SQL
below - abcd get inserted by the trigger]
> zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
> NOTICE: "a"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "b"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "c"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "d"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "x"
> NOTICE: "2003-07-26 19:17:30.948737"
> INSERT 223896 1
> So, all the timestamps except those for the last 'x' field are the
> same! These "the same" timestamps are really the timestamp of the
> first top-level INSERT. And the timestamps for the last field of the
> comma-separated string are the correct things. This last field is
> cultivated by the top-level trigger's call.
>
> If to set new.t for nested triggers explicitly (commented in the
> trigger code above), then all will be ok. But this is not a cure, of
> course.
>
> So, what does it mean? Is this a bug (PostgreSQL 7.3.2)? Or do I
> misunderstand something?
Not exactly a bug. The crucial thing is that 'now' gets evaluated when the
query is parsed and the plan built. For the main INSERT that's at the start
of the transaction (which is what you want).
For the trigger function, what happens is the plan for that insert gets
compiled the first time the function is called and 'now' gets frozen.
Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you
expect.
PS - I think this is mentioned in the manuals somewhere, but it's not
surprising you missed it. Interesting example.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:14 ` Re: Very strange 'now' behaviour in nested triggers. Richard Huxton <dev@archonet.com>
@ 2003-07-26 19:58 ` Denis Zaitsev <zzz@anda.ru>
2003-07-27 07:47 ` Re: Very strange 'now' behaviour in nested triggers. Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Denis Zaitsev @ 2003-07-26 19:58 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote:
> On Saturday 26 July 2003 14:39, Denis Zaitsev wrote:
> > In short, the idea this example is to test for is to split a
> > comma-separated value of some text attribute (given to the INSERT
> > operator) and then insert a row for each of the parts of that text
> > value. I've tried to do this thru a nested triggers approach.
>
> I'm not sure I'd use this approach for very long strings
Of course not a very deep recursion, the strings are expected to
consist of less than 10 pieces.
> Not exactly a bug. The crucial thing is that 'now' gets evaluated when the
> query is parsed and the plan built. For the main INSERT that's at the start
> of the transaction (which is what you want).
>
> For the trigger function, what happens is the plan for that insert gets
> compiled the first time the function is called and 'now' gets frozen.
Ok, thanks a much. I've realized...
> Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you
> expect.
>
> PS - I think this is mentioned in the manuals somewhere, but it's not
> surprising you missed it. Interesting example.
As I remember, namely 'now' is mentioned in the manuals, as the best
approach to keep the same value thru the whole transaction. That is
why I used it here. For now I've tested that now() does the thing.
Why? I remember that now() is changing thru the transaction, just
showing the current time...
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:14 ` Re: Very strange 'now' behaviour in nested triggers. Richard Huxton <dev@archonet.com>
2003-07-26 19:58 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
@ 2003-07-27 07:47 ` Richard Huxton <dev@archonet.com>
2003-07-27 11:09 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
0 siblings, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2003-07-27 07:47 UTC (permalink / raw)
To: Denis Zaitsev <zzz@anda.ru>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
> On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote:
>> On Saturday 26 July 2003 14:39, Denis Zaitsev wrote:
>
>> Solution: make the default now() or CURRENT_TIMESTAMP and all will be as
>> you
>> expect.
>>
>> PS - I think this is mentioned in the manuals somewhere, but it's not
>> surprising you missed it. Interesting example.
>
> As I remember, namely 'now' is mentioned in the manuals, as the best
> approach to keep the same value thru the whole transaction. That is
> why I used it here. For now I've tested that now() does the thing.
> Why? I remember that now() is changing thru the transaction, just
> showing the current time...
No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction.
The one that changes is timeofday() I think. See the "Functions and
Operators" section for details.
- Richard
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:14 ` Re: Very strange 'now' behaviour in nested triggers. Richard Huxton <dev@archonet.com>
2003-07-26 19:58 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 07:47 ` Re: Very strange 'now' behaviour in nested triggers. Richard Huxton <dev@archonet.com>
@ 2003-07-27 11:09 ` Denis Zaitsev <zzz@anda.ru>
0 siblings, 0 replies; 17+ messages in thread
From: Denis Zaitsev @ 2003-07-27 11:09 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Sun, Jul 27, 2003 at 08:47:16AM +0100, Richard Huxton wrote:
>
> No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction.
> The one that changes is timeofday() I think. See the "Functions and
> Operators" section for details.
Yes, indeed... Documentation describes this. And I haven't found
anything about 'now' that I used to say... I don't know why (my
memory leak?) :)
Thanks.
BTW, this text is at the bottom of the "Date/Time Functions and
Operators" section (functions-datetime.html):
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
Note: You do not want to use the third form when specifying a DEFAULT clause while
creating a table. The system will convert now to a timestamp as soon as the constant is
parsed, so that when the default value is needed, the time of the table creation would
be used! ...
It's nearly what you have written about. But I want to note <the time
of the table creation> phrase. Should it be fixed there?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
@ 2003-07-26 14:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2003-07-26 14:31 UTC (permalink / raw)
To: Denis Zaitsev <zzz@anda.ru>; +Cc: pgsql-sql
Denis Zaitsev <zzz@anda.ru> writes:
> create table xxx (
> s text,
> t timestamp
> default 'now'
> );
That's a dangerous way to define the default --- 'now' is taken as a
literal of type timestamp, which means it will be reduced to a timestamp
constant as soon as a statement that requires the default is planned.
You lose in plpgsql because of plan caching, but you'd also lose if you
tried to PREPARE the insert command. Example:
regression=# insert into xxx values('a');
INSERT 154541 1
regression=# insert into xxx values('b');
INSERT 154542 1
regression=# prepare s(text) as insert into xxx values($1);
PREPARE
regression=# execute s('q1');
EXECUTE
regression=# execute s('q2');
EXECUTE
regression=# select * from xxx;
s | t
----+----------------------------
a | 2003-07-26 10:18:51.364913
b | 2003-07-26 10:18:53.519648
q1 | 2003-07-26 10:19:21.795415
q2 | 2003-07-26 10:19:21.795415
(4 rows)
The default would work the way you want with almost any other way of
doing it. For instance
default now()
default current_timestamp
default localtimestamp
default 'now'::text
Given that you want timestamp without time zone, I'd probably use
"default localtimestamp".
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-26 20:01 ` Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Denis Zaitsev @ 2003-07-26 20:01 UTC (permalink / raw)
To: pgsql-sql
On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote:
> Denis Zaitsev <zzz@anda.ru> writes:
> > create table xxx (
> > s text,
> > t timestamp
> > default 'now'
> > );
>
> That's a dangerous way to define the default --- 'now' is taken as a
> literal of type timestamp, which means it will be reduced to a timestamp
> constant as soon as a statement that requires the default is planned.
> You lose in plpgsql because of plan caching, but you'd also lose if you
> tried to PREPARE the insert command. Example:
Aaa... So, the INSERT inside a trigger will use the 'now' for the
time this trigger is compiled (i.e. called first time)? Do I
understand right? And the only outer trigger uses the right 'now' as
its value goes from the top-level INSERT...
Thank you very much.
By the way, do you think this method with nested triggers has some
'moral weakness' vs. just cycling left-to-right on the comma-separated
string in the 'do instead' rule for some view of xxx?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
@ 2003-07-27 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 00:27 ` Re: Very strange 'now' behaviour in nested triggers. Roberto Mello <rmello@cc.usu.edu>
2003-07-28 16:43 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
2003-07-29 22:05 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-30 02:20 ` Re: Very strange 'now' behaviour in nested triggers. Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 4 replies; 17+ messages in thread
From: Tom Lane @ 2003-07-27 15:49 UTC (permalink / raw)
To: Denis Zaitsev <zzz@anda.ru>; +Cc: pgsql-sql
Denis Zaitsev <zzz@anda.ru> writes:
> On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote:
>> That's a dangerous way to define the default --- 'now' is taken as a
>> literal of type timestamp, which means it will be reduced to a timestamp
>> constant as soon as a statement that requires the default is planned.
> Aaa... So, the INSERT inside a trigger will use the 'now' for the
> time this trigger is compiled (i.e. called first time)? Do I
> understand right? And the only outer trigger uses the right 'now' as
> its value goes from the top-level INSERT...
Right.
I put up a proposal in pgsql-hackers to change this behavior:
http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
If we made that change then the "wrong" way of defining the default
would fail in an obvious fashion --- the 'now' would get reduced to a
particular time immediately at CREATE TABLE. Doubtless this would annoy
some people, but the "right" way of defining the default isn't really
any harder, and it would save folks from getting burnt in corner cases,
like you were.
Any comments?
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-28 00:27 ` Roberto Mello <rmello@cc.usu.edu>
3 siblings, 0 replies; 17+ messages in thread
From: Roberto Mello @ 2003-07-28 00:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote:
>
> I put up a proposal in pgsql-hackers to change this behavior:
> http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
> If we made that change then the "wrong" way of defining the default
> would fail in an obvious fashion --- the 'now' would get reduced to a
> particular time immediately at CREATE TABLE. Doubtless this would annoy
> some people, but the "right" way of defining the default isn't really
> any harder, and it would save folks from getting burnt in corner cases,
> like you were.
>
> Any comments?
I think that is a preferred behavior.
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
øøøøøøøøøøøøøøøøøøøøøøøø-----°*'. (Explosive Tagline)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-28 16:43 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-28 16:52 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 17+ messages in thread
From: Dmitry Tkach @ 2003-07-28 16:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
Tom Lane wrote:
>
>I put up a proposal in pgsql-hackers to change this behavior:
>http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
>If we made that change then the "wrong" way of defining the default
>would fail in an obvious fashion --- the 'now' would get reduced to a
>particular time immediately at CREATE TABLE. Doubtless this would annoy
>some people, but the "right" way of defining the default isn't really
>any harder, and it would save folks from getting burnt in corner cases,
>like you were.
>
>Any comments?
>
>
>
Why not get rid of 'now' alltogether? Are there any cases when it is
actually useful as opposed to now()?
Dima
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 16:43 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
@ 2003-07-28 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 17:06 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2003-07-28 16:52 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
Dmitry Tkach <dmitry@openratings.com> writes:
> Why not get rid of 'now' alltogether? Are there any cases when it is
> actually useful as opposed to now()?
Data entry. You don't necessarily have the option to invoke a function,
as opposed to just sending a string for the datetime input parser.
(It would be rather difficult for an application to allow this one case
without permitting SQL-injection attacks, I'd think.)
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 16:43 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
2003-07-28 16:52 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-28 17:06 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-28 17:12 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Dmitry Tkach @ 2003-07-28 17:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
>
>
>Data entry. You don't necessarily have the option to invoke a function,
>as opposed to just sending a string for the datetime input parser.
>(It would be rather difficult for an application to allow this one case
>without permitting SQL-injection attacks, I'd think.)
>
>
>
Does it mean that the *application* (not the database) user would then
have to know the exact specific way to represent the current time in his
data entry form?
Such an application looks like (how do I say it politely?) not a very
user-friendly one to me :-)
Dima
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 16:43 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
2003-07-28 16:52 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 17:06 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
@ 2003-07-28 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 17:18 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2003-07-28 17:12 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
Dmitry Tkach <dmitry@openratings.com> writes:
> Does it mean that the *application* (not the database) user would then
> have to know the exact specific way to represent the current time in his
> data entry form?
> Such an application looks like (how do I say it politely?) not a very
> user-friendly one to me :-)
So? "now()" is certainly not more user-friendly than "now". My point
is that wherever you are making the decision that you want to input
current time, there may be layers between you and the database that will
only want to pass data-value strings and not function invocations.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 16:43 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
2003-07-28 16:52 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 17:06 ` Re: Very strange 'now' behaviour in nested triggers. Dmitry Tkach <dmitry@openratings.com>
2003-07-28 17:12 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-28 17:18 ` Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 17+ messages in thread
From: Dmitry Tkach @ 2003-07-28 17:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
Tom Lane wrote:
>Dmitry Tkach <dmitry@openratings.com> writes:
>
>
>>Does it mean that the *application* (not the database) user would then
>>have to know the exact specific way to represent the current time in his
>>data entry form?
>>Such an application looks like (how do I say it politely?) not a very
>>user-friendly one to me :-)
>>
>>
>
>So? "now()" is certainly not more user-friendly than "now".
>
Nope... it isn't.
My point was that, if the app wanted to be user friendly, it would not
attempt to take the input directly from user and stuff it into the sql -
it would probably have some checkbox or drop-down list in the GUI form,
that would indicate that the user wants the current time stamp, and use
the the proper internal represntation in the generated sql...
In that case having to execute a function (now()) would not make it
vulnerable to a sql injection...
> My point
>is that wherever you are making the decision that you want to input
>current time, there may be layers between you and the database that will
>only want to pass data-value strings and not function invocations.
>
>
>
Yeah... I've actually found one after I sent that last message :-) - it
does seem to come handy in COPY TABLE - although, in that case, I'd say
it would be much more useful to make COPY TABLE understand the defined
defaults on the table, just the way INSERT does ...
Dima
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-29 22:05 ` Denis Zaitsev <zzz@anda.ru>
3 siblings, 0 replies; 17+ messages in thread
From: Denis Zaitsev @ 2003-07-29 22:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote:
> I put up a proposal in pgsql-hackers to change this behavior:
> http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
> If we made that change then the "wrong" way of defining the default
> would fail in an obvious fashion --- the 'now' would get reduced to a
> particular time immediately at CREATE TABLE. Doubtless this would annoy
> some people, but the "right" way of defining the default isn't really
> any harder, and it would save folks from getting burnt in corner cases,
> like you were.
>
> Any comments?
The proposed behavious promises to be "strict, simple and
well-defined". The current one definitely tends to have "dirty and
non-obvious side effects here and there". I think the new behaviour
should conquer. BTW, the documentation describes this proposed
behaviour (but why?), not the current one...
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-30 02:20 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-07-30 13:48 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2003-07-30 02:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
Could we cleanly convert 'now' to now()? I assume not because we pass
the string to the date code, and have no way to pass back a function
(now()).
Having now and now() behave differently certainly is strange.
CURRENT_TIMESTAMP works fine, but it does because it isn't in quotes.
Strangely, CURRENT_TIMESTAMP converts to 'now', not now(). Is that a
problem?
| CURRENT_TIMESTAMP
{
/*
* Translate as "'now'::text::timestamptz".
* See comments for CURRENT_DATE.
*/
A_Const *s = makeNode(A_Const);
TypeName *d;
s->val.type = T_String;
s->val.val.str = "now";
s->typename = SystemTypeName("text");
d = SystemTypeName("timestamptz");
/* SQL99 mandates a default precision of 6 for timestamp.
* Also, that is about as precise as we will get since
* we are using a microsecond time interface.
* - thomas 2001-12-07
*/
d->typmod = 6;
$$ = (Node *)makeTypeCast((Node *)s, d);
}
---------------------------------------------------------------------------
Tom Lane wrote:
> Denis Zaitsev <zzz@anda.ru> writes:
> > On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote:
> >> That's a dangerous way to define the default --- 'now' is taken as a
> >> literal of type timestamp, which means it will be reduced to a timestamp
> >> constant as soon as a statement that requires the default is planned.
>
> > Aaa... So, the INSERT inside a trigger will use the 'now' for the
> > time this trigger is compiled (i.e. called first time)? Do I
> > understand right? And the only outer trigger uses the right 'now' as
> > its value goes from the top-level INSERT...
>
> Right.
>
> I put up a proposal in pgsql-hackers to change this behavior:
> http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
> If we made that change then the "wrong" way of defining the default
> would fail in an obvious fashion --- the 'now' would get reduced to a
> particular time immediately at CREATE TABLE. Doubtless this would annoy
> some people, but the "right" way of defining the default isn't really
> any harder, and it would save folks from getting burnt in corner cases,
> like you were.
>
> Any comments?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Very strange 'now' behaviour in nested triggers.
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Re: Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Re: Very strange 'now' behaviour in nested triggers. Tom Lane <tgl@sss.pgh.pa.us>
2003-07-30 02:20 ` Re: Very strange 'now' behaviour in nested triggers. Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-07-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2003-07-30 13:48 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Denis Zaitsev <zzz@anda.ru>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Strangely, CURRENT_TIMESTAMP converts to 'now', not now(). Is that a
> problem?
No, it is not, because the text-to-timestamptz conversion is marked
volatile and won't be const-folded. I've always thought it was a very
ugly implementation though, mainly because it doesn't reverse-list
nicely in rule dumps. Sometime we should try to fix it so that it
reverse-lists as "current_timestamp(n)", and likewise for the similar
special cases in gram.y.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2003-07-30 13:48 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-26 13:39 Very strange 'now' behaviour in nested triggers. Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:14 ` Richard Huxton <dev@archonet.com>
2003-07-26 19:58 ` Denis Zaitsev <zzz@anda.ru>
2003-07-27 07:47 ` Richard Huxton <dev@archonet.com>
2003-07-27 11:09 ` Denis Zaitsev <zzz@anda.ru>
2003-07-26 14:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 20:01 ` Denis Zaitsev <zzz@anda.ru>
2003-07-27 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 00:27 ` Roberto Mello <rmello@cc.usu.edu>
2003-07-28 16:43 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-28 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 17:06 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-28 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-28 17:18 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-29 22:05 ` Denis Zaitsev <zzz@anda.ru>
2003-07-30 02:20 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-07-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox