[HACKERS] a question for share memory
Dear sir: Now ,I face a problem of expanding structure shmemvariableCache in the share memory. When it comes togive value to this new field.I can;t initialize the system. Thank you for your respond! Do You Yahoo!? 60
Re: [HACKERS] PITR Functional Design v2 for 7.5
On Monday 08 March 2004 23:28, Simon Riggs wrote: PITR Functional Design v2 for 7.5 Blimey - that's a long post :-) Thanks for explaining things simply enough that a non-hacker like me can understand. Well - almost understand ;-) Review of current Crash Recovery Is there any value in putting this section on techdocs or similar? We do get a small but steady trickle of people asking for details on internals, and I think this covers things in a different way to the WAL section of the manuals. PITR Proposed Solution To allow this to occur, the full backup *must* occur while the database is open or hot. This backup must include all data and clogs (and any tablespaces or logical links utilised). A continuous sequence of xlogs must also be available, stretching from the last checkpoint prior to the start of the backup through to whatever time is specified for the recovery point or until the end of the xlogs. So this is a standard cp/tar etc while the cluster is actually in use? XLogArchiveXlogs() returns a single XLOG filename, or NULL If an xlog file is waiting to be archived, then the archiver will discover the name of the xlog by using this API call. If more than one file is available to be archived, then it will be ignored. If the archiver is multi-threaded, it need not wait until it has executed XLogArchiveComplete before it executes XLogArchiveXlogs again. So this means: 1. The archiver is responsible for noticing that it is already archiving the filename returned (if it repeats the call too quickly). 2. The archiver can only ever archive one XLOG file at a time. The initial proposal is a simple scheme that uses file existence file extension to pass information between PostgreSQL and the archiver. This would take place in a peer directory of pg_xlog and pg_clog which has been named the pg_rlog directory. (r as in the strong first syllable ar in English pronunciation of archive) Any reason why not pg_pitr or pg_pitr_log? 1.2 pg_arch: simple xlog archiving tool Src/tools/ will add: pg_arch.c a single-threaded program that uses libpgarch.c to use API, but offers a simple copy facility from pg_xlog to another directory. The program will continue to wait and watch for archived files: it is not a file-filter type of program. It may be run as a foreground process (for testing etc), though is also designed to be run as a background process, typically executed at the same time as postmaster startup (through a mechanism such as service autostart mechanisms following system boot). pg_arch has two parameters: -D data-file root for particular instance of PostgreSQL -A archive directory Does the specification of these parameters (and any others) need to be part of the API? I'm thinking about the ability to drop in different archivers with each using the same pre-defined settings. 2. Recovery to Point-in-Time (RPIT) Recovery to will offer these options: 2.1 Recovery to end of logs (last time) 2.2 Recovery of all available on-line logs 2.3 Point in time recovery to the checkpoint AT or the last checkpoint before the time specified. The administrator is expected to be responsible for placing archived xlogs back into the pg_xlog directory. This may be a facility provided by the external archiver, a manual or other automated process. If any mistakes are made at this point then the administrator can then reselect appropriate xlogs and try again. There is no enforced limit to the number of recovery attempts possible. Just to clarify: 1. I can identify which XLOG files I need based on their timestamp? 2. Can I force a checkpoint using standard PG client APIs? So I can do close weekly payroll, force checkpoint. 3. We're restoring an entire cluster here, not just one database? How difficult would it be to strip out information for a single db - I'm thinking about the case where you may have limited backup storage and want to save an orders db but not a catalogue db. Or perhaps a hosting company with platinum customers getting PITR. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] NO WAIT ...
Here is the patch I promised (against current). Regression tests all
passed. One thing I have not checked is the doc(lock.sgml). For some
reason I failed to install docbook V4.2 (I have working docbook V3.1
though), and I couldn't test the correctness of the file. Also, it
would be nice if some one checks my English grammer:-)
--
Tatsuo Ishii
It seems NOWAIT is the winner...
--
Tatsuo Ishii
Oracle uses NOWAIT so we should go for that one.
Regards,
Hans
Tatsuo Ishii wrote:
If NOWAIT is the choice, I could live with it. If there's no
objection, I will go with NOWAIT, not NO WAIT.
--
Tatsuo Ishii
Tatsuo Ishii [EMAIL PROTECTED] writes:
LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
against current with some docs changes.
Dept of minor gripes: can we do this without turning NO into a
keyword? Even as a nonreserved word, I think that would be annoying.
no is a common abbreviation for number so I think it's likely to
get used as a column name.
If Oracle spells it NOWAIT then I'd be much happier with that...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Index: doc/src/sgml/ref/lock.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/lock.sgml,v
retrieving revision 1.40
diff -c -r1.40 lock.sgml
*** doc/src/sgml/ref/lock.sgml 14 Dec 2003 00:05:29 - 1.40
--- doc/src/sgml/ref/lock.sgml 9 Mar 2004 12:42:31 -
***
*** 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
--- 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ] [ NOWAIT ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
***
*** 34,41
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released. Once obtained,
!the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
--- 34,43
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released.
!If literalNOWAIT/literal is given, commandLOCK TABLE/command
!does not wait for acquiring lock, and throws an error instead.
!Once obtained, the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
Index: src/backend/access/heap/heapam.c
===
RCS file: /cvsroot/pgsql-server/src/backend/access/heap/heapam.c,v
retrieving revision 1.162
diff -c -r1.162 heapam.c
*** src/backend/access/heap/heapam.c16 Jan 2004 20:51:30 - 1.162
--- src/backend/access/heap/heapam.c9 Mar 2004 12:42:33 -
***
*** 464,469
--- 464,496
return r;
}
+ Relation
+ conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool nowait)
+ {
+ Relationr;
+
+ Assert(lockmode = NoLock lockmode MAX_LOCKMODES);
+
+ /* The relcache does all the real work... */
+ r = RelationIdGetRelation(relationId);
+
+ if (!RelationIsValid(r))
+ elog(ERROR, could not open relation with OID %u, relationId);
+
+ if (lockmode != NoLock)
+ {
+ if (nowait)
+ {
+ if (!ConditionalLockRelation(r, lockmode))
+ elog(ERROR, could not aquire relation lock);
+ }
+ else
+ LockRelation(r, lockmode);
+ }
+
+ return r;
+ }
+
/*
*relation_openrv - open any relation specified by a RangeVar
*
Re: [HACKERS] raising the default default_statistics_target
I think the thread you're thinking of is on or about this post:
http://archives.postgresql.org/pgsql-patches/2003-12/msg00039.php
Manfred Koizar produced a patch that modified index correlation by
sorting equal key values based on item pointers. The patch went as far
as getting accepted into the patch queue, but Tom raised some doubts
about it and it was subsequently removed.
Robert Treat
On Mon, 2004-03-08 at 14:41, Josh Berkus wrote:
Tom,
Are you sure you're not thinking of stats for functional indexes?
Positive.I even remember seeing that the patch was accepted.
The patch specifically had to do with a multi-column correlation algorithm for
improving the selectivity of multi-column indexes.
Problem is, with 1400 posts per month August to October, I can't find it, and
the keywords that I think are obvious don't turn anything up.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] [PATCHES] log_line_info
Bruce Momjian wrote: Andrew Dunstan wrote: After this is applied (fingers crossed) and everyone is happy, I will submit a patch to remove log_timestamp, log_pid and (if we are agreed on it) log_source_port. I have applied this patch. It will be a great addition to PostgreSQL. Is there agreement on removing these 3 config vars? cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] raising the default default_statistics_target
Robert Treat [EMAIL PROTECTED] writes: I think the thread you're thinking of is on or about this post: http://archives.postgresql.org/pgsql-patches/2003-12/msg00039.php Manfred Koizar produced a patch that modified index correlation by sorting equal key values based on item pointers. The patch went as far as getting accepted into the patch queue, but Tom raised some doubts about it and it was subsequently removed. Hm, that had nothing to do with multi-column correlation though. I'm at a loss to think of any work that matches with Josh's recollection. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] NO WAIT ...
Tatsuo Ishii [EMAIL PROTECTED] writes: Here is the patch I promised (against current). This is missing the necessary adjustments in backend/nodes/ (copy and equal funcs). Also the NOWAIT keyword must be added to the list of nonreserved keywords near the bottom of gram.y. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] log_line_info
Andrew Dunstan [EMAIL PROTECTED] writes: After this is applied (fingers crossed) and everyone is happy, I will submit a patch to remove log_timestamp, log_pid and (if we are agreed on it) log_source_port. Is there agreement on removing these 3 config vars? Now that I look at it, log_source_port does look pretty useless. While you are at it, please clean up the confusion about whether port-remote_host contains the port number or not (IMHO it should never do so; BackendInit() is doing things in the wrong order). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] log_line_info
After this is applied (fingers crossed) and everyone is happy, I will submit a patch to remove log_timestamp, log_pid and (if we are agreed on it) log_source_port. Is there agreement on removing these 3 config vars? There is from me. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] raising the default default_statistics_target
Guys, Hm, that had nothing to do with multi-column correlation though. I'm at a loss to think of any work that matches with Josh's recollection. H it's possible that early e-mails about Manfred's patch claimed to improve performance for multi-column indexes. But it's also possible I'm remembering something else. Darn it, though! 'cause multi-column correlation is one of our big issues on estimates for complex queries. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] raising the default default_statistics_target
On Sun, 7 Mar 2004, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Tom Lane wrote: This is something we need to consider, but we'll need more evidence before making a choice. One thing that we have very little data about is how much difference it makes in the quality of planner choices. Right, but is there a practical way to actually get this data? I haven't thought of one yet, but perhaps someone will have an idea. Hi Tom. I ran some very simple tests on analyze times and query plan times on a very simple table, with data randomly distributed. The index was on a date field, since that's what I was testing last. This was all done on my 512Meg memory 1.1GHz celeron workstation with an IDE drive. I'd love more input on better testing methodologies here... with 100k or 1M rows that look kinda like this: (I'll test 10M rows later, which means the dataset won't fit in memory, so there'll be lots of access going on. Right now the 1M row table is 80 meg) select * from test2 limit 5; info | dt | id -+-+- Francize perfectible swirling fluctuates| 2004-05-20 20:12:04 | 2721995 Fields chauffeur attentionality grandmother | 2004-04-07 14:36:02 | 2721996 Belgium bilked explosively defendant| 2004-09-16 16:27:22 | 2721997 perspectives Buenos Pollux discriminates| 2004-11-11 12:28:31 | 2721998 Victorianize Savonarola blackmails sufficed | 2004-02-27 21:17:20 | 2721999 (5 rows) here's what I get with different statistics targets for analyze times: 100k1M 1M analyze analyze plan target ms ms ms 10 250 875 2 20 350 1250 30 430 1500 40 520 1725 50 580 1900 60 690 2100 70 775 2175 80 850 2300 90 950 2400 100 100026002.5 200 18063700 300 26004800 400 26005900 500 26007200 700 26009500 1000260013000 5 Since this data is randomly distributed, I didn't bother doing a lot of testing to see how accurate each target setting was. If that would be useful to know I'd gladly test it, but I was only setting out to test the time to analyze and the time to plan. Note that I only tested 3 targets for planning time, as it didn't seem to make a very big difference. The query was: select * from test2 where dt between 'march 11, 2004' and 'march 13, 2004'; I also ran some quick tests on smaller tables (1000 and 10k rows) and there, the plateau that we see in the 100k analyze shows up much quicker, at something like 50 or so. I.e. the analyze time flattened out quickly and higher numbers cost very little if anything. Since this query was quite an easy plan, I'd expect to need a much more complex one to test the increase in planning time, say something that has to look at a lot of statistics. Any particular join type or something that's likely to do that? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] ECPG - Specifying connections, TSD, sqlca.
Shridhar, Once the patches I've put forward are applied there's still a further change I've got planned which will remove the mutex locking in the common case - a NULL/DEFAULT connection parameter (I'll post a patch soon). This leaves the threaded case with comparable performance to the non-threaded case (both have a function call to get the connection, plus a getspecific call in the threaded case). As such is there much benefit in adding support for the connection being supplied by a struct pointer? You'd also have to add in something like EXEC SQL GET DESCRIPTION xxx to get the pointer too. How would it improve things over how they are in the test_thread_implicit test program? I still think it's worthwhile investigating the use of GCC's __thread storage class specifier to remove the use of pthread_*specific in this case. This would also be a help to the WIN32 port since this specifier maps well to similar constructs in Microsoft's and Borland's compilers (see thread item in the TODO at developer.postgresql.org). And I still can't see how you'll bind sqlca to the connection object, but best of luck! Regards, Lee K. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [OT] Respository [was Re: [PERFORM] Feature request: smarter
Shridhar Daithankar wrote: On Sunday 07 March 2004 09:16, Tom Lane wrote: Personally I consider -c format the only one of the three that is readable for reviewing purposes, so even if I weren't intending immediate application, I'd ask for -c before looking at the patch. There are some folks who consider -u format readable, but I'm not one of them ... I was wondering what people use to keep track of their personal development especially when they do not have a cvs commit access. See the developer's FAQ. They usually use cporig to make copies of files they are going to modify, then difforig to send the diffs to us, or they copy the entire source tree, modify it, and do a recursive diff themselves. -- 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] PITR Functional Design v2 for 7.5
Simon, First off, let me compliment you on such a thourough proposal. I'm feeling very enthusiastic about 7.5 PITR based on the amount of thought you've given the project. Based on throuroughness, I wanted to make a few comments and suggestions. All of the below are in the category of extras it would be nice to have but are not essential to implementing PITR. Possibly they are all features to consider for 7.6/8.0/whatever; the next version. These comments are based on my personal experience as a professional contract DBA for PostgreSQL, MS SQL Server and SQL Anywhere. PITR features are designed to extend the existing Crash Recovery features so that a recovery can take place in situations where a crash recovery would not have been possible. These situations are: In my personal experience, the *primary* use of PITR is recovery from User Error. For example, with one SQL Server 7.0 installation for a law firm, I've made use of PITR 4 times over the last 4 years: once was because and HDD failed, the other three were all becuase of IT dept. staff running unconstrained UPDATE queries against the back end. For recovery with minimal loss of data, there are existing solutions, such as replication servers, in addition to PITR; for recovery from User Error, only PITR will suffice. There are a wide range of Backup and Recovery (BAR) products on the market, both open source and commercially licensed programs that provide facilities to perform full physical backups and individual file archives. The best way to foster wide adoption of PostgreSQL is to allow it to work in conjunction with any of these products. To this end, a Very perceptive of you. Good idea! wal_archive_policy and enable/disable archiving accordingly. This parameter can only be changed at server start. (This is required because the initial step of archiving each xlog is performed by the backend; if this were changeable after boot, then it might be possible for an individual backend to override the wal_archive_policy and choose not to archive - which would then effect the whole system and all users, not just the user making that choice). It is considered less desirable to Let me voice a real-world exception to this policy. Imagine that you are running an OLAP or decision-support database that analyzes data coming from an external source. Once a day you load 250MB of data via COPY and then does transformations on that data. While doing the load, you do *not* want the archiver running, as it would quickly fill up the WAL partition and backlog the archive tape. Under the proposed PITR spec, the only way to handle this would be to: 1) Full back up 2) Shut down PG 3) Restart PG without archiving 4) Load the data 5) Shut down PG again 6) Restart PG with archiving 7) Full back-up again. DBAs would like it much more if starting/stopping the archiver was possible via a superuser (not regular user) GUC.This would allow a much faster cycle: 1) Full back up 2) Stop archiving 3) Load the data 4) Restart archiving 5) Full back-up Related to the above, what I don't see in your paper or the proposed API is a way to coordinate full back-ups and WAL archiving. Obviously, the PITR Archive is only useful in reference to an existing full backup, so it is important to be able to associate a set of PITR archives with a particular full backup, or with some kind of backup checkpoint. I'm sure that you have a solution for this, I just didn't see it explained in your proposal, or didn't understand it. FWIW, I find the MSSQL PITR system awkward in the extreme and harrowing in its unreliability. So it's not a good model to copy There is no requirement for the archiver to halt when PostgreSQL shuts down, though may choose to do so or not, e.g. it may be desirable to have one archiver operate for multiple postmasters simultaneously. The I see that you've chosen the One archiver, many databases/clusters architecture. I can also see how this strategy will be easier than the many archivers strategy. Be prepared that, based on the needs of DBAs, you will get the following requests: A) Will it be possible to have the archiver process run on a seperate machine from PostgreSQL and access it over the network, via NFS or some other means? B) Will it be possible to define multiple output streams, so that database X and be archived to device Y and database N to device M? The out of space condition could therefore occur in two ways: 1. there is a single delay during which xlog filesystem fills 2. there could be a systematic delay which builds slowly until the xlog filesystem fills Given how PITR, and Tablespaces, both substantially increase the risk of running out of space on the xlog partition(s), it would be very nice to be able to arrange a WARNING whenever any PostgreSQL disk resource drops below a pre-defined percentage of availability.This could be done through a simple
[HACKERS] psqlscan.l
Hi! Could we have the output from psqlscan.l (in src/bin/psql) added to the nightly snapshots, the same way it's done with with the other flex output files, please? It would do wonders for those of us too lazy to fix their broken flex installations. And if the same script is used to generate the release tarballs, the problem will show up there as well when the time comes. //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] raising the default default_statistics_target
scott.marlowe [EMAIL PROTECTED] writes: Hi Tom. I ran some very simple tests on analyze times and query plan times on a very simple table, with data randomly distributed. The index was on a date field, since that's what I was testing last. Thanks. I also ran some quick tests on smaller tables (1000 and 10k rows) and there, the plateau that we see in the 100k analyze shows up much quicker, at something like 50 or so. I.e. the analyze time flattened out quickly and higher numbers cost very little if anything. The sample size is (IIRC) 300 times stats_target rows, so the plateau that you're seeing occurs when the sample size becomes the entire table. It would be useful to note how large the ANALYZE process got to be during these runs. Since this query was quite an easy plan, I'd expect to need a much more complex one to test the increase in planning time, say something that has to look at a lot of statistics. Any particular join type or something that's likely to do that? I'd say try a join on any reasonably plausible foreign-key relationship (unique key on one side, not-unique data on the other). That's probably the most common situation. As for making it complicated, just stack up a bunch of such joins ... 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] [OT] Respository [was Re: [PERFORM] Feature request:
Bruce Momjian wrote: Shridhar Daithankar wrote: I was wondering what people use to keep track of their personal development especially when they do not have a cvs commit access. See the developer's FAQ. They usually use cporig to make copies of files they are going to modify, then difforig to send the diffs to us, or they copy the entire source tree, modify it, and do a recursive diff themselves. I used to use cvsup to get a full copy of the repository, and then work locally out of that (check out and diff only). Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ECPG - Specifying connections, TSD, sqlca.
Lee Kindness wrote: Shridhar, Once the patches I've put forward are applied there's still a further change I've got planned which will remove the mutex locking in the common case - a NULL/DEFAULT connection parameter (I'll post a patch soon). This leaves the threaded case with comparable performance to the non-threaded case (both have a function call to get the connection, plus a getspecific call in the threaded case). As such is there much benefit in adding support for the connection being supplied by a struct pointer? You'd also have to add in something like EXEC SQL GET DESCRIPTION xxx to get the pointer too. How would it improve things over how they are in the test_thread_implicit test program? I still think it's worthwhile investigating the use of GCC's __thread storage class specifier to remove the use of pthread_*specific in this case. This would also be a help to the WIN32 port since this specifier maps well to similar constructs in Microsoft's and Borland's compilers (see thread item in the TODO at developer.postgresql.org). I would like to avoid compiler-specific thread stuff unless tests can show a performance benefit. -- 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] psqlscan.l
Magnus Hagander wrote: Could we have the output from psqlscan.l (in src/bin/psql) added to the nightly snapshots, the same way it's done with with the other flex output files, please? Done. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] psqlscan.l
Peter Eisentraut [EMAIL PROTECTED] writes: Magnus Hagander wrote: Could we have the output from psqlscan.l (in src/bin/psql) added to the nightly snapshots, the same way it's done with with the other flex output files, please? Done. My apologies ... intended it to do that in the first place, but I see I missed a step. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] cvs breakage
Fresh cvs tip checkout. If I configure with --prefix then cvs tip seems to fail make check - initdb fails with a complaint about a missing symbol for canonicalise_path. If I don't set the prefix then it works fine. I don't have any funny LD_LIBRARY_PATH stuff set. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cvs breakage
Andrew Dunstan wrote: Fresh cvs tip checkout. If I configure with --prefix then cvs tip seems to fail make check - initdb fails with a complaint about a missing symbol for canonicalise_path. If I don't set the prefix then it works fine. I don't have any funny LD_LIBRARY_PATH stuff set. That was a commit from yesterday. My guess is that you didn't make a gmake distclean before running configure. We moved canonicalise_path into /port from initdb. Maybe try ldconfig too. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] cvs breakage
Andrew Dunstan wrote: Fresh cvs tip checkout. If I configure with --prefix then cvs tip seems to fail make check - initdb fails with a complaint about a missing symbol for canonicalise_path. If I don't set the prefix then it works fine. I don't have any funny LD_LIBRARY_PATH stuff set. Further data point. The prefix used had a previous installation in it done before the canonicalise_path change. If I use --prefix=/totally/new/location I don't get this failure. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User defined types -- Social Security number...
Michael Chaney [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Mon, Mar 01, 2004 at 09:42:48AM -0800, Steve Crawford wrote: I missed the start of this thread but will chime in with a comment anyway. My rule is to select an appropriate numeric type of data if you will be doing numeric types of things to it, character types if you will be doing character manipulations, etc. I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN), SSN+7.86 but there are plenty of good reasons to need the first three characters (the area number), the middle two characters (the group number, and the last 4 characters (the serial number, often (ab)used as a password for banking and other purposes). Another excellent point. I often store zip codes as text for this reason. The only other thing that I would mention is that if the SSN field in the db will be a key of some sort, which is often the case, then it might be more efficient to store it as an integer. It might be more efficient to store it as a character string. The author should test in this case to determine the most efficient way. As for character vs. integer manipulations, in most scripting style languages, which is pretty much exlusively what I use, there's no need to think about types, and something like an SSN will silently change between being character or integer depending on what operations are being performed on it. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Ther are some other points I'd like to make -- If I store the SSN as an integer -- theoretically -- leading zeroes will be stripped (041-99-) -- my OWN ssn is a perfect example of this as it starts with a leading zero... This would cause a problem in that one of the requirements of an SSN is that the length be exactly 9 digits or 9 chars WITHOUT the dashes so a CHECK CONSTRAINT would be useful... But if the SSN is stored as an integer -- there is no check constraint that wouldn't fail for SSNs that start with one or more zeroes So I thought how about a varchar(9) field and insert/update triggers that do the formatting (adding the dashes on insert/update --) and validate the check contraints (9 chars + the dashes)... The two extra characters making a varchar(11) field are not a concern in the normalization or schema... I simply wanted a formatting function so that I dont have to do it in my scripting language or use the same CAST over and over and over in my select/insert/update statements I am mainly looking to do the formatting automatically rather than having to constantly format such a simple piece of data... It would be really sweet in postgreSQL if we could apply the equivalent of a printf(columnname) to the table definition -- MS Access has what they call an input mask and it comes in really handy -- however -- I havent used Access for anthing serious for about 4 years... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [HACKERS] Another crack at doing a Win32
Uytkownik Andrew Dunstan napisa: (guess) try configuring without readline. Or compile readline with your Mingw version, or just replace readline.a with readline.dll in your lib directory. All works with readline fine. Best regards Rony ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ECPG - Specifying connections, TSD, sqlca.
Lee Kindness wrote: From: Bruce Momjian [EMAIL PROTECTED] Lee Kindness wrote: I still think it's worthwhile investigating the use of GCC's __thread storage class specifier to remove the use of pthread_*specific in this case. This would also be a help to the WIN32 port since this specifier maps well to similar constructs in Microsoft's and Borland's compilers (see thread item in the TODO at developer.postgresql.org). I would like to avoid compiler-specific thread stuff unless tests can show a performance benefit. I think concerns re performance are largely moot - with the thread specific data performance is much the same as without. However native compiler support for thread specific data is much more straightforward and understandable - you simply end up with a variable that can be used like any other except there is a copy of it for each thread. To make ECPG thread-safe for WIN32 an additional set of thread calls will need to be added, and/or similar features to GCC's __thread storage specifier. If we end up adding these for WIN32 then it may as well be done for GCC too. I probably will experiment with it a bit (and get some real performance figure, rather than my hunch!)... Perhaps a cleaner way is to use an existing thread package with encompasses the various platform APIs - i.e. APR or ACE, or... But that's a big discussion, and not one I'm keen to get into at the moment. A more appropriate time is perhaps once the WIN32 port is completed? It would also be straightforward to encompass this in an PostgreSQL specific API to wrap around the various calls we use and, if available, make these no-ops when a suitable storage class is supplied by the compiler? I'd be happy to write this API if others saw it as a way forward. Perhaps someone would like to fwd this on to the hackers-win32 list (I'm not subscribed) to see what their view is on thread safety in the client libraries? And what approach they're planning? I guess my point was that if there isn't a big performance win, why do compiler specific and POSIX standard both in the same code. The compiler-specific might be clearer, but if we have to support non-gcc too, which we do, adding a cleaner solution to one that is already standard actually makes it look worse. I don't think MinGW support thread-specific right now (no TLS support), so we will need native Win32 in there anyway. Adding a third seems like more confusion. -- 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]
[HACKERS] Scalable postgresql using sys_epoll
IBM has rewritten their Domino database system to use the new sys_epoll call available in the Linux 2.6 kernel. Would Postgresql benefit from using this API? Is anyone looking at this? Anthony http://xminc.com/mt/ ---(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] How to get Relation name from Oid ??
Jonathan Gardner [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Friday 27 February 2004 12:01 pm, Halasipuram seshadri ramanujam wrote: Hello , Can somebody please tell me how to get the name of the relation (Attribute also) from the Oid and the otherway back (Oid from name) ?? There is a document on the system tables in the PostgreSQL documentation. http://www.postgresql.org/docs/7.4/static/catalogs.html pg_class is the relation you are looking for. -- Jonathan Gardner [EMAIL PROTECTED] You can also use 'path.totable'::regclass::oid to find the oid of a table, and 123456::regclass to find the path of a table given an oid. There is no similar functionality for attributes AFAIK. If you need to use the path returned from the regclass cast as text, you will need to create a cast from regclass to text - this can be achieved using the following functions - making use of the cstring type that the return/input functions for these types have in common. I'd not sure how safe an approach this is - and would appreciate any comments. CREATE OR REPLACE FUNCTION utilities.text(regclass) RETURNS text STRICT STABLE AS ' SELECT pg_catalog.textin(pg_catalog.regclassout($1::regclass));' LANGUAGE 'SQL'; CREATE OR REPLACE FUNCTION utilities.regclass(text) RETURNS regclass STRICT STABLE AS ' SELECT pg_catalog.regclassin(pg_catalog.textout($1::text));' LANGUAGE 'SQL'; CREATE CAST (regclass AS text) WITH FUNCTION utilities.text(regclass); CREATE CAST (text AS regclass) WITH FUNCTION utilities.regclass(text); Once you have created these functions/casts (here in the utilities schema) you can use 3245342::oid::regclass::text to find the path of a table given it's oid.This does take into account the current schema_path settings, so use of this cast may or may not schema-qualify the table name depending on the schema_path setting. -- Tom Hebbron www.hebbron.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Issue with 7.1.3 - regressions and such
I recently had the 'joy' of needing to compile a copy of 7.1, to
support a fairly crusty application where we'd have to do more testing
than we can justify in order to upgrade to some (vastly) newer
generation.
Ran into a couple of things worth mentioning:
1. Had a whole lot of gory problems due to 'less than wonderful'
handling of GCC versioning.
In older versions of GCC, there was just 1 one of output. Now there
are many. In new versions of PG configure, it strips out just the
first line.
# Create compiler version string
if test x$GCC = xyes ; then
#cc_string=GCC `${CC} --version`
cc_string=GCC `${CC} --version | sed q`
else
cc_string=$CC
fi
cat confdefs.h EOF
#define PG_VERSION_STR PostgreSQL $VERSION on $host, compiled by $cc_string
EOF
It took quite some looking around to figure out the root of this one,
as wildly unusual things were breaking.
2. Regression tests broke a bit in the area of timestamps.
Apparently as of Red Hat 8.0, there are entertaining changes in how
GLIBC deals with dates back before the start-of-UNIX epoch, so that
any dates back in the 1960s and earlier will have some timing
discrepancies.
I don't see that being terribly much worth fixing, but it should
remain 'expected.'
*** ./expected/timestamp.outThu May 3 15:00:37 2001
--- ./results/timestamp.out Thu Mar 4 12:26:22 2004
***
*** 572,578
| invalid
| -infinity
| infinity
! | Tue Dec 31 16:00:00 1968 PST
| Sat Feb 10 17:32:01 1996 PST
| invalid
| invalid
--- 572,578
| invalid
| -infinity
| infinity
! | Tue Dec 31 08:00:00 1968 PST
| Sat Feb 10 17:32:01 1996 PST
| invalid
| invalid
==
*** ./expected/abstime.out Thu Aug 16 14:36:45 2001
--- ./results/abstime.out Thu Mar 4 12:26:21 2004
***
*** 36,42
| current
| infinity
| -infinity
!| Sat May 10 23:59:12 1947 PST
| invalid
(8 rows)
--- 36,42
| current
| infinity
| -infinity
!| Sat May 10 15:59:12 1947 PST
| invalid
(8 rows)
--
output = (cbbrowne @ acm.org)
http://cbbrowne.com/info/spreadsheets.html
Rules of the Evil Overlord #17. When I employ people as advisors, I
will occasionally listen to their advice.
http://www.eviloverlord.com/
---(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] PITR Functional Design v2 for 7.5
Josh Berkus wrote: 5) Full back-up Related to the above, what I don't see in your paper or the proposed API is a way to coordinate full back-ups and WAL archiving. Obviously, the PITR Archive is only useful in reference to an existing full backup, so it is important to be able to associate a set of PITR archives with a particular full backup, or with some kind of backup checkpoint. I'm sure that you have a solution for this, I just didn't see it explained in your proposal, or didn't understand it. As far as I understand , full backup in the sense of pgsql means all data files including c_log where all transactions before the checkpoint are completely written to the data files. AFAICS there is a small detail missing so far. When I'm doing a file level hot backup, I can't be sure about the backup order. To be sure the cluster is in a consistent state regarding checkpoints, pg_clog must be the first directory backed up. If this isn't made sure, the situation could arise that the backed up clog version contains a checkpoint which marks a transaction completed that has been written to a file which was backed up earlier than the data write took place. This could be insured by doing the backup in two steps; first backing up pg_clog, and then the rest, restore being performed in the opposite order. But this seems to be not too fail safe, what if the admin doesn't know this/forgot about it? So IMHO a mechanism insuring this would be better. I could think of a solution where a second pg_clog directory is used, and a pgsql api for that which is called right before performing the file backup. Josh calls this second pg_clog the backup checkpoint. At the moment, a restart is done from clog + WAL, where clog might be too new in a hot backup situation as mentioned above. There should be a second pgsql restart mode, where checkpoints are not taken from that current clog, but the backup checkpoint clog which was created explicitely at backup time. This is somewhat similar to MSSQL's backup behaviour, where the transaction log (=WAL) is growing until a full backup has been performed successfully. Regards, Andreas ---(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] ECPG - Specifying connections, TSD, sqlca.
From: Bruce Momjian [EMAIL PROTECTED] Lee Kindness wrote: I still think it's worthwhile investigating the use of GCC's __thread storage class specifier to remove the use of pthread_*specific in this case. This would also be a help to the WIN32 port since this specifier maps well to similar constructs in Microsoft's and Borland's compilers (see thread item in the TODO at developer.postgresql.org). I would like to avoid compiler-specific thread stuff unless tests can show a performance benefit. I think concerns re performance are largely moot - with the thread specific data performance is much the same as without. However native compiler support for thread specific data is much more straightforward and understandable - you simply end up with a variable that can be used like any other except there is a copy of it for each thread. To make ECPG thread-safe for WIN32 an additional set of thread calls will need to be added, and/or similar features to GCC's __thread storage specifier. If we end up adding these for WIN32 then it may as well be done for GCC too. I probably will experiment with it a bit (and get some real performance figure, rather than my hunch!)... Perhaps a cleaner way is to use an existing thread package with encompasses the various platform APIs - i.e. APR or ACE, or... But that's a big discussion, and not one I'm keen to get into at the moment. A more appropriate time is perhaps once the WIN32 port is completed? It would also be straightforward to encompass this in an PostgreSQL specific API to wrap around the various calls we use and, if available, make these no-ops when a suitable storage class is supplied by the compiler? I'd be happy to write this API if others saw it as a way forward. Perhaps someone would like to fwd this on to the hackers-win32 list (I'm not subscribed) to see what their view is on thread safety in the client libraries? And what approach they're planning? L. ---(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] Slony-I makes progress
In the last exciting episode, [EMAIL PROTECTED] (Jochem van Dieten) wrote: Josh Berkus wrote: I personally don't think that a GUI tool should be the province of the Slony project. Seriously. I think that Slony should focus on a command-line api and catalogs, and allow the existing GUI projects to build a slony-supporting interface. Why a command line api? I believe it would make sense to be able to configure and control all nodes of the entire system from psql connected to any of the nodes. That would also facilitate the existing GUI projects in adding a Slony-manager. Interesting... That would mean that the 'server' part of the application would be 'monitoring' NOTIFY requests on each of the nodes, right? Hmm... Queue up some records in the slony1.node_requests table, to indicate what needs to be changed, then NOTIFY slony1. The server then has to look at _all_ the nodes for slony1.node_requests entries. It would be _very_ easy to write command line apps to manage this; no need to add any extra RPC scheme (e.g. - Java RMI, CORBA, talking to sockets), and no need to open extra firewall ports in addition to the ports already needed in order for Slony to communicate with the various databases. Further bonus: the GUI project need only have a database connection to one of the databases to control things. No need for ANYTHING else. After fleshing it out a little, that's a pretty slick approach. -- If this was helpful, http://svcs.affero.net/rm.php?r=cbbrowne rate me http://cbbrowne.com/info/multiplexor.html 0 7 * * * echo ...Linux is just a fad | mail [EMAIL PROTECTED] \ -s And remember... ---(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] cvs breakage
Peter Eisentraut wrote: Andrew Dunstan wrote: If I configure with --prefix then cvs tip seems to fail make check - initdb fails with a complaint about a missing symbol for canonicalise_path. If I don't set the prefix then it works fine. I don't have any funny LD_LIBRARY_PATH stuff set. You need to do make install before make check or you need to compile without rpath. I understood make check should be able to be run before an install. Turning off rpath in src/Makefile.global did the trick. I'm amazed this hasn't caught me before - maybe libpq has just been so stable it hasn't made a difference ... Should the version number be bumped? or is this just a developer gotcha? anyway, thanks cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] NO WAIT ...
Tatsuo Ishii [EMAIL PROTECTED] writes: Here is the patch I promised (against current). This is missing the necessary adjustments in backend/nodes/ (copy and equal funcs). Also the NOWAIT keyword must be added to the list of nonreserved keywords near the bottom of gram.y. Thanks for the review. I'll work on this. -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] The problem of making new system catalog
Hello,I made new system catalog.I think it is successful.But i got an error whenI tried to select statement to get content of this catalog.This error is"catalog is missing 2 attribute(s) for relid 16652".So,I found the sourcecode that this error occurs.This file is relcache.c.This file try to getattribute from pg_attribute.The problem of my new system catalog is theoid is not just 16652.It has a blank before 16652.When I try to find thisoid in pg_attribute like "select * from pg_attribute where attrelid =16652",I can't get a result.But when I run the query like "select * frompg_attribute where attrelid = %16652",I could get a result.I didn't touchanything for oid in pg_attribute.How can I solve this problem?Thank you
Re: [HACKERS] ECPG - Specifying connections, TSD, sqlca.
From: Bruce Momjian [EMAIL PROTECTED] Lee Kindness wrote: Perhaps a cleaner way is to use an existing thread package with encompasses the various platform APIs - i.e. APR or ACE, or... But that's a big discussion, and not one I'm keen to get into at the moment. A more appropriate time is perhaps once the WIN32 port is completed? It would also be straightforward to encompass this in an PostgreSQL specific API to wrap around the various calls we use and, if available, make these no-ops when a suitable storage class is supplied by the compiler? I'd be happy to write this API if others saw it as a way forward. I don't think MinGW support thread-specific right now (no TLS support), so we will need native Win32 in there anyway. Adding a third seems like more confusion. Ah, ok - i've not been following the win32 stuff so wasn't even sure on compilers being used. I'd agree at this stage there's no point muddying the waters even further! I'll get back to you with the patch to move common-case connection retrieval outwith the mutex once the earlier patches are applied. Thanks, L. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR Functional Design v2 for 7.5
Andreas Pflug Josh Berkus wrote: Related to the above, what I don't see in your paper or the proposed API is a way to coordinate full back-ups and WAL archiving. Obviously, the PITR Archive is only useful in reference to an existing full backup, so it is important to be able to associate a set of PITR archives with a particular full backup, or with some kind of backup checkpoint. I'm sure that you have a solution for this, I just didn't see it explained in your proposal, or didn't understand it. AFAICS there is a small detail missing so far. When I'm doing a file level hot backup, I can't be sure about the backup order. To be sure the cluster is in a consistent state regarding checkpoints, pg_clog must be the first directory backed up. If this isn't made sure, the situation could arise that the backed up clog version contains a checkpoint which marks a transaction completed that has been written to a file which was backed up earlier than the data write took place. This could be insured by doing the backup in two steps; first backing up pg_clog, and then the rest, restore being performed in the opposite order. Good spot. I'll add this to the design. Will think more on the backup checkpoint. Don't let me off the hook... Best Regards, Simon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Use average field correlation per hard disk page instead of global one?
Hello. I have a table of 2'500'000 tuples and 100'000 pages, and an index on non-unique field, to each key value corresponds approximately 50'000 tuples. Due to the updating algorithm the physical order of tuples in the table happens to be such that all equal keys are placed together, but not ordered globally. Correlation computed by VACUUM ANALYZE is 0.15. When computing indexscan cost for query with clause key = ? the planner makes it closer to Mackert and Lohman formula value than to selectivity * pages. As a result it chooses seqscan rather than indexscan while in fact indexscan is 20 times faster. The question is, which is the best way to correct this behavior? Maybe VACUUM ANALYZE could calculate some average of field correlation per page and even use this value somewhere inside (not outside) Mackert and Lohman formula? Are there any better ideas? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User defined types -- Social Security number...
Greg Patnude [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Michael Chaney [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
On Mon, Mar 01, 2004 at 09:42:48AM -0800, Steve Crawford wrote:
I missed the start of this thread but will chime in with a comment
anyway.
My rule is to select an appropriate numeric type of data if you will
be doing numeric types of things to it, character types if you will
be doing character manipulations, etc.
I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN),
SSN+7.86 but there are plenty of good reasons to need the first three
characters (the area number), the middle two characters (the group
number, and the last 4 characters (the serial number, often
(ab)used as a password for banking and other purposes).
Another excellent point. I often store zip codes as text for this
reason.
The only other thing that I would mention is that if the SSN field in
the db will be a key of some sort, which is often the case, then it
might be more efficient to store it as an integer. It might be more
efficient to store it as a character string. The author should test
in this case to determine the most efficient way.
As for character vs. integer manipulations, in most scripting style
languages, which is pretty much exlusively what I use, there's no
need to think about types, and something like an SSN will silently
change between being character or integer depending on what operations
are being performed on it.
Michael
--
Michael Darrin Chaney
[EMAIL PROTECTED]
http://www.michaelchaney.com/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
Ther are some other points I'd like to make --
If I store the SSN as an integer -- theoretically -- leading zeroes will
be
stripped (041-99-) -- my OWN ssn is a perfect example of this as it
starts with a leading zero...
This would cause a problem in that one of the requirements of an SSN is
that
the length be exactly 9 digits or 9 chars WITHOUT the dashes so a
CHECK
CONSTRAINT would be useful...
But if the SSN is stored as an integer -- there is no check constraint
that
wouldn't fail for SSNs that start with one or more zeroes So I thought
how about a varchar(9) field and insert/update triggers that do the
formatting (adding the dashes on insert/update --) and validate the check
contraints (9 chars + the dashes)...
The two extra characters making a varchar(11) field are not a concern in
the
normalization or schema... I simply wanted a formatting function so that I
dont have to do it in my scripting language or use the same CAST over and
over and over in my select/insert/update statements
I am mainly looking to do the formatting automatically rather than having
to
constantly format such a simple piece of data...
It would be really sweet in postgreSQL if we could apply the equivalent of
a
printf(columnname) to the table definition -- MS Access has what they call
an input mask and it comes in really handy -- however -- I havent used
Access for anthing serious for about 4 years...
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762
You might want to look at CREATE DOMAIN
e.g. (for ISBNs, we want to check the format, and the check digit - replace
with suitable regex and validation function for social security numbers)
CREATE OR REPLACE FUNCTION utilities.validate_ISBN_check_digit(char(10))
RETURNS boolean AS '
DECLARE
isbn_sum int:=0;
BEGIN
IF ($1 ~ ''^[0-9]{9}[0-9Xx]{1}$''::text) THEN
FOR i IN 1..10 LOOP
isbn_sum:= CASE
WHEN substring($1 from i for 1) IN (''X'',''x'') AND
i=10 THEN isbn_sum + (11-i * 10)
ELSE isbn_sum + (11-i * substring($1 from i for
1)::int)
END;
END LOOP;
IF mod(isbn_sum,11) = 0 THEN
RETURN ''t'';
END IF;
END IF;
RETURN ''f'';
END;
' LANGUAGE 'plpgsql';
COMMENT ON FUNCTION utilities.validate_ISBN_check_digit(char(10)) is
'validation function for ISBN check digits';
CREATE DOMAIN utilities.ISBN AS char(10) CONSTRAINT ISBN format CHECK
(VALUE ~ '^[0-9]{9}[0-9Xx]{1}$'::text) CONSTRAINT ISBN checkdigit CHECK
(utilities.validate_ISBN_check_digit(VALUE));
Hope that helps
--
Tom Hebbron
www.hebbron.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] grants
hi there i'm having troubles to find how to GRANT SELECT ON all-tables-onmydb TO specificuser this is just to give the access to specificuser to query the database and find troubles on it thnx for your time ---(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] How to get Relation name from Oid ??
Tom Hebbron [EMAIL PROTECTED] writes: If you need to use the path returned from the regclass cast as text, you will need to create a cast from regclass to text - this can be achieved using the following functions - making use of the cstring type that the return/input functions for these types have in common. I'd not sure how safe an approach this is - and would appreciate any comments. This works, and is safe in versions where cstring is a full-fledged type (I forget whether that was in 7.3 or 7.4). But it might be notationally cleaner to use plpgsql. plpgsql's idea of type coercion is to do exactly this output-to-string-and-input-again trick, so the functionality would be the same, but you'd only need to write CREATE OR REPLACE FUNCTION utilities.text(regclass) RETURNS text STRICT STABLE AS 'begin; return $1; end' LANGUAGE plpgsql; I am not sure about speed considerations. Pre-7.4 the SQL function method would certainly be slower, but as of 7.4 you can probably inline the SQL function and it might come out ahead. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Stuff that doesn't work yet in IPv6 patch
Bruce Momjian [EMAIL PROTECTED] writes: Tom, do you know how many of these issue are still open? Uh, none of them, I would hope. That message was a long time ago. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Stuff that doesn't work yet in IPv6 patch
Tom, do you know how many of these issue are still open? --- Tom Lane wrote: The IPv6 patch seems to still be a few bricks shy of a load. Grepping for places that handle AF_INET but not AF_INET6 revealed these unimplemented features: 1. IDENT authorization. Fails if either local or remote address is IPv6. 2. SSL. Postmaster allows SSL for AF_INET but not AF_INET6. 3. Client address display in backend's ps display seems to be v4 only. 4. pgstat code can only bind to 127.0.0.1 (v4 loopback). On a v6-only machine this would not exist, would it? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] PITR: Request for assistance with alpha test plan
A detailed proposal has now been published for PITR functionality. I'd like to now request assistance from anybody and everybody that USEs PostgreSQL, not just hackers, to work out a well-documented and comprehensive test plan for PITR. (Some have offered, though volunteers are required, so I would not make individual requests). Regression tests currently protect PostgreSQL from a range of ugly situations. PITR will need a similarly rigorous approach, though this cannot be easily added as regression tests because of the very nature of the PITR functions and possible failure scenarios. My suggestion is the coordinated assembly of a comprehensive test plan, BY USERS, for USERS. When such a test plan document is assembled, the tests described should be able to be repeated many times by many users on many platforms, though under a range of workloads, to ensure all bugs are found and reported. This is ideally suited to those who USE rather than extend the existing functionality. You are skilled people whose contribution is gratefully received in this endeavour. Based upon the possible failures already identified, it should be possible to write a set of tests that check for those failure conditions. In doing so, it may occur to you that other failure conditions may exist also and it would be good to raise those as possibilities now. This ISN'T a call for beta-testers, but a call for assistance in assembling the list of tests that alpha-testers will be asked to complete. If you don't understand what I'm asking for, then it may not yet be time for you to help; however, I am particularly interested in assistance from those who know they are using relatively obscure parts of the PostgreSQL product. Based upon volume of response, you may not receive individual thanks...but there will be a list of credits somewhere down the line Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] cvs breakage
Bruce Momjian [EMAIL PROTECTED] writes: This is for the 7.5/HEAD tree, so it is bumped. What we don't do is bump during development. No, we shouldn't do intermediate bumps during a devel cycle. Andrew, did you have an earlier 7.5devel libpq.so installed someplace? If so that's the gotcha you have to watch out for. The numbering is intended to keep libpqs from different release cycles separate, but not within a cycle. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] cvs breakage
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Turning off rpath in src/Makefile.global did the trick. I'm amazed this hasn't caught me before - maybe libpq has just been so stable it hasn't made a difference ... Should the version number be bumped? or is this just a developer gotcha? If it hasn't been bumped since 7.4 then it needs to be. Bruce, don't you normally do that at the start of a devel cycle? I show this for interfaces/libpq/Makefile: revision 1.96 date: 2003/11/30 06:09:50; author: momjian; state: Exp; lines: +2 -2 Bump all version numbers and version stamps mentioned in RELEASE_CHANGES. This is for the 7.5/HEAD tree, so it is bumped. What we don't do is bump during development. -- 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] cvs breakage
Andrew Dunstan [EMAIL PROTECTED] writes: Turning off rpath in src/Makefile.global did the trick. I'm amazed this hasn't caught me before - maybe libpq has just been so stable it hasn't made a difference ... Should the version number be bumped? or is this just a developer gotcha? If it hasn't been bumped since 7.4 then it needs to be. Bruce, don't you normally do that at the start of a devel cycle? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PITR Functional Design v2 for 7.5
Simon, SIGHUP - seems to allow different parameter settings in each backend Nope. SIGHUP means that you need to send a HUP to the postmaster, such as you would with changes to pg_hba.conf. SUSET - maybe what you're looking for??? Yes. This means that it can be changed, at runtime, but by the Superuser only. This is used for several settings which are possible to change at runtime but take effect system-wide. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] PITR Functional Design v2 for 7.5
Richard Huxton On Monday 08 March 2004 23:28, Simon Riggs wrote: PITR Functional Design v2 for 7.5 Review of current Crash Recovery Is there any value in putting this section on techdocs or similar? We do get a small but steady trickle of people asking for details on internals, and I think this covers things in a different way to the WAL section of the manuals. Certainly, though I would like to do all of that after it actually works! PITR Proposed Solution To allow this to occur, the full backup *must* occur while the database is open or hot. This backup must include all data and clogs (and any tablespaces or logical links utilised). A continuous sequence of xlogs must also be available, stretching from the last checkpoint prior to the start of the backup through to whatever time is specified for the recovery point or until the end of the xlogs. So this is a standard cp/tar etc while the cluster is actually in use? Yes. I will add a line in to clarify that. XLogArchiveXlogs() returns a single XLOG filename, or NULL If an xlog file is waiting to be archived, then the archiver will discover the name of the xlog by using this API call. If more than one file is available to be archived, then it will be ignored. If the archiver is multi-threaded, it need not wait until it has executed XLogArchiveComplete before it executes XLogArchiveXlogs again. So this means: 1. The archiver is responsible for noticing that it is already archiving the filename returned (if it repeats the call too quickly). 2. The archiver can only ever archive one XLOG file at a time. 1. No: I notice I missed a line saying XLogArchiveXlogs() in section 1.1.3 (corrected). Clarification: The archiver will not need to keep track of whether it is already archiving the same file (though sounds reasonable programming to do so anyway). The API call will never return the same log file twice to this call (by definition). That is implemented in my proposal by renaming the rlog entry to .busy, so it wont show up on subsequent calls. 2. a) There is no restriction on threading in the archiver; it can if it wishes archive many files simultaneously. Since PostgreSQL produces them one at a time, this implies a build up of xlogs, which is specifically not encouraged. An archiver would be encouraged to multi-thread to avoid peaks of demand where the archive process was occurring slower than xlogs were being written. b) The reference implementation won't be multi-threaded in its first incarnation (if I write it!!!be my guest, you have the API definition). You have also made me realise another failure condition which I have also added, todo with a failure of the copy process after this API call. The initial proposal is a simple scheme that uses file existence file extension to pass information between PostgreSQL and the archiver. This would take place in a peer directory of pg_xlog and pg_clog which has been named the pg_rlog directory. (r as in the strong first syllable ar in English pronunciation of archive) Any reason why not pg_pitr or pg_pitr_log? None. I like pg_pitr... Let's wait for other feedback to come in... 1.2 pg_arch: simple xlog archiving tool Does the specification of these parameters (and any others) need to be part of the API? I'm thinking about the ability to drop in different archivers with each using the same pre-defined settings. Those parameters ARE NOT part of the API. The parameters mentioned are command line switches on the simple external archiving program pg_arch. pg_arch is intended to be a simple archiver-side testing tool. It makes sense to make it available also. Basically, you can do whatever you like on the archiver side of the API...contrib beckons 2. Recovery to Point-in-Time (RPIT) Just to clarify: 1. I can identify which XLOG files I need based on their timestamp? 2. Can I force a checkpoint using standard PG client APIs? So I can do close weekly payroll, force checkpoint. 3. We're restoring an entire cluster here, not just one database? How difficult would it be to strip out information for a single db - I'm thinking about the case where you may have limited backup storage and want to save an orders db but not a catalogue db. Or perhaps a hosting company with platinum customers getting PITR. 1. Yes, the external timestamp gives that I think. Checking detail... 2. CHECKPOINT is a PostgreSQL SQL command which can be executed from any client. Yes, your scenario fits. 3. I tried to avoid that issue, but it rears its head. You seem to be specifying what you want though, so I'll have a think. More response required on 1 3...later! Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Functional Design v2 for 7.5
Josh Berkus [mailto:[EMAIL PROTECTED] First off, let me compliment you on such a thorough proposal. I'm feeling very enthusiastic about 7.5 PITR Thank you, though please realise that I am in many ways summarising a wide range of suggestions and earlier work into a coherent whole. Me too! I'm trying to pace myself through release and into subsequent maintenance of the new features. Best Regards, Simon ---(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] grants
hi there i'm having troubles to find how to GRANT SELECT ON all-tables-onmydb TO specificuser There isn't any such command. You need to write a stored procedure to do it for you in a loop. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] About hierarchical_query of Oracle
Who can tell me how to completeoracle's hierarchical_query through postgresql
Re: [HACKERS] [PATCHES] NO WAIT ...
This is missing the necessary adjustments in backend/nodes/ (copy and
equal funcs). Also the NOWAIT keyword must be added to the list of
nonreserved keywords near the bottom of gram.y.
Thanks for the review. I'll work on this.
Here is the revised patch.
--
Tatsuo Ishii
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/lock.sgml,v
retrieving revision 1.40
diff -c -r1.40 lock.sgml
*** doc/src/sgml/ref/lock.sgml 14 Dec 2003 00:05:29 - 1.40
--- doc/src/sgml/ref/lock.sgml 10 Mar 2004 01:35:18 -
***
*** 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
--- 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ] [ NOWAIT ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
***
*** 34,41
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released. Once obtained,
!the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
--- 34,43
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released.
!If literalNOWAIT/literal is given, commandLOCK TABLE/command
!does not wait for acquiring lock, and throws an error instead.
!Once obtained, the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
Index: src/backend/access/heap/heapam.c
===
RCS file: /cvsroot/pgsql-server/src/backend/access/heap/heapam.c,v
retrieving revision 1.162
diff -c -r1.162 heapam.c
*** src/backend/access/heap/heapam.c16 Jan 2004 20:51:30 - 1.162
--- src/backend/access/heap/heapam.c10 Mar 2004 01:35:21 -
***
*** 464,469
--- 464,496
return r;
}
+ Relation
+ conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool nowait)
+ {
+ Relationr;
+
+ Assert(lockmode = NoLock lockmode MAX_LOCKMODES);
+
+ /* The relcache does all the real work... */
+ r = RelationIdGetRelation(relationId);
+
+ if (!RelationIsValid(r))
+ elog(ERROR, could not open relation with OID %u, relationId);
+
+ if (lockmode != NoLock)
+ {
+ if (nowait)
+ {
+ if (!ConditionalLockRelation(r, lockmode))
+ elog(ERROR, could not aquire relation lock);
+ }
+ else
+ LockRelation(r, lockmode);
+ }
+
+ return r;
+ }
+
/*
*relation_openrv - open any relation specified by a RangeVar
*
Index: src/backend/commands/lockcmds.c
===
RCS file: /cvsroot/pgsql-server/src/backend/commands/lockcmds.c,v
retrieving revision 1.8
diff -c -r1.8 lockcmds.c
*** src/backend/commands/lockcmds.c 29 Nov 2003 19:51:47 - 1.8
--- src/backend/commands/lockcmds.c 10 Mar 2004 01:35:21 -
***
*** 59,65
aclcheck_error(aclresult, ACL_KIND_CLASS,
get_rel_name(reloid));
! rel = relation_open(reloid, lockstmt-mode);
/* Currently, we only allow plain tables to be locked */
if (rel-rd_rel-relkind != RELKIND_RELATION)
--- 59,65
aclcheck_error(aclresult, ACL_KIND_CLASS,
get_rel_name(reloid));
! rel = conditional_relation_open(reloid, lockstmt-mode,
lockstmt-nowait);
/* Currently, we only allow plain tables to be locked */
if (rel-rd_rel-relkind != RELKIND_RELATION)
Index: src/backend/nodes/copyfuncs.c
===
RCS file: /cvsroot/pgsql-server/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.277
diff -c -r1.277 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 14 Jan 2004 23:01:54 - 1.277
--- src/backend/nodes/copyfuncs.c 10 Mar 2004 01:35:25 -
***
*** 2316,2321
--- 2316,2322
COPY_NODE_FIELD(relations);
COPY_SCALAR_FIELD(mode);
+ COPY_SCALAR_FIELD(nowait);
return newnode;
}
Index:
Re: [HACKERS] About hierarchical_query of Oracle
Try contrib/tablefunc Chris Li Yuexin wrote: Who can tell me how to complete /oracle's / /hierarchical_query /through postgresql/ / ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Out of space situation and WAL log pre-allocation (was Tablespaces)
Simon Riggs [EMAIL PROTECTED] writes: Strict behaviour is fairly straightforward, you just PANIC! There is another mode possible as well. Oracle for example neither panics nor continues, it just freezes. It keeps retrying the transaction until it finds it has space. The sysadmin or dba just has to somehow create additional space by removing old files or however and the database will continue where it left off. That seems a bit nicer than panicing. When I first heard that I was shocked. It means implementing archive logs *created* a new failure mode where there was none before. I thought that was the dumbest idea in the world: who needed a backup process that increased the chances of an outage? Now I can see the logic, but I'm still not sure which mode I would pick if it was up to me. As others have said, I guess it would depend on the situation. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Timing of 'SELECT 1'
I am timing small queries, and found that a PREPARE/EXECUTE of SELECT 1 takes about 1.2ms on my machine. A normal SELECT doesn't take much longer, so I am wondering why a simpler query isn't faster. Looking at log_executor_stats, I see the following. Execute shows nothing taking much time, mostly .2ms, but the total seems high. I wonder if one of our standard query start/stop functions is taking too long and can be optimized. --- test= PREPARE xx AS SELECT 1; PREPARE -- run EXECUTE several times test= EXECUTE xx; LOG: PARSER STATISTICS DETAIL: ! system usage stats: ! 0.26 elapsed 0.07 user 0.21 system sec ! [0.006128 user 0.018384 sys total] ! 0/0 [17/16] filesystem blocks in/out ! 0/0 [0/0] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [21/97] messages rcvd/sent ! 0/0 [38/78] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: PARSE ANALYSIS STATISTICS DETAIL: ! system usage stats: ! 0.62 elapsed 0.04 user 0.10 system sec ! [0.006168 user 0.018504 sys total] ! 0/0 [17/16] filesystem blocks in/out ! 0/0 [0/0] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [21/98] messages rcvd/sent ! 0/0 [38/79] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: REWRITER STATISTICS DETAIL: ! system usage stats: ! 0.05 elapsed 0.02 user 0.04 system sec ! [0.006205 user 0.018615 sys total] ! 0/0 [17/16] filesystem blocks in/out ! 0/0 [0/0] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [21/99] messages rcvd/sent ! 0/0 [38/80] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: EXECUTOR STATISTICS DETAIL: ! system usage stats: ! 0.17 elapsed 0.04 user 0.12 system sec ! [0.006248 user 0.018744 sys total] ! 0/0 [17/16] filesystem blocks in/out ! 0/0 [0/0] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [21/100] messages rcvd/sent ! 0/0 [38/81] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: EXECUTOR STATISTICS DETAIL: ! system usage stats: ! 0.000288 elapsed 0.37 user 0.000113 system sec ! [0.006281 user 0.018845 sys total] ! 0/0 [17/16] filesystem blocks in/out ! 0/0 [0/0] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/1 [21/101] messages rcvd/sent ! 0/1 [38/82] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: duration: 1.230 ms ?column? -- 1 (1 row) --- -- 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] Use average field correlation per hard disk page instead of global one?
Alexey Nalbat [EMAIL PROTECTED] writes: Due to the updating algorithm the physical order of tuples in the table happens to be such that all equal keys are placed together, but not ordered globally. Hmm... this is of course a weak spot of the correlation-based estimation method. If you were doing a range query then the computed correlation might have some bearing on the cost, but when probing for a single key value, your table will behave much differently than the correlation model can guess. Are there any better ideas? None at the moment, but I'm open to suggestions. It seems like we might need different stats for equality probes than range probes. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Use average field correlation per hard disk
At 04:08 PM 10/03/2004, Tom Lane wrote: None at the moment, but I'm open to suggestions. It seems like we might need different stats for equality probes than range probes. What about my suggestion from August 2000: There might be a way to side-step the issue here. I assume that the index nodes contain a pointer to a record in a file, which has some kind of file position. By comparing the file positions on one leaf node, and then averaging the node cluster values, you might be able to get a pretty good idea of the *real* clustering. I don't use the CLUSTER command, but I have clustered data and would like to be able to take advantage of the fact if possible. *If* the record pointers can be used to indicate closeness, then the same approach of randomly sampling index nodes would seem to work. Then again, maybe I don't know enough about the storage techniques... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timing of 'SELECT 1'
Bruce Momjian [EMAIL PROTECTED] writes: I am timing small queries, and found that a PREPARE/EXECUTE of SELECT 1 takes about 1.2ms on my machine. A normal SELECT doesn't take much longer, so I am wondering why a simpler query isn't faster. Define normal SELECT. I can think of plenty of people who would be overjoyed if their average SELECT was only a couple millisecs. 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] [GENERAL] Shouldn't B'1' = 1::bit be true?
Bill Moran [EMAIL PROTECTED] writes: Am I missing something here? Hmm. It seems like int-to-bit casting ought to be aware of the bit-width one is casting to, and take that number of bits from the right end of the integer. This would make it be the inverse of the other direction. Right now it's only an inverse when you cast to and from bit(32). For shorter bitfield widths, we're effectively inserting at the right end of the integer, but removing bits from the left, which is not consistent. regression=# select B'11000'::bit(5)::int; int4 -- 24 (1 row) regression=# select 24::int::bit(32); bit -- 00011000 (1 row) regression=# select 24::int::bit(32)::bit(5); bit --- 0 (1 row) regression=# select 24::int::bit(5); bit --- 0 (1 row) If we made int-to-bit-N take the rightmost N bits, then the last two cases would yield different results, but that doesn't seem unreasonable to me. Or at least it's less unreasonable than bit(5)-to-int not being the inverse of int-to-bit(5). Comments? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
