Port Bug Report: No primary key possible with type reltime & timestamp

Updated TODO item:

* Creating index of TIMESTAMP & RELTIME fails, rename to DATETIME(Thomas)

Problem Description:
--------------------
Error message when trying to create a table
with a primary key on type reltime or timestamp

--------------------------------------------------------------------------

Test Case:
----------
create table periodes ( b reltime primary key ) ;
ERROR: Can't find a default operator class for type 703.

create table periodes ( b timestamp primary key ) ;
ERROR: Can't find a default operator class for type 1296.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Problem Description:
--------------------
Error message when trying to create a table
with a primary key on type reltime or timestamp
Solution:
---------

Use timespan and datetime instead. They are better supported; perhaps
in the next release "reltime" and "timestamp" will simply be aliases
for them...

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

Porting:
1) Seems like -O0/-O1 works best for this machine. It appears u get
spin lock errors/timeouts if i optimize at -O3, and -O2

2) in nabstime.h, the typedefs AbsoluteTime & RelativeTime ( was that
Absolutetime & Relativetime ) should be kept at a fixed ( for all ports
) size like int32. Adjusting it to what ever size time_t becomes leads
to problems with 'signed' words v. 'non-signed' extended longwords. For
instance the constant 0x80000001 is a negative 32bit integer, but as a
time_t it just a large positive number!.

3) Having problems with sign extension also creates problems for '@ 3
seconds ago'::reltime. see #2

4) You dont store reltime or abstime as 64bit's into the db. Are there
any plans to use 64bit alpha/linux timevalues as reltime & abstime ?
maybe reltime64 & abstime64? whats the sql world doing with 'seconds
since 1970' if the year is > 2038 ( which is the 32bit signed overflow )
?

5) having $(CC) -mieee all over just isn't good, particular if no float
operations are done. It slows down everthing. Is there a way to limit
this in the makefile?
gat

BTW these are porting issues ( but as well hacking issues ).

Porting:
1) Seems like -O0/-O1 works best for this machine. It appears u get
spin lock errors/timeouts if i optimize at -O3, and -O2

Yes, the 6.5.1 code will use:

CFLAGS:-O -mieee # optimization -O2 removed because of egcs problem

2) in nabstime.h, the typedefs AbsoluteTime & RelativeTime ( was that
Absolutetime & Relativetime ) should be kept at a fixed ( for all ports
) size like int32. Adjusting it to what ever size time_t becomes leads
to problems with 'signed' words v. 'non-signed' extended longwords. For
instance the constant 0x80000001 is a negative 32bit integer, but as a
time_t it just a large positive number!.

OK, the real problem is that we are using "magic" values to mark certain
values, and this is not done portably. Can you suggestion good values?
Can you send over a patch?

3) Having problems with sign extension also creates problems for '@ 3
seconds ago'::reltime. see #2

Same thing. We should not be using hard-coded values.

4) You dont store reltime or abstime as 64bit's into the db. Are there
any plans to use 64bit alpha/linux timevalues as reltime & abstime ?
maybe reltime64 & abstime64? whats the sql world doing with 'seconds
since 1970' if the year is > 2038 ( which is the 32bit signed overflow )
?

Not sure on this.

5) having $(CC) -mieee all over just isn't good, particular if no float
operations are done. It slows down everthing. Is there a way to limit
this in the makefile?
gat

What does that flag do, and where would it be needed or not needed?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Well, a reply, anyway

1) reltime & abstime values are stored in the DB as 4 byte values. The
definitions for abstime&reltime are also stored in the DB ( this from empiracle
debugging ) . If you do not plan to embrace the notion of #of seconds >
2^(32-1), and you dont want to alter the DB notion that storage is 4 bytes then

typedef int32 Absolutetime;
typedef int32 Relativetime;

would appear to be most preferable & more stable (majic #'s work ) than

typedef time_t Absolutetime;
typedef time_t Relativetime;

This is not a complete solution , as there are still some sign extension
problems as demonstratable by the regression tests.
If you want to use 64bit Absolutetime & reltimes, then you should adjust (
or make more abstract ) the concept of abstime&reltime. BUT
THIS IS NOT A PORTING ISSUE! I would just like to get the abstime*reltime to
behave much like the 32bit folks.

2) Can u add HAS_LONG_LONG to $(CFLAGS)
I dont have long long, but it turns on some code ( somewhere ) that fixes
another problem.

