Thread: dump with lo

dump with lo

From
Maciej Piekielniak
Date:
Hello pgsql-sql,
  I try copy my old database with large objects from postgresql7.4 to  postgresql-8.1.  How can I dump database with
lo?pg_dumpall don't save lo.
 

-- 
Best regards,Maciej                          mailto:piechcio@isb.com.pl



Re: dump with lo

From
Tom Lane
Date:
Maciej Piekielniak <piechcio@isb.com.pl> writes:
>    I try copy my old database with large objects from postgresql7.4 to
>    postgresql-8.1.
>    How can I dump database with lo? pg_dumpall don't save lo.

Use 8.1's pg_dump and pg_dumpall to dump from the old server.  We fixed
that problem finally.
        regards, tom lane


Re: dump with lo

From
Maciej Piekielniak
Date:
Hello Tom,

Thursday, March 2, 2006, 4:21:22 PM, you wrote:

TL> Maciej Piekielniak <piechcio@isb.com.pl> writes:
>>    I try copy my old database with large objects from postgresql7.4 to
>>    postgresql-8.1.
>>    How can I dump database with lo? pg_dumpall don't save lo.

TL> Use 8.1's pg_dump and pg_dumpall to dump from the old server.  We fixed
TL> that problem finally.

TL>                         regards, tom lane

 I try to copy pg_wrapper,PgCommon.pm to /usr/bin but when i type pg_dump -V i got: Argument "." isn't numeric in
numericlt(<) at PgCommon.pm line 439 Error: You must install at least one postgresql-client-<version>package
 
 I have debian sarge

-- 
Best regards,Maciej                            mailto:piechcio@isb.com.pl



Sequential scan where Index scan expected.

From
Bryce Nesbitt
Date:
I'm getting sequential scans (and poor performance), on scans using my
primary keys.
Can anyone help figure out why?

demo=# \d xx_thing                    Table "public.xx_thing"        Column          |            Type             |
Modifiers
-------------------------+-----------------------------+-----------thing_id              | bigint
|not null
 
thing_model           | character varying(128)      |thing_color           | character varying(128)      |thing_year
       | integer                     |
 
Indexes:   "xx_thing_pkey" primary key, btree (thing_id)


demo=# analyze verbose xx_thing_event;
INFO:  analyzing "public.xx_thing_event"
INFO:  "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows


demo=# explain update xx_thing_event set thing_color='foo' where
thing_event_id=10000;                            QUERY PLAN
---------------------------------------------------------------------Seq Scan on xx_thing_event  (cost=0.00..5842.48
rows=1width=110)  Filter: (thing_event_id = 10000)
 
(2 rows)




Sequential scan where Index scan expected (update)

From
Bryce Nesbitt
Date:
I'm getting sequential scans (and poor performance), on scans using my
primary keys.  This is an older postgres.
Can anyone help figure out why?


demo=# \d xx_thing                    Table "public.xx_thing"        Column          |            Type             |
Modifiers
-------------------------+-----------------------------+-----------thing_id              | bigint
|not null
 
thing_model           | character varying(128)      |thing_color           | character varying(128)      |thing_year
       | integer                     |
 
Indexes:   "xx_thing_pkey" primary key, btree (thing_id)


demo=# analyze verbose xx_thing_event;
INFO:  analyzing "public.xx_thing_event"
INFO:  "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows


demo=# explain update xx_thing_event set thing_color='foo' where
thing_event_id=10000;                            QUERY PLAN
---------------------------------------------------------------------Seq Scan on xx_thing_event  (cost=0.00..5842.48
rows=1width=110)  Filter: (thing_event_id = 10000)
 
(2 rows)



demo=# select * from version();                                                version
----------------------------------------------------------------------------------------------------------PostgreSQL
7.4.1on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
 
20030502 (Red Hat Linux 3.2.3-20)
(1 row)



Re: Sequential scan where Index scan expected (update)

From
Ragnar
Date:
On fim, 2006-03-02 at 23:28 -0800, Bryce Nesbitt wrote:
> I'm getting sequential scans (and poor performance), on scans using my
> primary keys.  This is an older postgres.
> Can anyone help figure out why?
> 
> 
> demo=# \d xx_thing
>                      Table "public.xx_thing"
>          Column          |            Type             | Modifiers
> -------------------------+-----------------------------+-----------
>  thing_id              | bigint                      | not null
...
> demo=# explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000;
>                              QUERY PLAN
> ---------------------------------------------------------------------
>  Seq Scan on xx_thing_event  (cost=0.00..5842.48 rows=1 width=110)
...
> demo=# select * from version();
>                                                  version
> ----------------------------------------------------------------------------------------------------------
>  PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3

try one of: ... WHERE thing_event_id=10000::bigint ... WHERE thing_event_id='10000'

or upgrade to >= 8.0

your problem is that the 10000 in 'thing_event_id=10000' is parsed as an
integer but not a bigint, so the planner
does not find a matching index. so you either have to
scpecify ::bigint or ::int8 in the query or quote the number, which will
cause postgres to cast it to the 
column's type.

if you try  ... WHERE thing_event_id=10000000000
you will see the index used because this number cannot 
be a int4 so is parsed as a bigint.

newer versions of Postgresql can deal with this.

gnari






Re: Sequential scan where Index scan expected (update)

