Обсуждение: backup and store oids

    Hi all, I'm using postgresql-7.0.2 on Linux RedHat 6.2 system.

    I need help to some question:

    1. I have problem insert oid object in a table, not always,
sometimes. I have the exception

        FastPath call returned FATAL 1:  my bits moved right off
        Recreate index pg_attribute_relid_attnum_index.

         I also have this problem on Solaris 7. It appear with a simple
table as a:
        create table ttest (pkey int8, test oid);

    2. When backup my database use:
            pg_dump dbname > dbname.pgdump

        but when restore it:
            cat dbname.pgdump | psql dbname

        oid objets are not restore correctly
        There's any other way to backup oid objects?

    Thanks, Gabi.


--
Gabriel López Millán
Facultad de Informática -Universidad de Murcia
30001 Murcia - España (Spain)
Telf: +34-968-364644 E-mail: gabilm@dif.um.es

>
>     2. When backup my database use:
>             pg_dump dbname > dbname.pgdump

Use: "pg_dump -o dbname > dbname.pgdump" to dump the oid's with the rest
of the data.

Poul L. Christiansen

Hi.

I'm trying to get an average value (avg()) of a certain attribute in a
table. The snag is, I don't want this across a whole table. I only want it
over a certain set of records. In effect, I want to do something like:

SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10

and then do an avg(somefield).

Can this be done without using temp tables, in a single query? I tried
making a function that does this, but even if I used temp tables, the
function didn't work, claiming that the table didn't exist.

Ideally, I want to do something like:

SELECT avg
(
    SELECT somefield
    FROM sometable
    WHERE otherfield = 'criteria'
    ORDER BY thirdfield
    LIMIT 10
) as somefieldname

But the parser doesn't seem to like it.

How can I do this?

Thanks.

Gordan

Gabriel Lopez <gabilm@dif.um.es> writes:
>     1. I have problem insert oid object in a table, not always,
> sometimes. I have the exception
>         FastPath call returned FATAL 1:  my bits moved right off
>         Recreate index pg_attribute_relid_attnum_index.

We've seen that reported before.  It suggests that there's some corner-
case bug lurking in the btree index routines.  We could find and fix it
given a reproducible test case ... but no one's been able to provide a
test case.  Can you?

            regards, tom lane

"Gordan Bobic" <gordan@freeuk.com> writes:
> over a certain set of records. In effect, I want to do something like:

> SELECT somefield
> FROM sometable
> WHERE otherfield = 'criteria'
> ORDER BY thirdfield
> LIMIT 10

> and then do an avg(somefield).

> Can this be done without using temp tables, in a single query?

Not if the order by/limit are essential to selecting the rows you need
to average over.  At least not in 7.0 ... in 7.1 this'll work:

SELECT avg(somefield) FROM
(SELECT somefield FROM sometable ... LIMIT 10) t1;

For the moment a temp table is the way to go.

            regards, tom lane

How about

SELECT
SUM(CASE WHEN otherfield = 'criteria' THEN 1 ELSE 0 END) as count_comply,
SUM(CASE WHEN otherfield = 'criteria' THEN somefield ELSE 0 END) as
sum_comply,
sum_comply/count_comply AS mean_comply
FROM sometable
ORDER BY thirdfield
LIMIT 10

?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Thursday, November 30, 2000 3:39 PM
To: Gordan Bobic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Built in Functions use with recordsets


"Gordan Bobic" <gordan@freeuk.com> writes:
> over a certain set of records. In effect, I want to do something like:

> SELECT somefield
> FROM sometable
> WHERE otherfield = 'criteria'
> ORDER BY thirdfield
> LIMIT 10

> and then do an avg(somefield).

> Can this be done without using temp tables, in a single query?

Not if the order by/limit are essential to selecting the rows you need
to average over.  At least not in 7.0 ... in 7.1 this'll work:

SELECT avg(somefield) FROM
(SELECT somefield FROM sometable ... LIMIT 10) t1;

For the moment a temp table is the way to go.

            regards, tom lane