3) -mieee informs the egcs compiler fot the alpha to inject 'trapb'
instructions at various places in a floating point computation. The trapb is a
pipeline stall forcing the processor to stop issueing instructions until all
current instructions in the pipeline have executed. This is done to capture a
possible 'fault' at a resomable time so you can backtrack to the instruction
that faulted and take some corrective measure. There are also rules for
backtracing, and repairing. The usage of -mieee inserted these trapb's all over
the place. The current egcs compiler appears to do a better job at it For
purely int operations, then a module need not be enhanced by the -mieee switch.

4) I'd give u some patches, but still getting the regression tests to work.
Where do I get 6.5.1, so I can work with that as a base

5) What is the floating point rounding set to ( up/down ). There seems to be an
extra digit of precision in ur i386, where the alpha port appears to round up (
and have 1 digit less :( )

gat

Bruce Momjian wrote:

Well, a reply, anyway

1) reltime & abstime values are stored in the DB as 4 byte values. The
definitions for abstime&reltime are also stored in the DB ( this from empiracle
debugging ) . If you do not plan to embrace the notion of #of seconds >
2^(32-1), and you dont want to alter the DB notion that storage is 4 bytes then

typedef int32 Absolutetime;
typedef int32 Relativetime;

would appear to be most preferable & more stable (majic #'s work ) than

typedef time_t Absolutetime;
typedef time_t Relativetime;

This is not a complete solution , as there are still some sign extension
problems as demonstratable by the regression tests.
If you want to use 64bit Absolutetime & reltimes, then you should adjust (
or make more abstract ) the concept of abstime&reltime. BUT
THIS IS NOT A PORTING ISSUE! I would just like to get the abstime*reltime to
behave much like the 32bit folks.

Makes sense. Using time_t does not make sense if we are forcing
everything to 4 bytes.

2) Can u add HAS_LONG_LONG to $(CFLAGS)
I dont have long long, but it turns on some code ( somewhere ) that fixes
another problem.

Check configure. It runs a test to see if long long works, and sets that
in include/config.h.

3) -mieee informs the egcs compiler fot the alpha to inject 'trapb'
instructions at various places in a floating point computation. The trapb is a
pipeline stall forcing the processor to stop issueing instructions until all
current instructions in the pipeline have executed. This is done to capture a
possible 'fault' at a resomable time so you can backtrack to the instruction
that faulted and take some corrective measure. There are also rules for
backtracing, and repairing. The usage of -mieee inserted these trapb's all over
the place. The current egcs compiler appears to do a better job at it For
purely int operations, then a module need not be enhanced by the -mieee switch.

I am stumped on why we even need -mieee, but someone supplied a patch to
add it.

4) I'd give u some patches, but still getting the regression tests to work.
Where do I get 6.5.1, so I can work with that as a base

Go to ftp.postgresql.org, and get the "snapshot". That will be 6.5.1 on
July 19th.

5) What is the floating point rounding set to ( up/down ). There seems to be an
extra digit of precision in ur i386, where the alpha port appears to round up (
and have 1 digit less :( )

Not sure where that is set. Would be fpsetround() on BSD/OS, however, I
don't see us setting it anywhere, so my guess is that we are using the
OS default for this.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

1) The reason why for -mieee is that if u care for some of the 'rare' floating point
exceptions ( as defined by alpha floating point hardware ) then u want to handle
them - as per ieee specifications to give u the correct ieee result. When the
processor cant handle the exceptions it (can ) traps to software assist routines
( hidden in the kernel ). But in order for the kernel to fix the exception u have to
stop the pipeline as close to the problem, so u can backtrace the user pc ( which is
by now quite a few instructions ahead of where the exception occured ) to the point
where it occured to see what register needs to have the correct value inserted.
Without the -mieee, the compiler will not arrange the float operations so that
it can be backstepped when a fault occures. The kernel then cannot fix the problem,
and forces a floating point exception onto the program. Death usually follows.
Therefor only do -mieee where u need to. ERGO can this flag be set individually
as per each individual makefile, and not as per ./configure ?
2) Then I want to report a bug - HAS_LONG_LONG in one of the 'c' files needs to be
turned on - I think there is only one - also for RH6.0/alpha. I dont think that
RH6.0/alpha has long long as a type and just uses long to define a 64bit quantity
3) Then can I presume that Absolutetime/Relativetime in nabstime.h will be changed
to int32?