From
Andrew Sullivan
Date:
On Thu, Mar 02, 2006 at 11:28:49PM -0800, Bryce Nesbitt wrote:
> Can anyone help figure out why?

Well. . .

> 
> 
> demo=# \d xx_thing           ^^^^^^^^

> -------------------------+-----------------------------+-----------
>  thing_id              | bigint                      | not null                          ^^^^^^

> demo=# explain update xx_thing_event set thing_color='foo' where                       ^^^^^^^^^^^^^^
. . .you haven't actually given us the right schema here, but if I
had to guess, I'd say you could put this
> thing_event_id=10000;                ^^^^^

in quotes.  The automatic int4-int8 coercion is probably your
problem.  Also

> ----------------------------------------------------------------------------------------------------------
>  PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3             ^^^^^

you need to upgrade PostgreSQL Right Now.  There are serious problems
with earlier 7.4 releases.  Get the latest, or risk data corruption.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes


Re: Sequential scan where Index scan expected (update)

From
Bryce Nesbitt
Date:
Andrew Sullivan wrote:<br /><blockquote cite="mid20060303124712.GC7057@phlogiston.dyndns.org" type="cite"><blockquote
type="cite"><prewrap="">thing_event_id=10000;   </pre></blockquote><pre wrap="">                 ^^^^^
 

in quotes.  The automatic int4-int8 coercion is probably your
problem.  Also </pre></blockquote> Yup that's it.  But this project uses (ugh) Hibernate.  I can't change it.  I may
haveto change<br /> from BIGINT primary keys to INT.<br /><br /> Also:<br /> Any hints on the table statistics?  I turn
themon, in a session, as user postgres, but get nothing:<br /><br /><tt>stage=# SHOW ALL;<br />              
name             |    setting<br /> --------------------------------+----------------<br /> ...<br />
 stats_block_level             | on<br />  stats_command_string           | on<br />  stats_reset_on_server_start    |
on<br/>  stats_row_level                | on<br />  stats_start_collector          | on<br /><br /> stage=# select *
frompg_statio_user_indexes;<br />   relid   | indexrelid | schemaname |           relname           |          
indexrelname          | idx_blks_read | idx_blks_hit<br />
----------+------------+------------+-----------------------------+----------------------------------+---------------+--------------<br
/> 18810975 |   18811183 | public     | eg_invoice                  | ix22f7bc70c7de2059               |             0
|           0<br />  18810979 |   18811184 | public     | eg_order                    |
ix522779518edf278d              |             0 |            0<br />  18810984 |   18811185 | public     |
eg_invoice_tax             | ix2f10773c8edf278d               |             0 |            0<br />  18810986 |  
18811186| public     | eg_order_line               | ixf8331222783867cc               |             0 |            0<br
/></tt><br/> 

Re: Sequential scan where Index scan expected (update)

From
Andrew Sullivan
Date:
On Fri, Mar 03, 2006 at 11:35:55AM -0800, Bryce Nesbitt wrote:
> Yup that's it.  But this project uses (ugh) Hibernate.  I can't change
> it.  I may have to change
> from BIGINT primary keys to INT.
> 

Well, you could upgrade from 7.4.  

> Also:
> Any hints on the table statistics?  I turn them on, in a session, as
> user postgres, but get nothing:

You have to SIGHUP to get those to take effect, IIRC.

A
-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Information security isn't a technological problem.  It's an economics
problem.    --Bruce Schneier


Re: Sequential scan where Index scan expected.

From
"Nikolay Samokhvalov"
Date:
please, show '\d xx_thing_event' too :-)

On 3/3/06, Bryce Nesbitt <bryce1@obviously.com> wrote:
> I'm getting sequential scans (and poor performance), on scans using my
> primary keys.
> Can anyone help figure out why?
>
> demo=# \d xx_thing
>                      Table "public.xx_thing"
>          Column          |            Type             | Modifiers
> -------------------------+-----------------------------+-----------
>  thing_id              | bigint                      | not null
> thing_model           | character varying(128)      |
>  thing_color           | character varying(128)      |
>  thing_year            | integer                     |
> Indexes:
>     "xx_thing_pkey" primary key, btree (thing_id)
>
>
> demo=# analyze verbose xx_thing_event;
> INFO:  analyzing "public.xx_thing_event"
> INFO:  "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
> total rows
>
>
> demo=# explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000;
>                              QUERY PLAN
> ---------------------------------------------------------------------
>  Seq Scan on xx_thing_event  (cost=0.00..5842.48 rows=1 width=110)
>    Filter: (thing_event_id = 10000)
> (2 rows)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--
Best regards,
Nikolay

Re: dump with lo

From
Markus Schaber
Date:
Hi, Marciej,

Maciej Piekielniak wrote:

> TL> Use 8.1's pg_dump and pg_dumpall to dump from the old server.  We fixed
> TL> that problem finally.
> 
>   I try to copy pg_wrapper,PgCommon.pm to /usr/bin but when i type
>   pg_dump -V i got:
>   Argument "." isn't numeric in numeric lt(<) at PgCommon.pm line 439
>   Error: You must install at least one
>   postgresql-client-<version>package

pg_wrapper etc. are debian specific and somewhat fragile if you don't
strictly obey the debian multi-cluster concept. Using a 8.1 client
against a 7.4 server is one of those problematic cases.

Install the postgresql-client-8.1 debian package and use
/usr/lib/postgresql/8.1/bin/psql directly.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org