Обсуждение: Thanks and questions...

    I'm new to postgres, but I've quickly become a big fan.  Thank you for
such a great project and I hope in the future to be able to contribute
to the effort.
    I'm a newbie to the list and as such, I haven't have much of a change
to lurk.  I hope my explanation isn't too long and my questions aren't
too forward.

    I am currently working on a project where I need to create a program
that will create a table, add rows to that table, and update another
table with info about the newly created table.  For those who may know,
the information is generated by something that is very much like
cflowd.  Essentially, every hour (or what ever you specify), a
compressed, pipe-delimited flat file is written to disk with information
about network usage.  This is happening on a beefy computer - ultra
sparc, 4x400Mhz processors, 1G of RAM.

    My goal is to import these files into the database.  During the busiest
times of the day, I expect that there will be 100,000/hr in a month or
so.

    The two issues are epoch time and performance.  I've read in the
mailing list archives and read a similar thread: "Importing data w/ Unix
timestamp."  In a previous project, I had a flat file, I imported it
into a temporary table, and then moved it to it real table to add
timestamp info.  I did this using perl DBI.  While the performance on
that that was great, it would be unacceptable for 100,000 insert a hour.

    What approach would be the most efficient way to accomplish this goal?
With what language or tools would you recommend?  If I were to leave the
time as a int4, epoch time, what would the select look like to return
other time formats?

    Thanks for your help!!

-g2

    Oh... a couple off topic questions.  Where can I read more about
libpgeasy?  (Bruce: thanks for writing this and your book!)   Does any
one know of any web sites that would have more examples of postgresql
tasks, projects, etc. ?

George Robinson II <george.robinson@eurekabroadband.com> writes:
>     What approach would be the most efficient way to accomplish this goal?
> With what language or tools would you recommend?  If I were to leave the
> time as a int4, epoch time, what would the select look like to return
> other time formats?

Presently the easiest way to get from Unix time to a stored timestamp
datum is to coerce to abstime first.

regression=# create table foo (f1 timestamp);
CREATE
-- this doesn't work:
regression=# insert into foo values(964824656);
ERROR:  Attribute 'f1' is of type 'timestamp' but expression is of type 'int4'
        You will need to rewrite or cast the expression
-- but this does:
regression=# insert into foo values(abstime(964824656));
INSERT 308042 1
regression=# select * from foo;
           f1
------------------------
 2000-07-28 18:50:56-04
(1 row)

I don't think this'd work in the context of a COPY command,
unfortunately, but it works fine in an INSERT.

            regards, tom lane

On Fri, Jul 28, 2000 at 06:53:41PM -0400, Tom Lane wrote:
> George Robinson II <george.robinson@eurekabroadband.com> writes:
> >     What approach would be the most efficient way to accomplish this goal?
> > With what language or tools would you recommend?  If I were to leave the
> > time as a int4, epoch time, what would the select look like to return
> > other time formats?
>
> Presently the easiest way to get from Unix time to a stored timestamp
> datum is to coerce to abstime first.
>
> regression=# insert into foo values(abstime(964824656));
>
> I don't think this'd work in the context of a COPY command,
> unfortunately, but it works fine in an INSERT.
>
>             regards, tom lane

Would it maybe be easier to store in the table as int4, then coerce on the
way out.  That way you could use copy.