Bruce Momjian wrote:

1) The reason why for -mieee is that if u care for some of the 'rare' floating point
exceptions ( as defined by alpha floating point hardware ) then u want to handle
them - as per ieee specifications to give u the correct ieee result. When the
processor cant handle the exceptions it (can ) traps to software assist routines
( hidden in the kernel ). But in order for the kernel to fix the exception u have to
stop the pipeline as close to the problem, so u can backtrace the user pc ( which is
by now quite a few instructions ahead of where the exception occured ) to the point
where it occured to see what register needs to have the correct value inserted.
Without the -mieee, the compiler will not arrange the float operations so that
it can be backstepped when a fault occures. The kernel then cannot fix the problem,
and forces a floating point exception onto the program. Death usually follows.
Therefor only do -mieee where u need to. ERGO can this flag be set individually
as per each individual makefile, and not as per ./configure ?

Right now, it is hard to have makefile-specific flags.

2) Then I want to report a bug - HAS_LONG_LONG in one of the 'c' files needs to be
turned on - I think there is only one - also for RH6.0/alpha. I dont think that
RH6.0/alpha has long long as a type and just uses long to define a 64bit quantity

Add 'set -x' to configure, and figure how how the test is working in
configure. Look at the configure output. It shows how it is setting
those flags.

3) Then can I presume that Absolutetime/Relativetime in nabstime.h will be changed
to int32?

Added to TODO:

* Make Absolutetime/Relativetime int4 because time_t can be int8 on some ports

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In the regression test rules.sql there is this SQL command

update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b;

Which causes my alpha port to go core. The above line can be reduced to:

update rtest_v1 set a = rtest_t3.a + 20 ;

which also causes the same problem. It seems that the 64 bit address
((Expr*)nodeptr)->oper gets truncated ( high 32 bits ) somewhere along the way.

I was able to locate the errant code in rewriteManip.c:712. but There seems to be a
bigger problem other than eraseing the upper 32bit address. It seems that
FindMatchingNew() returns a node of type T_Expr, rather than the expected type of
T_Var. Once u realize this then u can see why the now MISCAST "(Var *)
*nodePtr)->varlevelsup = this_varlevelsup" will cause a problem. On my alpha this erases
a portion in the address in the T_Expr. On the redhat 5.2/i386 this code seems to be
benign, BUT YOU ARE ERASEING SOMETHING that doesn't belong to to T_Expr !

So what gives?
gat
Maybe an assert() will help in finding some of the miscast returned types? Wuddya think?
sure would catch some of the boo-boo's hanging around

rewriteManip.c:
if (this_varno == info->new_varno &&
this_varlevelsup == sublevels_up)
{
n = FindMatchingNew(targetlist,
((Var *) node)->varattno);
if (n == NULL)
{
if (info->event == CMD_UPDATE)
{
*nodePtr = n = copyObject(node);
((Var *) n)->varno = info->current_varno;
((Var *) n)->varnoold = info->current_varno;
}
else
*nodePtr = make_null(((Var *) node)->vartype);
}
else
{
*nodePtr = copyObject(n);
((Var *) *nodePtr)->varlevelsup = this_varlevelsup; /* This
line zaps the address */
}
}

On Wed, 14 Jul 1999, Bruce Momjian wrote:

3) -mieee informs the egcs compiler fot the alpha to inject 'trapb'
instructions at various places in a floating point computation. The trapb is a
pipeline stall forcing the processor to stop issueing instructions until all
current instructions in the pipeline have executed. This is done to capture a
possible 'fault' at a resomable time so you can backtrack to the instruction
that faulted and take some corrective measure. There are also rules for
backtracing, and repairing. The usage of -mieee inserted these trapb's all over
the place. The current egcs compiler appears to do a better job at it For
purely int operations, then a module need not be enhanced by the -mieee switch.

I am stumped on why we even need -mieee, but someone supplied a patch to
add it.

