Palle Girgensohn (g...@pingpong.net)

--On torsdag, december 06, 2007 12.22.45 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> Is the real reason for the negative answer that there is a "hole" in the
>> pg_xlogs from 3 am - 9 am?
>
> That's half of it. The other half is that what you have from 3am is a
> pg_dump, not a filesystem-level snapshot of the database, and you'd need
> the latter to be able to replay the WAL log against.

OK, my assumptions where correct. Thanks.

/Palle

--On torsdag, december 06, 2007 17.45.57 +0100 Palle Girgensohn
<girgen@pingpong.net> wrote:

>
>
> --On torsdag, december 06, 2007 10.41.40 -0600 Scott Marlowe
> <scott.marlowe@gmail.com> wrote:
>
>> On Dec 6, 2007 10:24 AM, Palle Girgensohn <girgen@pingpong.net> wrote:
>>> Hi!
>>>
>>> A user accidentally deleted some results (i.e. tuples in a postgresql
>>> database) created this morning.
>>>
>>> I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab
>>> out 3 o'clock this morning.
>>>
>>> The results where created from about nine o'clock until noon, when the
>>> user accidentally removed the results.
>>>
>>> Would it be possible to replay the WAL logs and get the results from
>>> pg_xlog + the dump?
>>
>> No.
>>
>>> Or from a file system snapshot as of now (five pm) + pg_xlog?
>>
>> No.
>
> So, I *must* have an image of the entire database, and I *must* have all
> WAL files since the image snapshot was taken?

Is the real reason for the negative answer that there is a "hole" in the
pg_xlogs from 3 am - 9 am?

Palle

--On torsdag, december 06, 2007 10.41.40 -0600 Scott Marlowe
<scott.marlowe@gmail.com> wrote:

> On Dec 6, 2007 10:24 AM, Palle Girgensohn <girgen@pingpong.net> wrote:
>> Hi!
>>
>> A user accidentally deleted some results (i.e. tuples in a postgresql
>> database) created this morning.
>>
>> I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab
>> out 3 o'clock this morning.
>>
>> The results where created from about nine o'clock until noon, when the
>> user accidentally removed the results.
>>
>> Would it be possible to replay the WAL logs and get the results from
>> pg_xlog + the dump?
>
> No.
>
>> Or from a file system snapshot as of now (five pm) + pg_xlog?
>
> No.

So, I *must* have an image of the entire database, and I *must* have all
WAL files since the image snapshot was taken?

/Palle

Hi!

A user accidentally deleted some results (i.e. tuples in a postgresql
database) created this morning.

I have pg_xlog from about 9 o'clock until now. I have a pg_dump from ab out
3 o'clock this morning.

The results where created from about nine o'clock until noon, when the user
accidentally removed the results.

Would it be possible to replay the WAL logs and get the results from
pg_xlog + the dump? Or from a file system snapshot as of now (five pm) +
pg_xlog?

Regards,
Palle

--On torsdag, november 01, 2007 11.34.42 -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> --On torsdag, november 01, 2007 11.06.57 -0400 Tom Lane
>> <tgl@sss.pgh.pa.us>  wrote:
>>> Um, you did re-ANALYZE the table after changing the setting?
>
>> alter table login alter userid SET statistics 1000;
>> vacuum analyze login;
>
> Hm, that's the approved procedure all right. But the plans didn't
> change at all? Not even the estimated number of rows?

Estimated number of rows did change from ~400 to ~1900, but the timing was
the same.

Seems that the problem is that it is using an index on "when". Removing
that index (login_when_idx) changes the plan, and makes the query equally
fast whether group by is there or not. I may need the index, though, in
which one more index, on (userid, "when"), will fix the problem. I'd rather
get rid of an index than creating another one.

Anyway, I think I have two suggestions for a solution that will work for
me. I still think it is strange that the group by clause so radically
changes the behaviour and the query time.

Cheers,
Palle

pp=# \d login
                  Table "public.login"
 Column |           Type           |     Modifiers
--------+--------------------------+--------------------
 userid | text                     |
 kursid | integer                  |
 when   | timestamp with time zone |
 mode   | text                     | default 'pm'::text
Indexes:
    "login_kurs_user_idx" btree (kursid, userid)
    "login_userid_idx" btree (userid)
    "login_when_idx" btree ("when")
Foreign-key constraints:
    "pp_fk1" FOREIGN KEY (userid) REFERENCES person(userid) ON UPDATE 
CASCADE ON DELETE CASCADE
    "pp_fk2" FOREIGN KEY (kursid) REFERENCES course(id) ON UPDATE CASCADE 
ON DELETE CASCADE