Horton

Thread

  1. how to create index on timestamp field in pre v7 database

    Alex Howansky <alex@wankwood.com> — 2000-02-24T22:27:21Z

    This page:

http://www.postgresql.org/docs/postgres/datatype1033.htm

says to use timestamp in preference to datetime. It also says that datetime is
"equivalent to timestamp". Ok, so I'll use timestamp, no prob. But I want to
index that field. In v6.5.3, there is no timestamp_ops, only datetime_ops. In
v7.0b1 the datetime_ops is gone, and there is a timestamp_ops available, which
takes care of my problem. But until I put 7.0 on my production server, I can't
make an index on a timestamp field. My question is, how "equivalent" are these
types? Can I use datetime_ops to index a timestamp field in a v6.5.3 database?
I.e., can I do this:

create table thing ( bleh text, blah timetamp );
create index thing_blah on thing ( blah datetime_ops );

It runs without error, but is it legit? TIA...

-- 
Alex Howansky
Wankwood Associates
http://www.wankwood.com/

  2. Re: [SQL] how to create index on timestamp field in pre v7 database

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-24T23:57:50Z

    Alex Howansky <alex@wankwood.com> writes:
> My question is, how "equivalent" are these types?

They're the same code: we jacked up the name "timestamp" and rolled the
old datetime code underneath.  Strictly a matter of coming closer to
the SQL standard names for these datatypes.

> Can I use datetime_ops to index a timestamp field in a v6.5.3 database?

Similarly, "datetime_ops" in 6.5 is now "timestamp_ops".

As a rule, I'd suggest not bothering with opclasses in index
declarations.  The only situation where you need to select one is
where there is more than one possible opclass for the same datatype.
This holds for some of the geometric types, but not for any plain scalar
types like numerics or date/time types.  (You could think of an opclass
as specifying which sort order the index uses...)

			regards, tom lane

PS: Actually there's a second case where you must specify an opclass,
which is if you are creating a functional index; for some reason the
system can't figure out the right opclass in that case.  This is a bug,
no doubt ... never looked at it hard enough to see why it's failing.

  3. Re: [SQL] how to create index on timestamp field in pre v7 database

    Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk> — 2000-02-25T11:16:10Z

    ----------------------------------------------------------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Howansky <alex@wankwood.com>
Cc: <pgsql-sql@postgreSQL.org>
Sent: Thursday, February 24, 2000 11:57 PM
Subject: Re: [SQL] how to create index on timestamp field in pre v7
database


> Alex Howansky <alex@wankwood.com> writes:
> > My question is, how "equivalent" are these types?
>
> They're the same code: we jacked up the name "timestamp" and rolled
the
> old datetime code underneath.  Strictly a matter of coming closer to
> the SQL standard names for these datatypes.
>
> > Can I use datetime_ops to index a timestamp field in a v6.5.3
database?
>
> Similarly, "datetime_ops" in 6.5 is now "timestamp_ops".
>
> As a rule, I'd suggest not bothering with opclasses in index
> declarations.  The only situation where you need to select one is
> where there is more than one possible opclass for the same datatype.
> This holds for some of the geometric types, but not for any plain
scalar
> types like numerics or date/time types.  (You could think of an
opclass
> as specifying which sort order the index uses...)

Agreed - but note that pg_dump currently produces CREATE INDEX
statements with opclasses included.

That means running a script created by pg_dump v.6.5.x will fail under
7.0 because there is no index opclass of the type datetime?

Judging from my brief experiments that looks to be the case, anyway.
It looks like replacing all occurrences of datetime with timestamp in
the script works - even for the few functions I have that used the
datetime() function.

Yours,
Moray

  4. Re: [SQL] how to create index on timestamp field in pre v7 database

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-25T16:29:16Z

    "Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
> Agreed - but note that pg_dump currently produces CREATE INDEX
> statements with opclasses included.

Right, as it should since its purpose is to ensure you rebuild exactly
the same database.  I was just opining that handwritten CREATE INDEXes
usually can omit the opclass.  (BTW, I fixed the problem with functional
indexes needing an explicit opclass spec last night.)

> That means running a script created by pg_dump v.6.5.x will fail under
> 7.0 because there is no index opclass of the type datetime?

