AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Do we still need the lastsysoid column in pg_database if we do things this way? Seems like what you really want is to suppress all the objects that are in template0, so you really only need one lastsysoid value, namely template0's. The other entries are useless AFAICS. Where would you store the value if not in pg_database? Would probably best be a fixed value, that leaves room for modifications in template0, that might be necessary for new versions. This oid would have to be explicitly set as last step of initdb. Andreas
Re: AW: [HACKERS] Re: [GENERAL] Query caching
On Wed, 8 Nov 2000, Christof Petig wrote: Karel Zak wrote: What about parameters? Normally you can prepare a statement and execute it We have in PG parameters, see SPI, but now it's used inside backend only and not exist statement that allows to use this feature in be-fe. Sad. Since ecpg would certainly benefit from this. using different parameters. AFAIK postgres' frontend-backend protocol is not designed to take parameters for statements (e.g. like result presents results). A very long road to go. By the way, I'm somewhat interested in getting this feature in. Perhaps it should be part of a protocol redesign (e.g. binary parameters/results). Handling endianness is one aspect, floats are harder (but float-ascii-float sometimes fails as well). PREPARE name AS query [ USING type, ... typeN ] [ NOSHARE | SHARE | GLOBAL ] EXECUTE name [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] [ USING val, ... valN ] [ NOSHARE | SHARE | GLOBAL ] DEALLOCATE PREPARE [ name [ NOSHARE | SHARE | GLOBAL ]] [ ALL | ALL INTERNAL ] An example: PREPARE chris_query AS SELECT * FROM pg_class WHERE relname = $1 USING text; I would prefer '?' as a parameter name, since this is in the embedded sql standard (do you have a copy of the 94 draft? I can mail mine to you?) This not depend on query cache. The '$n' is PostgreSQL query parametr keyword and is defined in standard parser. The PREPARE statement not parsing query it's job for standard parser. Also the standard says a whole lot about guessing the parameter's type. Also I vote for ?::type or type(?) or sql's cast(...) (don't know it's syntax) instead of abusing the using keyword. The postgresql executor expect types of parametrs in separate input (array). I not sure how much expensive/executable is survey it from query. EXECUTE chris_query USING 'pg_shadow'; Great idea of yours to implement this! Since I was thinking about implementing a more decent schema for ecpg but had no mind to touch the backend and be-fe protocol (yet). It would be desirable to do an 'execute immediate using', since using input parameters would take a lot of code away from ecpg. By the way, PREPARE/EXECUTE is face only. More interesting in this period is query-cache-kernel. SQL92 is really a little unlike my PREPARE/EXECUTE. Karel
Re: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
At 08:59 9/11/00 +0100, Zeugswetter Andreas SB wrote: Just seems like we'd be forcing non-standard syntax on ourselves when/if CREATE DATABASE becomes CREATE SCHEMA; I do not think this will be the way. I know there was a lot of discussion of this a while ago, but was there a consistant set of decisions made? I'd be very interested if someone could post the current thinking re: schemas, catalogs, and environments, because the way I read the SQL99 docs, the catalog seems to correspond to a single postgres installation, and a schema seems to correspond to a postgres database (ie. tables and views are defined in a schema, and schemas are defined in a catalog, and catalogs are defined in an environment, and it looks like the environment is akin to the file system/implementation postmaster). Presumably this was raised before, but I'd love to see the consensus view, if it is documented. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited fromtemplate1
Tom Lane wrote: Do we still need the lastsysoid column in pg_database if we do things this way? Seems like what you really want is to suppress all the objects that are in template0, so you really only need one lastsysoid value, namely template0's. The other entries are useless AFAICS. regards, tom lane Right. All we dump after having a non-accessible template0 is the difference to that. So that a dump will create it's database from that template0 (no matter wherever it was created from originally) and "patch" it (i.e. restoring all diffs) to look like at dump time. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited fromtemplate1
Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Where would you store the value if not in pg_database? No other ideas at the moment. I was just wondering whether there was any way to delete it entirely, but seems like we want to have the value for template0 available. The old way of hardwiring knowledge into pg_dump was definitely not as good. To make pg_dump failsafe, we'd IMHO need to freeze all objects that come with template0 copying. For now we have oid's 1-16383 hardwired from the bki files. Some 16384-x get allocated by initdb after bootstrap, so we just need to bump the oid counter at the end of initdb (by some bootstrap interface command) to lets say 32768 and reject any attempt to touch an object with a lower oid. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [HACKERS] Text concat problem
At 05:47 PM 11/8/00 -0600, Luis =?UNKNOWN?Q?Maga=F1a?= wrote:
insert into employee(title,first_name,start_date,charge) values('Mr.
X','Smith',date(now()),'None');
insert into employee(title,first_name,start_date,charge) values('Mr.
Y','Smith',date(now()),'None');
insert into employee(title,first_name,start_date,charge) values('Mr.
Z','Smith',date(now()),'None');
so far there is no problem at all, the problem comes here:
select title || ' ' || first_name || ' ' || last_name as fullname from
employee;
fullname
(3 rows)
Doesn't work , I'm thinking it is because of the null value in last_name.
Right. NULL means "has no value", it's not the empty string. The result of
concatenating with NULL is NULL.
Have any idea or suggestion on how to workaround this situation.
It's a classic NULL issue. 1+NULL = NULL, too, for instance. Try
something like
"default ''" in your table definition rather than use null. Then you'll be
using
the empty string for concatenation. 'abc' || NULL = NULL. 'abc' || '' =
'abc'
which appears to be what you want.
This is standard SQL92 behavior...
- Don Baccus, Portland OR [EMAIL PROTECTED]
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
Re: [HACKERS] problems with configure
On Mié 08 Nov 2000 19:34, Tom Lane wrote: Well, maybe. But is it worth the trouble? Hard to believe anyone else did the same thing. If socklen_t exists, it's presumably the right thing to use, so if we just hardwire "void - socklen_t", I think it'd be OK. If we're wrong, we'll hear about it... Well, I would like to know how this is going to evolve. I will try to download an update with cvsup in a few hours. Hope theres something new. Else, please tell me what would be the best solution (even for the moment). Thanks -- "And I'm happy, because you make me feel good, about me." - Melvin Udall - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
At 09:47 9/11/00 -0500, Jan Wieck wrote: To make pg_dump failsafe, we'd IMHO need to freeze all objects that come with template0 copying. For now we have oid's 1-16383 hardwired from the bki files. Some 16384-x get allocated by initdb after bootstrap, so we just need to bump the oid counter at the end of initdb (by some bootstrap interface command) to lets say 32768 and reject any attempt to touch an object with a lower oid. I'd still like to see this number stored in the pgsql catalog somewhere (not just header files). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] problems with configure
Tom Lane writes: If socklen_t exists, it's presumably the right thing to use, so if we just hardwire "void - socklen_t", I think it'd be OK. If we're wrong, we'll hear about it... Ah, if only life were that simple ;-/ Depending on the version of Solaris and the compiler flags the third argument can be a pointer to socklen_t, void, size_t or int. For Solaris 7 8 the impression I get is that accept() is an XPG4v2 thing and so the compile flags should include one of the following sets of flags. The first specifies XPG4v2 (UNIX95), the second XPG5 (UNIX98). Using either will make the third argument socklen_t*. -D_XOPEN_SOURCE -D_XOPEN_SOURCE_EXTENDED or -D_XOPEN_SOURCE=500 Solaris 2.6 only groks the first of those. Setting the flags for XPG4v2 will use size_t* for arg3, otherwise it will be int*. The underlying types are the same width, size_t is unsigned. I'd expect that the program would work with either, give or take warnings about the signedness. The only choice of arg3 on Solaris 2.5 is int*. My bottom line is that flags for XPG4v2 should be set on Solaris. I've successfully run configure from the current CVS sources on Solaris 7 with the following workaround. I presume that there is a better place to apply the change. CPPFLAGS="-D_XOPEN_SOURCE -D_XOPEN_SOURCE_EXTENDED" configure -- Pete Forman -./\.- Disclaimer: This post is originated Western Geophysical -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- the opinion of Baker Hughes or http://www.crosswinds.net/~petef -./\.- its divisions. ***== My old email address [EMAIL PROTECTED] will ==*** ***== not be operational from Fri 10 to Tue 14 Nov 2000.==***
Re: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Philip Warner [EMAIL PROTECTED] writes: Presumably this was raised before, but I'd love to see the consensus view, if it is documented. AFAIR, the discussion trailed off without any specific decisions being made. One of the things that's still very open in my mind is whether we want to keep the existing notion of independent databases within an installation, and if so how that maps onto the SQL-defined concepts. To me, though, the point of independent databases is that they be *independent*, and therefore if we keep them I'd vote for mapping them to the top-level SQL notion (catalog, you said?). Schemas ought to be substructure within a database. regards, tom lane
Re: [HACKERS] Question about reliability?
Philip Warner [EMAIL PROTECTED] writes: I'd be interested to know the reason for killing the other backends; Because they all share the same shared-memory pool. After a backend crash you can't be sure whether shared memory is corrupted or not. (Even if it's not been actively scribbled on by code gone wild, there are still going to be proc-table and lock-table entries for the failed backend, and we have no other mechanism for getting rid of those.) Would there be any potential to avoid these (possibly) unnecessary deaths? No, at least it'll never get my vote. There is a reason why C-language procedure creation is only allowed to the superuser ;-) --- both in terms of reliability and in terms of ability to access data, you are granting the author of a C procedure 100% life-and-death power over your installation. If you find that his code is not reliable enough for you, either remove it, fix it, or make a separate playpen installation that he can crash at whim. regards, tom lane
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Jan Wieck [EMAIL PROTECTED] writes: For now we have oid's 1-16383 hardwired from the bki files. Some 16384-x get allocated by initdb after bootstrap, so we just need to bump the oid counter at the end of initdb (by some bootstrap interface command) to lets say 32768 and reject any attempt to touch an object with a lower oid. What do you mean by "touch"? The system catalogs certainly can't be made read-only in general. AFAIK we already have sufficient defenses against unwanted hackery on the system catalogs, and so I don't really see a need for another level of checking. regards, tom lane
AW: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
To me, though, the point of independent databases is that they be *independent*, and therefore if we keep them I'd vote for mapping them to the top-level SQL notion (catalog, you said?). Schemas ought to be substructure within a database. Yes, that was also "sort of" the bottom line of the lengthy thread, so I guess we could call it a plan. Andreas
Re: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
At 10:36 9/11/00 -0500, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Presumably this was raised before, but I'd love to see the consensus view, if it is documented. AFAIR, the discussion trailed off without any specific decisions being made. One of the things that's still very open in my mind is whether we want to keep the existing notion of independent databases within an installation, and if so how that maps onto the SQL-defined concepts. To me, though, the point of independent databases is that they be *independent*, I agree; it's a pain that one DB misbehaving kills an entire installation. and therefore if we keep them I'd vote for mapping them to the top-level SQL notion (catalog, you said?). Schemas ought to be substructure within a database. I think the hierarchy goes: Environment-Catalog-Schema From what I can tell: 1. the environment contains truly general things like the SQL parser, the tools for connecting to the DB etc - which I assume also contains the user-authorization stuff. 2. The catalog contains multiple schemas (this is the top level as far as data definitions go, I think). Some predefined schemas (eg. the DEFINITION_SCHEMA) contain views that allow querying of all schema definitions in the catalog. 3. Schemas are what we call databases. They contain tables, views wtc. The SQL standard is careful to avoid using the term database in these discussions, though at one point it does equate 'database' with the part of the environment that contains the actual SQL data (as opposed to metadata). It's a pretty broad definition, and contrary to most peoples expections, I think. Perhaps I'm wrong, but I think most people will equate database with a schema (ie. the thing in which you define tables). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Question about reliability?
At 10:43 9/11/00 -0500, Tom Lane wrote: Would there be any potential to avoid these (possibly) unnecessary deaths? No, at least it'll never get my vote. Presumably other than limiting to one 'database' per installation? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Philip Warner [EMAIL PROTECTED] writes: I think the hierarchy goes: Environment-Catalog-Schema From what I can tell: 1. the environment contains truly general things like the SQL parser, the tools for connecting to the DB etc - which I assume also contains the user-authorization stuff. In that case it would seem that environment maps to what we call an installation --- if users are environment-wide then that's certainly the natural mapping. Then database maps to catalog and schema is a new feature within databases. 3. Schemas are what we call databases. They contain tables, views wtc. Schemas are *not* databases, because (IIRC) it's possible for a single session to access multiple schemas. regards, tom lane
AW: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
3. Schemas are what we call databases. They contain tables, views wtc. Let us not start this all over again. Our database would correspond to a catalog if we put schemas below our database hierarchy. The standard requires, that you see all schemas within one catalog in one user session. We do not see tables in another database, thus our database is not equivalent to ANSI schemas. The standard also requires, that you can qualify a tablename with a schema, like: "myschema".tabname. This will be the most difficult thing for us. Andreas
Re: [HACKERS] problems with configure
Peter Eisentraut writes: [EMAIL PROTECTED] writes: Depending on the version of Solaris and the compiler flags the third argument can be a pointer to socklen_t, void, size_t or int. The argument is question cannot possibly be of a different width than int, unless someone is *really* on drugs at Sun. Therefore, if the third argument to accept() is "void *" then we just take "int". Evidently there will not be a compiler problem if you pass an "int *" where a "void *" is expected. The fact that int may be signed differently than the actual argument should not be a problem, since evidently the true argument type varies with compiler options, but surely the BSD socket layer does not. Unless there is more than one library that implements accept, or if accept is mapped as a macro to another function. Whatever, I'd be happier if "void *" were mapped to "unsigned int*" as that is what the Solaris 7 library is expecting. But it's no big deal if you want to go with signed. -- Pete Forman -./\.- Disclaimer: This post is originated Western Geophysical -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- the opinion of Baker Hughes or http://www.crosswinds.net/~petef -./\.- its divisions. ***== My old email address [EMAIL PROTECTED] will ==*** ***== not be operational from Fri 10 to Tue 14 Nov 2000.==***
Re: [HACKERS] Question about reliability?
At 10:43 AM 11/9/00 -0500, Tom Lane wrote: Would there be any potential to avoid these (possibly) unnecessary deaths? No, at least it'll never get my vote. Besides, it's not that difficult for an application to recover from these prophylactic backend deaths. My PG driver for AOLserver does so transparently, retrying queries that get the "sorry I've been asked to shut down because some other backend screwed up and died - please retry your query" but returning an error for the actual query that caused a backend to hose itself. The code using the driver is unaware that anything has happened (other than the thread issuing the query that hosed the backend that died in execution, of course). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] Recursive use of syscaches (was: relation ### modified while in use)
"Hiroshi Inoue" [EMAIL PROTECTED] writes: Does this occur after a prior error message? I have been suspicious because there isn't a mechanism to clear the syscache-busy flags during xact abort. I don't know if I've seen the cases you pointed out. I have the following gdb back trace. Obviously it calls SearchSysCache() for cacheId 10 twice. I was able to get another gdb back trace but discarded it by mistake. Though I've added pause() just after detecting recursive use of cache,backends continue the execution in most cases unfortunately. I've not examined the backtrace yet. But don't we have to nail system relation descriptors more than now ? I don't think that's the solution; nailing more descriptors than we absolutely must is not a pretty approach, and I don't think it solves this problem anyway. Your example demonstrates that recursive use of a syscache is perfectly possible when a cache inval message arrives just as we are about to search for a syscache entry. Consider the following path: 1. We are doing index_open and ensuing relcache entry load for some user index. In the middle of this, we need to fetch a not-currently-cached pg_amop entry that is referenced by the index. 2. As we open pg_amop, we receive an SI message for some other user index that is referenced in the current query and so currently has positive refcnt. We therefore attempt to rebuild that index's relcache entry. 3. At this point we have recursive invocation of relcache load, which may well lead to a recursive attempt to fetch the very same pg_amop entry that the outer relcache load is trying to fetch. Therefore, the current error test of checking for re-entrant lookups in the same syscache is bogus. It would still be bogus even if we refined it to notice whether the exact same entry is being sought. On top of that, we have the issue I was concerned about that there is no mechanism for clearing the cache-busy flags during xact abort. Rather than trying to fix this stuff, I propose that we simply remove the test for recursive use of a syscache. AFAICS it will never catch any real bugs in production. It might catch bugs in development (ie, someone messes up the startup sequence in a way that causes a truly circular cache lookup) but I think a stack overflow crash is a perfectly OK result then. regards, tom lane
Re: [HACKERS] initdb failure
"Kevin O'Gorman" [EMAIL PROTECTED] writes: I'm just catching up to the tip of the current tree, and find that I have a reported failure in initdb. initdb works fine for me (as of CVS from about 11:30AM EST today). Try running it with -d or -v or whatever the verbose-output option is to get more info. regards, tom lane
[HACKERS] Re: Recursive use of syscaches (was: relation ### modified while in use)
Tom Lane wrote: "Hiroshi Inoue" [EMAIL PROTECTED] writes: Does this occur after a prior error message? I have been suspicious because there isn't a mechanism to clear the syscache-busy flags during xact abort. I don't know if I've seen the cases you pointed out. I have the following gdb back trace. Obviously it calls SearchSysCache() for cacheId 10 twice. I was able to get another gdb back trace but discarded it by mistake. Though I've added pause() just after detecting recursive use of cache,backends continue the execution in most cases unfortunately. I've not examined the backtrace yet. But don't we have to nail system relation descriptors more than now ? I don't think that's the solution; nailing more descriptors than we absolutely must is not a pretty approach, I don't object to remove the check 'recursive use of cache' because it's not a real check of recursion. My concern is the robustness of rel cache. It seems pretty dangerous to discard system relation descriptors used for cache mechanism especially in case of error recovery. It also seems pretty dangerous to recontruct relation descriptors especially in case of error recovery. Regards. Hiroshi Inoue
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
Alfred Perlstein [EMAIL PROTECTED] writes: I have a program that does a: DECLARE getsitescursor CURSOR FOR select... I ^C'd it and it didn't properly shut down the channel to postgresql and I got this crash: ... These sources are the current CVS sources with the exception of some removed files by Marc. I tried this on my copy of 7.0.3: test7=# begin; declare c cursor for select * from foo; BEGIN SELECT test7=# fetch 1 from c; f1 1 (1 row) [kill -9 on the psql process from another window] test7=# Killed The postmaster log shows pq_recvbuf: unexpected EOF on client connection and no sign of a crash. So there's more to this than just killing a client that has a cursor. Can you provide a more complete example? regards, tom lane
[HACKERS] Tip of current tree: Seg fault in query
Tom asked me to bust it some more 8-)
I've attached the query and the gdb backtrace. This is very
repeatable, so if there's more info needed, let me know.
--
Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED]
Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED]
At school: mailto:[EMAIL PROTECTED]
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html
"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change"
-- Alfred North Whitehead
Script started on Thu Nov 9 17:19:02 2000
[kevin@glynnis OLAP]$ cat bust.sql
-- bust.sql
/* Cause a backend crash */
\qecho Running $Id: bust.sql,v 1.1 2000/11/10 01:12:49 kevin Exp kevin $
/* first, execute this: */
delete from tmprolling
;
EXPLAIN
select
a.product_level,
a.customer_level,
a.channel_level,
199504,
sum (a.dollarsales) / 4 As dollarsales
from
actvars a, prodlevel, custlevel
where
a.time_level in ('199501','199502','199503','199504')
and prodlevel.code_level = a.product_level
and custlevel.store_level = a.customer_level
and prodlevel.Class_level = 'I7BEZZM9YJND'
and custlevel.Retailer_level = 'TBVYYQ1ONYC8'
group by
a.product_level,
a.customer_level,
a.channel_level
;
\! date
insert into tmprolling (
product_level,
customer_level,
channel_level,
time_level,
dollarsales
)
select
a.product_level,
a.customer_level,
a.channel_level,
199504,
sum (a.dollarsales) / 4 As dollarsales
from
actvars a, prodlevel, custlevel
where
a.time_level in ('199501','199502','199503','199504')
and prodlevel.code_level = a.product_level
and custlevel.store_level = a.customer_level
and prodlevel.Class_level = 'I7BEZZM9YJND'
and custlevel.Retailer_level = 'TBVYYQ1ONYC8'
group by
a.product_level,
a.customer_level,
a.channel_level
;
[kevin@glynnis OLAP]$ psql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
kevin=# \i bust.sql
Running $Id: bust.sql,v 1.1 2000/11/10 01:12:49 kevin Exp kevin $
DELETE 0
psql:bust.sql:30: NOTICE: QUERY PLAN:
Group (cost=1379.83..1379.84 rows=1 width=68)
- Sort (cost=1379.83..1379.83 rows=1 width=68)
- Nested Loop (cost=0.00..1379.82 rows=1 width=68)
- Index Scan using ctl2 on custlevel (cost=0.00..2.01 rows=1 width=12)
- Materialize (cost=1377.16..1377.16 rows=52 width=56)
- Nested Loop (cost=0.00..1377.16 rows=52 width=56)
- Index Scan using plf2 on prodlevel (cost=0.00..3.06
rows=2 width=12)
- Index Scan using custindex on actvars a
(cost=0.00..915.85 rows=17 width=44)
EXPLAIN
Thu Nov 9 17:19:58 PST 2000
psql:bust.sql:59: pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:bust.sql:59: connection to server was lost
[kevin@glynnis OLAP]$
Script done on Thu Nov 9 17:20:11 2000
Script started on Thu Nov 9 17:19:32 2000
[root@glynnis bin]# gdb
GNU gdb 4.18
Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i386-redhat-linux".
(gdb) file postgres
Reading symbols from postgres...done.
(gdb) attach 3996
Attaching to program: /PG/pgsql-7.1/bin/postgres, Pid 3996
Reading symbols from /usr/lib/libz.so.1...done.
Reading symbols from /lib/libcrypt.so.1...done.
Reading symbols from /lib/libnsl.so.1...done.
Reading symbols from /lib/libdl.so.2...done.
Reading symbols from /lib/libm.so.6...done.
Reading symbols from /usr/lib/libreadline.so.3...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /usr/lib/libncurses.so.4...done.
Reading symbols from /lib/libc.so.6...done.
Reading symbols from /lib/ld-linux.so.2...done.
Reading symbols from /lib/libnss_files.so.2...done.
0x401a1df2 in __libc_recv () from /lib/libc.so.6
(gdb) c
Continuing.
Program received signal SIGSEGV, Segmentation fault.
attnameAttNum (rd=0x1, a=0x82172a0 "product_level") at parse_relation.c:967
967 for (i = 0; i rd-rd_rel-relnatts; i++)
(gdb) bt
#0 attnameAttNum (rd=0x1, a=0x82172a0 "product_level") at parse_relation.c:967
#1 0x809a818 in checkInsertTargets
Re: [HACKERS] Re: Recursive use of syscaches (was: relation ### modified while in use)
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: My concern is the robustness of rel cache. It seems pretty dangerous to discard system relation descriptors used for cache mechanism especially in case of error recovery. It also seems pretty dangerous to recontruct relation descriptors especially in case of error recovery. Why? We are able to construct all the non-nailed relcache entries from scratch during backend startup. That seems a sufficient proof that we can reconstruct any or all of them on demand. Hmm,why is it sufficent ? At backend startup there are no rel cache except some nailed rels. When 'reset system cache' message arrives,there would be many rel cache entries and some of them may be in use. In addtion there could be some inconsitency of db in the middle of the transaction. Is it safe to recon struct rel cache under the inconsistency ? Regards. Hiroshi Inoue
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
I said: So there's more to this than just killing a client that has a cursor. OK, after digging some more, it seems that the critical requirement is that the cursor's query contain a hash join. I've been able to reproduce a crash here... regards, tom lane
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
On Wednesday 08 November 2000 10:15, Tom Lane wrote: At 14:04 7/11/00 -0500, Jan Wieck wrote: FWIW, what about having another "template0" database, where nobody can add user data. Initially, template0 and template1 are identically. CREATE DATABASE get's a new switch (used by the pg_dump output) that tells to create it from the vanilla template0 DB (generalized, so someone can setup a couple of templaten's) and all objects inherited from template1 (those not in template0) are regularly dumped per database. I like that a lot. Solves the whole problem at a stroke, and even adds some extra functionality (alternate templates). How does this solve the 'ALTER FUNCTION' problem? -- Mark Hollomon
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
I said: OK, after digging some more, it seems that the critical requirement is that the cursor's query contain a hash join. Here's the deal: test7=# set enable_mergejoin to off; SET VARIABLE test7=# begin; BEGIN -- I've previously checked that this produces a hash join plan: test7=# declare c cursor for select * from foo t1, foo t2 where t1.f1=t2.f1; SELECT test7=# fetch 1 from c; f1 | f1 + 1 | 1 (1 row) test7=# abort; NOTICE: trying to delete portal name that does not exist. pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. This happens with either 7.0.2 or 7.0.3 (probably with anything back to 6.5, if not before). It does *not* happen with current development tip. The problem is that two "portal" structures are used. One holds the overall query plan and execution state for the cursor, and the other holds the hash table for the hash join. During abort, the portal manager tries to delete both of them. BUT: deleting the query plan causes query cleanup to be executed, which among other things deletes the hash join's table. Then the portal manager tries to delete the already-deleted second portal, which leads first to the above notice and then to Assert failure (and probably would lead to coredump if you didn't have Asserts on). Alternatively, it might try to delete the hash join portal first, which would leave the query cleanup code deleting an already-deleted portal, and doubtless still crashing. Current sources don't show the problem because hashtables aren't kept in portals anymore. I've thought for some time that CollectNamedPortals is a horrid kluge, and really ought to be rewritten. Hadn't seen it actually do the wrong thing before, but now... I guess the immediate question is do we want to hold up 7.0.3 release for a fix? This bug is clearly ancient, so I'm not sure it's appropriate to go through a fire drill to fix it for 7.0.3. Comments? regards, tom lane
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Mark Hollomon [EMAIL PROTECTED] writes: How does this solve the 'ALTER FUNCTION' problem? What's that got to do with it? regards, tom lane
Re: [HACKERS] Re: Recursive use of syscaches (was: relation ### modified while in use)
Hiroshi Inoue [EMAIL PROTECTED] writes: Tom Lane wrote: Why? We are able to construct all the non-nailed relcache entries from scratch during backend startup. That seems a sufficient proof that we can reconstruct any or all of them on demand. Hmm,why is it sufficent ? At backend startup there are no rel cache except some nailed rels. When 'reset system cache' message arrives,there would be many rel cache entries and some of them may be in use. Doesn't bother me. The ones that are in use will get rebuilt. That might trigger recursive rebuilding of system-table relcache entries, and consequently recursive syscache lookups, but so what? That already happens during backend startup: some relcache entries are loaded as a byproduct of attempts to build other ones. In addtion there could be some inconsitency of db in the middle of the transaction. Is it safe to recon struct rel cache under the inconsistency ? No worse than trying to start up while other transactions are running. We don't support on-the-fly modification of schemas for system catalogs anyway, so I don't see the issue. regards, tom lane
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
At 22:24 9/11/00 -0500, Mark Hollomon wrote: On Wednesday 08 November 2000 10:15, Tom Lane wrote: At 14:04 7/11/00 -0500, Jan Wieck wrote: FWIW, what about having another "template0" database, where nobody can add user data. Initially, template0 and template1 are identically. CREATE DATABASE get's a new switch (used by the pg_dump output) that tells to create it from the vanilla template0 DB (generalized, so someone can setup a couple of templaten's) and all objects inherited from template1 (those not in template0) are regularly dumped per database. I like that a lot. Solves the whole problem at a stroke, and even adds some extra functionality (alternate templates). How does this solve the 'ALTER FUNCTION' problem? I think both this and the OID-wrap problem will be permanent features until we have a non-oid-based dump procedure. Pretty much every piece of metadata needs some kind of 'I am a system object, don't dump me' flag. Relying of values of numeric OIDs is definitely clunky, but it's all we can do at the moment. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
I guess the immediate question is do we want to hold up 7.0.3 release for a fix? This bug is clearly ancient, so I'm not sure it's appropriate to go through a fire drill to fix it for 7.0.3. Comments? We have delayed 7.0.3 already. Tom is fixing so many bugs, we may find at some point that Tom never stops fixing bugs long enough for us to do a release. I say let's push 7.0.3 out. We can always do 7.0.4 later if we wish. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
* Bruce Momjian [EMAIL PROTECTED] [001109 18:55] wrote: I guess the immediate question is do we want to hold up 7.0.3 release for a fix? This bug is clearly ancient, so I'm not sure it's appropriate to go through a fire drill to fix it for 7.0.3. Comments? We have delayed 7.0.3 already. Tom is fixing so many bugs, we may find at some point that Tom never stops fixing bugs long enough for us to do a release. I say let's push 7.0.3 out. We can always do 7.0.4 later if we wish. I think being able to crash the backend by just dropping a connection during a pretty trivial query is a bad thing and it'd be more prudent to wait. I have no problem syncing with your guys CVS, but people using redhat RPMS and FreeBSD Packages are going to wind up with this bug if you cut the release before squashing it. :( -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
On Thu, 9 Nov 2000, Alfred Perlstein wrote: * Bruce Momjian [EMAIL PROTECTED] [001109 18:55] wrote: I guess the immediate question is do we want to hold up 7.0.3 release for a fix? This bug is clearly ancient, so I'm not sure it's appropriate to go through a fire drill to fix it for 7.0.3. Comments? We have delayed 7.0.3 already. Tom is fixing so many bugs, we may find at some point that Tom never stops fixing bugs long enough for us to do a release. I say let's push 7.0.3 out. We can always do 7.0.4 later if we wish. I think being able to crash the backend by just dropping a connection during a pretty trivial query is a bad thing and it'd be more prudent to wait. I have no problem syncing with your guys CVS, but people using redhat RPMS and FreeBSD Packages are going to wind up with this bug if you cut the release before squashing it. :( I'm going to fall behind Alfred on this one ... something this easy to reproduce is a show stopper ... Tom, if you can plug this one in the next, say, 48hrs (Saturday night), please do ... else, I'll announce 7.0.3 on Saturday night and we'll leave it with such a large showstopper :(
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
The Hermit Hacker [EMAIL PROTECTED] writes: Tom, if you can plug this one in the next, say, 48hrs (Saturday night), please do ... else, I'll announce 7.0.3 on Saturday night and we'll leave it with such a large showstopper :( I do have an idea for a simple stopgap answer --- testing now ... regards, tom lane
[HACKERS] Re: Tip of current tree: Seg fault in query
"Kevin O'Gorman" [EMAIL PROTECTED] writes: Program received signal SIGSEGV, Segmentation fault. attnameAttNum (rd=0x1, a=0x82172a0 "product_level") at parse_relation.c:967 967 for (i = 0; i rd-rd_rel-relnatts; i++) (gdb) bt #0 attnameAttNum (rd=0x1, a=0x82172a0 "product_level") at parse_relation.c:967 #1 0x809a818 in checkInsertTargets (pstate=0x8218350, cols=0x82172d0, attrnos=0xb058) at parse_target.c:374 It looks to me like pstate-p_target_relation contains garbage (it seems unlikely that 0x1 is a valid pointer). I changed the contents of struct ParseState recently, so I'm wondering if you haven't just got a problem with inconsistent object files. Does a make clean and rebuild fix it? regards, tom lane
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
The Hermit Hacker [EMAIL PROTECTED] writes: Tom, if you can plug this one in the next, say, 48hrs (Saturday night), Done. Want to generate some new 7.0.3 release-candidate tarballs? regards, tom lane
Re: [HACKERS] Results of testing WAL
"Mikheev, Vadim" [EMAIL PROTECTED] writes: Results: 5000 transactions took ~60 sec in 7.1, ~550 sec in 7.0.2 with fsync and ~60 sec without fsync. So, seems that WAL added not just complexity to system -:) Sounds great! I'm going to commit redo for sequences tomorrow evening and #define XLOG by default after this (initdb will be required). I suggest bumping the catversion.h number when you #define XLOG, so that people won't be able to accidentally start an old postmaster with new DB or vice versa. regards, tom lane
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
On Thu, 9 Nov 2000, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Tom, if you can plug this one in the next, say, 48hrs (Saturday night), Done. Want to generate some new 7.0.3 release-candidate tarballs? Done, and just forced a sync to ftp.postgresql.org of the new tarballs ... if nobody reports any probs with this by ~midnight tomorrow night, I'll finish up the 'release links' and get vince to add release info to the WWW site, followed by putting out an official announcement ... Great work, as always :)
Re: [HACKERS] 7.0.2 dies when connection dropped mid-transaction
* The Hermit Hacker [EMAIL PROTECTED] [001109 20:19] wrote: On Thu, 9 Nov 2000, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Tom, if you can plug this one in the next, say, 48hrs (Saturday night), Done. Want to generate some new 7.0.3 release-candidate tarballs? Done, and just forced a sync to ftp.postgresql.org of the new tarballs ... if nobody reports any probs with this by ~midnight tomorrow night, I'll finish up the 'release links' and get vince to add release info to the WWW site, followed by putting out an official announcement ... Great work, as always :) Tom rules. *thinking freebsd port should add user tgl rather than pgsql* :) -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Jan Wieck wrote: Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Where would you store the value if not in pg_database? No other ideas at the moment. I was just wondering whether there was any way to delete it entirely, but seems like we want to have the value for template0 available. The old way of hardwiring knowledge into pg_dump was definitely not as good. To make pg_dump failsafe, we'd IMHO need to freeze all objects that come with template0 copying. Here's another (somewhat) unhappy thought: what if there are objects in template1 or other databases that one doesn't want to dump or restore? This is very much the case for user-defined types that usually consist of multiple dozens of components. Currently, pg_dump picks them up based on their oid, whether or not they are sitting in template1, and dumps them in a non-restorable and non-portable manner along with the user data. Consequently, I have to write filters to pluck the type code out from the dump. The filters are ugly, unreliable and have to be maintained in sync with the types. Picture this, though: if int and float where user-defined types -- would anyone be happy seeing them in every dump? Or, even worse, responding to "object already exists" kind of problems during restore? Not that I couldn't get by like this; but since everybody seems unhappy too, maybe it's a good moment to consider a special 'dump' attribute for every object in the schema? The attribute could be looked at by dump and restore tools and set by whatever rules one may find appropriate. --Gene
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
At 23:23 9/11/00 -0600, [EMAIL PROTECTED] wrote: Philip Warner wrote: Relying of values of numeric OIDs is definitely clunky, but it's all we can do at the moment. I held that one up, but now I am wondering: would checking a "don't dump me" flag involve any more code or or would it be any more difficult than the current (oid n)? Seems like a straightforward change to me, so what's the reason for this "all we can do" sentiment? The imminent release of 7.1, the fact that I am not totally sold on the idea myself, and the fact that it would require a new attribute on many system tables. It is *a* solution to the problem, but I'd very much like to find a different one if possible. I have also mentioned this on two occasions now, and each has met with total silence. I have come to interpret this to mean either (a) the idea is too stupid to rate a comment, or (b) go ahead with the proposal. Since I am not really proposing anything, I assume the correct interpretation is (a). :-(. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1
Philip Warner [EMAIL PROTECTED] writes: I have also mentioned this on two occasions now, and each has met with total silence. I have come to interpret this to mean either (a) the idea is too stupid to rate a comment, or (b) go ahead with the proposal. More like "oof ..." You're right, it's *a* solution, but it'd involve a lot of tedious work. It's not just adding a column to all the system tables. If I interpret correctly what Mark and Gene are concerned about, it'd also mean changing the code so that any update to a system-table row would automatically clear the "I'm a standard item" flag. That's not just tedious, it's also the sort of thing that will break because someone forgets to do it someplace. I think everyone is keeping quiet until they can think of a better idea... regards, tom lane
Re: [HACKERS] Query caching
Karel Zak wrote: On Wed, 8 Nov 2000, Christof Petig wrote: Karel Zak wrote: What about parameters? Normally you can prepare a statement and execute it We have in PG parameters, see SPI, but now it's used inside backend only and not exist statement that allows to use this feature in be-fe. Sad. Since ecpg would certainly benefit from this. Postponed for future improvements ... PREPARE chris_query AS SELECT * FROM pg_class WHERE relname = $1 USING text; I would prefer '?' as a parameter name, since this is in the embedded sql standard (do you have a copy of the 94 draft? I can mail mine to you?) This not depend on query cache. The '$n' is PostgreSQL query parametr keyword and is defined in standard parser. The PREPARE statement not parsing query it's job for standard parser. I see. Also the standard says a whole lot about guessing the parameter's type. Also I vote for ?::type or type(?) or sql's cast(...) (don't know it's syntax) instead of abusing the using keyword. The postgresql executor expect types of parametrs in separate input (array). I not sure how much expensive/executable is survey it from query. That would involve changing the parser. Future project. EXECUTE chris_query USING 'pg_shadow'; Great idea of yours to implement this! Since I was thinking about implementing a more decent schema for ecpg but had no mind to touch the backend and be-fe protocol (yet). It would be desirable to do an 'execute immediate using', since using input parameters would take a lot of code away from ecpg. By the way, PREPARE/EXECUTE is face only. More interesting in this period is query-cache-kernel. SQL92 is really a little unlike my PREPARE/EXECUTE. I'm looking forward to get first experiences with the query cache kernel. I think it's the right way to go. Christof
Re: [HACKERS] Summary: what to do about INET/CIDR
Peter Eisentraut [EMAIL PROTECTED] writes: Well, we need *some* way to extract a representation like "w.x.y.z/n". If you don't like text() as the name of that formatting function, suggest another name... all_octets(cidr)::text maybe? No, because that doesn't accurately describe what it does for inet items --- those'd be shown with all octets anyway. For inet, the critical thing this function will do is force the netmask to be shown even if it's /32. Given that we are using host() for the function that shows just the IP address part of an inet/cidr value, how about hostandmask() for the function that always shows everything? I still prefer text() though. regards, tom lane
Schemas (Re: AW: [HACKERS] Unhappy thoughts about pg_dump and objects inherited from template1)
Philip Warner writes: I'd be very interested if someone could post the current thinking re: schemas, catalogs, and environments, because the way I read the SQL99 docs, the catalog seems to correspond to a single postgres installation, and a schema seems to correspond to a postgres database (ie. tables and views are defined in a schema, and schemas are defined in a catalog, and catalogs are defined in an environment, and it looks like the environment is akin to the file system/implementation postmaster). The thing you get from initdb is a "cluster of catalogs", a database is a "catalog", a schema is something below a catalog. (There is no such thing as an "environment" as a hierarchy level.) The idea was most likely that a schema would be a purely logical hierarchy but a catalog may be a physical hierarchy. (For example, it is not required that you can access more than one catalog from a connection.) I think all people that were interested in this issue agreed with this. (If not, you better speak up, because I'd like to see schemas implemented ASAP.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] problems with configure
[EMAIL PROTECTED] writes: Depending on the version of Solaris and the compiler flags the third argument can be a pointer to socklen_t, void, size_t or int. I think what I'm going to do is this: The argument is question cannot possibly be of a different width than int, unless someone is *really* on drugs at Sun. Therefore, if the third argument to accept() is "void *" then we just take "int". Evidently there will not be a compiler problem if you pass an "int *" where a "void *" is expected. The fact that int may be signed differently than the actual argument should not be a problem, since evidently the true argument type varies with compiler options, but surely the BSD socket layer does not. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Summary: what to do about INET/CIDR
* Tom Lane [EMAIL PROTECTED] [001109 10:30]: Peter Eisentraut [EMAIL PROTECTED] writes: Well, we need *some* way to extract a representation like "w.x.y.z/n". If you don't like text() as the name of that formatting function, suggest another name... all_octets(cidr)::text maybe? No, because that doesn't accurately describe what it does for inet items --- those'd be shown with all octets anyway. For inet, the critical thing this function will do is force the netmask to be shown even if it's /32. Given that we are using host() for the function that shows just the IP address part of an inet/cidr value, how about hostandmask() for the function that always shows everything? I still prefer text() though. What is the *PHILOSOPHICAL* objection to text() in this case? It's a TEXT output? LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
