Postgres under CygWin

"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

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

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

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

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

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

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

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:

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:

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.

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