Re: [HACKERS] localization problem (and solution)
> The issue is that if I set my machine's locale to Turkish or > French, say, it doesn't matter what locale I set during > initdb or in postgresql.conf, the server's log messages > always seem to come out in the machine's locale. Does this happen only for those locales? And how specifically do you set the locale? I just installed to verify, and my server goes up in english no problem, even though my locale is set to swedish. The client tools (psql, for example) come up in swedish, so it's definitly swedish locale. And by donig "set LANG=en" before I start psql, it comes up in english just fine. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] localization problem (and solution)
> > The issue is that if I set my machine's locale to Turkish > or French, > > say, it doesn't matter what locale I set during initdb or in > > postgresql.conf, the server's log messages always seem to > come out in > > the machine's locale. > > Does this happen only for those locales? And how specifically > do you set the locale? > > I just installed to verify, and my server goes up in english > no problem, even though my locale is set to swedish. The > client tools (psql, for > example) come up in swedish, so it's definitly swedish > locale. And by donig "set LANG=en" before I start psql, it > comes up in english just fine. I should probably say this is 8.1.1, not cvs head, but I don't recall any changes around this. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Removing SORTFUNC_LT/REVLT
On Wed, Dec 28, 2005 at 07:38:36PM -0500, Tom Lane wrote: > Martijn van Oosterhout writes: > > The issue is whether anything you want to ORDER BY needs to be > > described by an B-tree operator class, and hence have a real sort > > function. > > I think it's reasonable to remove that feature, *after* we provide > a workable substitute. So, "no" to both questions ... Hmm. By feature I assume you mean "ORDER BY ... USING" (which no-one could find an example of) and not "requiring the operator to be part of an opclass". The only people affected would be people who defined a less-than operator but no operator class, which you said yourself would probably just be encouraging programmer lazyness. I wasn't suggesting removing the ORDER BY ... USING syntax, just these two options from the sorting routines. In fact, I don't think we ever need to remove the syntax, just as long as the operator is part of an operator class, it'll be fine. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpiZF30UI8xD.pgp Description: PGP signature
[HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to Make failure
Looks like the plperl changes hurt Snake :-( C:/Perl/lib/CORE/win32iop.h:304:1: warning: "kill" redefined In file included from ../../../src/include/c.h:821, from ../../../src/include/postgres.h:48, from SPI.xs:2: ../../../src/include/port.h:197:1: warning: this is the location of the previous definition dlltool --export-all --output-def plperl.def plperl.o spi_internal.o SPI.o dllwrap -o libplperl.dll --dllname libplperl.dll --def plperl.def plperl.o spi_internal.o SPI.o -LC:/Perl/lib/CORE -L../../../src/backend -L../../../src/port -lperl58 -lpostgres Info: resolving _check_function_bodies by linking to __imp__check_function_bodies (auto-import) fu01.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname' nmth00.o(.idata$4+0x0): undefined reference to `_nm__check_function_bodies' collect2: ld returned 1 exit status c:\MinGW\bin\dllwrap.exe: c:\MinGW\bin\gcc exited with status 1 make[3]: *** [libplperl.a] Error 1 make[3]: Leaving directory `/usr/local/build-farm/HEAD/pgsql.5484/src/pl/plperl' make[2]: *** [all] Error 1 make[2]: Leaving directory `/usr/local/build-farm/HEAD/pgsql.5484/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/local/build-farm/HEAD/pgsql.5484/src' make: *** [all] Error 2 Regards, Dave -Original Message- From: PG Build Farm [mailto:[EMAIL PROTECTED] Sent: Thu 12/29/2005 2:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: PGBuildfarm member snake Branch HEAD Status changed from OK to Make failure The PGBuildfarm member snake had the following event on branch HEAD: Status changed from OK to Make failure The snapshot timestamp for the build that triggered this notification is: 2005-12-29 02:00:00 The specs of this machine are: OS: Windows / Server 2003 SP1 Arch: i686 Comp: gcc / 3.4.2 For more information, see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snake&br=HEAD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Missing DATE selectivity
On Tue, 2005-12-27 at 23:00 -0500, Bruce Momjian wrote: > Simon Riggs wrote: > > The TODO list has an item "add missing date selectivity", which is a > > hang-over from 1999 or before. > > > > Is this still an issue? Can somebody describe exactly what it is? > > > > I can't find the exact discussion of what the problem was/is, so its > > better to ask directly... > > Good question. I see DATEOID in selfuncs.c, so my guess is that at one > time we didn't have it, but now we do, and I never realized it. I have > removed the mention and updated the entry to: > > * Add missing rtree optimizer selectivity > I thought rtree was no longer supported either... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] WAL logs multiplexing?
On Thu, 2005-12-29 at 10:47 +0300, Dmitry Panov wrote: > On Wed, 2005-12-28 at 11:05 -0500, Tom Lane wrote: > > Dmitry Panov <[EMAIL PROTECTED]> writes: > > > Yes, but if the server has crashed earlier the script won't be called > > > and if the filesystem can't be recovered the changes will be lost. My > > > point is the server should write into both (or more) files at the same > > > time. > > > > As for that, I agree with the other person: a RAID array does that just > > fine, and with much higher performance than we could muster. > > > > BTW, I found something related in the TODO: > http://momjian.postgresql.org/cgi-bin/pgtodo?pitr > > I think both approaches have the right to exist, but I prefer my because > it looks more straightforward, it insures up-to-date recovery (no > delays) and it reduces the traffic (as the partial logs have to be > transferred in full by the proposed "archive_current_wal_command"). The > only drawback is performance. Simply replicating pg_xlog might be worthwhile for the truly paranoid, since it does help in the situation that you lose the RAID unit with your pg_xlog on it. But this facility is already available via hardware replication facilities, so I see no reason to build it into the DBMS. Replicating pg_xlog to NFS would not work very well performance wise and has some major undefined behaviour in most failure modes, so I would never do that. However, there is a case to be made for "continuous xlog record archival" which could get closer to 0% data loss in the event of failure, though with higher performance hit than current PITR. I'll look into that some more - but no promises. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Wed, 2005-12-28 at 20:58 -0500, Bruce Momjian wrote:
> Having read through this thread, I would like to propose a
> syntax/behavior.
>
> I think we all now agree that the logging is more part of the table than
> the command itself. Right now we have a COPY LOCK patch, but people are
> going to want to control logging for INSERT INTO ... SELECT, and UPDATE,
> and all sorts of other things, so I think we are best adding an ALTER
> TABLE capability. I am thinking of this syntax:
>
> ALTER TABLE name RELIABILITY option
>
> where "option" is:
>
> DROP [ TABLE ON CRASH ]
> DELETE [ ROWS ON CRASH ]
> EXCLUSIVE
> SHARE
>
> Let me explain each option. DROP would drop the table on a restart
> after a non-clean shutdown. It would do _no_ logging on the table and
> allow concurrent access, plus index access. DELETE is the same as DROP,
> but it just truncates the table (perhaps TRUNCATE is a better word).
>
> EXCLUSIVE would allow only a single session to modify the table, and
> would do all changes by appending to the table, similar to COPY LOCK.
> EXCLUSIVE would also not allow indexes because those can not be isolated
> like appending to the heap. EXCLUSIVE would write all dirty shared
> buffers for the table and fsync them before committing. SHARE is the
> functionality we have now, with full logging.
>
> Does this get us any closer to a TODO item? It isn't great, but I think
> it is pretty clear, and I assume pg_dump would use ALTER to load each
> table. The advanage is that the COPY statements themselves are
> unchanged so they would work in loading into older versions of
> PostgreSQL.
First off, thanks for summarising a complex thread.
My view would be that this thread has been complex because everybody has
expressed a somewhat different requirement, which could be broken down
as:
1. The need for a multi-user-accessible yet temporary table
2. Loading data into a table immediately after it is created (i.e. in
same transaction), including but not limited to a reload from pg_dump
3. How to load data quickly into an existing table (COPY)
4. How to add/modify data quickly in an existing table (INSERT SELECT,
UPDATE)
I can see the need for all of those individually; my existing patch
submission covers (2) and (3) only. I very much like your thought to
coalesce these various requirements into a single coherent model.
For requirement (1), table level options make sense. We would:
- CREATE TABLE ALLTHINGS
- ALTER TABLE ALLTHINGS RELIABILITY DELETE ROWS ON RECOVERY
- lots of SQL, all fast because not logged
(2) is catered for adequately by the existing COPY patch i.e. it will
detect whether a table has just been created and then avoid writing WAL.
In the patch, pg_dump has *not* been altered to use COPY LOCK, so a
pg_dump *will* work with any other version of PostgreSQL, which *would
not* be the case if we added ALTER TABLE ... RELIABILITY statements into
it. Also, a pg_dump created at an earlier version could also be loaded
faster using the patch. The only requirement is to issue all SQL as part
of the same transaction - which is catered for by the
--single-transaction option on pg_restore and psql. So (2) is catered
for fully without the need for an ALTER TABLE ... RELIABILITY statement
or COPY LOCK.
For requirement (3), I would use table level options like this:
(the table already exists and is reasonably big; we should not assume
that everybody can and does use partitioning)
- ALTER TABLE RELIABILITY ALLTHINGS2 EXCLUSIVE
- COPY
- ALTER TABLE RELIABILITY ALLTHINGS2 SHARE
For a load into an existing table I would always do all three actions
together. COPY LOCK does exactly that *and* does it atomically.
The two ways of doing (3) have a few pros/cons either way:
Pro for ALTER TABLE:
- same syntax as req (1)
- doesn't need the keyword LOCK
- allows INSERT SELECT, UPDATE operations also (req 4)
Cons:
- existing programs have to add additional statements to take advantage
of this; with COPY LOCK we would add just a single keyword
- operation is not atomic, which might lead to some operations waiting
for a lock to operate as unlogged, since they would execute before the
second ALTER TABLE gets there
- operation will be understood by some, but not others. They will forget
to switch the RELIABILITY back on and then lose their whole table when
the database crashes. (watch...)
...but would it be a problem to have both?
So, my thinking would be to separate things into two:
a) Add a TODO item "shared temp tables" that caters for (1) and (4)
ALTER TABLE name RELIABILITY
{DELETE ROWS AT RECOVERY | FULL RECOVERY}
(syntax TBD)
which would
- truncate all rows and remove all index entries during recovery
- use shared_buffers, not temp_buffers
- never write xlog records, even when in PITR mode
- would avoid writing WAL for both heap *and* index tuples
b) Leave the COPY patch as is, since it caters for reqs (2) and (3) as
*separate* optimizations
Re: [HACKERS] localization problem (and solution)
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: We have a build failure to fix first: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=loris&dt=2005-12-29%2000:44:52 Weird. It seems to be choking on linking to check_function_bodies, but plpgsql does that exactly the same way, and there's no problem there. I wonder whether all those warnings in the perl header files mean anything ... I have committed a fix - the perl headers were mangling DLLIMPORT so I moved the declaration above the perl includes. I would also like to add -Wno-comment to the CFLAGS for win32/gcc, top suppress at least some of those warnings. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
> So, my thinking would be to separate things into two:
> a) Add a TODO item "shared temp tables" that caters for (1) and (4)
>
> ALTER TABLE name RELIABILITY
> {DELETE ROWS AT RECOVERY | FULL RECOVERY}
> (syntax TBD)
DELETE ROWS AT RECOVERY would need to be careful or disallowed when
referenced via a foreign key to ensure the database is not restored in
an inconsistent state.
--
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Thu, 2005-12-29 at 09:35 -0500, Rod Taylor wrote:
> > So, my thinking would be to separate things into two:
> > a) Add a TODO item "shared temp tables" that caters for (1) and (4)
> >
> > ALTER TABLE name RELIABILITY
> > {DELETE ROWS AT RECOVERY | FULL RECOVERY}
> > (syntax TBD)
>
> DELETE ROWS AT RECOVERY would need to be careful or disallowed when
> referenced via a foreign key to ensure the database is not restored in
> an inconsistent state.
I think we'd need to apply the same rule as we do for temp tables: they
cannot be referenced by a permanent table.
There are possibly some other restrictions also. Anyone?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Removing SORTFUNC_LT/REVLT
Martijn van Oosterhout writes: > Hmm. By feature I assume you mean "ORDER BY ... USING" (which no-one > could find an example of) and not "requiring the operator to be part of > an opclass". > In fact, I don't think we ever need to remove the syntax, just as long > as the operator is part of an operator class, it'll be fine. Well, no, that's not the problem: the problem is that you should be able to specify ORDER BY any sort ordering that the system can deal with, and the USING syntax is in fact too impoverished to do that. What if the mentioned operator is in more than one operator class? I believe that ATM the code makes a random choice of which opclass' sort function to use, which pretty much sucks. I haven't had time yet to digest the material you posted yesterday about COLLATE. Maybe there's a solution in there, but I think it could only happen if we assume that every potential sort operator appears in only one opclass. Which seems like a pretty restrictive assumption, even granted that COLLATE will start to carry some of the load of picking different sorting options. What I'd really like is to deprecate the "USING operator" syntax in favor of a "USING operatorclassname" syntax. Actually, "USING opclass [ASC/DESC]" would get the job done, since given an opclass you can certainly run the sort function either normal or reverse. We could keep the "USING operator" syntax but insist that it's only allowed if there's exactly one possible opclass mapping. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] localization problem (and solution)
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I would also like to add -Wno-comment to the CFLAGS for win32/gcc, top > suppress at least some of those warnings. Why don't you complain to the Perl people, instead? The fact that no such warnings occur on Unix Perl installations makes these seem pretty suspicious. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Bruce Momjian said: > DROP would drop the table on a restart > after a non-clean shutdown. It would do _no_ logging on the table and > allow concurrent access, plus index access. DELETE is the same as > DROP, but it just truncates the table (perhaps TRUNCATE is a better > word). > > EXCLUSIVE would allow only a single session to modify the table, and > would do all changes by appending to the table, similar to COPY LOCK. > EXCLUSIVE would also not allow indexes because those can not be > isolated like appending to the heap. EXCLUSIVE would write all dirty > shared buffers for the table and fsync them before committing. SHARE > is the functionality we have now, with full logging. I an horribly scared that this will be used as a "performance boost" for normal use. I would at least like to see some restrictions that make it harder to mis-use. Perhaps restrict to superuser? cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Missing DATE selectivity
Simon Riggs <[EMAIL PROTECTED]> writes: > On Tue, 2005-12-27 at 23:00 -0500, Bruce Momjian wrote: >> * Add missing rtree optimizer selectivity > I thought rtree was no longer supported either... We still have rtree-like opclasses. I agree the TODO item is worded in a way that makes it look obsolete though. How about * Improve selectivity functions for geometric operators regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Andrew Dunstan wrote: > Bruce Momjian said: > > DROP would drop the table on a restart > > after a non-clean shutdown. It would do _no_ logging on the table and > > allow concurrent access, plus index access. DELETE is the same as > > DROP, but it just truncates the table (perhaps TRUNCATE is a better > > word). > > > > EXCLUSIVE would allow only a single session to modify the table, and > > would do all changes by appending to the table, similar to COPY LOCK. > > EXCLUSIVE would also not allow indexes because those can not be > > isolated like appending to the heap. EXCLUSIVE would write all dirty > > shared buffers for the table and fsync them before committing. SHARE > > is the functionality we have now, with full logging. > > > I an horribly scared that this will be used as a "performance boost" for > normal use. I would at least like to see some restrictions that make it > harder to mis-use. Perhaps restrict to superuser? Certainly restrict to table owner. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Simon Riggs <[EMAIL PROTECTED]> writes: > My view would be that this thread has been complex because everybody has > expressed a somewhat different requirement, which could be broken down > as: > 1. The need for a multi-user-accessible yet temporary table > 2. Loading data into a table immediately after it is created (i.e. in > same transaction), including but not limited to a reload from pg_dump > 3. How to load data quickly into an existing table (COPY) > 4. How to add/modify data quickly in an existing table (INSERT SELECT, > UPDATE) > I can see the need for all of those individually; my existing patch > submission covers (2) and (3) only. I very much like your thought to > coalesce these various requirements into a single coherent model. However, you then seem to be arguing for still using the COPY LOCK syntax, which I think Bruce intended would go away in favor of using these ALTER commands. Certainly that's what I'd prefer --- COPY has got too darn many options already. > In the patch, pg_dump has *not* been altered to use COPY LOCK, so a > pg_dump *will* work with any other version of PostgreSQL, which *would > not* be the case if we added ALTER TABLE ... RELIABILITY statements into > it. Wrong --- the good thing about ALTER TABLE is that an old version of Postgres would simply reject it and keep going. Therefore we could get the speedup in dumps without losing compatibility, which is not true of COPY LOCK. BTW, this is a perfect example of the use-case for not abandoning a dump-file load simply because one command fails. (We have relied on this sort of reasoning many times before, too, for example by using "SET default_with_oids" in preference to CREATE TABLE WITH/WITHOUT OIDS.) I don't think that "wrap the whole load into begin/end" is really a very workable answer, because there are far too many scenarios where you can't do that. Another one where it doesn't help is a data-only dump. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] localization problem (and solution)
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I have committed a fix - the perl headers were mangling DLLIMPORT so I > moved the declaration above the perl includes. BTW, probably a cleaner answer is to put check_function_bodies into some header file instead of having an "extern" in the PLs' .c files. I was thinking about that yesterday, but couldn't decide where was a good place to put it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Bruce Momjian writes: > Andrew Dunstan wrote: >> I an horribly scared that this will be used as a "performance boost" for >> normal use. I would at least like to see some restrictions that make it >> harder to mis-use. Perhaps restrict to superuser? > Certainly restrict to table owner. I can see the argument for superuser-only: decisions about data integrity tradeoffs should be reserved to the DBA, who is the one who will get blamed if the database loses data, no matter how stupid his users are. But I'm not wedded to that. I could live with table-owner. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > My view would be that this thread has been complex because everybody has > > expressed a somewhat different requirement, which could be broken down > > as: > > 1. The need for a multi-user-accessible yet temporary table > > 2. Loading data into a table immediately after it is created (i.e. in > > same transaction), including but not limited to a reload from pg_dump > > 3. How to load data quickly into an existing table (COPY) > > 4. How to add/modify data quickly in an existing table (INSERT SELECT, > > UPDATE) > > > I can see the need for all of those individually; my existing patch > > submission covers (2) and (3) only. I very much like your thought to > > coalesce these various requirements into a single coherent model. > > However, you then seem to be arguing for still using the COPY LOCK > syntax, which I think Bruce intended would go away in favor of using > these ALTER commands. Certainly that's what I'd prefer --- COPY has > got too darn many options already. > > > In the patch, pg_dump has *not* been altered to use COPY LOCK, so a > > pg_dump *will* work with any other version of PostgreSQL, which *would > > not* be the case if we added ALTER TABLE ... RELIABILITY statements into > > it. > > Wrong --- the good thing about ALTER TABLE is that an old version of > Postgres would simply reject it and keep going. Therefore we could get > the speedup in dumps without losing compatibility, which is not true > of COPY LOCK. > > BTW, this is a perfect example of the use-case for not abandoning a > dump-file load simply because one command fails. (We have relied on > this sort of reasoning many times before, too, for example by using > "SET default_with_oids" in preference to CREATE TABLE WITH/WITHOUT OIDS.) > I don't think that "wrap the whole load into begin/end" is really a very > workable answer, because there are far too many scenarios where you > can't do that. Another one where it doesn't help is a data-only dump. Yep, Tom is echoing my reaction. There is a temptation to add things up onto existing commands, e.g. LOCK, and while it works, it makes for some very complex user API's. Having COPY behave differently because it is in a transaction is fine as long as it is user-invisible, but once you require users to do that to get the speedup, it isn't user-invisible anymore. (I can see it now, "Why is pg_dump putting things in transactions?", "Because it prevents it from being logged." "Oh, should I be doing that in my code?" "Perhaps, if you want ..." You can see where that discussion is going. Having them see "ATER TABLE ... RELIBILITY TRUNCATE" is very clear, and very clear on how it can be used in user code.) I think there is great utility in giving users one API, namely RELIABILITY (or some other keyword), and telling them that is where they control logging. I realize adding one keyword, LOCK, to an existing command isn't a big deal, but once you decentralize your API enough times, you end up with a terribly complex database system. It is this design rigidity that helps make PostgreSQL so much easier to use than other database systems. I do think it is valid concern about someone use the table between the CREATE and the ALTER TABLE RELIABILITY. One solution would be to allow the RELIABILITY as part of the CREATE TABLE, another is to tell users to create the table inside a transaction. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Missing DATE selectivity
Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Tue, 2005-12-27 at 23:00 -0500, Bruce Momjian wrote: > >> * Add missing rtree optimizer selectivity > > > I thought rtree was no longer supported either... > > We still have rtree-like opclasses. I agree the TODO item is worded > in a way that makes it look obsolete though. How about > > * Improve selectivity functions for geometric operators Thanks, updated. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] localization problem (and solution)
Tom Lane said: > Andrew Dunstan <[EMAIL PROTECTED]> writes: >> I would also like to add -Wno-comment to the CFLAGS for win32/gcc, top >> suppress at least some of those warnings. > > Why don't you complain to the Perl people, instead? The fact that no > such warnings occur on Unix Perl installations makes these seem pretty > suspicious. > Well, it's probably not even the Perl people - perl's config_h.SH seems to do the right thing and put a space between the second / and *, so that the compiler won't complain, so it could be ActiveState's doing. Maybe I'll just make a tiny script to fix config.h in my perl distro. There is a more serious problem, though, in these warnings. Perl is apparently trying to hijack the *printf functions, just as libintl tries to do. There's a #define we can set to inhibit that, and I think we should. That would leave 2 lots of warnings to fix - one about uid_t/gid_t and one about isnan. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char and i18n
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Euler Taveira de Oliveira wrote: > --- Euler Taveira de Oliveira <[EMAIL PROTECTED]> escreveu: > > > I have a patch like this. But this was for 7.4.x. I have to take a > > look > > at it. > > > The patch is attached. It implements day and month i18n. I fixed a few > misspelling comments. Docs is attached too. > > template1=# select to_char(now(), 'Day, DD Month '); >to_char > -- > Sunday , 25 December 2005 > (1 registro) > > template1=# select to_char(now(), 'TMDay, DD TMMonth '); > to_char > --- > Domingo, 25 Dezembro 2005 > (1 registro) > > template1=# > > > Comments? > > Euler Taveira de Oliveira > euler[at]yahoo_com_br > > > > > > > > > ___ > Yahoo! doce lar. Fa?a do Yahoo! sua homepage. > http://br.yahoo.com/homepageset.html Content-Description: 1242239392-i18n-date.diff [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] localization problem (and solution)
Tom Lane said: > Andrew Dunstan <[EMAIL PROTECTED]> writes: >> I have committed a fix - the perl headers were mangling DLLIMPORT so I >> moved the declaration above the perl includes. > > BTW, probably a cleaner answer is to put check_function_bodies into > some header file instead of having an "extern" in the PLs' .c files. I > was thinking about that yesterday, but couldn't decide where was a good > place to put it. > miscadmin.h ? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > Bruce Momjian said: > > DROP would drop the table on a restart > > after a non-clean shutdown. It would do _no_ logging on the table and > > allow concurrent access, plus index access. DELETE is the same as > > DROP, but it just truncates the table (perhaps TRUNCATE is a better > > word). > > > > EXCLUSIVE would allow only a single session to modify the table, and > > would do all changes by appending to the table, similar to COPY LOCK. > > EXCLUSIVE would also not allow indexes because those can not be > > isolated like appending to the heap. EXCLUSIVE would write all dirty > > shared buffers for the table and fsync them before committing. SHARE > > is the functionality we have now, with full logging. > > I an horribly scared that this will be used as a "performance boost" for > normal use. I would at least like to see some restrictions that make it > harder to mis-use. Perhaps restrict to superuser? Well that's its whole purpose. At least you can hardly argue that you didn't realize the consequences of "DELETE ROWS ON RECOVERY"... :) Some thoughts: a) I'm not sure I understand the purpose of EXCLUSIVE. When would I ever want to use it instead of DELETE ROWS? b) It seems like the other feature people were talking about of not logging for a table created within the same transaction should be handled by having this flag implicitly set for any such newly created table. Ie, the test for whether to log would look like: if (!table->logged && table->xid != myxid) ... c) Every option in ALTER TABLE should be in CREATE TABLE as well. d) Yes as someone else mentioned, this should only be allowable on a table with no foreign keys referencing it. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Greg Stark wrote:
> "Andrew Dunstan" <[EMAIL PROTECTED]> writes:
>
> > Bruce Momjian said:
> > > DROP would drop the table on a restart
> > > after a non-clean shutdown. It would do _no_ logging on the table and
> > > allow concurrent access, plus index access. DELETE is the same as
> > > DROP, but it just truncates the table (perhaps TRUNCATE is a better
> > > word).
> > >
> > > EXCLUSIVE would allow only a single session to modify the table, and
> > > would do all changes by appending to the table, similar to COPY LOCK.
> > > EXCLUSIVE would also not allow indexes because those can not be
> > > isolated like appending to the heap. EXCLUSIVE would write all dirty
> > > shared buffers for the table and fsync them before committing. SHARE
> > > is the functionality we have now, with full logging.
> >
> > I an horribly scared that this will be used as a "performance boost" for
> > normal use. I would at least like to see some restrictions that make it
> > harder to mis-use. Perhaps restrict to superuser?
>
> Well that's its whole purpose. At least you can hardly argue that you didn't
> realize the consequences of "DELETE ROWS ON RECOVERY"... :)
True. I think we are worried about non-owners using it, but the owner
had to grant permissions for others to modify it, so we might be OK.
> Some thoughts:
>
> a) I'm not sure I understand the purpose of EXCLUSIVE. When would I ever want
> to
>use it instead of DELETE ROWS?
Good question. The use case is doing COPY into a table that already had
data. EXCLUSIVE allows additions to the table but preserves the
existing data on a crash.
> b) It seems like the other feature people were talking about of not logging
>for a table created within the same transaction should be handled by
>having this flag implicitly set for any such newly created table.
>Ie, the test for whether to log would look like:
>
>if (!table->logged && table->xid != myxid) ...
Yes, the question is whether we want to limit users to having this
optimization _only_ when they have created the table in the same
transaction, and the short answer is we don't.
> c) Every option in ALTER TABLE should be in CREATE TABLE as well.
I looked into that and see that things like:
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
are not supported by CREATE TABLE, and probably shouldn't be because the
value can be changed after the table is created. I think the only
things we usually support in CREATE TABLE are those that cannot be
altered.
> d) Yes as someone else mentioned, this should only be allowable on a table
>with no foreign keys referencing it.
Right, and EXCLUSIVE can not have an index either.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] Fwd: Re: [ADMIN] migrating oracle table to PostgresQL
I believe this is one of the projects that we have slated for removal, since
it lives nicely elsewhere. Is it worth me writing up a patch to remove the
files and update the README to provide pointers to the project site and maybe
a few other places like the oraclecompat project (and maybe even
enterprisedb) or does someone with commit want to just go ahead and do it?
Robert Treat
-- Forwarded Message --
Subject: Re: [ADMIN] migrating oracle table to PostgresQL
Date: Thursday 29 December 2005 01:16
From: Gourish Singbal <[EMAIL PROTECTED]>
To: "Goulet, Dick" <[EMAIL PROTECTED]>
Cc: Bradley Kieser <[EMAIL PROTECTED]>, Murugan G
<[EMAIL PROTECTED]>, pgsql-admin@postgresql.org
ora2pg tool can be used to accomplish the migration from oracle to
postgresql.
download it from here...
http://freshmeat.net/projects/ora2pg/
The one in contib directory is an old version i guess.
About clob, blob, and long columns in Oracle all become 'text' in
postgresql.
On 12/28/05, Goulet, Dick <[EMAIL PROTECTED]> wrote:
> As I browse through the contrib directory I find an Oracle directory with
> example perl scripts to do just as your asking.
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:
> [EMAIL PROTECTED] On Behalf Of Bradley Kieser
> Sent: Wednesday, December 28, 2005 10:12 AM
> To: Murugan G
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] migrating oracle table to PostgresQL
>
> Yes.
> But Oracle is completely useless at dumping data. You may want to use
> the sqltool in HSQLDB (http://hsqldb.org/) or similar that is able to
> connect both to Oracle and PG.
>
> PLSQL developer is also able to do a connection to both but is
> commercial software.
>
> Alternatively, if you are really adventurous, set up a JDBC link to the
> PG table via DBLINK in Oracle and then pump across that way.
>
> Murugan G wrote:
> > Hello,
> > I have some tables containing clob, blob, and long columns in Oracle,
> > Is it possible to migrate these tables into PostgresQL along with data
> >
> > Thanks and regards,
> > G.Murugan,
> > Sr. Software Engineer.
> > Extn. 1658
>
> ===
>
> > Sobha Renaissance Information Technology Private Limited
> > An SEI-CMM & P-CMM Level 5 Company
> > World's 1^st SSE-CMM Level 5 Company
> > BS 7799 certified by British Standards Institute
> > ISO 9001:2000 Certified by TÜV Rheinland/Berlin-Brandenburg
> > A Six Sigma Practice Company
> > Tel: + 91 80 51951999; Fax: + 91 80 51523300; Video Conference: + 91
> > 80 5125
> > Email : [EMAIL PROTECTED]; Web:_ www.renaissance-it.com_
>
> ===
>
> > The information transmitted is intended only for the person or entity
> > to which it is addressed and may contain confidential and/or
> > privileged material. Any review, retransmission, dissemination or
> > other use of, or taking of any action in reliance upon, this
> > information by persons or entities other than the intended recipient
> > is prohibited. If you received this in error, please contact the
> > sender and delete all copies from any computer.
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
--
Best,
Gourish Singbal
---
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] localization problem (and solution)
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > Tom Lane said: >> BTW, probably a cleaner answer is to put check_function_bodies into >> some header file instead of having an "extern" in the PLs' .c files. I >> was thinking about that yesterday, but couldn't decide where was a good >> place to put it. > miscadmin.h ? Ugh :-( I was thinking about pg_proc.h, because the variable itself is in pg_proc.c, but that seems pretty ugly too. Another possibility is to move the variable someplace else... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
Hi,
I have a query that previously worked fine using pg8 on Fedora. Since
then we've moved to a FreeBSD 6 server running pg8.1.1 and the query
doesn't seem to ever finish.
I have VACUUM ANALYZEd the database. Here is the query:
SELECT column_name, table_schema, table_name, c.data_type,
et.data_type as array_type,
col_description('codes.countries'::regclass,ordinal_position),
c.character_maximum_length
FROM information_schema.columns c
LEFT JOIN information_schema.element_types et
ON et.object_schema = table_schema
AND et.object_name = table_name
AND et.array_type_identifier = c.dtd_identifier
WHERE table_schema='codes' and table_name='countries'
ORDER BY ordinal_position
-- replaces 'codes' and 'countries' with a schema and table that exist
One fellow on IRC using FreeBSD 4.11 and pg8.1.1 can reproduce the problem.
Any suggestions?
Thanks in advance,
sebastian
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] localization problem (and solution)
Tom Lane said: > "Andrew Dunstan" <[EMAIL PROTECTED]> writes: >> Tom Lane said: >>> BTW, probably a cleaner answer is to put check_function_bodies into >>> some header file instead of having an "extern" in the PLs' .c files. >>> I was thinking about that yesterday, but couldn't decide where was a >>> good place to put it. > >> miscadmin.h ? > > Ugh :-( I was thinking about pg_proc.h, because the variable itself is > in pg_proc.c, but that seems pretty ugly too. Another possibility is > to move the variable someplace else... I trust whatever choice you make. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: plperl vs LC_COLLATE (was Re: [HACKERS] Possible savepoint bug)
Tom Lane wrote: Michael Paesold <[EMAIL PROTECTED]> writes: This is a theory. The whole database was loaded using pg_restore, I still have the original dump so I will have a look at the dump now. The database actually contains some plperl functions. OK, I think I have reproduced the problem. initdb in C locale, then start postmaster with LANG=en_US.UTF-8 in its environment. Then: I had reproduced the problem here with a stripped down dump file from my backup, but your test case is much simpler, as usual. :-) In the meantime, Michael, I'd suggest modifying your postmaster start script to force LANG=C, and then reindexing all indexes you have on text/varchar/char columns. That should get you out of the immediate problem and prevent it from recurring before we have a fix. I had already reindexed all tables in a clean session and have now added "export LANG=C" to the profile of the postgres unix account. I cannot reproduce the bug after doing so. Thank you for your quick help debugging the problem. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
On Thu, Dec 29, 2005 at 11:14:59AM -0800, Sebastian wrote: > I have a query that previously worked fine using pg8 on Fedora. Since > then we've moved to a FreeBSD 6 server running pg8.1.1 and the query > doesn't seem to ever finish. How long did you wait? In one of my tests the query took over three times as long to finish in 8.1.1 as it did in 8.0.5, but it did finish. However, EXPLAIN fails in 8.1.1: test=> EXPLAIN SELECT ... ERROR: record type has not been registered Something about the information_schema.element_types view seems to be the problem: test=> EXPLAIN SELECT * FROM information_schema.element_types; ERROR: record type has not been registered -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
I've waited 10 minutes before cancelling. On pg8 it runs in less than a second : test=> EXPLAIN SELECT * FROM information_schema.element_types; : ERROR: record type has not been registered I can reproduce this... - sebastian On 12/29/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Thu, Dec 29, 2005 at 11:14:59AM -0800, Sebastian wrote: > > I have a query that previously worked fine using pg8 on Fedora. Since > > then we've moved to a FreeBSD 6 server running pg8.1.1 and the query > > doesn't seem to ever finish. > > How long did you wait? In one of my tests the query took over three > times as long to finish in 8.1.1 as it did in 8.0.5, but it did finish. > However, EXPLAIN fails in 8.1.1: > > test=> EXPLAIN SELECT ... > ERROR: record type has not been registered > > Something about the information_schema.element_types view seems to > be the problem: > > test=> EXPLAIN SELECT * FROM information_schema.element_types; > ERROR: record type has not been registered > > -- > Michael Fuhr > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > I've waited 10 minutes before cancelling. On pg8 it runs in less than a second How many columns in the table? In 8.1.1 I'm seeing a nearly exponential increase in time with each extra column, at least up to about five columns; with more columns the time continues to increase although not as sharply. I don't see such an increase in 8.0.5. Querying the views individually doesn't take long; I wonder if the planner is doing something wrong with the join operation. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
> How many columns in the table? There are 4 columns in the table On 12/29/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > > I've waited 10 minutes before cancelling. On pg8 it runs in less than a > > second > > How many columns in the table? In 8.1.1 I'm seeing a nearly > exponential increase in time with each extra column, at least up > to about five columns; with more columns the time continues to > increase although not as sharply. I don't see such an increase in > 8.0.5. Querying the views individually doesn't take long; I wonder > if the planner is doing something wrong with the join operation. > > -- > Michael Fuhr > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Removing SORTFUNC_LT/REVLT
On Thu, Dec 29, 2005 at 10:49:23AM -0500, Tom Lane wrote: > Well, no, that's not the problem: the problem is that you should be able > to specify ORDER BY any sort ordering that the system can deal with, and > the USING syntax is in fact too impoverished to do that. What if the > mentioned operator is in more than one operator class? I believe that > ATM the code makes a random choice of which opclass' sort function to > use, which pretty much sucks. Ah, that problem. Yeah, at this stage we can't really do much about that. All I've done at this stage is made it so that if the operator isn't a member of any operator class it displays: ERROR: Couldn't find order function associated with operator XXX HINT: Create a B-tree operator class with this operator in it Eventually the COLLATE option should make the choice unambiguous. But I'm not there yet. One benefit right now is that it permits some code reorganisation to remove the myFunctionCall2 hack, by passing FunctionCallInfo rather than FmgrInfo. > We could keep the "USING operator" syntax but insist that it's only > allowed if there's exactly one possible opclass mapping. Can't do that yet, that breaks reverse operator classes. Ofcourse, we may be able to do it eventually if there's another way to do the same thing. The main problem being that ORDER BY x ASC is actually made equivalent to ORDER BY x USING <, so the code still has to work for the USING case to not break the normal case. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpw2Y8u7jY9k.pgp Description: PGP signature
Re: [HACKERS] broken 'SHOW TABLE'-like query works in 8, not 8.1.1
On Dec 29 2005, Michael Fuhr wrote: On Thu, Dec 29, 2005 at 12:12:52PM -0800, Sebastian wrote: > I've waited 10 minutes before cancelling. On pg8 it runs in less than > a second How many columns in the table? In 8.1.1 I'm seeing a nearly exponential increase in time with each extra column, at least up to about five columns; with more columns the time continues to increase although not as sharply. I don't see such an increase in 8.0.5. Querying the views individually doesn't take long; I wonder if the planner is doing something wrong with the join operation. For clarification, I'm the 4.11-FreeBSD guy refered to above, and with a very simple table, it comes right back with NO results, but I may not have what it's looking for in the table definition. I **DO** get the explain failure, which seems, to me, to be a bug. :( LER -- Larry Rosenman, Database Support Engineer, E-Mail: [EMAIL PROTECTED] Pervasive Software, 12365B Riata Trace Parkway, Austin, TX 78727 Office: 512-231-6173 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fwd: Re: [ADMIN] migrating oracle table to PostgresQL
Robert Treat wrote:
> I believe this is one of the projects that we have slated for removal, since
> it lives nicely elsewhere. Is it worth me writing up a patch to remove the
> files and update the README to provide pointers to the project site and maybe
> a few other places like the oraclecompat project (and maybe even
> enterprisedb) or does someone with commit want to just go ahead and do it?
We have been waiting for someone to do it for _too_ long. Please feel
free to submit something. I recommend you get the list of changes from
the archinves that we agreed to for /contrib and use that as a guide.
I am still waiting for all valid gborg projects to be moved to pgfoundry.
How many years has that been in the air?
I know people have said they would complete these items, but they have
had sufficient time, and it is time to bring in other people for the job.
Our web site is looking great and is well organized. It is time for us
to do the same for add-on projects.
---
> Robert Treat
>
> -- Forwarded Message --
>
> Subject: Re: [ADMIN] migrating oracle table to PostgresQL
> Date: Thursday 29 December 2005 01:16
> From: Gourish Singbal <[EMAIL PROTECTED]>
> To: "Goulet, Dick" <[EMAIL PROTECTED]>
> Cc: Bradley Kieser <[EMAIL PROTECTED]>, Murugan G
> <[EMAIL PROTECTED]>, pgsql-admin@postgresql.org
>
> ora2pg tool can be used to accomplish the migration from oracle to
> postgresql.
> download it from here...
> http://freshmeat.net/projects/ora2pg/
>
> The one in contib directory is an old version i guess.
>
> About clob, blob, and long columns in Oracle all become 'text' in
> postgresql.
>
> On 12/28/05, Goulet, Dick <[EMAIL PROTECTED]> wrote:
> > As I browse through the contrib directory I find an Oracle directory with
> > example perl scripts to do just as your asking.
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:
> > [EMAIL PROTECTED] On Behalf Of Bradley Kieser
> > Sent: Wednesday, December 28, 2005 10:12 AM
> > To: Murugan G
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] migrating oracle table to PostgresQL
> >
> > Yes.
> > But Oracle is completely useless at dumping data. You may want to use
> > the sqltool in HSQLDB (http://hsqldb.org/) or similar that is able to
> > connect both to Oracle and PG.
> >
> > PLSQL developer is also able to do a connection to both but is
> > commercial software.
> >
> > Alternatively, if you are really adventurous, set up a JDBC link to the
> > PG table via DBLINK in Oracle and then pump across that way.
> >
> > Murugan G wrote:
> > > Hello,
> > > I have some tables containing clob, blob, and long columns in Oracle,
> > > Is it possible to migrate these tables into PostgresQL along with data
> > >
> > > Thanks and regards,
> > > G.Murugan,
> > > Sr. Software Engineer.
> > > Extn. 1658
> >
> > ===
> >
> > > Sobha Renaissance Information Technology Private Limited
> > > An SEI-CMM & P-CMM Level 5 Company
> > > World's 1^st SSE-CMM Level 5 Company
> > > BS 7799 certified by British Standards Institute
> > > ISO 9001:2000 Certified by T?V Rheinland/Berlin-Brandenburg
> > > A Six Sigma Practice Company
> > > Tel: + 91 80 51951999; Fax: + 91 80 51523300; Video Conference: + 91
> > > 80 5125
> > > Email : [EMAIL PROTECTED]; Web:_ www.renaissance-it.com_
> >
> > ===
> >
> > > The information transmitted is intended only for the person or entity
> > > to which it is addressed and may contain confidential and/or
> > > privileged material. Any review, retransmission, dissemination or
> > > other use of, or taking of any action in reliance upon, this
> > > information by persons or entities other than the intended recipient
> > > is prohibited. If you received this in error, please contact the
> > > sender and delete all copies from any computer.
> >
> > ---(end of broadcast)---
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
> > ---(end of broadcast)---
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to [EMAIL PROTECTED] so that your
> > message can get through to the mailing list cleanly
>
> --
> Best,
> Gourish Singbal
>
> ---
>
>
>
> --
> Robert Treat
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
Re: [HACKERS] Does VACUUM reorder tables on clustered indices
Tom, has this bug been addressed or documented? --- Tom Lane wrote: > Andrew Sullivan <[EMAIL PROTECTED]> writes: > > On Sun, Dec 18, 2005 at 10:08:22PM -0500, Tom Lane wrote: > >> Just for the record, that behavior is seriously broken: it violates > >> MVCC if any of the deleted tuples are still visible to anyone else. > > > Does it remove tuples that VACUUM FULL wouldn't? > > Yes. CLUSTER works on SnapshotNow, so it will remove committed-dead > tuples even if there are still open transactions that could see them. > Of course, said transactions couldn't be actively using the table > while the CLUSTER runs, because it takes an exclusive table lock. > But they *could* look at it afterwards. Offhand I think you'd only > be likely to notice the difference if the open transactions were > SERIALIZABLE --- in READ COMMITTED mode, by the time they could look > at the clustered table, they'd likely be using a snapshot that postdates > the DELETE. > > [ experiments a bit... ] Hmm. Actually, it's far worse than I > thought. It looks like CLUSTER puts the tuples into the new table with > its own xid, which means that concurrent serializable transactions will > see the new table as completely empty! > > << session 1 >> > > regression=# select * from int4_tbl; > f1 > - >0 > 123456 > -123456 > 2147483647 > -2147483647 > (5 rows) > > regression=# create index fooi on int4_tbl(f1); > CREATE INDEX > regression=# begin isolation level serializable; > BEGIN > regression=# select 2+2; -- establish transaction snapshot > ?column? > -- > 4 > (1 row) > > << session 2 >> > > regression=# delete from int4_tbl where f1 = -123456; > DELETE 1 > regression=# cluster fooi on int4_tbl; > CLUSTER > > << back to session 1 >> > > regression=# select * from int4_tbl; > f1 > > (0 rows) > > regression=# commit; > COMMIT > regression=# select * from int4_tbl; > f1 > - > -2147483647 >0 > 123456 > 2147483647 > (4 rows) > > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Does VACUUM reorder tables on clustered indices
Bruce Momjian writes: > Tom, has this bug been addressed or documented? No. Please add a TODO: * Make CLUSTER preserve recently-dead tuples per MVCC requirements I have not tested, but I suspect the table-rewriting variants of ALTER TABLE have the same problem. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Does VACUUM reorder tables on clustered indices
Tom Lane wrote: > Bruce Momjian writes: > > Tom, has this bug been addressed or documented? > > No. Please add a TODO: > > * Make CLUSTER preserve recently-dead tuples per MVCC requirements > > I have not tested, but I suspect the table-rewriting variants of ALTER TABLE > have the same problem. Added. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] EINTR error in SunOS
I encountered an error today (can't repeat) on SunOS 5.8: --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); + ERROR: could not open relation 1663/16384/37713: Interrupted system call The reason I guess is the open() call is interrupted by a signal (what signal BTW?). This error may be specific to SunOS/Solaris, but POSIX does say that an EINTR is possible on open(), close(), read(), write() and also the fopen() family: http://www.opengroup.org/onlinepubs/007908799/xsh/open.html We have patched read()/write(), shall we do so to open()/close() and also fopen() family? Patching files other than fd.c seems unnecessary for two reasons: (1) they are not frequently exercised; (2) they don't have the basic errno-check code there. Regards, Qingqing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] EINTR error in SunOS
Qingqing Zhou <[EMAIL PROTECTED]> writes: > + ERROR: could not open relation 1663/16384/37713: Interrupted system call > The reason I guess is the open() call is interrupted by a signal (what > signal BTW?). I've heard of this in connection with NFS ... is your DB on an NFS filesystem by any chance? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] EINTR error in SunOS
"Tom Lane" <[EMAIL PROTECTED]> wrote > Qingqing Zhou <[EMAIL PROTECTED]> writes: >> + ERROR: could not open relation 1663/16384/37713: Interrupted system >> call > >> The reason I guess is the open() call is interrupted by a signal (what >> signal BTW?). > > I've heard of this in connection with NFS ... is your DB on an NFS > filesystem by any chance? > Exactly. I guess school machines love NFS. Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fwd: Re: [ADMIN] migrating oracle table to PostgresQL
On Thursday 29 December 2005 21:35, Bruce Momjian wrote:
> Robert Treat wrote:
> > I believe this is one of the projects that we have slated for removal,
> > since it lives nicely elsewhere. Is it worth me writing up a patch to
> > remove the files and update the README to provide pointers to the project
> > site and maybe a few other places like the oraclecompat project (and
> > maybe even enterprisedb) or does someone with commit want to just go
> > ahead and do it?
>
> We have been waiting for someone to do it for _too_ long. Please feel
> free to submit something. I recommend you get the list of changes from
> the archinves that we agreed to for /contrib and use that as a guide.
>
> I know people have said they would complete these items, but they have
> had sufficient time, and it is time to bring in other people for the job.
>
Turns out I can't really make a patch that removes files without write access
to CVS. There's some funky things you can do with diff -N and patch -E but
seems like a pain. Attached is a patch that at least updates the general
contrib README and Makefile, someone else will have to actually delete the
files in the oracle/ directory. (If yall want to give me write access, I
could spend some time cleaning up some more of these) Oh, and no special
write up for the README, looking at some of the other contribs that did get
removed, apparently we don't do that, but the commit should probably at least
mention the project page, http://freshmeat.net/projects/ora2pg/
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
? oracle.patch
Index: Makefile
===
RCS file: /projects/cvsroot/pgsql/contrib/Makefile,v
retrieving revision 1.61
diff -c -r1.61 Makefile
*** Makefile 1 Sep 2005 22:02:44 - 1.61
--- Makefile 30 Dec 2005 05:41:13 -
***
*** 37,43
# adddepend \ (does not have a makefile)
# mSQL-interface \ (requires msql installed)
# mac \ (does not have a makefile)
- # oracle \ (does not have a makefile)
# start-scripts \ (does not have a makefile)
# xml2 \ (requires libxml installed)
--- 37,42
Index: README
===
RCS file: /projects/cvsroot/pgsql/contrib/README,v
retrieving revision 1.82
diff -c -r1.82 README
*** README 29 Jul 2005 19:38:21 - 1.82
--- README 30 Dec 2005 05:41:13 -
***
*** 98,107
Maps numeric files to table names
by B Palmer <[EMAIL PROTECTED]>
- oracle -
- Converts Oracle database schema to PostgreSQL
- by Gilles Darold
-
pg_buffercache -
Real time queries on the shared buffer cache
by Mark Kirkwood <[EMAIL PROTECTED]>
--- 98,103
---(end of broadcast)---
TIP 6: explain analyze is your friend
