Re: quick question abt pg_dump and restore

am  Wed, dem 09.01.2008, um 14:07:13 +0000 mailte Raymond O'Donnell folgendes:

> On 09/01/2008 14:02, Josh Harrison wrote:
>
> >When restoring the pg_dumped data thro psql does the rows of the table
> >are restored in the same order? ie for example if
> >Table A has rows r1,r2,r3,r4,r5 in this order, then if I pg_dump and
> >restore it to another database, will it have the rows in the same order
> >r1,r2,r3,r4,r5? Does this apply to big tables also?
>
> If you use the text dump format, you can see the order in which the rows
> are restored.

Right, but within the table the rows hasn't an order. You can see this
when you select the ctid-column:

test=# create table order_test (i int);
CREATE TABLE
test=*# insert into order_test values (1);
INSERT 0 1
test=*# insert into order_test values (2);
INSERT 0 1
test=*# insert into order_test values (3);
INSERT 0 1
test=*# select ctid, i from order_test ;
 ctid  | i
-------+---
 (0,1) | 1
 (0,2) | 2
 (0,3) | 3
(3 rows)

test=*# update order_test set i=20 where i=2;
UPDATE 1
test=*# update order_test set i=2 where i=20;
UPDATE 1
test=*# select ctid, i from order_test ;
 ctid  | i
-------+---
 (0,1) | 1
 (0,3) | 3
 (0,5) | 2
(3 rows)



Now a pg_dump:

ALTER TABLE public.order_test OWNER TO webmaster;

--
-- Data for Name: order_test; Type: TABLE DATA; Schema: public; Owner:
webmaster
--

COPY order_test (i) FROM stdin;
1
3
2
\.


Now the question: what is the correct order?