Re: [HACKERS] OWNER TO on all objects
I think this is wrong, primarily because it's gonna be seriously incompatible with existing dump files. The existing technique is that each TOC entry says who owns the object. You should use that information and not have to rely on new additions to the file format. This is why GRANT/REVOKE has to be postponed to the end. I think it would be a lot simpler and more reliable if you also postponed ALTER OWNER. OK, implementing this is nasty. How do I collect up all the ACLs from EXISTING custom archives and move them to the end?? This is hard because ACLs are just dependents on their parent object and cannot be sorted on their own to the end of the dump. Since the dumping process outputs to stdout as it goes along, I'd have to create some big in-memory string of all acls and owners collected so far. That seems bad. The alternative is to scan the entire archive twice. On the second scan I would only output owner and acl commands. Another option is to simply not bother fixing old custom dumps. They could just still restore exactly how they would have without any changes from me. I would add new TOC types to the 7.5 pg_dump that could be sorted to the end... What do I do? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] SPI question
I'm describing view using the following SPI function.
All is fine except attrelid is everywhere set to 0. What I'm doing wrong ?
How can I get table oid and column id for every column in SQL result ?
PG_FUNCTION_INFO_V1(check_view);
Datum
check_view(PG_FUNCTION_ARGS)
{
int spiRet=0;
PQExpBuffer queryBuff = createPQExpBuffer();
char *schemaName = GET_STR(PG_GETARG_TEXT_P(0));
char *viewName = GET_STR(PG_GETARG_TEXT_P(1));
int ret = -1;
if (schemaName == NULL)
elog(ERROR, schemaName not set);
if (viewName == NULL)
elog(ERROR, viewName not set);
if ((spiRet = SPI_connect()) 0)
elog(ERROR, rlog: SPI_connect returned %d, spiRet);
printfPQExpBuffer(queryBuff,
SELECT definition
FROM pg_views WHERE schemaname='%s'
AND viewname ='%s';,schemaName,viewName
);
ret = SPI_exec(queryBuff-data,1);
//elog(NOTICE, %s,queryBuff-data);
if (ret == SPI_OK_SELECT){
if ( SPI_processed 0 ){
TupleDesc tupdesc = SPI_tuptable-tupdesc;
printfPQExpBuffer(
queryBuff,
%s,
SPI_getvalue(SPI_tuptable-vals[0],tupdesc,1)
);
}else{
elog(ERROR, Unexisting view %s.%s, schemaName,viewName );
}
}else{
elog(ERROR, Error executing %s, queryBuff-data );
}
//elog(NOTICE, %s, queryBuff-data );
ret = SPI_exec(queryBuff-data,1);
if (ret 0){
elog(ERROR, Error executing %s, queryBuff-data );
}else{
int i=0;
TupleDesc tupdesc = SPI_tuptable-tupdesc;
elog(NOTICE,);
elog(NOTICE,View %s.%s - column count:%
i,schemaName,viewName,tupdesc-natts);
for (i=0; i tupdesc-natts; i++){
elog(NOTICE, colname %s, tupdesc-attrs[i]-attname.data);
elog(NOTICE,-);
elog(NOTICE, attrelid %i,
(int)tupdesc-attrs[i]-attrelid);
elog(NOTICE, atttypid %i, tupdesc-attrs[i]-atttypid);
elog(NOTICE, attlen %i, tupdesc-attrs[i]-attlen);
elog(NOTICE, attnum %i, tupdesc-attrs[i]-attnum);
elog(NOTICE, attstattarget %i,
tupdesc-attrs[i]-attstattarget);
elog(NOTICE, attndims %i, tupdesc-attrs[i]-attndims);
elog(NOTICE, attcacheoff %i,
tupdesc-attrs[i]-attcacheoff);
elog(NOTICE, atttypmod %i, tupdesc-attrs[i]-atttypmod);
elog(NOTICE, attbyval %i, tupdesc-attrs[i]-attbyval);
elog(NOTICE, attstorage %i, tupdesc-attrs[i]-attstorage);
elog(NOTICE, attisset %i, tupdesc-attrs[i]-attisset);
elog(NOTICE, attalign %i, tupdesc-attrs[i]-attalign);
elog(NOTICE, attnotnull %i, tupdesc-attrs[i]-attnotnull);
elog(NOTICE, atthasdef %i, tupdesc-attrs[i]-atthasdef);
elog(NOTICE, attisdropped %i,
tupdesc-attrs[i]-attisdropped);
elog(NOTICE, attislocal %i, tupdesc-attrs[i]-attislocal);
elog(NOTICE, attinhcount %i,
tupdesc-attrs[i]-attinhcount);
}
}
PG_RETURN_BOOL(true);
}
CREATE OR REPLACE FUNCTION check_view (text,text) RETURNS bool
AS '/usr/local/pgsql/lib/libplpq.so','check_view' LANGUAGE 'c'
WITH (isstrict);
SELECT check_view('pg_catalog','pg_tables');
Here is the result (not that attrelid is 0 for all cols):
NOTICE:
NOTICE: View pg_catalog.pg_tables - column count:6
NOTICE: colname schemaname
NOTICE: -
NOTICE: attrelid 0
NOTICE: atttypid 19
NOTICE: attlen 64
NOTICE: attnum 1
NOTICE: attstattarget -1
NOTICE: attndims 0
NOTICE: attcacheoff -1
NOTICE: atttypmod -1
NOTICE: attbyval 0
NOTICE: attstorage 112
NOTICE: attisset 0
NOTICE: attalign 105
NOTICE: attnotnull 0
NOTICE: atthasdef 0
NOTICE: attisdropped 0
NOTICE: attislocal 1
NOTICE: attinhcount 0
NOTICE: colname tablename
NOTICE: -
NOTICE: attrelid 0
NOTICE: atttypid 19
NOTICE: attlen 64
NOTICE: attnum 2
NOTICE: attstattarget -1
NOTICE: attndims 0
NOTICE: attcacheoff -1
NOTICE: atttypmod -1
NOTICE: attbyval 0
NOTICE: attstorage
[HACKERS] Using domains for case insensitivity
Hi all, A while back I asked about creating a case insensitive postgresql. Tom, at the time, suggested I create a case insensitive type instead. I am now trying to go that route, and am wondering whether domains will provide a shortcut for me. As far as I understand the task at hand, I am quite capable of using all of the existing varchar functions for input, output, storage, conversions and so on. The only thing I need to override is the comparison functions (and the resulting index creation, of course). According to the docs, domains are not meant for that purpose, but for changing constraints of a type. Is it possible to define a domain that will have the same defaults and constraints as the base type, but will have different comparison functions? Will that provide me with what I need? Many thanks, Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Using domains for case insensitivity
Shachar Shemesh wrote: According to the docs, domains are not meant for that purpose, but for changing constraints of a type. Is it possible to define a domain that will have the same defaults and constraints as the base type, but will have different comparison functions? Will that provide me with what I need? Domains constrain the allowed values of a data type and nothing more. If you were able to override operators, then you would create a new data type, thus losing a fundamental property of domains. So this is not the route you want to pursue. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] in out send recv functions - immutable or stable?
Hi all, When I look at the int4 functions (int4in, int4out, int4send, int4recv), they are all marked immutable. Then again, when I look at the varchar functions, the in and out functions are immutable, but the send and receive functions are stable. Is there a reason for this? Shachar P.S. This is PG 7.4 on Debian unstable, coming from the standard deb. -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] signal 11 on AIX: 7.4.2
On Mon, May 10, 2004 at 11:59:40AM -0400, Andrew Sullivan wrote: On the weekend, we ran a set of tests on the offending system to see if we could re-create it. We set up the triggering conditions just as they'd been when it happened, and alas, no segfault. So although this was pretty much regularly reproducible when it actually happened, it's now a note to the Journal of Irreproducible Results. I hate when that happens. I hate it even more when the symptom comes back inexplicably. We had it again. For the record, here's what gdb says (there are some high-bit characters in here; dunno how they'll come though in mail): (gdb) bt #0 0xd01d7778 in memmove () from /usr/lib/libc.a(shr.o) #1 0xd0326e1c in getaddrinfo2 () from /usr/lib/libc.a(shr.o) #2 0xd0327b6c in getaddrinfo () from /usr/lib/libc.a(shr.o) #3 0x10058668 in WriteControlFile () at xlog.c:2121 #4 0x101f8f78 in init_execution_state (src=0x202acd8c , argOidVect=0x7308710b, nargs=4, rettype=539520040, haspolyarg=-104 '\230') at functions.c:121 #5 0x101f9304 in init_sql_fcache (finfo=0xdeadbeef) at functions.c:250 #6 0x101fa57c in set_tz (tz=0x7308710b Address 0x7308710b out of bounds) at variable.c:261 #7 0x101fa9a4 in assign_timezone (value=0x202ad398 , doit=-1 'ÿ', interactive=-8 'ø') at variable.c:584 #8 0x1000466c in PostgresMain (argc=1, argv=0x2002cf38, username=0x1 ) at postgres.c:2560 #9 0x100040b0 in PostgresMain (argc=537240896, argv=0xdeadbeef, username=0xdeadbeef Address 0xdeadbeef out of bounds) at postgres.c:2307 #10 0x10002530 in exec_parse_message (query_string=0x2a24 , stmt_name=0x5 , paramTypes=0x0, numParams=0) at postgres.c:1216 #11 0x10001f84 in exec_simple_query ( query_string=0x2005a540 'ÿ' repeats 40 times) at postgres.c:980 #12 0x15f0 in main (argc=1, argv=0xdeadbeef) at main.c:228 -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Casts question
Hi all, I have defined a datatype called varcharci, shamelessly yanking the input, output, recv and send functions from varchar. This means (as far as I understand things) that this type is binary compatible with varchar. As such, I used the following two lines: create cast ( varcharci AS varchar ) WITHOUT FUNCTION AS IMPLICIT; create cast ( varchar AS varcharci ) WITHOUT FUNCTION AS ASSIGNMENT; I defined two tables. Both have a column called name. One is a varchar, and the other is a varcharci. When I try to do the following select, I get an error: test=# select test2.id as id-1, test3.id as id-2, test2.name from test2 inner join test3 on test2.name=test3.name; ERROR: operator does not exist: character varying = varcharci HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. When I add an explicit cast, everything works: sun=# select test2.id as id-1, test3.id as id-2, test2.name from test2 inner join test3 on test2.name=cast(test3.name as varchar); results go here Why is that? Being as it is that no operator = is defined for varcharci, and that the cast from varchar to varcharci is as assignment anyways, shouldn't postgres be able to do the cast implicitly? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Status in 7.5 patches
[EMAIL PROTECTED] wrote: On 16 Jun, Bruce Momjian wrote: With today being June 16th, we are half-way into the one month extension of the feature freeze, now scheduled for July 1. Here is the status on the various outstanding features: Tablespaces - This has been in the queue since June 1 and should have been reviewed and applied by now. We must give this first priority. Would there happen to be a newer patch for tablespaces than tablespace-20.diff.gz? This one has a couple of rejects against today's CVS head. No. We will have to merge those in. I am reviewing the patch right now and will have some comments for Gavin. I think Tom is looking it over too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Status in 7.5 patches
On 16 Jun, Bruce Momjian wrote: With today being June 16th, we are half-way into the one month extension of the feature freeze, now scheduled for July 1. Here is the status on the various outstanding features: Tablespaces - This has been in the queue since June 1 and should have been reviewed and applied by now. We must give this first priority. Would there happen to be a newer patch for tablespaces than tablespace-20.diff.gz? This one has a couple of rejects against today's CVS head. Thanks, Mark ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] signal 11 on AIX: 7.4.2
Andrew Sullivan wrote: On Mon, May 10, 2004 at 11:59:40AM -0400, Andrew Sullivan wrote: On the weekend, we ran a set of tests on the offending system to see if we could re-create it. We set up the triggering conditions just as they'd been when it happened, and alas, no segfault. So although this was pretty much regularly reproducible when it actually happened, it's now a note to the Journal of Irreproducible Results. I hate when that happens. I hate it even more when the symptom comes back inexplicably. We had it again. For the record, here's what gdb says (there are some high-bit characters in here; dunno how they'll come though in mail): (gdb) bt #0 0xd01d7778 in memmove () from /usr/lib/libc.a(shr.o) #1 0xd0326e1c in getaddrinfo2 () from /usr/lib/libc.a(shr.o) #2 0xd0327b6c in getaddrinfo () from /usr/lib/libc.a(shr.o) #3 0x10058668 in WriteControlFile () at xlog.c:2121 #4 0x101f8f78 in init_execution_state (src=0x202acd8c , argOidVect=0x7308710b, nargs=4, rettype=539520040, haspolyarg=-104 '\230') at functions.c:121 #5 0x101f9304 in init_sql_fcache (finfo=0xdeadbeef) at functions.c:250 #6 0x101fa57c in set_tz (tz=0x7308710b Address 0x7308710b out of bounds) at variable.c:261 #7 0x101fa9a4 in assign_timezone (value=0x202ad398 , doit=-1 'ÿ', interactive=-8 'ø') at variable.c:584 #8 0x1000466c in PostgresMain (argc=1, argv=0x2002cf38, username=0x1 ) at postgres.c:2560 #9 0x100040b0 in PostgresMain (argc=537240896, argv=0xdeadbeef, username=0xdeadbeef Address 0xdeadbeef out of bounds) at postgres.c:2307 #10 0x10002530 in exec_parse_message (query_string=0x2a24 , stmt_name=0x5 , paramTypes=0x0, numParams=0) at postgres.c:1216 #11 0x10001f84 in exec_simple_query ( query_string=0x2005a540 'ÿ' repeats 40 times) at postgres.c:980 #12 0x15f0 in main (argc=1, argv=0xdeadbeef) at main.c:228 Well, the bad news is that this backtrace isn't very useful. It states the query you sent was 40 0xff's, and it says you called assign_timezone, which called set_tz, which then shows it calling init_sql_fcache() (impossible), which later calls WriteControlFile() impossible, which calls getaddrinfo() (impossible). My only guess is that getaddrinfo in your libc has a bug somehow that is corrupting the stack (hance the improper backtrace), then crashing. As to the cause, I assume this is not reproducable, right? Is there something unusual about your DNS setup or something that might have changed recently that caused getaddrinfo() to do something new? Of course, the memmove() might be causing the problem and the getaddrinfo is a corrupt part of the backtrace too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] signal 11 on AIX: 7.4.2
On Thu, Jun 17, 2004 at 01:12:10PM -0400, Bruce Momjian wrote: Well, the bad news is that this backtrace isn't very useful. No kidding. It's pretty frustrating. My only guess is that getaddrinfo in your libc has a bug somehow that is corrupting the stack (hance the improper backtrace), then crashing. It could be libc on AIX, I suppose, but it strikes me as sort of odd that nobody else ever seens this. Unless nobody else is using AIX 5.1, which is of course possible. One hypothesis is that this is happening at start up time (this core dump didn't show up in the data/ area, but in the init directory, however, which makes that theory a little suspect). As to the cause, I assume this is not reproducable, right? Is there Well, it's reproduced itsef a few times, but it isn't reproducible at will, and we have no clue what is causing it. something unusual about your DNS setup or something that might have changed recently that caused getaddrinfo() to do something new? Nothing has changed recently, but we started having this not long after promoting an RS/6000 to production on AIX 5.1. Before that we were all-Solaris. We have never managed to tickle this on a test machine. It's pretty tough to guess what might be going on, at least for me. If there are any AIX gurus around, I'd sure like to talk to them. (I do have a budget to pay such gurus, BTW!) Of course, the memmove() might be causing the problem and the getaddrinfo is a corrupt part of the backtrace too. Yeah, which is why it's so frustrating. If I could see what it was doing when it did it, I'd be able to tell. But without knowing why it's happening, there's no way to sit up for 6 weeks while I wait for it to happen. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PITR Recovery
On Wed, 2004-06-16 at 23:50, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Is that something you'd be able to do as a starting point for the other changes? It's easier for a committer to do this, than for me to do it and then another to review it... I'm up to my eyeballs in tablespaces right now, but if you can wait a couple days for this ... Whatever minimises your time...seriously Say the word and I'll do it. Simon ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Status in 7.5 patches
On Thu, 2004-06-17 at 18:00, [EMAIL PROTECTED] wrote: On 16 Jun, Bruce Momjian wrote: With today being June 16th, we are half-way into the one month extension of the feature freeze, now scheduled for July 1. Here is the status on the various outstanding features: Tablespaces - This has been in the queue since June 1 and should have been reviewed and applied by now. We must give this first priority. Would there happen to be a newer patch for tablespaces than tablespace-20.diff.gz? This one has a couple of rejects against today's CVS head. 2 weeks is a long time in Software... It's in the queue and Gavin has written loads of code, Regards, Simon ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] SCO embraces MySQL
[bcc to hackers, general] A match made in heaven... http://news.com.com/2110-7343_3-5236745.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] signal 11 on AIX: 7.4.2
Andrew Sullivan wrote: On Thu, Jun 17, 2004 at 01:12:10PM -0400, Bruce Momjian wrote: Well, the bad news is that this backtrace isn't very useful. No kidding. It's pretty frustrating. My only guess is that getaddrinfo in your libc has a bug somehow that is corrupting the stack (hance the improper backtrace), then crashing. It could be libc on AIX, I suppose, but it strikes me as sort of odd that nobody else ever seens this. Unless nobody else is using AIX 5.1, which is of course possible. One hypothesis is that this is happening at start up time (this core dump didn't show up in the data/ area, but in the init directory, however, which makes that theory a little suspect). When you say init directory, what do you mean? /bin? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