That someone would be me. :) I supplied a patch to add about a
year ago as that was the only way I could get some of the date/time code
work correctly. If it is needed anywhere anymore, then it is down in
src/backend/util/adt, as that is where the datetime code is/was that were
causing FPEs to occur on regression testing. Without that flag, the
datetime code used to blow up all over the place. Might be worthwhile to
try removing it, recompiling, and running regression tests to see if it
needed anymore. That, and fixing the datetime code so it is not needed in
the first place (if it is still needed).
The biggest problem area for pgsql on Linux/Alpha at the moment is
in the datetime code, including what reltime and abstime regression tests
exercise.
If anyone wants me to test pgsql patches on Alpha, feel free to
send them my way, and I will give them a test on my XL366 Alpha running
Debian 2.1.
TTYL.

----------------------------------------------------------------------------
| "For to me to live is Christ, and to die is gain." |
| --- Philippians 1:21 (KJV) |
----------------------------------------------------------------------------
| Ryan Kirkpatrick | Boulder, Colorado | rkirkpat@nag.cs.colorado.edu |
----------------------------------------------------------------------------
| http://www-ugrad.cs.colorado.edu/~rkirkpat/ |
----------------------------------------------------------------------------

Thats NOT THE PROBLEM.
Although u have localize the -mieee/float in the date stuff, u have needlessly
inflicted the -mieee switch on ALL compiled modules.
I would have prefered it to be makefile ( Certainly on a SUBSYS.o, and even better on
on a per module.o) compile under a makefile switch
ie: ( or something simular )

if eq($(CPUID),alpha)
myfloat.o: myfloat.c
$(CC) $(CFLAGS) -mieee myfloat.c -o myfloat.o
fi

Ryan Kirkpatrick wrote:

Uncle George <gatgul@voicenet.com> writes:

In the regression test rules.sql there is this SQL command
update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b;
Which causes my alpha port to go core.

Yeah. This was reported by Pedro Lobo on 11 June, and we've been
patiently waiting for Jan to decide what to do about it :-(

You could stop the coredump by putting a test into ResolveNew:

{
*nodePtr = copyObject(n);
+ if (IsA(*nodePtr, Var))
((Var *) *nodePtr)->varlevelsup = this_varlevelsup;
}

but what's not so clear is what's supposed to happen when the
replacement item *isn't* a Var.  I tried to convince myself that nothing
needed to happen in that case, but wasn't successful.  (Presumably the
replacement expression contains no instances of the variable being
replaced, so recursing into it with ResolveNew shouldn't be needed
--- but maybe its varlevelsup values need adjusted?)

regards, tom lane

Thats NOT THE PROBLEM.
Although u have localize the -mieee/float in the date stuff, u have needlessly
inflicted the -mieee switch on ALL compiled modules.
I would have prefered it to be makefile ( Certainly on a SUBSYS.o, and even better on
on a per module.o) compile under a makefile switch
ie: ( or something simular )

if eq($(CPUID),alpha)
myfloat.o: myfloat.c
$(CC) $(CFLAGS) -mieee myfloat.c -o myfloat.o
fi

OK, I have added code in utils/adt/Makefile as:

# seems to be required for some date/time stuff 07/19/1999 bjm
ifeq ($(CPU),alpha)
CFLAGS+= -mieee
endif

This is in the current tree, not 6.5.1. Please test and let me know if
this helps. I also added a Makefile-visible variable called CPU. Seems
we really don't have such a variable already available in the
Makefile-scope.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

if eq($(CPUID),alpha)
myfloat.o: myfloat.c
$(CC) $(CFLAGS) -mieee myfloat.c -o myfloat.o
fi

# seems to be required for some date/time stuff 07/19/1999 bjm
ifeq ($(CPU),alpha)
CFLAGS+= -mieee
endif
This is in the current tree, not 6.5.1. Please test and let me know
if this helps. I also added a Makefile-visible variable called CPU.
Seems we really don't have such a variable already available in the
Makefile-scope.

I imagine that this flag is specific to the compiler. It would
probably be best to leave it to patches until the alpha issues are
solved for every OS environment; sorry I don't have a platform myself
to test on.

btw, RedHat is interested in doing a maintenance release of Postgres
rpms, and would dearly love to have the Alpha port problems solved (or
vica versa; they hate that their shipping rpms are broken or not
available on one of their three supported architectures).

Uncle G, could you tell us the actual port string configure generates
for your platform? At the moment, PORTNAME on my i686 box says
"linux", and I don't see architecture info. But perhaps we can have
configure deduce an ARCH parameter too? It already knows it when first
identifying the system...

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart wrote:

