Re: [HACKERS] MERGE vs REPLACE
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: It seems to me that it has always been implicitly assumed around here that the MERGE command would be a substitute for a MySQL-like REPLACE functionality. After rereading the spec it seems that this is not the case. MERGE always operates on two different tables, which REPLACE doesn't do. Normally I'd plump for following the standard ... but AFAIR, we have had bucketloads of requests for REPLACE functionality, and not one request for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a whole lot harder and slower than REPLACE, it seems that we could do worse than to concentrate on doing REPLACE for now. (We can always come back to MERGE some other day.) I would also like to add that MySQL's REPLACE is not exactly an INSERT OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the fields not specified in the query are set to their defaults: i.e. CREATE TABLE t (a int PRIMARY KEY, b int, c int); INSERT INTO t (a, b, c) VALUES (1, 1, 2); SELECT * FROM t; +---+--+--+ | a | b| c| +---+--+--+ | 1 |1 |2 | +---+--+--+ REPLACE INTO t (a, b) VALUES (1, 1); SELECT * FROM t; +---+--+--+ | a | b| c| +---+--+--+ | 1 |1 | NULL | +---+--+--+ I wanted to point it out this because people are commonly mistaking this. Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] CONNECT BY PRIOR
Hi, I'm just a little bit confused because I expected postgresql to be able t connect by prior but as I have seen it is not. :-( Are there any plans to support this in the main distribution? If have found a patch to porstgres but I don't want to apply any patches but only use the vanilla postgresql. BTW: The patch is available at http://gppl.moonbone.ru/ Cheers, Yann ---(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] SIGSEGV taken on 8.1 during dump/reload
Gregory Maxwell wrote: So it turned out that he didn't... Is this a sign that we need to include a versioning symbol in SOs so we can give a nice clear error message module foo compiled for PostgreSQL 8.0.2 this is PostgreSQL 8.1. I think this would rarely work in practice. For example, during the elog-ereport transition, any module compiled against the wrong server would immediately get an unresolved symbol: elog/ereport before you can run your nice version check. I had thought about this issue back then because it was an extrememly common occurrence; then only thing I could think of is that you trick the dynamic loader to first reference a symbol with an obvious name like if_you_see_this_in_an_error_message_you_have_a_version_mismatch. However, this would likely be platform dependent and maybe confuse users even more. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Sat, Nov 12, 2005 at 12:28:48PM +0100, Peter Eisentraut wrote: I think this would rarely work in practice. For example, during the elog-ereport transition, any module compiled against the wrong server would immediately get an unresolved symbol: elog/ereport before you can run your nice version check. Actually, that doesn't worry me. What worries me is that people who don't use ereport won't get any error messages at all yet have completely different expectations at to the structure of various internal structures. So the idea is to force failure when it would otherwise succeed, not just for the pretty error messages but for stability of the system. I would be in favour if storing the CATALOG_VERSION in the pg_finfo struct and rejecting anything that doesn't match. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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. pgpb8gCsNAuNe.pgp Description: PGP signature
[HACKERS] vacuumlo doesn't seems to have help
Postresql 8.1.0: I was looking what does vacuumlo provide in contrib, but it seems there is a problem with messages given by this program: [EMAIL PROTECTED] regress]$ /usr/bin/vacuumlo vacuumlo: missing required argument: database name Try 'vacuumlo -?' for help. [EMAIL PROTECTED] regress]$ /usr/bin/vacuumlo -? [EMAIL PROTECTED] regress]$ --help or -h return invalid option. If someone can fix the -? or the message return by this program. Thanks by advance. pgpmL69ycQX1s.pgp Description: PGP signature
Re: [HACKERS] How to find a number of connections
Thank you, this is very handy. I'd like to understand why the result of this query is different from the number of Postgres processes reported by `ps`. (The query returns 8, ps shows 11) One process is really postmaster, the others seem to be doing some house chores: stats buffer process and stats collector process. Do they actually use connections, decrementing what's available from the max_connections value? Thank you -Original Message- From: [EMAIL PROTECTED] On Behalf Of Bryan White ... ... Brusser, Michael wrote: Is there a way to find a number of current connections on Postgres 7.3.x ? select count(*) from pg_stat_activity ---(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] SIGSEGV taken on 8.1 during dump/reload
Martijn van Oosterhout kleptog@svana.org writes: So the idea is to force failure when it would otherwise succeed, not just for the pretty error messages but for stability of the system. Exactly. Peter's right that we'd not always get a nice error message --- but it's not hard to figure out unresolved symbol failures. As we just were reminded, it can be really hard to figure out minor incompatibilities with wrong-version libraries, and the real point of the proposal is to save us from going through *that* again. I would be in favour if storing the CATALOG_VERSION in the pg_finfo struct and rejecting anything that doesn't match. Not sure that CATALOG_VERSION is an amazingly useful thing to use. I think the major version number (eg 8.1) would be sufficient, and it'd certainly give error messages that meant more to the casual user. The problem with CATALOG_VERSION is that we bump it basically for changes in the on-disk contents of a freshly initdb'd database, which does not have all that much to do with the ABI seen by a shared library. To have something useful that is finer-grain than major version number, I think we'd need to invent a separate version number that could be bumped whenever we made incompatible changes in in-memory structures or function APIs. Which'd be almost every day during development :-( I don't think it's worth trying to do that. People who work with development tip should know to recompile their libraries whenever they recompile the main system. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] How to find a number of connections
Brusser, Michael [EMAIL PROTECTED] writes: One process is really postmaster, the others seem to be doing some house chores: stats buffer process and stats collector process. Do they actually use connections, No. 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] SIGSEGV taken on 8.1 during dump/reload
I thought of an alternative approach to the library version problem: what about taking a leaf from the usual shared library versioning approach, ie, put the version number into the library file name? So instead of loading, say, plpgsql.so we'd insist on loading plpgsql.so.8.2. This would avoid Peter's objection that the dynamic linker might give a hard-to-interpret error message, and it'd not require assuming that the library uses V1 function call convention either. On the other hand, it'd be relatively easy for clueless lusers to defeat; I can readily imagine someone copying foo.so.8.2 to foo.so.8.3 when the backend complained that it couldn't find the latter. So maybe it's not what we want. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Sat, Nov 12, 2005 at 10:47:35AM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: I would be in favour if storing the CATALOG_VERSION in the pg_finfo struct and rejecting anything that doesn't match. Not sure that CATALOG_VERSION is an amazingly useful thing to use. I think the major version number (eg 8.1) would be sufficient, and it'd certainly give error messages that meant more to the casual user. Sure, CATALOG_VERSION isn't that useful, but it's the only thing in the header files that gives any kind of indication what version you're compiling against. PG_VERSION is a string, which diminishes its usefulness considerably. The problem with CATALOG_VERSION is that we bump it basically for changes in the on-disk contents of a freshly initdb'd database, which does not have all that much to do with the ABI seen by a shared library. To have something useful that is finer-grain than major version number, I think we'd need to invent a separate version number that could be bumped whenever we made incompatible changes in in-memory structures or function APIs. Which'd be almost every day during development :-( I don't think it's worth trying to do that. People who work with development tip should know to recompile their libraries whenever they recompile the main system. People working with development versions are more likely to get it right, and more importantly, they're less likely to complain to the list as they're likely to know what's happening. What we're dealing with is laymen crossing completely different versions of postgres, and the catalog version will catch them. Unless someone is actually willing to maintain a seperate ABI version, using the catalog version will at least solve the major problem. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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. pgprfekbHdToK.pgp Description: PGP signature
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Martijn van Oosterhout kleptog@svana.org writes: Sure, CATALOG_VERSION isn't that useful, but it's the only thing in the header files that gives any kind of indication what version you're compiling against. PG_VERSION is a string, which diminishes its usefulness considerably. How so? All we care about is being able to (1) compare for equality, and (2) print out something useful in error messages. I claim that PG_VERSION does #1 equally well and #2 better. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Sat, Nov 12, 2005 at 11:18:51AM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Sure, CATALOG_VERSION isn't that useful, but it's the only thing in the header files that gives any kind of indication what version you're compiling against. PG_VERSION is a string, which diminishes its usefulness considerably. How so? All we care about is being able to (1) compare for equality, and (2) print out something useful in error messages. I claim that PG_VERSION does #1 equally well and #2 better. I was thinking of compile time. The compiler can compare CATALOG_VERSION in #if statements, but it can't compare strings. Trying to make a module that compiles against several different versions of postgres requires testing against CATALOG_VERSION because there's nothing else. However, if we purely want distinguish between major releases in the loading of modules (thus implying no ABI changes between 8.1.0 and 8.1.7), then PG_VERSION will do fine. Another way that doesn't require code changes would be to make a dummy symbol containing the version and referring to it in pg_finfo. Then you'd get error messages like: Couldn't find symbol 'PG_version_verify_8_1'. i.e. let the dynamic linker do the work. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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. pgp805SDLFAg5.pgp Description: PGP signature
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Martijn van Oosterhout kleptog@svana.org writes: On Sat, Nov 12, 2005 at 11:18:51AM -0500, Tom Lane wrote: How so? All we care about is being able to (1) compare for equality, and (2) print out something useful in error messages. I claim that PG_VERSION does #1 equally well and #2 better. I was thinking of compile time. The compiler can compare CATALOG_VERSION in #if statements, but it can't compare strings. We aren't asking the compiler to compare anything, though. I'm imagining just that the PG_FUNCTION_INFO_V1 macro will insert the value into the Pg_finfo_record struct, and the comparison will happen at run time in dfmgr.c. Another way that doesn't require code changes would be to make a dummy symbol containing the version and referring to it in pg_finfo. Then you'd get error messages like: Couldn't find symbol 'PG_version_verify_8_1'. i.e. let the dynamic linker do the work. That would be attractive if we could get it to happen without the assumption that the library uses PG_FUNCTION_INFO_V1 ... but if it still needs that assumption, it doesn't seem like much of an improvement. It's not always easy for people to see dynamic-linker error messages, so I'd rather the message were issued under our control when possible. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Sat, Nov 12, 2005 at 12:03:00PM -0500, Tom Lane wrote: That would be attractive if we could get it to happen without the assumption that the library uses PG_FUNCTION_INFO_V1 ... but if it still needs that assumption, it doesn't seem like much of an improvement. It's not always easy for people to see dynamic-linker error messages, so I'd rather the message were issued under our control when possible. If you want something that works even if people don't use PG_FUNCTION_INFO_V1, you need something like the linux kernel source does. During the main build the kernel generate a vmmagic.o object. This defines a number of symbols including a block containing flags about endianness, spinlocks, etc. Any module expecting to be loaded needs to link it in. While loading you simply memcmp() the block with what you're expecting and fail if it doesn't match. Note, this is significantly more finegrained, in that it can pickup descrepicies in HAVE_INT64_TIMESTAMP, NAMEDATALEN, INDEX_MAX_KEYS, etc. The kind of things that currently appear in pg_controldata. In the future maybe a 32/64 bit flag. If we don't like imposing link time constraints, we could require people to include: #ifdef PG_MAGIC_BLOCK PG_MAGIC_BLOCK; #endif In any one of their source files and put the definition in a header file somewhere. This may be even better. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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. pgpul1DVAKwAS.pgp Description: PGP signature
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Martijn van Oosterhout kleptog@svana.org writes: If we don't like imposing link time constraints, we could require people to include: #ifdef PG_MAGIC_BLOCK PG_MAGIC_BLOCK; #endif I was hoping to avoid forcing source-code changes, but something like that might be the best solution. Anyone think it's unreasonable? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Cursor estimated row count
An occasionally asked question is How can I find out how many rows
a cursor will return? to which the answer is Fetch them all. But
what about a way to get the planner's estimate? Would anybody find
that useful? Does the code below look close to being correct?
test= EXPLAIN SELECT * FROM pg_class;
QUERY PLAN
Seq Scan on pg_class (cost=0.00..6.88 rows=188 width=163)
(1 row)
test= BEGIN;
BEGIN
test= DECLARE curs CURSOR FOR SELECT * FROM pg_class;
DECLARE CURSOR
test= SELECT cursor_plan_rows('curs');
cursor_plan_rows
--
188
(1 row)
#include postgres.h
#include fmgr.h
#include nodes/pg_list.h
#include nodes/plannodes.h
#include utils/portal.h
Datum cursor_plan_rows(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(cursor_plan_rows);
Datum
cursor_plan_rows(PG_FUNCTION_ARGS)
{
char*portalname = PG_GETARG_CSTRING(0);
Portal portal;
Plan*plan;
portal = GetPortalByName(portalname);
if (!PortalIsValid(portal)) {
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_CURSOR),
errmsg(cursor \%s\ does not exist, portalname)));
}
if (!portal-planTrees) {
ereport(ERROR,
(errcode(ERRCODE_INVALID_CURSOR_STATE),
errmsg(cursor \%s\ has no plan trees, portalname)));
}
plan = linitial(portal-planTrees);
if (!plan) {
ereport(ERROR,
(errcode(ERRCODE_INVALID_CURSOR_STATE),
errmsg(cursor \%s\ plan is NULL, portalname)));
}
PG_RETURN_FLOAT8(plan-plan_rows);
}
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Cursor estimated row count
Michael Fuhr [EMAIL PROTECTED] writes: An occasionally asked question is How can I find out how many rows a cursor will return? to which the answer is Fetch them all. But what about a way to get the planner's estimate? Would anybody find that useful? Given how far off it frequently is, I can't believe that any of the people who ask for the feature would find this a satisfactory answer :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Sat, Nov 12, 2005 at 12:44:23PM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: If we don't like imposing link time constraints, we could require people to include: #ifdef PG_MAGIC_BLOCK PG_MAGIC_BLOCK; #endif I was hoping to avoid forcing source-code changes, but something like that might be the best solution. Anyone think it's unreasonable? Alternativly, you could make it optional for a release (print warning that magic block wasn't found). Next release require it. It's a small enough change that it wouldn't require huge amounts of effort on the part of module writers. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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. pgpqUmuC7pu2w.pgp Description: PGP signature
Re: [HACKERS] Cursor estimated row count
On Sat, Nov 12, 2005 at 01:50:20PM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: An occasionally asked question is How can I find out how many rows a cursor will return? to which the answer is Fetch them all. But what about a way to get the planner's estimate? Would anybody find that useful? Given how far off it frequently is, I can't believe that any of the people who ask for the feature would find this a satisfactory answer :-( That aside, do you see any problems with the code? Is it at least returning the right wrong answer? ;-) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Martijn van Oosterhout kleptog@svana.org writes: I was hoping to avoid forcing source-code changes, but something like that might be the best solution. Anyone think it's unreasonable? Alternativly, you could make it optional for a release (print warning that magic block wasn't found). Next release require it. What's the point of waiting? We'd be forcing people to add it sooner or later, so why not sooner? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CONNECT BY PRIOR
Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. On 11/12/05, Yann Michel [EMAIL PROTECTED] wrote: Hi,I'm just a little bit confused because I expected postgresql to be ablet connect by prior but as I have seen it is not. :-(Are there any plans to support this in the main distribution? If have found a patch to porstgres but I don't want to apply any patches butonly use the vanilla postgresql.BTW: The patch is available at http://gppl.moonbone.ru/ Cheers,Yann---(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] How to find a number of connections
Tom Lane wrote: Brusser, Michael [EMAIL PROTECTED] writes: One process is really postmaster, the others seem to be doing some house chores: stats buffer process and stats collector process. Do they actually use connections, No. But note that in 8.1, autovacuum does. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CONNECT BY PRIOR
Hi, On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote: Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Fine! Looking forward to that! Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PG_DUMP and table locking in PG7.4
Hi, I'm using PG_DUMP for backing up a postgres 7.4 database. As I have seen, the pg_dump aquires a table lock while dump the table's content. What will happen, if I have a basic table and several inherited tables. Will the PG_DUMP tool only aquire locks on the inherited tables or on the parent-table, too? Thanks in advance! Cheers, Yann ---(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] CONNECT BY PRIOR
That's GREAT news, hope you can make it to 8.2 .. it will be really a good hit On 11/13/05, Yann Michel [EMAIL PROTECTED] wrote: Hi, On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote: Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Fine! Looking forward to that! Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multi-table-unique-constraint
Most of the people who have thought about this have figured that the right solution involves a single index spanning multiple tables (hence, adding a table ID to the index entry headers in such indexes). This fixes the lookup and entry problems, but it's not any help for the lock-against-schema-mods problem, and it leaves you with a real headache if you want to drop just one of the tables. 'Tis a hard problem :-( Maybe the solution is to make inherited tables actually the same table, and jank it with an extra per-row attribute to differentiate them or something :) Might make constraint_exclusion less useful then. Chris ---(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] SIGSEGV taken on 8.1 during dump/reload
Tom Lane wrote: On the other hand, it'd be relatively easy for clueless lusers to defeat; I can readily imagine someone copying foo.so.8.2 to foo.so.8.3 when the backend complained that it couldn't find the latter. So maybe it's not what we want. Hmm...but isn't the version number also something that can be stored in the shared library itself during link time (e.g., via the -soname option to the linker)? The manpage for ld under Linux implies that this will cause the executable that's linked against the shared object to look explicitly for a library with the soname specified by the shared object. I don't know if that just causes the dynamic linker to look for a file with the specified soname or if it will actually examine the shared object under consideration to make sure it has the DT_SONAME field in question, however. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
