Re: [HACKERS] server closed connection on a select query
2005/11/23, Joshua D. Drake <[EMAIL PROTECTED]>: > > >I **REALLY** wish you would STOP saying that, Bruce. The current OpenServer > >Compiler (UDK), is the same as on UnixWare, and is **MUCH** better than the > >Old SVR3 compiler. > > > >We **REALLY** **SHOULD** look at it. > > > > > Well actually no, we shouldn't. Regardless of the technical good or > bad.. We are talking about SCO here. > The socio-political ramifications of supporting such as beast alone > should be enough for every > PostgreSQL and OSS user to run screaming from the building. > I kind of agree with you on this, Josh. Unfortunately, a long time ago, in my work, a choice has been made to use SCO with our customers (more than 1000). And I'm not the one who can choose to move to another OS. I would better use linux, my life would be simpler in many ways but I have to deal with this choice. > Regardless if what Bruce says is FUD or not the reality is, I seriously > doubt anyone here wants to > support or take bug reports for a product that is supported by a company > that is the complete > antithesis of everything good about FOSS. > I understand what your positions are and I respect them. But I think we should be very careful with what we say because FUD is not a good thing, even for us. If I posted my message here, it was only to know if someone already had this experience and found a way to fix it. It didn't want you to put extra code to fix a buggy compiler. Regards. -- Guillaume. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A few pgindent oddities
Bruce Momjian wrote:
> Tom Lane wrote:
> > Since we were breaking our usual rule by re-indenting the 8.1 branch,
> > I took the time to eyeball the whole "cvs diff" for changes that weren't
> > just comment block fixes. I found a few things that need attention.
> >
> > This change is disturbing first because it seems completely unnecessary,
> > and second because I'm not convinced that every C preprocessor will deal
> > correctly with a comment continued off a #endif line:
> >
>
> I don't understand why this first problem happened. Alvaro and I talked
> about it but I could not determine the cause. I did not want to modify
> the indent code at this stage just to fix it. I will look for a fix
> later.
I removed the comment. Let's see if we hit it again.
> > Index: contrib/pgbench/pgbench.c
> > ***
> > *** 1110,1116
> > fprintf(stderr, "Use limit/ulimt to increase the
> > limit before using pgbench.\n");
> > exit(1);
> > }
> > ! #endif /* #if !(defined(__CYGWIN__) || defined(__MINGW32__)) */
> > break;
> > case 'C':
> > is_connect = 1;
> > --- 1110,1117
> > fprintf(stderr, "Use limit/ulimt to increase the
> > limit before using pgbench.\n");
> > exit(1);
> > }
> > ! #endif /* #if !(defined(__CYGWIN__) ||
> > ! * defined(__MINGW32__)) */
> > break;
> > case 'C':
> > is_connect = 1;
> >
> >
> > This change seems odd and unnecessary as well:
>
> I saw this one to and was stumped at the cause. We have other 'typedef
> enum' lines in the code which were not mangled, just this one. Again,
> needs research.
I fixed this one by hacking pgindent script to left-justify all typedefs
in that file only.
> > Index: src/interfaces/libpq/libpq-fe.h
> > ***
> > *** 35,41
> >
> > /* Application-visible enum types */
> >
> > ! typedef enum
> > {
> > /*
> >* Although it is okay to add to this list, values which become unused
> > --- 35,41
> >
> > /* Application-visible enum types */
> >
> > ! typedef enum
> > {
> > /*
> >* Although it is okay to add to this list, values which become unused
> >
> >
> > regards, tom lane
> >
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup.| Newtown Square, Pennsylvania 19073
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Practical error logging for very large COPY
Actually, there are really only a few errors people want to trap I imagine: - CHECK constraints (all handled in ExecConstraints) - Duplicate keys - Foreign key violations (all handled by triggers) Rather than worry about all the events we can't safely trap, how about we simply deal with the handful that are trappable. For example, we let people create an ON ERROR trigger and use the existing trigger interface. We have three possibilities: Trap as many as we can and in the 'rejects' table have an 'sqlstate' field that has the SQLSTATE code generated by the failure. That way you can trivially look for all the ones that failed for whatever reason you like. Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Practical error logging for very large COPY statements
Seems similar to the pgloader project on pgfoundry.org. It is similar and good, but I regard that as a workaround rather than the way forward. Yes, your way would be rad :) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tablespaces and non-empty directories
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Jim C. Nasby wrote: >> Along those lines, is there anything else that would benefit from being >> moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and >> pg_twophase are candidates as well? > Hmm, I doubt moving any of the SLRU files (clog, subtrans, multixact) > will have much of an impact. Certainly pushing them onto the WAL spindle would be a serious misstep. There is a good case for giving WAL its own dedicated disk --- there is no case that I've seen for giving any of these their own disk. > If there's too much I/O on those, a better > solution would be to increase the number of buffers allocated to them. > Currently we use 8 for all of them which is probably not appropiate for > everyone. I've just been looking at a test case provided by Rob Creager that causes some pretty severe contention on SubtransControlLock. There are a number of possible answers to this, but increasing the number of pg_subtrans buffers is definitely one of them. I think it's probably time we got rid of the assumption that all the uses of slru.c should have the same number of buffers ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Should libedit be preferred to
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > If we are going to move toward libedit then libedit should be included > in core. We already do support libedit; support does not mean "include", for either readline or libedit. I think it'd be reasonable to provide a configure option to control selection of libedit or readline on platforms where both are (or appear to be) available. I'm not excited about changing the default behavior, though, especially not on the grounds that "IBM just broke readline on AIX and therefore we should deprecate readline everywhere", which appears to be the reasoning offered so far. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] syntax extension for unsupported JOINs coming from a binary only (unmodifyable) program
Wolfgang <[EMAIL PROTECTED]> writes: > Here are some little changes to the postgreSQL server backend I found > beeing convenient for me You don't seriously expect any of this to get applied, do you? nullstr0 reverts a deliberate change made in PG 7.3. It's way past time to be complaining about that. LIKE_IS_ILIKE ... uh, well, no it isn't. If you'd like it to be, a better approach would be to define a case-insensitive datatype (see for example citext on pgfoundry) or a case-insensitive locale. The proposed join change is, so far as I can see, a serious breakage of the SQL spec. Perhaps you should fix the application to generate valid SQL instead. (Even if it were a reasonable thing to do, postgres.c is not a reasonable place to do it.) More generally, it's been quite some time since we've looked with favor on feature changes enabled by #ifdefs. Those aren't convenient for anybody. Lastly, diffs that are not -c or -u format will be rejected out of hand; they are far too risky to apply to source code that is not exactly the same version you started from. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Should libedit be preferred to
Chuck McDevitt wrote: Another vote for libedit support... We at Greenplum definitely want to use it. If we are going to move toward libedit then libedit should be included in core. Otherwise you are creating a dependency on the largest postgresql used OS (linux). The advantage here of course is that we would be able to eliminate readline support and focus only on libedit. The downside is yet another software in core. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] server closed connection on a select query
I **REALLY** wish you would STOP saying that, Bruce. The current OpenServer Compiler (UDK), is the same as on UnixWare, and is **MUCH** better than the Old SVR3 compiler. We **REALLY** **SHOULD** look at it. Well actually no, we shouldn't. Regardless of the technical good or bad.. We are talking about SCO here. The socio-political ramifications of supporting such as beast alone should be enough for every PostgreSQL and OSS user to run screaming from the building. Regardless if what Bruce says is FUD or not the reality is, I seriously doubt anyone here wants to support or take bug reports for a product that is supported by a company that is the complete antithesis of everything good about FOSS. Sincerely, Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] syntax extension for unsupported JOINs coming from a binary only (unmodifyable) program
Here are some little changes to the postgreSQL server backend I found beeing convenient for me while I was attempting to get interoparability with a binary only program. The patch attached is tested and works for 8.0.0beta3. The so called big ones under these DBMS eat the following kind of join without complaining: select a.val1, b.val2, c.val3 from t1 a left outer join t2 b on (a.id1=b.id2), t1left outer join t3 c on (a.id1=c.id3); the content of the patch reorders the parse tree to get the same result as this (working recursively for subselects etc.): select a.val1, b.val2, c.val3 from t1 a left outer join t2 b on (a.id1=b.id2) left outer join t3 c on (a.id1=c.id3); Wolfgang extend_syntax.tgz Description: GNU Unix tar archive ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] server closed connection on a select query
On Nov 22, 2005, at 7:04 PM, Alvaro Herrera wrote: Larry Rosenman wrote: Sorry for the top post. If you can get a UDK license, that will work better. Oh, so one gets a buggy compiler by default and has to pay for a better one? Cool! I'm drooling already, I want one of those SCO things, where do I get it? Pity those GCC guys, having only one compiler. no, the old compiler also costs :(. It's just that the UDK compiler is the newer one, and works with both OpenServer and UnixWare. -- Alvaro Herrera http://www.amazon.com/gp/registry/ CTMLCN8V17R4 "Use it up, wear it out, make it do, or do without" -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] server closed connection on a select query
Larry Rosenman wrote: > Sorry for the top post. If you can get a UDK license, that will work > better. Oh, so one gets a buggy compiler by default and has to pay for a better one? Cool! I'm drooling already, I want one of those SCO things, where do I get it? Pity those GCC guys, having only one compiler. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "Use it up, wear it out, make it do, or do without" ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tablespaces and non-empty directories
On Wed, 23 Nov 2005 11:23 am, Gavin Sherry wrote: > > Along those lines, is there anything else that would benefit from being > > moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and > > pg_twophase are candidates as well? > > pgsql_tmp Does anyone have any recommendations about which of these would contend with each other for disk IO? I'm looking to put together a doco addition about multi-disk setup, so far I have something like: /mnt/pg_base /mnt/pg_xlog /mnt/pg_tab1 /mnt/pg_idx1 ...but is there significant gain in moving other bits from pg_base to a different spindle? If so, what can be safely combined, and what would definitely cause contention? I know that the answer would vary for different types of DB activity, but any "rough guides" would be a handy place to start. Regards, Philip. -- "Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it." - Brian W. Kernighan - Utiba Pty Ltd This message has been scanned for viruses and dangerous content by Utiba mail server and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving count(*)
On Tue, Nov 22, 2005 at 06:11:01PM -0500, Bruce Momjian wrote: > [EMAIL PROTECTED] wrote: > > A solution enhancing the above mentioned indexes, to maintain a count > > for whole index blocks, would allow whole index blocks that satisfy > > the WHERE clause to be counted, assuming the whole index block is > > visible in the current transaction. > I think it would be very difficult to generate a per-index-page > visibility bit because I can't think of a normal database operation that > would allow us to update it. It requires that an index scan visit every > heap page to check the visibility of the tuples. However, we almost > never do a full-index scan because it is so much slower than a heap > scan. It would be easy to keep a heap-visible bit up-to-date (because > we do full-heap scans occasionally), but that would require the index > to load the heap page to find the bit. (The bit isn't on the index, it > is on the heap). Vacuum time? > Jan has been talking about have a bitmap to track pages that need > vacuuming, and I am wondering if the same system could be used to track > the heap-dirty bits. Putting one bit on every 8k disk page means we have > to load the 8k page to read the bit, while a centralized bitmap would > load 64k page bits in a single 8k page. That one page would cover 500MB > of a table. Seems vacuum could use the same bitmap values. Sounds correct. > Assuming we track 100 tables regularly, that would require 800k of shared > memory. We would have to pagein/out the bitmaps as needed, but we could > throw them away on a crash and rebuild as part of normal operation. > FSM has not been a concurrency bottleneck, so I am thinking this would > not be either. > I suppose it would require a new filesystem file for each table. *nod* Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Should libedit be preferred to
Another vote for libedit support... We at Greenplum definitely want to use it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tablespaces and non-empty directories
Jim C. Nasby wrote: > On Tue, Nov 22, 2005 at 01:38:34PM -0500, Bruce Momjian wrote: > > > > * Allow the pg_xlog directory location to be specified during initdb > > with a symlink back to the /data location > > > > I think the only reason it is not done yet is because it is so easy to > > do for admins, and it is impossible to do while the server is running. > > Along those lines, is there anything else that would benefit from being > moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and > pg_twophase are candidates as well? Hmm, I doubt moving any of the SLRU files (clog, subtrans, multixact) will have much of an impact. If there's too much I/O on those, a better solution would be to increase the number of buffers allocated to them. Currently we use 8 for all of them which is probably not appropiate for everyone. Not sure about pg_twophase, but I doubt it's used on a too much performance critical path (after all, there an awful lot of other work to do to "prepare" a transaction.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A few pgindent oddities
Bruce Momjian wrote:
> > And what happened here?
>
> I saw this one to and was stumped at the cause. We have other 'typedef
> enum' lines in the code which were not mangled, just this one. Again,
> needs research.
>
> > Index: src/interfaces/libpq/libpq-fe.h
> > ***
> > *** 35,41
> >
> > /* Application-visible enum types */
> >
> > ! typedef enum
> > {
> > /*
> >* Although it is okay to add to this list, values which become unused
> > --- 35,41
> >
> > /* Application-visible enum types */
> >
> > ! typedef enum
> > {
> > /*
> >* Although it is okay to add to this list, values which become unused
Ah the cause of this one is this at the top of src/interfaces/libpq/libpq-fe.h:
#ifdef __cplusplus
extern "C"
{
#endif
Not sure I can blame pgindent. Of course the fact that the other
'typedef enum' lines in the file are not indented isn't consistent.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] A few pgindent oddities
Tom Lane wrote:
> Since we were breaking our usual rule by re-indenting the 8.1 branch,
> I took the time to eyeball the whole "cvs diff" for changes that weren't
> just comment block fixes. I found a few things that need attention.
>
> This change is disturbing first because it seems completely unnecessary,
> and second because I'm not convinced that every C preprocessor will deal
> correctly with a comment continued off a #endif line:
>
I don't understand why this first problem happened. Alvaro and I talked
about it but I could not determine the cause. I did not want to modify
the indent code at this stage just to fix it. I will look for a fix
later.
> Index: contrib/pgbench/pgbench.c
> ***
> *** 1110,1116
> fprintf(stderr, "Use limit/ulimt to increase the limit
> before using pgbench.\n");
> exit(1);
> }
> ! #endif /* #if !(defined(__CYGWIN__) || defined(__MINGW32__)) */
> break;
> case 'C':
> is_connect = 1;
> --- 1110,1117
> fprintf(stderr, "Use limit/ulimt to increase the limit
> before using pgbench.\n");
> exit(1);
> }
> ! #endif /* #if !(defined(__CYGWIN__) ||
> ! * defined(__MINGW32__)) */
> break;
> case 'C':
> is_connect = 1;
>
>
> This change seems odd and unnecessary as well:
This is caused by this part of pgindent:
# workaround for indent bug with 'else' handling
# indent comment so BSD indent will move it
sed 's;\([} ]\)else[]*\(/\*.*\)$;\1else\
\2;g' |
The problem is that BSD indent crashes on:
else /* test */
{
Not sure why, but I guess I can fix it some day. :-)
> Index: src/backend/access/transam/slru.c
> ***
> *** 252,258
> /* indeed, the I/O must have failed */
> if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS)
> shared->page_status[slotno] = SLRU_PAGE_EMPTY;
> ! else/* write_in_progress */
> {
> shared->page_status[slotno] = SLRU_PAGE_VALID;
> shared->page_dirty[slotno] = true;
> --- 253,260
> /* indeed, the I/O must have failed */
> if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS)
> shared->page_status[slotno] = SLRU_PAGE_EMPTY;
> ! else
> ! /* write_in_progress */
> {
> shared->page_status[slotno] = SLRU_PAGE_VALID;
> shared->page_dirty[slotno] = true;
>
>
> And what happened here?
I saw this one to and was stumped at the cause. We have other 'typedef
enum' lines in the code which were not mangled, just this one. Again,
needs research.
> Index: src/interfaces/libpq/libpq-fe.h
> ***
> *** 35,41
>
> /* Application-visible enum types */
>
> ! typedef enum
> {
> /*
>* Although it is okay to add to this list, values which become unused
> --- 35,41
>
> /* Application-visible enum types */
>
> ! typedef enum
> {
> /*
>* Although it is okay to add to this list, values which become unused
>
>
> regards, tom lane
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tablespaces and non-empty directories
On Tue, 22 Nov 2005, Jim C. Nasby wrote: > On Tue, Nov 22, 2005 at 01:38:34PM -0500, Bruce Momjian wrote: > > Gavin Sherry wrote: > > > > Related question: are there plans afoot to allow specifying an alternate > > > > location for pg_xlog (or pg_delete-me-not) to save doing the > > > > shutdown-DB, mv > > > > directory to other disk, symlink, start-DB dance? > > > > > > People have discussed it but I don't know of anyone working on it. > > > > TODO has: > > > > * Allow the pg_xlog directory location to be specified during initdb > > with a symlink back to the /data location > > > > I think the only reason it is not done yet is because it is so easy to > > do for admins, and it is impossible to do while the server is running. > > Along those lines, is there anything else that would benefit from being > moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and > pg_twophase are candidates as well? pgsql_tmp Gavin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tablespaces and non-empty directories
On Tue, Nov 22, 2005 at 01:38:34PM -0500, Bruce Momjian wrote: > Gavin Sherry wrote: > > > Related question: are there plans afoot to allow specifying an alternate > > > location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, > > > mv > > > directory to other disk, symlink, start-DB dance? > > > > People have discussed it but I don't know of anyone working on it. > > TODO has: > > * Allow the pg_xlog directory location to be specified during initdb > with a symlink back to the /data location > > I think the only reason it is not done yet is because it is so easy to > do for admins, and it is impossible to do while the server is running. Along those lines, is there anything else that would benefit from being moved? pg_clog and pg_subtrans come to mind; but maybe pg_multixact and pg_twophase are candidates as well? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Improving count(*)
On Tue, Nov 22, 2005 at 06:11:01PM -0500, Bruce Momjian wrote: > [EMAIL PROTECTED] wrote: > Jan has been talking about have a bitmap to track pages that need > vacuuming, and I am wondering if the same system could be used to track > the heap-dirty bits. Putting one bit on every 8k disk page means we have > to load the 8k page to read the bit, while a centralized bitmap would > load 64k page bits in a single 8k page. That one page would cover 500MB > of a table. Seems vacuum could use the same bitmap values. > > Assuming we track 100 tables regularly, that would require 800k of shared > memory. We would have to pagein/out the bitmaps as needed, but we could > throw them away on a crash and rebuild as part of normal operation. > > FSM has not been a concurrency bottleneck, so I am thinking this would > not be either. > > I suppose it would require a new filesystem file for each table. ISTM that the requirements here are very similar to the requirements for the FSM, at least from a high-level: Track all pages where condition X is true. Is there value to using the same framework for both cases? Maybe it makes more sense to store free space info for a relation using a bitmap, rather than storing individual page numbers. Or conversely, storing 'dirty page info' should maybe be done in a similar fasion to FSM instead of a bitmap. If we wanted to provide the ultimate in tunability we'd offer both solutions; some tables will have a large number of pages with free space on them (which would favor storing that info in a bitmap); likewise some tables will have a small number of pages that are 'dirty', which would favor storing a list of page numbers instead of a bitmap. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] server closed connection on a select query
Sorry for the top post. If you can get a UDK license, that will work better. LER On Nov 22, 2005, at 12:02 PM, Guillaume Lelarge wrote: Sorry for answering this late. 2005/11/16, Larry Rosenman : Bruce Momjian wrote: The SCO compiler is so buggy (and for so many years) I see no reason to even look at a bug report from someone using it. I **REALLY** wish you would STOP saying that, Bruce. The current OpenServer Compiler (UDK), is the same as on UnixWare, and is **MUCH** better than the Old SVR3 compiler. We **REALLY** **SHOULD** look at it. I'll take the bug report directly to SCO if I get enough detail. I was using cc, exactly "SCO UNIX Development System Release 5.1.2A 27Jul00". Last week, I tried with "UX:i386cc: INFO: SCO UNIX Development System Release 5.2.0A 07Oct05" but I had the same results. I think I'm doing something wrong but I don't know what. I launched postgres in standalone mode and a core file is created. I used gdb on it to see the backtrace : (gdb) bt #0 0x081bf5fd in booltestsel () #1 0x08141125 in clause_selectivity () #2 0x081409d2 in clauselist_selectivity () #3 0x0814295b in set_baserel_size_estimates () #4 0x0813ff42 in set_plain_rel_pathlist () #5 0x0813ff12 in set_base_rel_pathlists () #6 0x0813fe1d in make_one_rel () #7 0x0814b455 in query_planner () #8 0x0814bfa0 in grouping_planner () #9 0x0814ba11 in subquery_planner () #10 0x0814b6c7 in planner () #11 0x08181e07 in pg_plan_query () #12 0x08181ea0 in pg_plan_queries () #13 0x081820ce in exec_simple_query () #14 0x081849dd in PostgresMain () #15 0x08161d92 in BackendRun () #16 0x081616c9 in BackendStartup () #17 0x0815fba8 in ServerLoop () #18 0x0815f67c in PostmasterMain () #19 0x08127d91 in main () #20 0x08074d1a in _start () -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
Jaime Casanova wrote: the FROM clause is required by default (starting with 8.1) unless you change a postgresql.conf parameter. and i don't think that idea will have any fan... Bruce already replied to your first statement so, what idea won't have any fan ? It's not that we would change what MERGE does. Postgres just does not requeire FROM clause in SELECT and second parameter of MERGE can be SELECT which means you can do what REPLACE) does without problems and without breaking something or violating standard and like I said you can do the same in oracle using dual. Btw about that keys, oracle gives error on many-to-one or many-to-many relationship between the source and target tables. -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving count(*)
Jim C. Nasby wrote: > On Fri, Nov 18, 2005 at 02:56:52PM -0500, Gregory Maxwell wrote: > > However, some great ideas have been proposed here which would not only > > help in that case but would otherwise be quite useful. > > > > *Inclusion of a 'MVCC inflight' bit in indexes which would allow > > skipping MVCC checks in clumps of an index scan which have no pending > > changes. This would further close the performance gap between PG and > > non-MVCC databases for some workloads. > > *Introduction of high performance table sampling, which would be > > useful in many applications (including counting where there is a where > > clause) as well as for testing and adhoc queries. > > and > > *a estimate_count() that provides the planner estimate, which would > > return right away and provide what is really needed most of the time > > people try to count(*) on a large table. > > What about Greg Stark's idea of combining Simon's idea of storing > per-heap-block xmin/xmax with using that information in an index scan? > ISTM that's the best of everything that's been presented: it allows for > faster index scans without adding a lot of visibility overhead to the > index heap, and it also allows VACUUM to hit only pages that need > vacuuming. Presumably this could also be used as the on-disk backing for > the FSM, or it could potentially replace the FSM. Right, but xmin/xmax is too detailed. We just need a single bit to say all the rows in the heap page are visible to everyone. Seem my earlier posting. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Improving count(*)
[EMAIL PROTECTED] wrote: > On Fri, Nov 18, 2005 at 03:46:42PM +, Richard Huxton wrote: > > Simon Riggs wrote: > > >One of the major complaints is always "Select count(*) is slow". > > Although there seem to have been plenty of ideas on this they all seem > > to just provide a solution for the "whole table" case. It might be that > > the solution provides other benefits, but for this one case it does seem > > like a lot of work. > > Or, it wasn't explained properly as to how the WHERE clause would > function. > > The solution involving an index that has visibility information should > work fine with a WHERE clause. Only index rows that match the clause > are counted. > > A solution enhancing the above mentioned indexes, to maintain a count > for whole index blocks, would allow whole index blocks that satisfy > the WHERE clause to be counted, assuming the whole index block is > visible in the current transaction. I think it would be very difficult to generate a per-index-page visibility bit because I can't think of a normal database operation that would allow us to update it. It requires that an index scan visit every heap page to check the visibility of the tuples. However, we almost never do a full-index scan because it is so much slower than a heap scan. It would be easy to keep a heap-visible bit up-to-date (because we do full-heap scans occasionally), but that would require the index to load the heap page to find the bit. (The bit isn't on the index, it is on the heap). Jan has been talking about have a bitmap to track pages that need vacuuming, and I am wondering if the same system could be used to track the heap-dirty bits. Putting one bit on every 8k disk page means we have to load the 8k page to read the bit, while a centralized bitmap would load 64k page bits in a single 8k page. That one page would cover 500MB of a table. Seems vacuum could use the same bitmap values. Assuming we track 100 tables regularly, that would require 800k of shared memory. We would have to pagein/out the bitmaps as needed, but we could throw them away on a crash and rebuild as part of normal operation. FSM has not been a concurrency bottleneck, so I am thinking this would not be either. I suppose it would require a new filesystem file for each table. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving count(*)
Gregory Maxwell wrote: > On 11/21/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > What about Greg Stark's idea of combining Simon's idea of storing > > per-heap-block xmin/xmax with using that information in an index scan? > > ISTM that's the best of everything that's been presented: it allows for > > faster index scans without adding a lot of visibility overhead to the > > index heap, and it also allows VACUUM to hit only pages that need > > vacuuming. Presumably this could also be used as the on-disk backing for > > the FSM, or it could potentially replace the FSM. > > This should be a big win all around, especially now since in memory > bitmaps make it more likely that some classes of queries will be pure > index. I still think it would be useful to have a estimated_count() > which switches to whatever method is needed to get a reasonably > accurate count quickly (stats when there are no wheres we can't > predict, sampling otherwise if the involved tables are large, and a > normal count in other cases.) Added to TODO: * Add estimated_count(*) to return an estimate of COUNT(*) This would use the planner ANALYZE statistatics to return an estimated count. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] A few pgindent oddities
Since we were breaking our usual rule by re-indenting the 8.1 branch,
I took the time to eyeball the whole "cvs diff" for changes that weren't
just comment block fixes. I found a few things that need attention.
This change is disturbing first because it seems completely unnecessary,
and second because I'm not convinced that every C preprocessor will deal
correctly with a comment continued off a #endif line:
Index: contrib/pgbench/pgbench.c
***
*** 1110,1116
fprintf(stderr, "Use limit/ulimt to increase the limit
before using pgbench.\n");
exit(1);
}
! #endif /* #if !(defined(__CYGWIN__) || defined(__MINGW32__)) */
break;
case 'C':
is_connect = 1;
--- 1110,1117
fprintf(stderr, "Use limit/ulimt to increase the limit
before using pgbench.\n");
exit(1);
}
! #endif /* #if !(defined(__CYGWIN__) ||
! * defined(__MINGW32__)) */
break;
case 'C':
is_connect = 1;
This change seems odd and unnecessary as well:
Index: src/backend/access/transam/slru.c
***
*** 252,258
/* indeed, the I/O must have failed */
if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS)
shared->page_status[slotno] = SLRU_PAGE_EMPTY;
! else/* write_in_progress */
{
shared->page_status[slotno] = SLRU_PAGE_VALID;
shared->page_dirty[slotno] = true;
--- 253,260
/* indeed, the I/O must have failed */
if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS)
shared->page_status[slotno] = SLRU_PAGE_EMPTY;
! else
! /* write_in_progress */
{
shared->page_status[slotno] = SLRU_PAGE_VALID;
shared->page_dirty[slotno] = true;
And what happened here?
Index: src/interfaces/libpq/libpq-fe.h
***
*** 35,41
/* Application-visible enum types */
! typedef enum
{
/*
* Although it is okay to add to this list, values which become unused
--- 35,41
/* Application-visible enum types */
! typedef enum
{
/*
* Although it is okay to add to this list, values which become unused
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Time for pgindent?
Tom Lane wrote: > I see Alvaro and Andrew have landed the patches they were working on > last week, so maybe today is a good time to do that re-pgindent we > were discussing. Done. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Should libedit be preferred to libreadline?
On Mon, Nov 21, 2005 at 07:50:48PM -0500, Andrew Dunstan wrote: > > Nice analysis, but we can't hack configure like that. It has to be able > to be fully generated from its sources. I think the other source file > you would need to look at is config/programs.m4. (Not sure about quoting > $ac_popdir - why only that one?) > > Also, I suspect we'd want to enable the libedit preference with a switch > rather than just force it, if we want to go this way. BTW, we've run into issues with readline from a licensing standpoint. It would be really nice if libedit was supported where practical (I suspect most mainstream OSes support libedit) since it's BSD licensed. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bind variables, soft vs hard parse
On Mon, Nov 21, 2005 at 09:14:33PM +0100, Marcus Engene wrote: > Jim C. Nasby wrote: > >It might be more useful to look at caching only planning and not > >parsing. I'm not familiar with the output of the parsing stage, but > >perhaps that could be hashed to use as a lookup into a cache of planned > >queries. I suspect that would remove issues of different search_paths. > > A really stupid question, in the cached query-string, wouldn't it be > possible to add the env specifics? Ie the string to check against is > something like > > search_paths=...\n > SELECT ... > > Or would there be too much stuff to append/prepend? It's probably possible, but the thing is, afaik parsing just isn't a bottleneck, so it's just not worth messing with that phase. If you do end up with some super-complex query that does have a non-trivial parse time, I believe views store a pre-parsed representation of the view definition (otherwise there'd be issues with changing search_path between when you create a view and when you use it), so you could effectively cache something by just stuffing it into a view. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] someone working to add merge?
On Fri, Nov 18, 2005 at 09:03:25PM +0100, Martijn van Oosterhout wrote: > I'd say implement SQL MERGE which doesn't have any really unusual > features. And seperately implement some kind of INSERT OR UPDATE which > works only for a table with a primary key. Is there any reeason this has to be a PK; shouldn't a unique index with no nullable fields work just as well? It seems bad to limit this to just a PK if we can avoid it. For example, if you have something that's logging hits to web pages, you might have this table: CREATE TABLE url ( url_id serial PRIMARY KEY, url textNOT NULL UNIQUE ); I prefer having url_id as the PK because it's how you normally access the table. But ISTM that there are cases where yo'd want to be able to merge on two different sets of fields in one table, which is impossible if we limit it to PK merges only. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MERGE vs REPLACE
On Tue, Nov 22, 2005 at 11:57:48AM +0100, Martijn van Oosterhout wrote: > Rather than trying to make MERGE do something it wasn't designed for, > we should probably be spending our efforts on triggers for error > conditions. Maybe something like: > > CREATE TRIGGER foo AFTER ERROR ON bar EXECUTE baz(); > > Where baz would be passed NEW and OLD just like a normal trigger and if > the trigger return NULL, the update is ignored. In the meantime the > function can divert the insert to another table if it likes. This seems > like a much more workable and useful addition. I agree that we shouldn't try and distort MERGE into something fancy. The AFTER ERROR trigger is a very interesting idea, since it could handle many different cases. But I'm worried that people might not want that behavior on by default for everything done against some table. I think it'd be better to have some way to specify in a command that you want to use some kind of error-handling trigger. Though presumably the underlying framework would be same, so it shouldn't be hard to support both. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug in predicate indexes?
On Mon, Nov 21, 2005 at 08:40:38PM -0500, Tom Lane wrote: > You should find out what the problem is before you start writing > documentation about it ;-). This has nothing whatever to do with > bigint. Damn, there's 5 minutes of my life that I won't get back! ;P > What the code is trying to do is prove that "X op C1" implies "X op C2" > where the constants aren't necessarily the same and the operators are > drawn from the same btree opclass, but might themselves be different. > Some examples: > > X = 4 implies X > 3, because 4 > 3 > X <= 7 implies X < 3, because 7 < 3 Erm... shouldn't that be because 3 < 7 ? :) > X > 7 doesn't imply X < 14 > The bottom line is that if you want the predicate prover to be at all > smart about a comparison in the index WHERE clause, the comparison can't > be cross-type. Otherwise, the only way it will match it is with an > exact match to the query's WHERE clause. Example: this will still work > > query: WHERE bigintcol = 42 > index: WHERE bigintcol = 42 > > but not this: > > query: WHERE bigintcol = 42 > index: WHERE bigintcol >= 4 > > The last case needs "bigintcol >= 4::bigint" in the index predicate in > order to be provable from a related-but-not-identical query condition. I assume part of this is due to how we cast bare numbers? > This applies to anyplace where we have cross-type comparisons, which > in a quick look in pg_operator seems to be > > <(integer,bigint) > <(bigint,integer) > <(smallint,integer) > <(integer,smallint) > <(real,double precision) > <(double precision,real) > <(smallint,bigint) > <(bigint,smallint) > <(date,timestamp without time zone) > <(date,timestamp with time zone) > <(timestamp without time zone,date) > <(timestamp with time zone,date) > <(timestamp without time zone,timestamp with time zone) > <(timestamp with time zone,timestamp without time zone) I think it's more than that, but my query might be off... decibel=# select count(*) from (select distinct l.typname,r.typname from pg_opclass c join pg_operator o on (c.opcintype=o.oprleft) join pg_type l on (o.oprleft=l.oid) join pg_type r on (o.oprright=r.oid)) a; 88 (that's 8.0.3, btw) > I'm not sure this is worth documenting given that it's likely to change > by 8.2 anyway. I agree with Josh that this should be documented backwards... assuming that my count of 88 is correct, I think it's best to just specify that it's recommended to always explicitely cast any constants in a predicate. Let me know if I'm on the wrong track with any of this, otherwise I'll work on a set of patches. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
On 11/22/05, Bruce Momjian wrote: > Jaime Casanova wrote: > > > > > > And yes merge CAN be used to do REPLACE (oracle uses their dummy table > > > for this, we can use the fact that FROM clause isn't required in > > > postgres). > > > > > > > the FROM clause is required by default (starting with 8.1) unless you > > change a postgresql.conf parameter. > > > > and i don't think that idea will have any fan... > > No, it is not, try SELECT 1. Oracle requires SELECT 1 FROM dual. The > change in 8.1 is that SELECT pg_class.relname no longer works. You have to > do SELECT relname FROM pg_class. > > -- > Bruce Momjian| http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > touche... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
Jaime Casanova wrote: > > > > And yes merge CAN be used to do REPLACE (oracle uses their dummy table > > for this, we can use the fact that FROM clause isn't required in postgres). > > > > the FROM clause is required by default (starting with 8.1) unless you > change a postgresql.conf parameter. > > and i don't think that idea will have any fan... No, it is not, try SELECT 1. Oracle requires SELECT 1 FROM dual. The change in 8.1 is that SELECT pg_class.relname no longer works. You have to do SELECT relname FROM pg_class. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tablespaces and non-empty directories
Gavin Sherry wrote: > > Related question: are there plans afoot to allow specifying an alternate > > location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, mv > > directory to other disk, symlink, start-DB dance? > > People have discussed it but I don't know of anyone working on it. TODO has: * Allow the pg_xlog directory location to be specified during initdb with a symlink back to the /data location I think the only reason it is not done yet is because it is so easy to do for admins, and it is impossible to do while the server is running. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Update to FAQ
I have update the first section of the FAQ: http://www.postgresql.org/docs/faqs.FAQ.html In particular: o reordered items to be more logical o added clarification of license o added section about bug reporting replies o added section of feature requests -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] server closed connection on a select query
Sorry for answering this late. 2005/11/16, Larry Rosenman : > Bruce Momjian wrote: > > The SCO compiler is so buggy (and for so many years) I see no reason > > to even look at a bug report from someone using it. > > > > I **REALLY** wish you would STOP saying that, Bruce. The current OpenServer > Compiler (UDK), is the same as on UnixWare, and is **MUCH** better than the > Old SVR3 compiler. > > We **REALLY** **SHOULD** look at it. > > I'll take the bug report directly to SCO if I get enough detail. > I was using cc, exactly "SCO UNIX Development System Release 5.1.2A 27Jul00". Last week, I tried with "UX:i386cc: INFO: SCO UNIX Development System Release 5.2.0A 07Oct05" but I had the same results. I think I'm doing something wrong but I don't know what. I launched postgres in standalone mode and a core file is created. I used gdb on it to see the backtrace : (gdb) bt #0 0x081bf5fd in booltestsel () #1 0x08141125 in clause_selectivity () #2 0x081409d2 in clauselist_selectivity () #3 0x0814295b in set_baserel_size_estimates () #4 0x0813ff42 in set_plain_rel_pathlist () #5 0x0813ff12 in set_base_rel_pathlists () #6 0x0813fe1d in make_one_rel () #7 0x0814b455 in query_planner () #8 0x0814bfa0 in grouping_planner () #9 0x0814ba11 in subquery_planner () #10 0x0814b6c7 in planner () #11 0x08181e07 in pg_plan_query () #12 0x08181ea0 in pg_plan_queries () #13 0x081820ce in exec_simple_query () #14 0x081849dd in PostgresMain () #15 0x08161d92 in BackendRun () #16 0x081616c9 in BackendStartup () #17 0x0815fba8 in ServerLoop () #18 0x0815f67c in PostmasterMain () #19 0x08127d91 in main () #20 0x08074d1a in _start () So I tried to work on the booltestsel function (src/backend/utils/adt/selfuncs.c file). I added some elog statements to see where problems arise. When I put an elog statement in these lines, my issue is gone : /* * Get first MCV frequency and derive frequency for true */ if (DatumGetBool(values[0])) freq_true = numbers[0]; else freq_true = 1.0 - numbers[0] - freq_null; /* * Next derive frequency for false. Then use these as appropriate * to derive frequency for each case. */ freq_false = 1.0 - freq_true - freq_null; So, I don't get it. I have absolutely no problems with gcc on linux, but I have this issue on cc (5.1.2A and 5.2.0A releases). Larry, if you have some more questions, please ask. I don't know whose bug it is (c compiler or PostgreSQL... or, perhaps, me) but I want to get rit of it. Regards. -- Guillaume. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Practical error logging for very large COPY
On Tue, Nov 22, 2005 at 12:16:00PM -0500, Tom Lane wrote: > I think the distinction you are proposing between constraint errors > and datatype errors is entirely artificial. Who's to say what is a > constraint error and what is a datatype error, especially when you > start thinking about cases like varchar length constraints or > domain-type constraints? If we create a mechanism that behaves > differently depending on whether the error is detected before or after > we try to form a tuple containing the data, we're going to have > something that is exceedingly awkward to use, because the behavior will > be nearly arbitrary from the user's viewpoint. By that reasoning, to be consistant, we should never pass any data at all, which seems even more useless. This is however what other databases do, but I think we can do better. I don't think the distinction is really that arbitrary. If the data can be represented as a tuple in the correct datatypes, you're fine. Domain types are tricky, but to be consistant they should get the tuple data either. My main reasoning is that while you can write a 3 line Perl script to verify the format of all your integers, you can't write a script in any language to check for foreign key constraints or duplicate key errors. So those are the important actions. If it comes down to making datatype errors untrappable then I think I can live with that. MY thinking was that if it happening in the executor or parser, tough. That we only handle errors happing at the final insert/update/delete. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpzgZld9AGTD.pgp Description: PGP signature
Re: [HACKERS] server closed connection on a select query
Guillaume Lelarge <[EMAIL PROTECTED]> writes: >>> I've installed a 8.1.0 PostgreSQL server on a SCO OpenServer 5.0.6. > Some tests failed.. strangely, it seems int4 and int8 share the same > range. This is expected behavior if the platform's C compiler doesn't support any 64-bit integer type. We go out of our way to make sure that PG will still work (with reduced functionality, ie int8 is really int4) on such platforms. But you might prefer to get a better C compiler instead. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Practical error logging for very large COPY
Tom Lane <[EMAIL PROTECTED]> writes: > > It would be nice to be able to have the former loaded into an actual table > > where it can be queried and perhaps fixed and reloaded. > > > The latter clearly cannot. > > Sure it can --- you just have to dump it as raw text (or perhaps bytea, > as someone suggested upthread). I didn't just say "loaded into an actual table" I said "loaded into an actual table where it can be queried and perhaps fixed and reloaded". From a practical point of view having the data in the already parsed format is a whole lot more useful. You can then do a query to look up the record it conflicted with or look up possible foreign key values that would work instead of the failed reference. You can also insert it directly into the table instead of having to dump it out to a text file and load it with COPY again. Actually I think it would be useful to be able to do this to constraints generally, not just during COPY. If I update or insert a record and it fails due to a constraint violation it would be handy to be able to view the failed record. Perhaps what's really needed is something like CREATE TRIGGER AFTER CONSTRAINT VIOLATION which can then go ahead and insert the record into some other table if it feels like. COPY then would just need an option to proceed even after an error. Presumably only to be used if you're inserting into a clean ETL table, not directly into production tables. > I think the distinction you are proposing between constraint errors > and datatype errors is entirely artificial. Who's to say what is a > constraint error and what is a datatype error, especially when you > start thinking about cases like varchar length constraints or > domain-type constraints? If we create a mechanism that behaves > differently depending on whether the error is detected before or after > we try to form a tuple containing the data, we're going to have > something that is exceedingly awkward to use, because the behavior will > be nearly arbitrary from the user's viewpoint. Well sure from a theoretical point of view. However from a practical point of view there's a whole lot more that can be done with the data once it's in a meaningful format. There's not much you can do with text other than stare at it (and you can't even necessarily do that with bytea). -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] server closed connection on a select query
Sorry for this late answer. I tried a lot of things and it still doesn't work. 2005/11/11, Martijn van Oosterhout : > On Thu, Nov 10, 2005 at 11:53:04PM +0100, Guillaume LELARGE wrote: > > Hi, > > > > I've installed a 8.1.0 PostgreSQL server on a SCO OpenServer 5.0.6. It > > seemed to work well with psql and such tools. I tried to connect to > > this server with pgAdmin3 and a query failed. I tried to find which > > part of the query was wrong and I have a strange result : > > > > SELECT 1 FROM pg_language WHERE lanispl IS TRUE; > > this one crashes the server. > > > > SELECT 1 FROM pg_language WHERE lanispl = true; > > works. > > Does this pass regression testing (ie make check)? It looks like it's > dying all over the please. Posting a backtrace (bt in gdb) would be > more helpful. > Some tests failed.. strangely, it seems int4 and int8 share the same range. I didn't go further on this because I don't think this will resolve my real issue. See my next answer to Larry for more informations. -- Guillaume. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Practical error logging for very large COPY
Greg Stark <[EMAIL PROTECTED]> writes: > I think that's precisely the point here though. There are basically two > categories of errors: > 1) Data that can be parsed and loaded but generates some sort of constraint >violation such as a UNIQUE violation, foreign key violation, or other >constraint violation. > 2) Data that can't be parsed as the correct data type at all. > It would be nice to be able to have the former loaded into an actual table > where it can be queried and perhaps fixed and reloaded. > The latter clearly cannot. Sure it can --- you just have to dump it as raw text (or perhaps bytea, as someone suggested upthread). I think the distinction you are proposing between constraint errors and datatype errors is entirely artificial. Who's to say what is a constraint error and what is a datatype error, especially when you start thinking about cases like varchar length constraints or domain-type constraints? If we create a mechanism that behaves differently depending on whether the error is detected before or after we try to form a tuple containing the data, we're going to have something that is exceedingly awkward to use, because the behavior will be nearly arbitrary from the user's viewpoint. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 8.1.0 catalog corruption
Bob Ippolito <[EMAIL PROTECTED]> writes: > On Nov 21, 2005, at 5:50 PM, Tom Lane wrote: >> Hm, do the drop/add constraint functions get executed even when >> clone_table decides not to make a new table? If so, that would >> probably explain the pattern I'm seeing in the dump of many updates of the >> pg_class row. > Yes, they do. The constraints are there for constraint exclusion. I dug through the dump more closely and determined that the newest remaining version of the ping_1132387200 row claims to have been outdated by transaction 000d585f. However, its ctid points to an item slot that seems to have been reused by a much later transaction (000fac5c). So I'm afraid all the evidence is gone about what really happened :-(. If we had caught the problem earlier maybe we could have learned more. If you see it happen again, could you get dumps of pg_class (in both dump formats) as quickly as possible? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO item "%Allow pg_hba.conf be controlled via
I found this thread on google, http://groups.google.nl/group/pgsql.hackers/browse_thread/thread/1ccb7ade8d7e7475/8b10fb1ca5fdd3ef?lnk=st&q=pg_hba.conf+settings+to+be+controlled+via+SQL&rnum=3&hl=nl#8b10fb1ca5fdd3ef > Bruno Wolff III wrote: >> On Tue, Nov 22, 2005 at 10:57:19 +0100, >> [EMAIL PROTECTED] wrote: >> >>>Just out of curiosity. Is there someone involved with ToDo item %Allow >>>pg_hba.conf settings to be controlled via SQL? > > pgAdmin with the admin81 functions can handle this... > > Regards, > Andreas > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] order by, for custom types
On 2005-11-22, at 17:17, Martijn van Oosterhout wrote: On Tue, Nov 22, 2005 at 05:14:35PM +0100, Grzegorz Jaskiewicz wrote: Ok, I hacked btree for my type, and surely I can have both btree and gist at the same time on the same column. /me is now going to have a look on btree_gist. You don't actually have to have a btree defined on your column for ORDER BY to work, you just need to define a btree OPERATOR CLASS so that PostgreSQL knows what you mean by "ORDER BY". yep, I know. Thanks. Anyhow, I wanted to see if having two indexes will make it faster/ slower. and if it is possible in first place. -- GJ "If we knew what we were doing, it wouldn't be called Research, would it?" - AE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Practical error logging for very large COPY
Tom Lane <[EMAIL PROTECTED]> writes: > Martijn van Oosterhout writes: > > On Tue, Nov 22, 2005 at 09:58:44AM -0500, Tom Lane wrote: > >> The general problem that needs to be solved is "trap any error that > >> occurs during attempted insertion of a COPY row, and instead of aborting > >> the copy, record the data and the error message someplace else". > > > Actually, there are really only a few errors people want to trap I > > imagine: > > You've forgotten bad data, eg "foo" in an integer field, or an > untranslatable multibyte character. The bad-data problem is what lets > out trigger-based solutions, or indeed anything that presumes that the > bad data can be forced into a particular representation. I think that's precisely the point here though. There are basically two categories of errors: 1) Data that can be parsed and loaded but generates some sort of constraint violation such as a UNIQUE violation, foreign key violation, or other constraint violation. 2) Data that can't be parsed as the correct data type at all. It would be nice to be able to have the former loaded into an actual table where it can be queried and perhaps fixed and reloaded. The latter clearly cannot. I would say it should just generate a log entry. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO item "%Allow pg_hba.conf be controlled via SQL"
Bruno Wolff III wrote: On Tue, Nov 22, 2005 at 10:57:19 +0100, [EMAIL PROTECTED] wrote: Just out of curiosity. Is there someone involved with ToDo item “%Allow pg_hba.conf settings to be controlled via SQL”? pgAdmin with the admin81 functions can handle this... Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] order by, for custom types
On Tue, Nov 22, 2005 at 05:14:35PM +0100, Grzegorz Jaskiewicz wrote: > Ok, I hacked btree for my type, and surely I can have both btree and > gist at the same time on the same column. > /me is now going to have a look on btree_gist. You don't actually have to have a btree defined on your column for ORDER BY to work, you just need to define a btree OPERATOR CLASS so that PostgreSQL knows what you mean by "ORDER BY". Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpnSZX1wDOMm.pgp Description: PGP signature
Re: [HACKERS] order by, for custom types
On 2005-11-22, at 16:39, Martijn van Oosterhout wrote: On Tue, Nov 22, 2005 at 04:24:21PM +0100, Grzegorz Jaskiewicz wrote: Translation: you do know how to define a sortable order (ie, generate the text version and compare); you're just too lazy to create the operators to do it ... We do have WORKING < , > , etc operators, and ::text cast already. Thing is, can I have btree and gist indexes at the same time ? Sure, did you look at the ltree example someone pointed you to? Have a nice day, Ok, I hacked btree for my type, and surely I can have both btree and gist at the same time on the same column. /me is now going to have a look on btree_gist. Thanks folks. -- GJ "If we knew what we were doing, it wouldn't be called Research, would it?" - AE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Practical error logging for very large COPY
On Tue, Nov 22, 2005 at 10:45:50AM -0500, Tom Lane wrote: > Martijn van Oosterhout writes: > > Actually, there are really only a few errors people want to trap I > > imagine: > > You've forgotten bad data, eg "foo" in an integer field, or an > untranslatable multibyte character. The bad-data problem is what lets > out trigger-based solutions, or indeed anything that presumes that the > bad data can be forced into a particular representation. So don't pass the row in that case. The trigger still has the oppotunity to return a null tuple to have the error ignored. I don't think it diminishes the benefits of the idea, being that a general trigger mechanism is way better than adding special exception blocks to INPERT and/or COPY to handle special cases. I've looked around some other RDBMSs and they don't tell you in the exception handler the row that caused the error, so we're hardly behind the pack here. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpqJNhFsSSKL.pgp Description: PGP signature
Re: [HACKERS] TODO item "%Allow pg_hba.conf be controlled via SQL"
On Tue, Nov 22, 2005 at 10:57:19 +0100, [EMAIL PROTECTED] wrote: > Just out of curiosity. Is there someone involved with ToDo item %Allow > pg_hba.conf settings to be controlled via SQL? I don't remember any discussions about this recently, so I doubt it is being actively worked on right now. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] order by, for custom types
On Tue, 22 Nov 2005, Grzegorz Jaskiewicz wrote: Thing is, can I have btree and gist indexes at the same time ? no, we have contrib/btree_gist for this Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Practical error logging for very large COPY
Martijn van Oosterhout writes: > On Tue, Nov 22, 2005 at 09:58:44AM -0500, Tom Lane wrote: >> The general problem that needs to be solved is "trap any error that >> occurs during attempted insertion of a COPY row, and instead of aborting >> the copy, record the data and the error message someplace else". > Actually, there are really only a few errors people want to trap I > imagine: You've forgotten bad data, eg "foo" in an integer field, or an untranslatable multibyte character. The bad-data problem is what lets out trigger-based solutions, or indeed anything that presumes that the bad data can be forced into a particular representation. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] order by, for custom types
On Tue, Nov 22, 2005 at 04:24:21PM +0100, Grzegorz Jaskiewicz wrote: > >Translation: you do know how to define a sortable order (ie, generate > >the text version and compare); you're just too lazy to create the > >operators to do it ... > We do have WORKING < , > , etc operators, and ::text cast already. > Thing is, can I have btree and gist indexes at the same time ? Sure, did you look at the ltree example someone pointed you to? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpCMdb9e3NaL.pgp Description: PGP signature
Re: [HACKERS] Practical error logging for very large COPY
On Tue, Nov 22, 2005 at 09:58:44AM -0500, Tom Lane wrote: > The general problem that needs to be solved is "trap any error that > occurs during attempted insertion of a COPY row, and instead of aborting > the copy, record the data and the error message someplace else". Seen > in that light, implementing a special path for uniqueness violations is > pretty pointless. Actually, there are really only a few errors people want to trap I imagine: - CHECK constraints (all handled in ExecConstraints) - Duplicate keys - Foreign key violations (all handled by triggers) Rather than worry about all the events we can't safely trap, how about we simply deal with the handful that are trappable. For example, we let people create an ON ERROR trigger and use the existing trigger interface. We have three possibilities: - They return the same tuple, throw the error - They return NULL, ignore error, goto next tuple - They return a different tuple, retest the conditions The trigger can then do anything a normal trigger can do, including copying to another table if people like that. This doesn't seem like awfully hard work, does it? Initially at least, no TRY blocks needed... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpjgPY5X94i2.pgp Description: PGP signature
Re: [HACKERS] order by, for custom types
On 2005-11-22, at 15:45, Tom Lane wrote: "Kevin McArthur" <[EMAIL PROTECTED]> writes: This is acceptable to create a unique constraint, however, we cannot mark the column unique, without defining btree operators, which clearly are not possible for sorting. Is there any way to base the operators based on the text representation of the type for strict equality (not to be confused with same or equivilent) and thus use that not as an ordering method, but as a simple equality for uniqueness. Translation: you do know how to define a sortable order (ie, generate the text version and compare); you're just too lazy to create the operators to do it ... We do have WORKING < , > , etc operators, and ::text cast already. Thing is, can I have btree and gist indexes at the same time ? -- GJ "If we knew what we were doing, it wouldn't be called Research, would it?" - AE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Practical error logging for very large COPY
Simon Riggs <[EMAIL PROTECTED]> writes: > I have committed the sin of omission again. > Duplicate row violation is the big challenge, but not the only function > planned. Formatting errors occur much more frequently, so yes we'd want > to log all of that too. And yes, it would be done in the way you > suggest. > Here's a fuller, but still brief sketch: > COPY ... FROM > [ERRORTABLES format1 [uniqueness1] >[ERRORLIMIT percent]] This is getting worse, not better :-( The general problem that needs to be solved is "trap any error that occurs during attempted insertion of a COPY row, and instead of aborting the copy, record the data and the error message someplace else". Seen in that light, implementing a special path for uniqueness violations is pretty pointless. You could almost do this today in about five minutes with a PG_TRY construct. The hard part is to distinguish errors that COPY can safely trap from errors that must be allowed to abort the transaction anyway (usually because the backend won't be in a consistent state if it's not allowed to do post-abort cleanup). I think the latter class would mostly be "internal" errors, and so not trapping them shouldn't be a big problem for usefulness; but we can't simply ignore the possibility that they would occur during COPY. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] order by, for custom types
"Kevin McArthur" <[EMAIL PROTECTED]> writes: > This is acceptable to create a unique constraint, however, we cannot mark > the column unique, without defining btree operators, which clearly are not > possible for sorting. Is there any way to base the operators based on the > text representation of the type for strict equality (not to be confused with > same or equivilent) and thus use that not as an ordering method, but as a > simple equality for uniqueness. Translation: you do know how to define a sortable order (ie, generate the text version and compare); you're just too lazy to create the operators to do it ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] order by, for custom types
Take the query. select a,b from dupa where b::text in (select b::text from dupa group by b::text having count(b) > 2); This is acceptable to create a unique constraint, however, we cannot mark the column unique, without defining btree operators, which clearly are not possible for sorting. Is there any way to base the operators based on the text representation of the type for strict equality (not to be confused with same or equivilent) and thus use that not as an ordering method, but as a simple equality for uniqueness. Kevin McArthur - Original Message - From: "Andrew - Supernews" <[EMAIL PROTECTED]> To: Sent: Saturday, November 19, 2005 10:54 PM Subject: Re: [HACKERS] order by, for custom types On 2005-11-19, Grzegorz Jaskiewicz <[EMAIL PROTECTED]> wrote: Wildcards cause things not to work as they should consider everything in [] brackets to be a possible choice and those three: a = 1.2.3.4 b = 1.[2,3].3.4 c = 1.3.3.4 a = b, b = c, but a <> c, I was told that because of that btree won't work on my type. (on irc, that was AndrewSN as I recall). Probably. But nothing stops you defining equality and ordering operators that _do_ work for btree, and hence sorting, it's just that those operators won't be any use for the matching semantics. It's clear that for your data type that there is a concept of "equality" in which all three of your values a,b,c above are unequal. My advice would be (and I'm sure I suggested this at the time) that you reserve the '=' operator for a true equality operation, and use some other operator such as ~ or @ for the "matches" semantics that you want for your application. Having an intransitive '=' operator violates the POLA, even if it doesn't actively break anything otherwise (I have no idea if it does). I do have all operators required for btree, no operator class defined, every single operator. Btree requires some function apart from operators, this one is not defined, but I do have = operator as well. You still don't seem to understand that what btree requires is not an operator _called_ '=', but an operator with the logical semantics of "equality". That operator can be called anything you please (it doesn't have to have the name '='). Sorting doesn't need an equality operator, since it can fabricate one if given a suitable < operator, i.e. one that constitutes a strict weak ordering over the elements to be sorted; it can rely on the fact that NOT(a < b) AND NOT(b < a) implies that a and b are equivalent for sorting purposes. (The requirement that < constitute a strict weak ordering is enough to ensure that this is an equivalence relation, and therefore transitive; if < does not meet this requirement then sorting may give wrong answers, loop forever, or possibly crash.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Webiste problems
I apologise if this isn't the correct list to post this issue to, I just wanted to give a heads up that the main postgresql.org website doesn't seem to be giving access to any ftp mirrors. Needless to say, when I tried to download what I was after from the main server, I just got a 'too many concurrent connections' error. Regards, Mark Wilkinson ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MERGE vs REPLACE
On Tue, Nov 22, 2005 at 04:20:12AM +0100, Petr Jelinek wrote: > It was already said here that oracle and db2 both use MERGE, dunno about > mssql. > > And yes merge CAN be used to do REPLACE (oracle uses their dummy table > for this, we can use the fact that FROM clause isn't required in postgres). Statements about MERGE on the web: http://www.dba-oracle.com/oracle_tips_rittman_merge.htm http://databasejournal.com/features/db2/article.php/3322041 http://certcities.com/editorial/columns/story.asp?EditorialsID=51 http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.doc/sqls578.htm http://www.jdixon.dotnetdevelopersjournal.com/i_want_my_sql_2005_merge_statement.htm http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0010873.htm http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci978700,00.html Not one (*not one!*) of these mentions any special handling of duplicate keys. They even go to pains to say that any errors cause everything to rollback. The last one is especially interesting: : Is there any way to capture errors from a MERGE statement? Also, is : there any way to know how many records were inserted or updated for the : MERGE statement like SQL%ROWCOUNT? Any assistance greatly appreciated. : : You capture errors the same way you would if you were doing regular : INSERT and UPDATE statementswith exception handlers. Just include a : WHEN OTHERS exception handler in the block where your MERGE statement : is and have to display SQLCODE and SQLERRM if an error occurs. Then you : can figure out which specific errors are occurring and create : individual exception handlers for those. There are even places that tell you how to decompose your MERGE into an INSERT plus UPDATE statement. The real advantage of MERGE is that the semantics prevent your updating a row you just inserted, which is harder in the general case but easy if the executor is handling the rows one at a time. Rather than trying to make MERGE do something it wasn't designed for, we should probably be spending our efforts on triggers for error conditions. Maybe something like: CREATE TRIGGER foo AFTER ERROR ON bar EXECUTE baz(); Where baz would be passed NEW and OLD just like a normal trigger and if the trigger return NULL, the update is ignored. In the meantime the function can divert the insert to another table if it likes. This seems like a much more workable and useful addition. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpqFtq6mXEYi.pgp Description: PGP signature
[HACKERS] SHOW ALL output too wide
I've noticed that in 8.1 the output of SHOW ALL includes a description column. This makes the output very wide which makes it hard to use from psql (I need to make the terminal window 164 characters wide to not get any line wrapping). I wish I would have noticed this before 8.0 was out and then I would have voted no. Also, how come it's not implemented by a \show command in psql that queries pg_settings. Then it would work like most other commands. And one could have a \show+ command that include the description. Actually, I'm going to implement a \show command and send to -patches and then SHOW can even be deprecated (if we want). SHOW is just a command line client command, that is implemented in the server. That is not how we normally do things in pg (for example, we have \d instead of a server DESCRIBE command). -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Web page down (ad server)
On Tue, 22 Nov 2005, Dennis Bjorklund wrote: On Tue, 22 Nov 2005, Dennis Bjorklund wrote: ps. The cvs server also seems to be down (postgresql.org). Forgot to say in the last mail, but this also works now. Seems like I should have waited some more before sending the mail. I waited 30 minutes but I should have waited 40... Next time it happens, if it happens ... could you run a traceroute on postgresql.org? Everything appears to be fine, and been fine, for ~23 days now, so it isn't the server crashing/rebooting or anything like that ... and my network connection from home hasn't drop'd or anything, so my route to the server(s) appears to be clean ... am curious if maybe your routing was down, and, if so, at what point ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Web page down (ad server)
On Tue, Nov 22, 2005 at 09:22:18AM +0100, Dennis Bjorklund wrote: > Something is wrong with the web site for me. I look at: > > http://www.postgresql.org/developer/ > Turns out it's related to the ads, so if I just adblock the ad server I > can see the page just fine. Kind of bad it's needed however :-) > > I also tried to show the page in opera and it looks the same as in > firefox. I had this happen to me a little while ago. Any page related to postgres would stop halfway. Eventually I tracked it down to it trying to access a completely different site (probably the ads). I Null routed that IP and everything came back fine. It would be possible to format the adblock in such a way that it doesn't prevent rendering if it isn't there... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpeaHjBXdjNl.pgp Description: PGP signature
Re: [HACKERS] Should libedit be preferred to libreadline?
On Tue, Nov 22, 2005 at 10:07:15AM +0100, Zeugswetter Andreas DCP SD wrote: > PS: I'd prefer if readline was only linked where it is needed, namely in > psql. The problem as stated is that people don't want to maintain lists of libraries as needed by each program, so we link all of them. Since it seems to always be the same few libraries that cause us problems, maybe a simpler approach would be to, in the Makefile, use filter-out to exclude libraries you *know* aren't needed. Like in the Makefile we put: postgres: $(OBJS) $(CC) $(CFLAGS) $(LDFLAGS) $(export_dynamic) $^ $(filter-out -lreadline -ledit, $(LIBS)) -o $@ So instead of maintaining lists of what each binary needs, we can maintain a few lists of what certain binaries *don't* need. Actually, I think the backend is the only thing important enough to worry about this, although if readline is exporting memcpy that it's quite possible other binaries might be affected. Maybe create a BACKEND_LIBS which contains a shorter list. Even the GCC --as-needed flag can't save you from libs exporting functions they shouldn't... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp8Pn6BoBdmd.pgp Description: PGP signature
[HACKERS] TODO item "%Allow pg_hba.conf be controlled via SQL"
Just out of curiosity. Is there someone involved with ToDo item %Allow pg_hba.conf settings to be controlled via SQL? Regards, Gevik. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Practical error logging for very large COPY
On Mon, 2005-11-21 at 19:05 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Flow of control would be to: > > > locate page of index where value should go > > lock index block > > _bt_check_unique, but don't error > > if violation then insert row into ERRORTABLE > > else > > insert row into data block > > insert row into unique index > > unlock index block > > do other indexes > > Ugh. Do you realize how many levels of modularity violation are implied > by that sketch? IMHO the above is fairly ugly, but I suggest it now because: 1. I want to avoid uniqueness violations in COPY 2. The logic used is very similar to that recently proposed for MERGE. If anybody has a better idea for (1), shout it out now. If the logic is OK for MERGE, then it should be OK for COPY with uniqeness violation trapping also. Both use uniqueness checking first, so you'd need to argue against both or neither. > Have you even thought about the fact that we have more > than one kind of index? Yes, but they don't support unique indexes do they? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Practical error logging for very large COPY
On Tue, 2005-11-22 at 10:00 +0800, Christopher Kings-Lynne wrote: > Seems similar to the pgloader project on pgfoundry.org. It is similar and good, but I regard that as a workaround rather than the way forward. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Should libedit be preferred to libreadline?
> With AIX 5, the easiest way to get a shared object is to pass "-bexpall" > to the linker. This results in all symbols being exported. Yes, that is another reason not to use this broken switch. And last time I checked (AIX 4.3.3), -bexpall did not export all needed symbols (e.g. globals) from the backend eighter. And the counterpart -bimpall did also not work. Dynamic loading did not work without the .imp and .exp files :-( Andreas PS: I'd prefer if readline was only linked where it is needed, namely in psql. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Web page down (ad server)
On Tue, 22 Nov 2005, Dennis Bjorklund wrote: > ps. The cvs server also seems to be down (postgresql.org). Forgot to say in the last mail, but this also works now. Seems like I should have waited some more before sending the mail. I waited 30 minutes but I should have waited 40... -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Web page down (ad server)
On Tue, 22 Nov 2005, Magnus Hagander wrote: > > > Turns out it's related to the ads, so if I just adblock the > > ad server I can see the page just fine. Kind of bad it's > > needed however :-) > > > > I also tried to show the page in opera and it looks the same > > as in firefox. > > Intersting. It works fine for me. Did you try multiple times? (Since you > tried a different browser, I assume you did). Of course. Maybe it's was some dns problem (all the adresses seemed to resolve, but maybe some adress have been updated but not propagated to all dns servers, or something. What do I know?). As I said, I could solve it just fine by using adblock so it's not a problem for me. I just wanted to tell someone about it. I did try like 50 times during 30 minutes. Every time it got stuck waiting on 200.46.208.156. And guess what, now that I remove the adblock to try some more it seems to work again. Aaarg, computers drive me crazy :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Web page down (ad server)
[moved to -www] > Something is wrong with the web site for me. I look at: > > http://www.postgresql.org/developer/ > > then this is what I see: > > http://www.zigo.dhs.org/~dennis/tmp/dev.png > > (everything is there except the main content that is not). Yikes. > Turns out it's related to the ads, so if I just adblock the > ad server I can see the page just fine. Kind of bad it's > needed however :-) > > I also tried to show the page in opera and it looks the same > as in firefox. Intersting. It works fine for me. Did you try multiple times? (Since you tried a different browser, I assume you did). Could be just one mirror that's broken - can you check which mirror you're serving off? Also, does it serve any specific ad? Or does it break with different ads? (Should be visible from view source) > ps. The cvs server also seems to be down (postgresql.org). Can't say anything about that one. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Web page down (ad server)
Something is wrong with the web site for me. I look at: http://www.postgresql.org/developer/ then this is what I see: http://www.zigo.dhs.org/~dennis/tmp/dev.png (everything is there except the main content that is not). Turns out it's related to the ads, so if I just adblock the ad server I can see the page just fine. Kind of bad it's needed however :-) I also tried to show the page in opera and it looks the same as in firefox. ps. The cvs server also seems to be down (postgresql.org). -- /Dennis Björklund ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Practical error logging for very large COPY
On Mon, 2005-11-21 at 19:38 -0500, Andrew Dunstan wrote: > > Tom Lane wrote: > > >Simon Riggs <[EMAIL PROTECTED]> writes: > > > > > >>What I'd like to do is add an ERRORTABLE clause to COPY. The main > >>problem is how we detect a duplicate row violation, yet prevent it from > >>aborting the transaction. > >> > >If this only solves the problem of duplicate keys, and not any other > >kind of COPY error, it's not going to be much of an advance. > > > Yeah, and I see errors from bad data as often as from violating > constraints. Maybe the best way if we do something like this would be to > have the error table contain a single text, or maybe bytea, field which > contained the raw offending input line. I have committed the sin of omission again. Duplicate row violation is the big challenge, but not the only function planned. Formatting errors occur much more frequently, so yes we'd want to log all of that too. And yes, it would be done in the way you suggest. Here's a fuller, but still brief sketch: COPY ... FROM [ERRORTABLES format1 [uniqueness1] [ERRORLIMIT percent]] where Format1, Uniqueness1 would be created from new by this command (or error if they already exist) Format1 would hold formatting errors so would be in a blob table with cols (line number, col number, error number, fullrowstring) Uniqueness1 would be same definition as table, but with no indexes This table would be optional, indicating no uniqueness violation checks would be needed to be carried out. If present and yet no unique indexes exist, then Uniqueness1 would be ignored (and not created). ERRORLIMIT percent would abort the COPY if more than percent errors were found, after the first 1000 records (that limit could also be stated if required). Without the ERRORTABLES clause, COPY would work exactly as it does now. How does that sound? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