btw, RedHat is interested in doing a maintenance release of Postgres
rpms, and would dearly love to have the Alpha port problems solved (or
vica versa; they hate that their shipping rpms are broken or not
available on one of their three supported architectures).

Well, in order to do this properly for linux/alpha & the egcs compiler u
need to know more, or realize more on the dangers of casting. Please
note that I haven't said improperly, blithely, or arbitarily. Things
just happen in the alpha if things are not properly casted. In the case
of postgres this happens to be a (major) problem with function calls &
function parameters. I have fixed just enough to get the regression
tests to work.
BTW I'd really love to have a redhat 6.0/alpha cd but not at the going
price of $79.00

Uncle G, could you tell us the actual port string configure generates
for your platform? At the moment, PORTNAME on my i686 box says
"linux", and I don't see architecture info. But perhaps we can have
configure deduce an ARCH parameter too? It already knows it when first
identifying the system...

What is PORTNAME. i ( as well as others ) use uname.

RedHat is interested in doing a maintenance release of Postgres
rpms

I have fixed just enough to get the regression
tests to work.
BTW I'd really love to have a redhat 6.0/alpha cd but not at the going
price of $79.00

I heard that Costco (a discounting volume retailer) has the grey-box
(MacMillan?) version of RH6.0 for $25...

What is PORTNAME. i ( as well as others ) use uname.

It is defined in src/Makefile.global. We would need to be able to
check for both OS (linux) and architecture (alpha); perhaps Bruce's
recent change to give a "CPU" variable is just what we need. I'll add
the PORTNAME check to the relevant Makefile.

If you can send patches for what you have changed, I can incorporate
them into an RPM for testing (built on a RH5.2-i686 box, but the
source rpm can be rebuilt on yours).

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

I imagine that this flag is specific to the compiler. It would
probably be best to leave it to patches until the alpha issues are
solved for every OS environment; sorry I don't have a platform myself
to test on.

btw, RedHat is interested in doing a maintenance release of Postgres
rpms, and would dearly love to have the Alpha port problems solved (or
vica versa; they hate that their shipping rpms are broken or not
available on one of their three supported architectures).

Uncle G, could you tell us the actual port string configure generates
for your platform? At the moment, PORTNAME on my i686 box says
"linux", and I don't see architecture info. But perhaps we can have
configure deduce an ARCH parameter too? It already knows it when first
identifying the system...

OK, I have made it:

ifeq ($(CPU),alpha)
ifeq ($(CC), gcc)
CFLAGS+= -mieee
endif
ifeq ($(CC), egcs)
CFLAGS+= -mieee
endif
endif

I can always rip it out later.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

OK, I have made it:

ifeq ($(CPU),alpha)
ifeq ($(CC), gcc)
CFLAGS+= -mieee
endif
ifeq ($(CC), egcs)
CFLAGS+= -mieee
endif
endif

Great. I think that is closer to what is needed...

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

I imagine that this flag is specific to the compiler. It would
probably be best to leave it to patches until the alpha issues are
solved for every OS environment; sorry I don't have a platform myself
to test on.

btw, RedHat is interested in doing a maintenance release of Postgres
rpms, and would dearly love to have the Alpha port problems solved (or
vica versa; they hate that their shipping rpms are broken or not
available on one of their three supported architectures).

Uncle G, could you tell us the actual port string configure generates
for your platform? At the moment, PORTNAME on my i686 box says
"linux", and I don't see architecture info. But perhaps we can have
configure deduce an ARCH parameter too? It already knows it when first
identifying the system...

OK, I have made it:

ifeq ($(CPU),alpha)
ifeq ($(CC), gcc)
CFLAGS+= -mieee
endif
ifeq ($(CC), egcs)
CFLAGS+= -mieee
endif
endif

I can always rip it out later.

Let me reiterate Thomas's comments on this. Alpha has been a very
bad port for us. I realize the problems are complex, but each alpha
person seems to know only 80% of what we need to get things working
100%. We get partial solutions to small problems, that just seem to
fix things long enough for current release. We had one release that
would not even initdb on alpha. We really need alpha folks to get their
noses to the grindstones and give us some solid causes/fixes to their
problems.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026