Re: [HACKERS] Detecting corrupted pages earlier
On Tue, 18 Feb 2003, Tom Lane wrote: > The header is only a dozen or two bytes long, so torn-page syndrome > won't result in header corruption. No. But the checksum would detect both header corruption and torn pages. Two for the price of one. But I don't think it's worth changing the page layout for, either. Maybe, if anybody still cares next time the page layout is changed, pop it in with whatever else is being changed. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Detecting corrupted pages earlier
Curt Sampson <[EMAIL PROTECTED]> writes: > Well, I wasn't proposing the whole page, just the header. That would be > significantly cheaper (in fact, there's no real need even for a CRC; > probably just xoring all of the words in the header into one word would > be fine) and would tell you if the page was torn during the write, which > was what I was imagining the problem might be. The header is only a dozen or two bytes long, so torn-page syndrome won't result in header corruption. The cases I've been able to study look like the header and a lot of the following page data have been overwritten with garbage --- when it made any sense at all, it looked like the contents of non-Postgres files (eg, plain text), which is why I mentioned the possibility of disks writing data to the wrong sector. Another recent report suggested that all bytes of the header had been replaced with 0x55, which sounds more like RAM or disk-controller malfeasance. You're right that we don't need a heck of a powerful check to catch this sort of thing. I was envisioning checks comparable to what's now in PageAddItem: valid pagesize, valid version, pd_lower and pd_upper and pd_special sane relative to each other and to the pagesize. I think this would be nearly as effective as an XOR sum --- and it has the major advantage of being compatible with the existing page layout. I'd like to think we're done munging the page layout for awhile. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Detecting corrupted pages earlier
On Mon, 17 Feb 2003, Tom Lane wrote: > Curt Sampson <[EMAIL PROTECTED]> writes: > > > If it's any kind of a serious problem, maybe it would be worth keeping > > a CRC of the header at the end of the page somewhere. > > See past discussions about keeping CRCs of page contents. Ultimately > I think it's a significant expenditure of CPU for very marginal returns > --- the layers underneath us are supposed to keep their own CRCs or > other cross-checks, and a very substantial chunk of the problem seems > to be bad RAM, against which occasional software CRC checks aren't > especially useful. Well, I wasn't proposing the whole page, just the header. That would be significantly cheaper (in fact, there's no real need even for a CRC; probably just xoring all of the words in the header into one word would be fine) and would tell you if the page was torn during the write, which was what I was imagining the problem might be. But bad memory, well, not much you can do about that beyond saying, "buy ECC, dude." cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL replay logic (was Re: [PERFORM] Mount options for
On Tue, 18 Feb 2003, Bruce Momjian wrote: > > Added to TODO: > > * Allow WAL information to recover corrupted pg_controldata >... > > Using pg_control to get the checkpoint position speeds up the > > recovery process, but to handle possible corruption of pg_control, > > we should actually implement the reading of existing log segments > > in reverse order -- newest to oldest -- in order to find the last > > checkpoint. This has not been implemented, yet. So if you do this, do you still need to store that information in pg_control at all? cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL replay logic (was Re: [PERFORM] Mount options for
Added to TODO: * Allow WAL information to recover corrupted pg_controldata --- Curt Sampson wrote: > On Fri, 14 Feb 2003, Bruce Momjian wrote: > > > Is there a TODO here, like "Allow recovery from corrupt pg_control via > > WAL"? > > Isn't that already in section 12.2.1 of the documentation? > > Using pg_control to get the checkpoint position speeds up the > recovery process, but to handle possible corruption of pg_control, > we should actually implement the reading of existing log segments > in reverse order -- newest to oldest -- in order to find the last > checkpoint. This has not been implemented, yet. > > cjs > -- > Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org > Don't you know, in this new Dark Age, we're all light. --XTC > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Detecting corrupted pages earlier
Tom Lane wrote: > Curt Sampson <[EMAIL PROTECTED]> writes: > > On Mon, 17 Feb 2003, Tom Lane wrote: > >> Postgres has a bad habit of becoming very confused if the page header of > >> a page on disk has become corrupted. > > > What typically causes this corruption? > > Well, I'd like to know that too. I have seen some cases that were > identified as hardware problems (disk wrote data to wrong sector, RAM > dropped some bits, etc). I'm not convinced that that's the whole story, > but I have nothing to chew on that could lead to identifying a software > bug. > > > If it's any kind of a serious problem, maybe it would be worth keeping > > a CRC of the header at the end of the page somewhere. > > See past discussions about keeping CRCs of page contents. Ultimately > I think it's a significant expenditure of CPU for very marginal returns > --- the layers underneath us are supposed to keep their own CRCs or > other cross-checks, and a very substantial chunk of the problem seems > to be bad RAM, against which occasional software CRC checks aren't > especially useful. I believe the farthest we got was the idea of adding a CRC page check option in case you suspected bad hardware. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] postgresql and oracle, compatibility assessment
[EMAIL PROTECTED] ("Martin Matusiak") wrote:
> Would it be possible to create some sort of transparent API based on
> ODBC to be used with PostgreSQL and Oracle? I know there exists a
> JDBC - ODBC bridge for java.
If you wrote your application exclusively using JDBC using functions
existing in both PostgreSQL and Oracle, you'd certainly have a "lowest
common denominator" that would satisfy this requirement.
You would be taking advantage neither of PostgreSQL's strengths nor of
Oracle's strengths, which is considerably wasteful on both sides of
the coin.
You would have to design your application so that it does not use
either system's stored procedure systems, because they are not totally
interoperable.
You would have to ensure that your access patterns were concurrently
compatible with Oracle rollback buffers and PostgreSQL MVCC.
At the end of all this, you more than likely have an application that
will perform questionably in both places...
--
If this was helpful, rate me
http://www3.sympatico.ca/cbbrowne/lsf.html
Would-be National Mottos:
USA: "There oughta' be a law!"
---(end of broadcast)---
TIP 3: 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] Group by, count, order by and limit
My 3rd attempt to post ... Consider this query on a large table with lots of different IDs: SELECT id FROM my_table GROUP BY id ORDER BY count(id) LIMIT 10; It has an index on id. Obviously, the index helps to evaluate count(id) for a given value of id, but count()s for all the `id's should be evaluated, so sort() will take most of the time. Is there a way to improve performance of this query? If not, please give some indication to do a workaround on the source itself, so perhaps I may be able to come out with a patch. Thanks in advance. Anuradha -- Debian GNU/Linux (kernel 2.4.21-pre4) There are three ways to get something done: (1) Do it yourself. (2) Hire someone to do it for you. (3) Forbid your kids to do it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Detecting corrupted pages earlier
Curt Sampson <[EMAIL PROTECTED]> writes: > On Mon, 17 Feb 2003, Tom Lane wrote: >> Postgres has a bad habit of becoming very confused if the page header of >> a page on disk has become corrupted. > What typically causes this corruption? Well, I'd like to know that too. I have seen some cases that were identified as hardware problems (disk wrote data to wrong sector, RAM dropped some bits, etc). I'm not convinced that that's the whole story, but I have nothing to chew on that could lead to identifying a software bug. > If it's any kind of a serious problem, maybe it would be worth keeping > a CRC of the header at the end of the page somewhere. See past discussions about keeping CRCs of page contents. Ultimately I think it's a significant expenditure of CPU for very marginal returns --- the layers underneath us are supposed to keep their own CRCs or other cross-checks, and a very substantial chunk of the problem seems to be bad RAM, against which occasional software CRC checks aren't especially useful. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] lock method
Have you read the README file in storage/lmgr/README? --- Sumaira Ali wrote: [ text/html is unsupported, treating like TEXT/PLAIN ] > hi, does anyone know what lockmethod means >in the lock.h file and whats the use of lockmethodTable.? > thank you > SumairaAdd photos to your messages with href="http://g.msn.com/8HMOENUS/2749";>MSN 8. Get 2 months FREE*. > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Detecting corrupted pages earlier
On Mon, 17 Feb 2003, Tom Lane wrote: > Postgres has a bad habit of becoming very confused if the page header of > a page on disk has become corrupted. What typically causes this corruption? If it's any kind of a serious problem, maybe it would be worth keeping a CRC of the header at the end of the page somewhere. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] postgresql and oracle, compatibility assessment
Would it be possible to create some sort of transparent API based on ODBC to be used with PostgreSQL and Oracle? I know there exists a JDBC - ODBC bridge for java. Martin - Original Message - From: "Hannu Krosing" <[EMAIL PROTECTED]> To: "Martin Matusiak" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, February 17, 2003 4:03 PM Subject: Re: [HACKERS] postgresql and oracle, compatibility assessment > Martin Matusiak kirjutas E, 17.02.2003 kell 16:53: > > Greetings, > > > > I am doing a project for college developing a java system utilizing a > > RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering > > exactly how impossible would it be to make it compatible with both. > > Postgre is said to be completely ANSI SQL complaint, > > PostgreSQL is *much* more ANSI comliant than Oracle. > > > is it feasible to imagine one could run dumps from Postgre into Oracle > > and vice versa? > > Not all data types are named the same (Oracles varchar is varchar2, > etc.) > > > Alternatively, run separate queries on both to set the structure and > > relations, then transfer only data? > > Much more likely to succeed. > > You may be interested in OpenACS ( http://openacs.org/ ) project, which > runs on both Oracle and PostgreSQL. > > - > Hannu > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy]
People seemed to like the idea: Add a script to ask system configuration questions and tune postgresql.conf. --- Bruce Momjian wrote: > Peter Eisentraut wrote: > > Tom Lane writes: > > > > > Well, as I commented later in that mail, I feel that 1000 buffers is > > > a reasonable choice --- but I have to admit that I have no hard data > > > to back up that feeling. > > > > I know you like it in that range, and 4 or 8 MB of buffers by default > > should not be a problem. But personally I think if the optimal buffer > > size does not depend on both the physical RAM you want to dedicate to > > PostgreSQL and the nature and size of the database, then we have achieved > > a medium revolution in computer science. ;-) > > I have thought about this and I have an idea. Basically, increasing the > default values may get us closer, but it will discourage some to tweek, > and it will cause problems with some OS's that have small SysV params. > > So, my idea is to add a message at the end of initdb that states people > should run the pgtune script before running a production server. > > The pgtune script will basically allow us to query the user, test the OS > version and perhaps parameters, and modify postgresql.conf with > reasonable values. I think this is the only way to cleanly get folks > close to where they should be. > > For example, we can ask them how many rows and tables they will be > changing, on average, between VACUUM runs. That will allow us set the > FSM params. We can ask them about using 25% of their RAM for shared > buffers. If they have other major apps running on the server or have > small tables, we can make no changes. We can basically ask them > questions and use that info to set values. > > We can even ask about sort usage maybe and set sort memory. We can even > control checkpoint_segments this way if they say they will have high > database write activity and don't worry about disk space usage. We may > even be able to compute some random page cost estimate. > > Seems a script is going to be the best way to test values and assist > folks in making reasonable decisions about each parameter. Of course, > they can still edit the file, and we can ask them if they want > assistance to set each parameter or leave it alone. > > I would restrict the script to only deal with tuning values, and tell > people they still need to review that file for other useful parameters. > > Another option would be to make a big checklist or web page that asks > such questions and computes proper values, but it seems a script would > be easiest. We can even support '?' which would explain why the > question is being ask and how it affects the value. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Don't 'kill -9' the postmaster
Re: [HACKERS] Please apply patch
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Teodor Sigaev wrote: > Please apply patches for contrib/ltree. > > ltree_73.patch.gz - for 7.3 : > Fix ~ operation bug: eg '1.1.1' ~ '*.1' > > ltree_74.patch.gz - for current CVS > Fix ~ operation bug: eg '1.1.1' ~ '*.1' > Add ? operation > Optimize index storage > > Last change needs drop/create all ltree indexes, so only for 7.4 > > Thank you. > > -- > Teodor Sigaev > [EMAIL PROTECTED] > [ application/gzip is not supported, skipping... ] [ application/gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] A couple of small fixes for 7.3.2 buglets
I am not sure about this patch. First, src/bin/pg_dump/po/pt_BR.po isn't in CVS anymore. Seems we don't have a Portugese translation file anymore for this. As far as Kerberos, you are the first to mention those additional libraries. Perhaps something for LIBS in Makefile.global would fix this, but I am not sure how general those changes are. Comments anyone? --- Tom Ivar Helbekkmo wrote: > The patch below fixes a pair of little typos in a language > localization file, and adds a few more shared libraries to the list of > such that libpq might depend on. (I need them to get things to work > properly with Kerberos 5 under NetBSD, where the lack of them breaks > libpgtcl.so run-time dynamic linking, so e.g. pgaccess won't work.) > > Incidentally, to actually get Kerberos 5 support under NetBSD, I have > to do the following: > > % INCLUDES="-I/usr/include/krb5"; export INCLUDES > % LIBS="-lkrb5 -lkrb -lcrypto -lasn1 -lcom_err -lroken"; export LIBS > % ./configure --with-krb5=/usr [...] > > If there is a way to get this information into the configure script, > that would be a Good Thing. :-) > > -tih > > *** src/bin/pg_dump/po/pt_BR.po~ Tue Dec 10 21:00:27 2002 > --- src/bin/pg_dump/po/pt_BR.po Fri Feb 7 07:36:13 2003 > *** > *** 189,196 > msgstr " -Z 0-9 n?vel de compress?o para formatos comprimidos\n" > > #: pg_dump.c:675 pg_dumpall.c:238 pg_restore.c:396 > ! msgid " --help show this help, then exit\vn" > ! msgstr " --help mostra essa ajuda, e termina\vn" > > #: pg_dump.c:676 pg_dumpall.c:239 pg_restore.c:397 > msgid " --versionoutput version information, then exit\n" > --- 189,196 > msgstr " -Z 0-9 n?vel de compress?o para formatos comprimidos\n" > > #: pg_dump.c:675 pg_dumpall.c:238 pg_restore.c:396 > ! msgid " --help show this help, then exit\n" > ! msgstr " --help mostra essa ajuda, e termina\n" > > #: pg_dump.c:676 pg_dumpall.c:239 pg_restore.c:397 > msgid " --versionoutput version information, then exit\n" > *** src/interfaces/libpq/Makefile~Fri Dec 13 23:18:22 2002 > --- src/interfaces/libpq/Makefile Fri Feb 7 08:47:47 2003 > *** > *** 29,35 > # Add libraries that libpq depends (or might depend) on into the > # shared library link. (The order in which you list them here doesn't > # matter.) > ! SHLIB_LINK += $(filter -lcrypt -ldes -lkrb -lcom_err -lcrypto -lk5crypto -lkrb5 >-lssl -lsocket -lnsl -lresolv -lintl, $(LIBS)) > > > all: all-lib > --- 29,35 > # Add libraries that libpq depends (or might depend) on into the > # shared library link. (The order in which you list them here doesn't > # matter.) > ! SHLIB_LINK += $(filter -lcrypt -ldes -lkrb -lcom_err -lcrypto -lk5crypto -lkrb5 >-lssl -lsocket -lnsl -lresolv -lintl -lutil -lasn1 -lroken, $(LIBS)) > > > all: all-lib > > -- > Tom Ivar Helbekkmo, Senior System Administrator, EUnet Norway > www.eunet.no T: +47-22092958 M: +47-93013940 F: +47-22092901 > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] IpcSemaphoreKill: ...) failed: Invalid argument
> This is a fairly spectacular failure :-(. As far as I can see from the > semctl and shmctl man pages, the only plausible reason for EINVAL is > that something had deleted the semaphores and shared memory out from > under Postgres. I do not believe that Postgres itself could have done > that --- it had to be some external agency. Unless the kernel is > broken, whatever requested those deletions had to be running as root or > as postgres in order to have the necessary permissions. You sure you > didn't have some loose-cannon script running around issuing ipcrm > commands? Or ipcclean? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] deadlock in REINDEX
On Mon, 17 Feb 2003, Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > On Mon, 2003-02-17 at 18:39, Tom Lane wrote: > >> If you release the lock then I think you are opening yourself to worse > >> troubles than this one, having to do with someone renaming/deleting the > >> table and/or index out from under you. > > > Presumably, the renaming/deleting operation acquires an exclusive lock > > and then holds it until transaction commit, right? If so, then wouldn't > > we still be okay: the REINDEX would lock the index in access share mode, > > find the OID of the heap rel, unlock the index, lock the heap rel in > > access exclusive mode, then try to re-open & lock the index, find that > > it no longer exists and then elog(ERROR). > > That approach might be deadlock-free, but that doesn't mean it is > surprise-free. For example, if the other guy did an ALTER TABLE RENAME Perhaps the change that needs to be made is: if(IsUnderPostmaster) elog(ERROR,"You cannot run REINDEX INDEX in multi-user mode"); to ReindexIndex() or some other appropriate place (with a better error message). Gavin ---(end of broadcast)--- TIP 3: 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] deadlock in REINDEX
Neil Conway <[EMAIL PROTECTED]> writes: > On Mon, 2003-02-17 at 18:39, Tom Lane wrote: >> If you release the lock then I think you are opening yourself to worse >> troubles than this one, having to do with someone renaming/deleting the >> table and/or index out from under you. > Presumably, the renaming/deleting operation acquires an exclusive lock > and then holds it until transaction commit, right? If so, then wouldn't > we still be okay: the REINDEX would lock the index in access share mode, > find the OID of the heap rel, unlock the index, lock the heap rel in > access exclusive mode, then try to re-open & lock the index, find that > it no longer exists and then elog(ERROR). That approach might be deadlock-free, but that doesn't mean it is surprise-free. For example, if the other guy did an ALTER TABLE RENAME on the index, it'd be possible that what you are actually reindexing is now differently named than it was before (and, perhaps, there is now some other index that has the original name and is the one the user really meant). This is not so dangerous in the REINDEX case, maybe, but it could be unhappy-making in the DROP case. > Whether or not that solution actually works, ISTM there must be *some* > method of locking that is free of deadlocks -- saying "oh well, it's not > a common case anyway" doesn't strike me as being satisfactory :-\ Basically, I'm not convinced that a deadlock failure is so much worse than any other failure that we should open ourselves to other surprises in order to avoid a deadlock. It's a judgment call though. Any other comments out there? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] deadlock in REINDEX
On Mon, 2003-02-17 at 18:39, Tom Lane wrote: > > I was thinking of changing reindex_index() to acquire an AccessShareLock > > on the index in question, find its parent rel ID, release the lock, then > > acquire an AccessExclusiveLock on the parent rel, followed by an > > AccessExclusiveLock on the index in question. > > If you release the lock then I think you are opening yourself to worse > troubles than this one, having to do with someone renaming/deleting the > table and/or index out from under you. Presumably, the renaming/deleting operation acquires an exclusive lock and then holds it until transaction commit, right? If so, then wouldn't we still be okay: the REINDEX would lock the index in access share mode, find the OID of the heap rel, unlock the index, lock the heap rel in access exclusive mode, then try to re-open & lock the index, find that it no longer exists and then elog(ERROR). Whether or not that solution actually works, ISTM there must be *some* method of locking that is free of deadlocks -- saying "oh well, it's not a common case anyway" doesn't strike me as being satisfactory :-\ Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Q: pg_catalog views, OIDs and search_path
Ian Barwick <[EMAIL PROTECTED]> writes: > Q: is there any likelihood of the pg_catalog views (pg_views, pg_tables, >pg_indexes, pg_rules, possibly others I have missed) returning the >relevant OID or (probably cleaner) the result of pg_table_is_visible() >as a boolean? That strikes me as awfully ugly --- the apparent contents of the view would change depending on your search_path setting. Which seems weird. > Otherwise the only workaround will be to ignore the catalog views and > work with pg_class directly, which I will probably do, but it > feels like a step backwards. I don't see a problem with it. The only thing those views really do for you is keep you from having to know the character codes for relkind, which is not a lot of implementation-dependence compared to all the other stuff that \d knows ... regards, tom lane ---(end of broadcast)--- TIP 3: 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] deadlock in REINDEX
Neil Conway <[EMAIL PROTECTED]> writes: > Naturally, this situation is not a very common one. But it seems to me > that the practice of acquiring locks in REINDEX in an inconsistent order > is asking for trouble: REINDEX TABLE locks the heap rel first, followed > by any indexes of the heap rel, but REINDEX INDEX locks the target > index, followed by the heap rel. Hence a deadlock condition (the > explicit lock table above just serves to make the window of opportunity > much larger). > This should be fixed, right? Only if the cure isn't worse than the disease. > I was thinking of changing reindex_index() to acquire an AccessShareLock > on the index in question, find its parent rel ID, release the lock, then > acquire an AccessExclusiveLock on the parent rel, followed by an > AccessExclusiveLock on the index in question. If you release the lock then I think you are opening yourself to worse troubles than this one, having to do with someone renaming/deleting the table and/or index out from under you. The fact that REINDEX INDEX might fail if there are concurrent conflicting operations doesn't bother me a whole lot; but not holding a lock throughout the operation does. AFAICS, REINDEX INDEX is only a disaster-recovery tool anyway, and so is not likely to be run in parallel with other operations. The scenarios I can think of where you might want to do REINDEX routinely would always use REINDEX TABLE, I should think. BTW, I imagine DROP INDEX has a similar issue, and CLUSTER might depending on what it locks first (but it would be easy to fix it to lock the table first, since it has both names). regards, tom lane ---(end of broadcast)--- TIP 3: 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] pg environment? metadata?
> Hello, > > I was wondering what kind of functions/constants exist in Postgre to dig up > metadata. I barely scratched the surface of Oracle but I know you find > things like user_tables there that can be used to extract info about your > tables. What I'm looking for is some kind of functions to extract column > names, possibly data types, etc. And by that I don't mean console commands, > sql statements that will do the job with tcp/ip. > > Moreover, are there any ANSI standards for this kind of thing? Or each one > to his own? > > > Martin > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COUNT and Performance ...
Bruce Momjian <[EMAIL PROTECTED]> writes: > I didn't think pgstattuple had proper visibility checks. It doesn't, see followup discussion. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Q: pg_catalog views, OIDs and search_path
I'm preparing a patch to make more psql slash commands tab-completable (\di, \dv etc-) and have come across the following dilemma: - only relations visible in the current search path should be returned [*] - to determine visibilty via pg_catalog.pg_table_is_visible(), the relation's OID is necessary; - using (say) pg_catalog.pg_views to obtain view names seems to be the "cleaner" approach (making psql independent from the backend etc.) - views don't come with OIDs As is psql currently uses pg_catalog.pg_views to complete view names, meaning it will happily tab-complete (say) DROP VIEW with a view _not_ in the current search path. If executed the statement naturally produces the error 'ERROR: view "..." does not exist'. Q: is there any likelihood of the pg_catalog views (pg_views, pg_tables, pg_indexes, pg_rules, possibly others I have missed) returning the relevant OID or (probably cleaner) the result of pg_table_is_visible() as a boolean? Otherwise the only workaround will be to ignore the catalog views and work with pg_class directly, which I will probably do, but it feels like a step backwards. [*] at least, this is how \d currently behaves and IMHO is intuitive. \d should of course operate on schema names too, to enable completion of relation names not in the search path; tentative patch will follow. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Version 7.2.3 Vacuum abnormality
Andrew Sullivan <[EMAIL PROTECTED]> writes: > On Thu, Feb 13, 2003 at 03:27:01PM +1000, Paul L Daniels wrote: >> NOTICE: Rel xamefiles: Uninitialized page 708135 - fixing >> NOTICE: Rel xamefiles: Uninitialized page 708136 - fixing >> NOTICE: Rel xamefiles: Uninitialized page 708137 - fixing > This is a known and, it turns out, not real serious bug. See, e.g., > http://archives.postgresql.org/pgsql-hackers/2002-11/msg00486.php > I seem to recall Tom Lane noticing (uh, a few weeks ago, I think) > that the problem happens not just on system tables. The mechanism I described in the above-referenced message only occurs for nailed-in-cache system tables. Given Daniels' report (and one or two others) I am suspicious that there's some path whereby rd_targblock can fail to get reset after a vacuum for non-system tables too --- but it hasn't been identified yet. If what Daniels saw is due to a problem like that, then it's pretty harmless. If it's something else, the implications might be more dire. regards, tom lane ---(end of broadcast)--- TIP 3: 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] deadlock in REINDEX
I noticed a pretty obscure deadlock condition with REINDEX in CVS HEAD: client1: nconway=# create table a (b int unique, c int unique); CREATE TABLE nconway=# begin; BEGIN nconway=# lock table a in access exclusive mode; LOCK TABLE client2: nconway=# reindex index a_b_key; < blocks, waiting to acquire an access exclusive lock on the heap relation "a" > client1: nconway=# reindex table a; ERROR: deadlock detected Naturally, this situation is not a very common one. But it seems to me that the practice of acquiring locks in REINDEX in an inconsistent order is asking for trouble: REINDEX TABLE locks the heap rel first, followed by any indexes of the heap rel, but REINDEX INDEX locks the target index, followed by the heap rel. Hence a deadlock condition (the explicit lock table above just serves to make the window of opportunity much larger). This should be fixed, right? I was thinking of changing reindex_index() to acquire an AccessShareLock on the index in question, find its parent rel ID, release the lock, then acquire an AccessExclusiveLock on the parent rel, followed by an AccessExclusiveLock on the index in question. Comments? Cheers, Neil P.S. I noticed this because I was browsing through REINDEX, trying to see if it would be possible to allow it to acquire less exclusive locks... -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] POSIX regex performance bug in 7.3 Vs. 7.2
Bruce Momjian <[EMAIL PROTECTED]> writes: > Can this improvement get merged up into CVS current, or did you already > do that Tom? It's irrelevant to current. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] new version of btree_gist
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Oleg Bartunov wrote: > Bruce, > > we just released new version of contrib/btree_gist > (7.3 and current CVS) with support of int8, float4, float8 > in addition to int4. Thanks Janko Richter for contribution. > Could you, please, download entire archive (12Kb) from > http://www.sai.msu.su/~megera/postgres/gist/btree_gist/btree_gist.tar.gz > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] POSIX regex performance bug in 7.3 Vs. 7.2
Can this improvement get merged up into CVS current, or did you already do that Tom? --- Tatsuo Ishii wrote: > > Nice work, Tatsuo! Wade, can you confirm that this patch solves your > > problem? > > > > Tatsuo, please commit into REL7_3 branch only --- I'm nearly ready to do > > a wholesale replacement of the regex code in HEAD, so you wouldn't > > accomplish much except to create a merge problem for me ... > > Ok. I have just committed into the 7.3 stable branch. > -- > Tatsuo Ishii > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_hba.conf hostmask.
Added to TODO: * Allow CIDR format to be used in pg_hba.conf --- Kurt Roeckx wrote: > Currently in pg_hba.conf you specify the ip addresses that can > connect with 2 fields: the ip address and the mask. > > What do you think about changing it to ip address/mask? Where > mask can be both the current mask, or the prefix length. > > It's so much handier to use, especially for ipv6. > > > Kurt > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] COUNT and Performance ...
I didn't think pgstattuple had proper visibility checks.
---
Hans-Jürgen Schönig wrote:
> This patch adds a note to the documentation describing why the
> performance of min() and max() is slow when applied to the entire table,
> and suggesting the simple workaround most experienced Pg users
> eventually learn about (SELECT xyz ... ORDER BY xyz LIMIT 1).
>
> Any suggestions on improving the wording of this section would be
> welcome.
>
> Cheers,
>
>
> --
>
> ORDER and LIMIT work pretty fast (no seq scan).
> In special cases there can be another way to avoid seq scans:
>
>
> action=# select tuple_count from pgstattuple('t_text');
> tuple_count
> -
>14203
> (1 row)
>
> action=# BEGIN;
> BEGIN
> action=# insert into t_text (suchid) VALUES ('10');
> INSERT 578606 1
> action=# select tuple_count from pgstattuple('t_text');
> tuple_count
> -
>14204
> (1 row)
>
> action=# ROLLBACK;
> ROLLBACK
> action=# select tuple_count from pgstattuple('t_text');
> tuple_count
> -
>14203
> (1 row)
>
>
> If people want to count ALL rows of a table. The contrib stuff is pretty
> useful. It seems to be transaction safe.
>
> The performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz):
>
>
> root@actionscouts:~# time psql action -c "select tuple_count from
> pgstattuple('t_text');"
> tuple_count
> -
>14203
> (1 row)
>
>
> real0m0.266s
> user0m0.030s
> sys 0m0.020s
> root@actionscouts:~# time psql action -c "select count(*) from t_text"
> count
> ---
> 14203
> (1 row)
>
>
> real0m0.701s
> user0m0.040s
> sys 0m0.010s
>
>
> I think that this could be a good workaround for huge counts (maybe
> millions of records) with no where clause and no joins.
>
> Hans
>
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Version 7.2.3 Vacuum abnormality
On Thu, Feb 13, 2003 at 03:27:01PM +1000, Paul L Daniels wrote: > Good evening, tonight while running my routine vacuum, the following came up on my >screen: > > ---8<--- > NOTICE: Rel xamefiles: Uninitialized page 708135 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708136 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708137 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708138 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708599 - fixing > ---8<--- This is a known and, it turns out, not real serious bug. See, e.g., http://archives.postgresql.org/pgsql-hackers/2002-11/msg00486.php I seem to recall Tom Lane noticing (uh, a few weeks ago, I think) that the problem happens not just on system tables. I think 7.2.4 is supposed to partially fix this, but ISTR that there is something about it which can't be fixed without forcing a catalog change (which forces initdb, and is therefore Not Allowed for dot-releases). A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Yet another configuration patch with include, and configuration dir
This is a patch that allows PostgreSQL to use a configuration
file that is outside the main database directory.
It adds one more command line parameter, "-C" which
specifies either the location of the postgres configuration
file or a directory containing the configuration files.
A patched version of PostgreSQL will function as:
--- Configuration file ---
postmaster -C /etc/postgres/postgresql.conf
This will direct the postmaster program to use the
configuration file "/etc/postgres/postgresql.conf"
--- Configuration Directory ---
postmaster -C /etc/postgres
This will direct the postmaster program to search the
directory "/etc/postgres" for the standard configuration
file names: posgresql.conf, pg_hba.conf, and pg_ident.conf.
--- postgresql.conf ---
Within the main configuration file are four additional
parameters: include, hba_conf,ident_conf, and data_dir.
They are used as:
include = '/etc/postgres/debug.conf'
data_dir = '/vol01/postgres'
hba_conf = '/etc/postgres/pg_hba_conf'
ident_conf = '/etc/postgres/pg_ident.conf'
The "-D" option on the command line overrides the "data_dir"
in the configuration file.
If no hba_conf and/or ident_conf setting is specified, the default
$PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used.
This patch is intended to move the PostgreSQL configuration out of the
data directory so that it can be modified and backed up.
This patch is also useful for running multiple servers with the same
parameters:
postmaster -C /etc/postgres/postgresql.conf -D /VOL01/postgres -p 5432
postmaster -C /etc/postgres/postgresql.conf -D /VOL02/postgres -p 5433
To apply the patch, enter your PostreSQL source directory, and run:
cat pgec-PGVERSON.patch | patch -p 1
diff -u -r postgresql-7.3.2/src/backend/libpq/hba.c
postgresql-7.3.2.ec/src/backend/libpq/hba.c
--- postgresql-7.3.2/src/backend/libpq/hba.cSat Dec 14 13:49:43 2002
+++ postgresql-7.3.2.ec/src/backend/libpq/hba.c Mon Feb 17 14:57:37 2003
@@ -35,6 +35,7 @@
#include "miscadmin.h"
#include "nodes/pg_list.h"
#include "storage/fd.h"
+#include "utils/guc.h"
#define IDENT_USERNAME_MAX 512
@@ -837,10 +838,22 @@
if (hba_lines)
free_lines(&hba_lines);
- /* Put together the full pathname to the config file. */
- bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
- conf_file = (char *) palloc(bufsize);
- snprintf(conf_file, bufsize, "%s/%s", DataDir, CONF_FILE);
+ /* Explicit HBA in config file */
+ if(explicit_hbafile && strlen(explicit_hbafile))
+ {
+ bufsize = strlen(explicit_hbafile)+1;
+ conf_file = (char *) palloc(bufsize);
+ strcpy(conf_file, explicit_hbafile);
+ }
+ else
+ {
+ char *confloc = (explicit_isdir) ? explicit_pgconfig : DataDir;
+ /* put together the full pathname to the config file */
+ bufsize = (strlen(confloc) + strlen(CONF_FILE) + 2) * sizeof(char);
+ conf_file = (char *) palloc(bufsize);
+ snprintf(conf_file, bufsize, "%s/%s", confloc, CONF_FILE);
+ }
+ /* printf("hba_conf: %s\n", conf_file); */
file = AllocateFile(conf_file, "r");
if (file == NULL)
@@ -979,10 +992,22 @@
if (ident_lines)
free_lines(&ident_lines);
- /* put together the full pathname to the map file */
- bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
- map_file = (char *) palloc(bufsize);
- snprintf(map_file, bufsize, "%s/%s", DataDir, USERMAP_FILE);
+ /* Explicit IDENT in config file */
+ if(explicit_identfile && strlen(explicit_identfile))
+ {
+ bufsize = strlen(explicit_identfile)+1;
+ map_file = (char *) palloc(bufsize);
+ strcpy(map_file, explicit_identfile);
+ }
+ else
+ {
+ /* put together the full pathname to the map file */
+ char *confloc = (explicit_isdir) ? explicit_pgconfig : DataDir;
+ bufsize = (strlen(confloc) + strlen(USERMAP_FILE) + 2) * sizeof(char);
+ map_file = (char *) palloc(bufsize);
+ snprintf(map_file, bufsize, "%s/%s", confloc, USERMAP_FILE);
+ }
+ /* printf("ident_conf: %s\n", map_file); */
file = AllocateFile(map_file, "r");
if (file == NULL)
diff -u -r postgresql-7.3.2/src/backend/postmaster/postmaster.c
postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c
--- postgresql-7.3.2/src/backend/postmaster/postmaster.cWed Jan 15 19:27:17
2003
+++ postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c Mon Feb 17 12:14:12
+2003
@@ -421,7 +421,7 @@
opterr = 1;
- while ((opt = getopt(argc, argv, "A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:")) != -1)
+ while ((opt = getopt(argc, argv, "A:a:B:b:C:c:D:d:Fh:ik:lm:MN:no:p:Ss-:")) !=
+-1)
{
switch (opt)
Re: [HACKERS] new Configuration patch, implements 'include'
Bruce Momjian wrote: mlw wrote: I don't like the idea of specifying a directory, per se' because if you have multiple database installations, how would you share the configuration without symlinks? Oh, for example, you would be sharing postgresql.conf, perhaps, but not pg_hba.conf. Actually, the other way around. An "official" pg_hba.conf and pg_ident.conf setup by an admin, but differing postgresql.conf files for different databases. Does that make sense? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Detecting corrupted pages earlier
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: Tom> Postgres has a bad habit of becoming very confused if the Tom> page header of a page on disk has become corrupted. In Tom> particular, bogus values in the pd_lower field tend to make I haven't read this piece of pgsql code very carefully so I apologize if what I suggest is already present. One "standard" solution to handle disk page corruption is the use of "consistency" bits. The idea is that the bit that starts every 256th byte of a page is a consistency bit. In a 8K page, you'd have 32 consistency bits. If the page is in a "consistent" state, then all 32 bits will be either 0 or 1. When a page is written to disk, the "actual" bit in each c-bit position is copied out and placed in the header (end/beginning) of the page. With a 8K page, there will be one word that contains the "actual" bit. Then the c-bits are all either set or reset depending on the state when the page was last read: if on read time the c-bits were set, then on write time they are reset. So when you read a page, if some of the consistency bits are set and some others are reset then you know that there was a corruption. This is of course based on the assumption that most disk arms manage to atomically write 256 bytes at a time. -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Detecting corrupted pages earlier
Postgres has a bad habit of becoming very confused if the page header of a page on disk has become corrupted. In particular, bogus values in the pd_lower field tend to make it look like there are many more tuples than there really are, and of course these "tuples" contain garbage. That leads to core dumps, weird complaints about out-of-range transaction numbers (the latter generally in the form of an abort referencing a nonexistent pg_clog file), and other un-fun stuff. I'm thinking of modifying ReadBuffer() so that it errors out if the page read in does not contain either zeroes or a valid-looking header. (The exception for zeroes seems to be needed for hash indexes, which tend to initialize pages out-of-order.) This would make it much easier for people to recognize situations where a page header has become corrupted on disk. Comments? Can anyone think of a scenario where this would be a bad idea? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] new Configuration patch, implements 'include'
mlw wrote: > I don't like the idea of specifying a directory, per se' because if you > have multiple database installations, how would you share the > configuration without symlinks? Oh, for example, you would be sharing postgresql.conf, perhaps, but not pg_hba.conf. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Questions about indexes?
Curt Sampson wrote: > On Mon, 16 Feb 2003, Ryan Bradetich wrote: > > Since my only requirement is that the rows be unique, I have developed a > > custom MD5 function in C, and created an index on the MD5 hash of the > > concatanation of all the fields. > > Well, that won't guarantee uniqueness, since it's perfectly possible > to have two different rows hash to the same value. (If that weren't > possible, your hash would have to contain as much information as the row > itself, and your space savings wouldn't be nearly so dramatic.) That's true, but even if he has 4 billion rows it drops the probability of a duplicate down to something like one in 4 billion, so it's probably a safe enough bet. His application doesn't require absolute uniqueness, fortunately, so md5 works well enough in this case. Otherwise md5 wouldn't be a terribly good hash... -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] new Configuration patch, implements 'include'
Tom Lane wrote: mlw <[EMAIL PROTECTED]> writes: If no hba_conf and/or ident_conf setting is specified, the default $PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used. Doesn't anybody see the (a) inconsistency and (b) uselessness of this? If you are trying to keep your config files out of the data directory, it's hardly sensible to default to finding two out of three there. We should have a -C that specifies a *directory*, and all three config files should be sought therein. The argument that that somehow forces people to use symlinks doesn't convince me at all. But I've grown tired of arguing, because it's clear that I'm making no impact whatever :-(. I'm done with this thread. Tom, I don't know why you are arguing at all. One thing I wish to impress on you, I think it is a point of view you are missing. It isn't about something being "easier" as much as it is about being flexable enough to fit into the deployment strategy of the admin or vendor. Sometimes you make things more difficult when you make it more standardized. When I setup a system with Apache, PHP, PostgreSQL, named, et al, I am always just irritated that PostgreSQL's configuration parameters can not be stored with all the others. I usually make one install tarball or zip that contains all the binaries and configuration. I can't do that with PostgreSQL. I don't like the idea of specifying a directory, per se' because if you have multiple database installations, how would you share the configuration without symlinks? I will modify my patch to check if the configuration parameter is a directory. If it is, it will make the default filenames within the directory and post it when it is gone. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] new Configuration patch, implements 'include'
Tom Lane wrote: > mlw <[EMAIL PROTECTED]> writes: > > If no hba_conf and/or ident_conf setting is specified, the default > > $PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used. > > Doesn't anybody see the (a) inconsistency and (b) uselessness of this? > If you are trying to keep your config files out of the data directory, > it's hardly sensible to default to finding two out of three there. > > We should have a -C that specifies a *directory*, and all three config > files should be sought therein. The argument that that somehow forces > people to use symlinks doesn't convince me at all. I think the issue here is that symlinks are OK to implement unusual configuration cases, and I think we can say having the three config files in different directories is unusual. I think you have to weigh the downside of using symlinks for rare configurations compared to the complexity of specifying the config file locations in three separate cases. I had thrown out the idea of putting the config files in their own directory _under_ /data, like /data/etc, so you could just symlink that directory to somewhere else. Makes backups of config files easy, and makes it initdb-safe, because only the symlink can be under /data. However, no one commented on it, so I assume they didn't like it. It seems like a nice middle ground to me. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] IpcSemaphoreKill: ...) failed: Invalid argument
Christoph Haller wrote: > No, I'm not sure at all about a loose-cannon script running around > issuing ipcrm commands. > I have to ask the other staff members what scripts are running. > I already had a suspicion that something like an ipcrm command is > causing this, > but it was denied. Now, with your support they probably will believe me. If you want to track it down and the people on your staff don't already know what's going on, you can move the ipcrm binary out of the way (to, say, ipcrm.bin) and replace it with a shell script that looks something like this: #!/bin/sh (echo "ipcrm called with the following arguments:" echo for i in "$@" do echo "$i" ; done echo echo "Current programs running:" echo ps -elf) >/tmp/ipcrm.out.$$ exec "$0".bin "$@" Then just look for /tmp/ipcrm.out.* files and examine their contents. (I think I got the arguments to ps right. It's been so long since I've had to mess with a SysVr4 style system that I'm not sure anymore. If it's a BSD-style ps then the arguments should be -auxww). -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Sun, Feb 16, 2003 at 12:16:44AM -0500, Tom Lane wrote: > Nor will I buy an argument that only a few developers have need for test > installations. Ordinary users will want to do that anytime they are > doing preliminary tests on a new PG version before migrating their > production database to it. To the extent that you make manual selection > of a nonstandard datadir location more difficult and error-prone, you > are hurting them too. Not only that. For safety's sake, you may need to run multiple postmasters on one machine (so that database user X can't DoS database user Y, for instance). And making that sort of production-grade work more difficult and error-prone would also be bad. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Hard problem with concurrency
Christopher Kings-Lynne wrote: > > If you don't have a primary key already, create a unique index on the > > combination you want to be unique. Then: > > > > . Try to insert the record > > . If you get a duplicate key error > > then do update instead > > > > No possibilities of duplicate records due to race conditions. If two > people > > try to insert/update at the same time you'll only get one of the two > results, > > but that's the downside of the general approach you've taken. It's a tad > > inefficient if the usual case is updates, but certainly not less efficient > > than doing table locks. > > The idea was to stop our postgres logs being spammed up with unique > constraint violation warningsin which case your solution above is > identical to our current one. Update and if it fails, insert, except since > the row is likely to already be there - our current way will be a bit more > efficient. To control the spamming, use server_min_messages before the INSERT. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Fri, Feb 14, 2003 at 10:35:41AM -0500, Tom Lane wrote: > FHS or no FHS, I would think that the preferred arrangement would be to > keep Postgres' config files in a postgres-owned subdirectory, not > directly in /etc. That way you need not be root to edit them. (My idea Besides, what are you going to do for people installing on a box where they don't have root? Are they going to need a whole mess of extra directories in their private copy? > of an editor, Emacs, always wants to write a backup file, so I dislike > having to edit files that live in directories I can't write.) > > Here's a pretty topic for a flamewar: should it be /etc/postgres/ or > /etc/postgresql/ ? Wow, two flamewar topics in one mail. I'm impressed. Andrew "ed is the one true editor" Sullivan -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: 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] new Configuration patch, implements 'include'
mlw <[EMAIL PROTECTED]> writes: > If no hba_conf and/or ident_conf setting is specified, the default > $PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used. Doesn't anybody see the (a) inconsistency and (b) uselessness of this? If you are trying to keep your config files out of the data directory, it's hardly sensible to default to finding two out of three there. We should have a -C that specifies a *directory*, and all three config files should be sought therein. The argument that that somehow forces people to use symlinks doesn't convince me at all. But I've grown tired of arguing, because it's clear that I'm making no impact whatever :-(. I'm done with this thread. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
Kevin Brown <[EMAIL PROTECTED]> writes: > Is it possible for the database engine to properly deal with a > database when it is told to use a different database encoding than the > one the database was initdb'd with? It can't be "told to use a different database encoding". However, the default *client* encoding matches the database encoding, and that is something that can be set in the config file. >> If we do it the way I suggested (dump into the datadir, which is >> initially empty, same as always) then it cannot overwrite your existing >> config files. Think of it as providing a suggested config file to >> compare against what you have. > There is one minor complication: what if there's an existing config > file in the target directory? If there's anything at all in the target directory, initdb refuses to run. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] new Configuration patch, implements 'include'
This is a patch that allows PostgreSQL to use a configuration
file that is outside the main database directory.
It adds one more command line parameter, "-C" which
specifies the location of the postgres configuration file.
A patched version of PostgreSQL will function as:
postmaster -C /etc/postgres/postgresql.conf
This will direct the postmaster program to use the
configuration file "/etc/postgres/postgresql.conf"
Within this file are four additional parameters: include,
hba_conf,ident_conf, and data_dir.
They are used as:
include = '/etc/postgres/debug.conf'
data_dir = '/vol01/postgres'
hba_conf = '/etc/postgres/pg_hba_conf'
ident_conf = '/etc/postgres/pg_ident.conf'
The "-D" option on the command line overrides the "data_dir"
in the configuration file.
If no hba_conf and/or ident_conf setting is specified, the default
$PGDATA/pg_hba.conf and/or $PGDATA/pg_ident.conf will be used.
This patch is intended to move the PostgreSQL configuration out of the
data directory so that it can be modified and backed up.
This patch is also useful for running multiple servers with the same
parameters:
postmaster -C /etc/postgres/postgresql.conf -D /VOL01/postgres -p 5432
postmaster -C /etc/postgres/postgresql.conf -D /VOL02/postgres -p 5433
To apply the patch, enter your PostreSQL source directory, and run:
cat pgec-PGVERSON.patch | patch -p 1
diff -u -r postgresql-7.3.2/src/backend/libpq/hba.c
postgresql-7.3.2.ec/src/backend/libpq/hba.c
--- postgresql-7.3.2/src/backend/libpq/hba.cSat Dec 14 13:49:43 2002
+++ postgresql-7.3.2.ec/src/backend/libpq/hba.c Mon Feb 17 09:30:15 2003
@@ -35,6 +35,7 @@
#include "miscadmin.h"
#include "nodes/pg_list.h"
#include "storage/fd.h"
+#include "utils/guc.h"
#define IDENT_USERNAME_MAX 512
@@ -837,10 +838,20 @@
if (hba_lines)
free_lines(&hba_lines);
- /* Put together the full pathname to the config file. */
- bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
- conf_file = (char *) palloc(bufsize);
- snprintf(conf_file, bufsize, "%s/%s", DataDir, CONF_FILE);
+ /* Explicit HBA in config file */
+ if(explicit_hbafile && strlen(explicit_hbafile))
+ {
+ bufsize = strlen(explicit_hbafile)+1;
+ conf_file = (char *) palloc(bufsize);
+ strcpy(conf_file, explicit_hbafile);
+ }
+ else
+ {
+ /* put together the full pathname to the config file */
+ bufsize = (strlen(DataDir) + strlen(CONF_FILE) + 2) * sizeof(char);
+ conf_file = (char *) palloc(bufsize);
+ snprintf(conf_file, bufsize, "%s/%s", DataDir, CONF_FILE);
+ }
file = AllocateFile(conf_file, "r");
if (file == NULL)
@@ -979,10 +990,20 @@
if (ident_lines)
free_lines(&ident_lines);
- /* put together the full pathname to the map file */
- bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
- map_file = (char *) palloc(bufsize);
- snprintf(map_file, bufsize, "%s/%s", DataDir, USERMAP_FILE);
+ /* Explicit IDENT in config file */
+ if(explicit_identfile && strlen(explicit_identfile))
+ {
+ bufsize = strlen(explicit_identfile)+1;
+ map_file = (char *) palloc(bufsize);
+ strcpy(map_file, explicit_identfile);
+ }
+ else
+ {
+ /* put together the full pathname to the map file */
+ bufsize = (strlen(DataDir) + strlen(USERMAP_FILE) + 2) * sizeof(char);
+ map_file = (char *) palloc(bufsize);
+ snprintf(map_file, bufsize, "%s/%s", DataDir, USERMAP_FILE);
+ }
file = AllocateFile(map_file, "r");
if (file == NULL)
diff -u -r postgresql-7.3.2/src/backend/postmaster/postmaster.c
postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c
--- postgresql-7.3.2/src/backend/postmaster/postmaster.cWed Jan 15 19:27:17
2003
+++ postgresql-7.3.2.ec/src/backend/postmaster/postmaster.c Mon Feb 17 09:30:15
+2003
@@ -421,7 +421,7 @@
opterr = 1;
- while ((opt = getopt(argc, argv, "A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:")) != -1)
+ while ((opt = getopt(argc, argv, "A:a:B:b:C:c:D:d:Fh:ik:lm:MN:no:p:Ss-:")) !=
+-1)
{
switch (opt)
{
@@ -441,6 +441,9 @@
case 'b':
/* Can no longer set the backend executable file to
use. */
break;
+ case 'C': // MLW
+ explicit_pgconfig = optarg;
+ break;
case 'D':
potential_DataDir = optarg;
break;
@@ -564,13 +567,23 @@
ExitPostmaster(1);
}
- /*
-* Now we can set the data directory, and then read postgresql.conf.
Re: [HACKERS] client_encoding directive is ignored in
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > The database encoding is set to the encoding when the database was > created and the default value of the client encoding is set to same as > the database encoding. This behavior will not be changed by the change > I proposed. As long as it still behaves that way by default, I guess we won't create any surprises. Okay, I withdraw my objection. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Questions about indexes?
On Mon, 16 Feb 2003, Ryan Bradetich wrote: > I am not sure why all the data is duplicated in the index ... Well, you have to have the full key in the index, or how would you know, when you look at a particular index item, if it actually matches what you're searching for? MS SQL server does have an interesting option that would help you a lot in this case: clustered indexes. A table may have a single clustered index, and each leaf node of the index stores not just the key but actually the entire row. Thus, in a case like yours, you'd store the row only once, not twice. Without thinking too hard about it (my usual mode of operation on this list :-)) this could probably be implemented in postgresql. But I don't think it would be entirely trivial, and your case is unusual enough that I very much doubt whether it would be worth implementing to fix that alone. It would also offer the advantage that any lookup using the clustered index would save fetching the heap page after that as well, but it's hard to say if the savings would be worth the work. > Since my only requirement is that the rows be unique, I have developed a > custom MD5 function in C, and created an index on the MD5 hash of the > concatanation of all the fields. Well, that won't guarantee uniqueness, since it's perfectly possible to have two different rows hash to the same value. (If that weren't possible, your hash would have to contain as much information as the row itself, and your space savings wouldn't be nearly so dramatic.) cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] IpcSemaphoreKill: ...) failed: Invalid argument
> > This is a fairly spectacular failure :-(. As far as I can see from the > semctl and shmctl man pages, the only plausible reason for EINVAL is > that something had deleted the semaphores and shared memory out from > under Postgres. I do not believe that Postgres itself could have done > that --- it had to be some external agency. Unless the kernel is > broken, whatever requested those deletions had to be running as root or > as postgres in order to have the necessary permissions. You sure you > didn't have some loose-cannon script running around issuing ipcrm > commands? > No, I'm not sure at all about a loose-cannon script running around issuing ipcrm commands. I have to ask the other staff members what scripts are running. I already had a suspicion that something like an ipcrm command is causing this, but it was denied. Now, with your support they probably will believe me. Thanks for the quick reply. Regards, Christoph ---(end of broadcast)--- TIP 3: 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] IpcSemaphoreKill: ...) failed: Invalid argument
Christoph Haller <[EMAIL PROTECTED]> writes: > I've seen this (see below) in the postmaster's log-file. > I doubt this is normal behaviour. > I'm using PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 > Does anybody know what may cause calls to semctl resp. shmctl > (semaphore control resp. shared memory control) to fail? FWIW, I do all my Postgres development on HPUX 10.20 with gcc, and I've never seen anything like this. > ZeroProcSemaphore: semctl(id=2450,SETVAL) failed: Invalid argument > DEBUG: server process (pid 10237) exited with exit code 255 > DEBUG: terminating any other active server processes > DEBUG: all server processes terminated; reinitializing shared memory > and semaphores > IpcSemaphoreKill: semctl(707088, 0, IPC_RMID, ...) failed: Invalid > argument > IpcSemaphoreKill: semctl(2449, 0, IPC_RMID, ...) failed: Invalid > argument > IpcSemaphoreKill: semctl(2450, 0, IPC_RMID, ...) failed: Invalid > argument > IpcMemoryDelete: shmctl(312410, 0, 0) failed: Invalid argument This is a fairly spectacular failure :-(. As far as I can see from the semctl and shmctl man pages, the only plausible reason for EINVAL is that something had deleted the semaphores and shared memory out from under Postgres. I do not believe that Postgres itself could have done that --- it had to be some external agency. Unless the kernel is broken, whatever requested those deletions had to be running as root or as postgres in order to have the necessary permissions. You sure you didn't have some loose-cannon script running around issuing ipcrm commands? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Questions about indexes?
Ryan Bradetich <[EMAIL PROTECTED]> writes: > the table would look like: > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user x has an invalid shell. > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has an invalid shell. Ah, I see your point now. (Thinks: what about separating the "anomaly" column into an "identifier" and a "complaint" column: 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | x| user has an invalid shell. 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | y| user has an invalid shell. 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | y| user has expired password. 2 | Mon Feb 17 00:34:24 MST 2003 | f101 | /foo | file has improper owner. No, that doesn't quite work either, unless you are willing to make the categories more specific. At which point the category and the anomaly text become equivalent. Actually I'm wondering why you bother with the category at all; isn't it implied by the anomaly text?) > I agree with you, that I would not normally add the anomally to the > index, except for the unique row requirement. Thinking about it now, > maybe I should guarentee unique rows via a check constraint... A check constraint won't be efficient either, at least not without a supporting index. Possibly you could index just the host and timestamp columns, which would not be unique but it would cut the number of rows the constraint would need to examine to something manageable. But I'm still thinking that enforcing uniqueness is a waste of time. What exactly is so harmful about it if 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user x has an invalid shell. appears twice? How likely is that anyway (especially if you don't truncate the timestamp precision)? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgresql and oracle, compatibility assessment
Martin Matusiak kirjutas E, 17.02.2003 kell 16:53: > Greetings, > > I am doing a project for college developing a java system utilizing a > RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering > exactly how impossible would it be to make it compatible with both. > Postgre is said to be completely ANSI SQL complaint, PostgreSQL is *much* more ANSI comliant than Oracle. > is it feasible to imagine one could run dumps from Postgre into Oracle > and vice versa? Not all data types are named the same (Oracles varchar is varchar2, etc.) > Alternatively, run separate queries on both to set the structure and > relations, then transfer only data? Much more likely to succeed. You may be interested in OpenACS ( http://openacs.org/ ) project, which runs on both Oracle and PostgreSQL. - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] postgresql and oracle, compatibility assessment
Greetings, I am doing a project for college developing a java system utilizing a RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering exactly how impossible would it be to make it compatible with both. Postgre is said to be completely ANSI SQL complaint, is it feasible to imagine one could run dumps from Postgre into Oracle and vice versa? Alternatively, run separate queries on both to set the structure and relations, then transfer only data? PostgreSQL 7.3 Oracle 8.x Best regards, Martin Matusiak
[HACKERS] IpcSemaphoreKill: ...) failed: Invalid argument
Hi, I've seen this (see below) in the postmaster's log-file. I doubt this is normal behaviour. I'm using PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 Does anybody know what may cause calls to semctl resp. shmctl (semaphore control resp. shared memory control) to fail? The application program ( C code using the libpq - C Library ) crashed because of a segmentation violation. I've searched the archive for ZeroProcSemaphore IpcSemaphoreKill IpcMemoryDelete with no results. Any hints welcome. Thanks in advance. Regards, Christoph DEBUG: database system is ready NOTICE: COMMIT: no transaction in progress NOTICE: COMMIT: no transaction in progress DEBUG: pq_recvbuf: unexpected EOF on client connection DEBUG: pq_recvbuf: unexpected EOF on client connection ZeroProcSemaphore: semctl(id=2450,SETVAL) failed: Invalid argument DEBUG: server process (pid 10237) exited with exit code 255 DEBUG: terminating any other active server processes DEBUG: all server processes terminated; reinitializing shared memory and semaphores IpcSemaphoreKill: semctl(707088, 0, IPC_RMID, ...) failed: Invalid argument IpcSemaphoreKill: semctl(2449, 0, IPC_RMID, ...) failed: Invalid argument IpcSemaphoreKill: semctl(2450, 0, IPC_RMID, ...) failed: Invalid argument IpcMemoryDelete: shmctl(312410, 0, 0) failed: Invalid argument DEBUG: database system was interrupted at 2003-02-17 11:22:36 MET DEBUG: checkpoint record is at 0/47EA788 DEBUG: redo record is at 0/47EA788; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 16242; next oid: 368814 DEBUG: database system was not properly shut down; automatic recovery in progress DEBUG: redo starts at 0/47EA7C8 DEBUG: ReadRecord: record with zero length at 0/48864B8 DEBUG: redo done at 0/4886490 DEBUG: database system is ready ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hard problem with concurrency
On Sun, Feb 16, 2003 at 23:51:49 -0500, Greg Stark <[EMAIL PROTECTED]> wrote: > > Hm, odd, nobody mentioned this solution: > > If you don't have a primary key already, create a unique index on the > combination you want to be unique. Then: > > . Try to insert the record > . If you get a duplicate key error > then do update instead I think this solution depends on records not being deleted. Otherwise a record could be deleted between the insert attempt and the update attempt. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] client_encoding directive is ignored in
> But this argument is mostly irrelevant if the proposed change will not > affect behavior in a default installation. I guess I'm not entirely > clear on exactly which cases it will affect. What will your proposed > change do in each possible combination (database encoding is SQL_ASCII > or not, client_encoding is defined in postgresql.conf or not, > PGCLIENTENCODING is set in postmaster's environment or not, etc)? The database encoding is set to the encoding when the database was created and the default value of the client encoding is set to same as the database encoding. This behavior will not be changed by the change I proposed. Anyway I will list up none default behaviors. case 1: PGCLIENTENCODING environment variable is set when postmaster starts up case 2: -c 'client_encoding=some_encoding is set when postmaster starts up case 3: PGOPTIONS environment variable is set frontend starts up case 4: GUC variable client_encoding is set current behavior: show client_encoding shows the encoding set by PGCLIENTENCODING/postmaster opton/PGOPTIONS/GUC variable but actual client encoding is same as the database encoding. Thus pg_client_encoding() returns the database encoding. Also client/database encoding conversion is not peformed(bug). After changes: show client_encoding shows the encoding set by PGCLIENTENCODING/postmaster opton/PGOPTIONS/GUC variable. pg_client_encoding() returns the client encoding as expected. Client/database encoding conversion will be peformed. case 5: PGCLIENTENCODING environment variable is set frontend starts up case 6: SET client_encoding command is issued current behavior: show client_encoding shows the encoding set by PGCLIENTENCODING/SET command. pg_client_encoding() returns the client encoding as expected. Client/database encoding conversion will be peformed. After changes: same as above. -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Questions about indexes?
> I ended up with few only indexes on the operations table, because the > processes that fill it up do minimal lookups to see if data is already in the > table, if not do inserts. Then at regular intervals, the table is cleaned up - > that is, a process to remove the duplicate is run. This unfortunately costs > OIDs, but I found no other reasonable way to do the fast inserts. Perhaps the > best way is to create the table without OIDs (but wouldn't this still waste > OIDs?) use COPY and then clean afterwards? No, WITHOUT OIDS is implemented specifically to not waste OIDs. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Questions about indexes?
>>>Ryan Bradetich said: > the table would look like: > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user x has an invalid shell. > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has an invalid shell. > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has expired password. > 2 | Mon Feb 17 00:34:24 MST 2003 | f101 | file /foo has improper owner. > etc... > > So I do not need the anomaly to be part of the index, I only need it to > > I agree with you, that I would not normally add the anomally to the > index, except for the unique row requirement. Thinking about it now, > maybe I should guarentee unique rows via a check constraint... > > Thanks for making me think about this in a different way! (sorry this is a bit long) Ryan, I use somewhat similarly structured data (archived records of various events) and when the database was setup (back when this baby was called postgres95), I too used indexes on all possible fields. My database consists of an 'operations' table, which holds for the last x days period (example) and several tables with archived records (per month, or per-year - see later, The operations table can have frequent updates, which add new data. Data is never modified but often lookups are made. The archived tables are generated once and forever from the operations table (possibly merging in the future, but I haven't yet made my mind on this) - then access is read-only, although sufficiently frequent. What I found for the many years of operating this database on different PostgreSQL versions and hardware is that indexes have considerable cost. :) So does the need to not miss anything from the operations table (that is, collect data from many places and have have it all it there). I ended up with few only indexes on the operations table, because the processes that fill it up do minimal lookups to see if data is already in the table, if not do inserts. Then at regular intervals, the table is cleaned up - that is, a process to remove the duplicate is run. This unfortunately costs OIDs, but I found no other reasonable way to do the fast inserts. Perhaps the best way is to create the table without OIDs (but wouldn't this still waste OIDs?) use COPY and then clean afterwards? The archived tables are generated, then cleaned up. Then, as Tom suggested indexes are put on the archived tables, only for the fields that are used in queries. Once the table is created, there is no way duplicated data will exist, as it will not be inserted into. Therefore no need for UNIQUE index enforcement. If you need to have one large 'history' table, then perhaps you will just have to do (slow :) selects for each record before each insert, or just insert the data and then run the cleanup process. Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
