Re: pg_dump and pgpool

Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:

> On Wed, 2004-12-29 at 23:12, Greg Stark wrote:
> > Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:
> >
> > > What's happening is that there are two databases behind pgpool, and each
> > > has managed to assign a different (set of) OID(s) to the table(s). So,
> > > when pg_dump asks for an OID, it gets two different ones.
> >
> > If pgpool is so good at maintaining consistency between databases how did they
> > end up with different OIDs?
>
> That's rather disingenuous, considering that OIDs are more of an
> internal artifact of the databases, while the USER data stored therein
> is what I, or anyone else, would consider the word consistency applies
> to. The fact that both databases have different OIDs for the same
> objects has nothing to do with the userland data being consistent or
> not.

It's not like the database uses random() to generate OIDs. To reach different
OIDs you would have had to issue a different sequence of DDL statements.

It's a bit of a pain since there's no sure way to resync the databases using
DDL. But surely if you dumped one of the copies and restored a clean copy of
the database on both machines they would end up with consistent OIDs?

Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:

> No. I have a perfectly consistent database. It happens to reside on a
> cluster of two machines which have different internal ids assigned to
> the same objects, which, when I throw bog standard SQL at them, I get
> the same answer from both. That is consistent.

But you're not throwing BOG-standard SQL at them, you're running pg_dump
against them which is using non-BOG-standard SQL. If you want pg_dump to work
against them I think you need to keep them consistent at a lower level.

> Users are consistently warned to never use OIDs as PKs, yet PostgreSQL
> the database does just that. My data is coherent. I'll explain more in
> my reply to Tom Lane...

If Postgres used sequences then your sequences would be out of sync. The point
is that at the level pg_dump is working the databases really are inconsistent.

Perhaps one day pg_dump could be reimplemented entirely in terms of
information_schema where the inconsistencies happen to be hidden. But I doubt
it can be today. And I suspect you could arrive at inconsistent
information_schema if you use different ddl anyways.

--
greg