An embarrassing problem.  We are going to work around this by having
the 7.0 parser discard the word "datetime" if it sees it in the opclass
position.  There are a couple of other now-dead opclass names that will
be discarded in the same way.  Klugy, but it will get the job done for
reading old dump files.

(This hack is not in 7.0beta1, but will be in beta2.)

			regards, tom lane

  5. Re: missing function datetime()

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-25T16:36:23Z

    moray.mcconnachie@computing-services.oxford.ac.uk writes:
> It looks like replacing all occurrences of datetime with timestamp in
> the script works - even for the few functions I have that used the
> datetime() function.

Oooh --- another case we didn't think about.  We really should continue
to make a function named datetime() available, even though the type it
produces will officially be named timestamp.

I can see two ways to do that: either add another function to pg_proc
(five of them actually :-(), or hack the parser to translate a function
name 'datetime' to 'timestamp'.  Ugly as the second one sounds, it has
a couple of advantages.  First, it would provide an automatic upgrade
path: future dumps of 7.0 databases would show the correct function
name, at least for uses in rules.  So we could hope to get rid of the
compatibility hack someday.  Second, the parser has special treatment
for functions that are named the same as datatypes --- it knows they
represent type coercions --- so a function named "datetime" won't really
work quite the way it should for type resolution.

Not sure which way to jump.  Comments anyone?

			regards, tom lane

  6. Re: [SQL] how to create index on timestamp field in pre v7 database

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-02-25T16:54:40Z

    > "Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
> > Agreed - but note that pg_dump currently produces CREATE INDEX
> > statements with opclasses included.
> 
> Right, as it should since its purpose is to ensure you rebuild exactly
> the same database.  I was just opining that handwritten CREATE INDEXes
> usually can omit the opclass.  (BTW, I fixed the problem with functional
> indexes needing an explicit opclass spec last night.)
> 
> > That means running a script created by pg_dump v.6.5.x will fail under
> > 7.0 because there is no index opclass of the type datetime?
> 
> An embarrassing problem.  We are going to work around this by having
> the 7.0 parser discard the word "datetime" if it sees it in the opclass
> position.  There are a couple of other now-dead opclass names that will
> be discarded in the same way.  Klugy, but it will get the job done for
> reading old dump files.
> 
> (This hack is not in 7.0beta1, but will be in beta2.)

It is my understanding we are generating a new beta every night, so it
should be there now.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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

  7. Re: [HACKERS] Re: missing function datetime()

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-28T16:26:35Z

    Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> I can see two ways to do that: either add another function to pg_proc
>> (five of them actually :-(), or hack the parser to translate a function
>> name 'datetime' to 'timestamp'.  Ugly as the second one sounds, it has
>> a couple of advantages.

> xlateSqlFunc() is already in gram.y; enjoy...

Will do, just wanted to see if you approved or not.

When you finish catching up on the back email, I'd like to know what
you think about the question of comment lexing.  Should we change scan.l
so that it will recognize /* or -- as comment starters even if they are
embedded in what would currently be considered a long operator token?
If so, is it OK to depend on yyless() to do it, or is that a flex-ism?

I'd also kind of like to put in a test to detect unterminated comments
and literals.  The flex manual recommends <<EOF>> but that only works
in flex.  I was speculating that a rule for <xq> followed by nothing
might work, if all the other <xq> rules match at least one character.
Thoughts?

			regards, tom lane

  8. Re: [HACKERS] Re: missing function datetime()

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-28T16:26:42Z

    > I can see two ways to do that: either add another function to pg_proc
> (five of them actually :-(), or hack the parser to translate a function
> name 'datetime' to 'timestamp'.  Ugly as the second one sounds, it has
> a couple of advantages.  First, it would provide an automatic upgrade
> path: future dumps of 7.0 databases would show the correct function
> name, at least for uses in rules.  So we could hope to get rid of the
> compatibility hack someday.  Second, the parser has special treatment
> for functions that are named the same as datatypes --- it knows they
> represent type coercions --- so a function named "datetime" won't really
> work quite the way it should for type resolution.

xlateSqlFunc() is already in gram.y; enjoy...

                  - Thomas

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