Thread: Postgres under CygWin

Postgres under CygWin

From
"Nigel J. Andrews"
Date:

No, this isn't part of the native/cygwin arguement.

I've followed the discussions on this topic and I've also been contacted
regarding the possibility of doing some work relating to moving from a Linux
host to a Windows host. Now I've no idea why these people's client is insisting
on the move and I've tried to give the impression I think it a silly thing to
do but it remains that this is what they are trying to do.

The problem I have is that I've not done anything with postgres under windows
at all. Now, I'm prepared to invest the time installing and having a little
play around with things before saying 'yes I can do it'. However, what is
worrying me is that this 'research' is unlikely to show up any problems
associated with moving data between the systems. Are there any such problems or
is it how I think it'll be and the restore under Windows will have no problems
with the data dumped under Linux?

Thanks in advance for any clarification on this,

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants


Re: Postgres under CygWin

From
Tom Lane
Date:
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> However, what is worrying me is that this 'research' is unlikely to
> show up any problems associated with moving data between the
> systems. Are there any such problems or is it how I think it'll be and
> the restore under Windows will have no problems with the data dumped
> under Linux?

I can't think of any reason to expect problems at that stage.  Our dump
files are usually pretty portable across different systems.

            regards, tom lane

Re: Postgres under CygWin

From
Doug Fields
Date:
>I can't think of any reason to expect problems at that stage.  Our dump
>files are usually pretty portable across different systems.

FWIW, I agree with Tom.

I ported our 200+ meg database from Linux to Cygwin (back when 7.1.3 was
the most recent, perhaps six months ago) to test to see if it would be
possible to run our application on a "single Windows box" for use at trade
shows, merely as a demonstration.

I had no problems loading the dumps and running the app. It was vastly less
friendly than running on Linux, of course, but that is not unexpected.

I can't speak to stability though; I didn't do extensive testing.

Doug



Altering existing table to be WITHOUT OIDs

From
Doug Fields
Date:
Hello again,

The documentation does not give any information about how I might do this
in the ALTER TABLE, so it may not be possible, at least, not that way.

Now that I have migrated to 7.2.1 and stabilized, I want to disable OIDs on
all of my tables which have their own sequence primary keys.

How can I do this? (I don't want to know how I figure out what tables they
are - just how, given a table, I convert  to WITHOUT OIDs.)

Thanks,

Doug


Re: Altering existing table to be WITHOUT OIDs

From
Neil Conway
Date:
On Fri, 24 May 2002 13:45:48 -0400
"Doug Fields" <dfields-pg-general@pexicom.com> wrote:
> Now that I have migrated to 7.2.1 and stabilized, I want to disable OIDs on
> all of my tables which have their own sequence primary keys.
>
> How can I do this? (I don't want to know how I figure out what tables they
> are - just how, given a table, I convert  to WITHOUT OIDs.)

I don't know of a way. However, keep in mind that WITHOUT OIDS is not
(currently) a storage optimization -- the OID field is still stored on disk.
The WITHOUT OIDS option is primarily for people who might be in danger
of OID-wraparound.

So I wouldn't be too concerned about it.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Altering existing table to be WITHOUT OIDs

From
Doug Fields
Date:
>I don't know of a way. However, keep in mind that WITHOUT OIDS is not
>(currently) a storage optimization -- the OID field is still stored on disk.
>The WITHOUT OIDS option is primarily for people who might be in danger
>of OID-wraparound.

Thanks. Yes, I am aware that it is not a storage optimization; however, I
may be adding 25 million + records to some tables each day, and this will
quickly wrap any 31 or 32 bit number. I actually use OIDs on one table as a
pkey (really a way to attempt the enforcing of a UNIQUE constraint without
the overhead of a 4 or 5 column index) and don't want them wrapping.

Cheers,

Doug



Re: Altering existing table to be WITHOUT OIDs

From
Tom Lane
Date:
Doug Fields <dfields-pg-general@pexicom.com> writes:
> The documentation does not give any information about how I might do this
> in the ALTER TABLE, so it may not be possible, at least, not that way.

