Обсуждение: dump with lo
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
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
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
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)
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)
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
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
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/>
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
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
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