ALTER TABLE doesn't support it, but you could reach in and tweak
pg_class.relhasoids for your tables.  I think you would also need to
delete the pg_attribute row for oid for each such table if you wanted
to have a perfectly clean result.

            regards, tom lane

Removing Referential Integrity

From
Doug Fields
Date:
Hi all,

In 7.2.1...

I have a few tables built with REFERENCES for which I would like to
permanently remove these constraints in the search of higher performance
INSERTs.

 From pg_dump, I see these commands:

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'accounts';
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where
pg_class.oid = tgrelid) WHERE relname = 'accounts';

I'm not sure, however, if that actually permanently removes the CONSTRAINT
TRIGGER. There does not seem to be an equivalent REMOVE CONSTRAINT TRIGGER,
and DROP TRIGGER won't work on the trigger reported by psql's \d command.

Any thoughts?

Thanks,

Doug


Re: Removing Referential Integrity

From
Stephan Szabo
Date:
On Sat, 25 May 2002, Doug Fields wrote:

> Hi all,
>
> In 7.2.1...
>
> I have a few tables built with REFERENCES for which I would like to
> permanently remove these constraints in the search of higher performance
> INSERTs.
>
>  From pg_dump, I see these commands:
>
> -- Disable triggers
> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'accounts';
> -- Enable triggers
> UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where
> pg_class.oid = tgrelid) WHERE relname = 'accounts';
>
> I'm not sure, however, if that actually permanently removes the CONSTRAINT
> TRIGGER. There does not seem to be an equivalent REMOVE CONSTRAINT TRIGGER,
> and DROP TRIGGER won't work on the trigger reported by psql's \d command.
>
> Any thoughts?

Drop trigger should work fine as long as you double quote the names
because they're mixed case.


Re: Altering existing table to be WITHOUT OIDs

From
Doug Fields
Date:
For the benefit for anyone who may search this list in the future for this
information, I used the following queries to implement Tom's suggestion on
the table "list_entries" in this example:

UPDATE pg_class SET relhasoids=false WHERE relname='list_entries';
DELETE FROM pg_attribute
     WHERE attrelid = (SELECT oid FROM pg_class WHERE relname =
'list_entries')
         AND attname='oid';

You might want to also include the reltype in the sub-SELECT if you re-use
names willy-nilly.

Cheers,

Doug


At 04:06 PM 5/24/2002, Tom Lane wrote:
>Doug Fields <dfields-pg-general@pexicom.com> writes:
> > The documentation does not give any information about how I might do this
> > in the ALTER TABLE, so it may not be possible, at least, not that way.
>
>ALTER TABLE doesn't support it, but you could reach in and tweak
>pg_class.relhasoids for your tables.  I think you would also need to
>delete the pg_attribute row for oid for each such table if you wanted
>to have a perfectly clean result.
>
>                         regards, tom lane



Re: Altering existing table to be WITHOUT OIDs

From
Larry Rosenman
Date:
On Sat, 2002-05-25 at 15:17, Doug Fields wrote:
> For the benefit for anyone who may search this list in the future for this
> information, I used the following queries to implement Tom's suggestion on
> the table "list_entries" in this example:
>
> UPDATE pg_class SET relhasoids=false WHERE relname='list_entries';
> DELETE FROM pg_attribute
>      WHERE attrelid = (SELECT oid FROM pg_class WHERE relname =
> 'list_entries')
>          AND attname='oid';
>
> You might want to also include the reltype in the sub-SELECT if you re-use
> names willy-nilly.
Thanks Doug.  Worked great for a table I forgot to include the WITHOUT
OIDS option on.  (gets 4-5000 rows every 5 minutes :-) ).


>
> Cheers,
>
> Doug
>
>
> At 04:06 PM 5/24/2002, Tom Lane wrote:
> >Doug Fields <dfields-pg-general@pexicom.com> writes:
> > > The documentation does not give any information about how I might do this
> > > in the ALTER TABLE, so it may not be possible, at least, not that way.
> >
> >ALTER TABLE doesn't support it, but you could reach in and tweak
> >pg_class.relhasoids for your tables.  I think you would also need to
> >delete the pg_attribute row for oid for each such table if you wanted
> >to have a perfectly clean result.
> >
> >                         regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749