Re: [PATCHES] datum passed to macro which expects a pointer
Hi all,
Attached are more fixes.
Thanks,
Gavin, with Feng Tian
Index: src/backend/access/common/heaptuple.c
===
RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/access/common/heaptuple.c,v
retrieving revision 1.120
diff -c -p -r1.120 heaptuple.c
*** src/backend/access/common/heaptuple.c 1 Jan 2008 19:45:45 - 1.120
--- src/backend/access/common/heaptuple.c 13 Apr 2008 13:06:53 -
*** heap_form_tuple(TupleDesc tupleDescripto
*** 890,896
else if (att[i]-attlen == -1
att[i]-attalign == 'd'
att[i]-attndims == 0
! !VARATT_IS_EXTENDED(values[i]))
{
values[i] = toast_flatten_tuple_attribute(values[i],
att[i]-atttypid,
--- 890,896
else if (att[i]-attlen == -1
att[i]-attalign == 'd'
att[i]-attndims == 0
! !VARATT_IS_EXTENDED(DatumGetPointer(values[i])))
{
values[i] = toast_flatten_tuple_attribute(values[i],
att[i]-atttypid,
*** heap_formtuple(TupleDesc tupleDescriptor
*** 1001,1007
else if (att[i]-attlen == -1
att[i]-attalign == 'd'
att[i]-attndims == 0
! !VARATT_IS_EXTENDED(values[i]))
{
values[i] = toast_flatten_tuple_attribute(values[i],
att[i]-atttypid,
--- 1001,1007
else if (att[i]-attlen == -1
att[i]-attalign == 'd'
att[i]-attndims == 0
! !VARATT_IS_EXTENDED(DatumGetPointer(values[i])))
{
values[i] = toast_flatten_tuple_attribute(values[i],
att[i]-atttypid,
Index: src/backend/access/common/indextuple.c
===
RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/access/common/indextuple.c,v
retrieving revision 1.85
diff -c -p -r1.85 indextuple.c
*** src/backend/access/common/indextuple.c 1 Jan 2008 19:45:45 - 1.85
--- src/backend/access/common/indextuple.c 13 Apr 2008 18:16:44 -
*** index_form_tuple(TupleDesc tupleDescript
*** 73,79
* If value is stored EXTERNAL, must fetch it so we are not depending
* on outside storage. This should be improved someday.
*/
! if (VARATT_IS_EXTERNAL(values[i]))
{
untoasted_values[i] =
PointerGetDatum(heap_tuple_fetch_attr((struct varlena *)
--- 73,79
* If value is stored EXTERNAL, must fetch it so we are not depending
* on outside storage. This should be improved someday.
*/
! if (VARATT_IS_EXTERNAL(DatumGetPointer(values[i])))
{
untoasted_values[i] =
PointerGetDatum(heap_tuple_fetch_attr((struct varlena *)
*** index_form_tuple(TupleDesc tupleDescript
*** 85,92
* If value is above size target, and is of a compressible datatype,
* try to compress it in-line.
*/
! if (!VARATT_IS_EXTENDED(untoasted_values[i])
! VARSIZE(untoasted_values[i]) TOAST_INDEX_TARGET
(att-attstorage == 'x' || att-attstorage == 'm'))
{
Datum cvalue = toast_compress_datum(untoasted_values[i]);
--- 85,92
* If value is above size target, and is of a compressible datatype,
* try to compress it in-line.
*/
! if (!VARATT_IS_EXTENDED(DatumGetPointer(untoasted_values[i]))
! VARSIZE(DatumGetPointer(untoasted_values[i])) TOAST_INDEX_TARGET
(att-attstorage == 'x' || att-attstorage == 'm'))
{
Datum cvalue = toast_compress_datum(untoasted_values[i]);
Index: src/backend/access/common/printtup.c
===
RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/access/common/printtup.c,v
retrieving revision 1.101
diff -c -p -r1.101 printtup.c
*** src/backend/access/common/printtup.c 1 Jan 2008 19:45:45 - 1.101
--- src/backend/access/common/printtup.c 13 Apr 2008 13:14:52 -
*** printtup(TupleTableSlot *slot, DestRecei
*** 340,346
}
/* Clean up detoasted copy, if any */
! if (attr != origattr)
pfree(DatumGetPointer(attr));
}
--- 340,346
}
/* Clean up detoasted copy, if any */
! if (DatumGetPointer(attr) != DatumGetPointer(origattr))
pfree(DatumGetPointer(attr));
}
*** printtup_20(TupleTableSlot *slot, DestRe
*** 423,429
pfree(outputstr);
/* Clean up detoasted copy, if any */
! if (attr != origattr)
pfree(DatumGetPointer(attr));
}
--- 423,429
pfree(outputstr);
/* Clean up detoasted copy, if any */
! if (DatumGetPointer(attr) != DatumGetPointer(origattr))
pfree(DatumGetPointer(attr));
}
*** debugtup(TupleTableSlot *slot, DestRecei
*** 537,543
pfree(value);
/* Clean up detoasted copy, if any */
! if (attr != origattr)
pfree(DatumGetPointer(attr));
}
printf(\t\n);
--- 537,543
pfree(value);
/* Clean up detoasted copy, if any */
! if (DatumGetPointer(attr) != DatumGetPointer(origattr))
[PATCHES] datum passed to macro which expects a pointer
This may seem a little pedantic but I noticed a few places where we pass a datum to a macro which treats the datum as a pointer. This works now but might not in the future (if, say, Datum were to be 8 bytes). Thanks, Gavin Index: src/backend/utils/adt/varlena.c === RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/utils/adt/varlena.c,v retrieving revision 1.164 diff -c -p -c -r1.164 varlena.c *** src/backend/utils/adt/varlena.c 25 Mar 2008 22:42:44 - 1.164 --- src/backend/utils/adt/varlena.c 12 Apr 2008 21:10:01 - *** text_substring(Datum str, int32 start, i *** 754,760 * If we're working with an untoasted source, no need to do an extra * copying step. */ ! if (VARATT_IS_COMPRESSED(str) || VARATT_IS_EXTERNAL(str)) slice = DatumGetTextPSlice(str, slice_start, slice_size); else slice = (text *) DatumGetPointer(str); --- 754,761 * If we're working with an untoasted source, no need to do an extra * copying step. */ ! if (VARATT_IS_COMPRESSED(DatumGetPointer(str)) || ! VARATT_IS_EXTERNAL(DatumGetPointer(str))) slice = DatumGetTextPSlice(str, slice_start, slice_size); else slice = (text *) DatumGetPointer(str); Index: src/backend/utils/mb/mbutils.c === RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/utils/mb/mbutils.c,v retrieving revision 1.69 diff -c -p -c -r1.69 mbutils.c *** src/backend/utils/mb/mbutils.c 9 Jan 2008 23:43:54 - 1.69 --- src/backend/utils/mb/mbutils.c 12 Apr 2008 21:16:09 - *** pg_convert_to(PG_FUNCTION_ARGS) *** 313,319 result = DirectFunctionCall3(pg_convert, string, src_encoding_name, dest_encoding_name); ! PG_RETURN_BYTEA_P(result); } /* --- 313,319 result = DirectFunctionCall3(pg_convert, string, src_encoding_name, dest_encoding_name); ! PG_RETURN_BYTEA_P(DatumGetPointer(result)); } /* *** pg_convert_from(PG_FUNCTION_ARGS) *** 340,346 * in this case it will be because we've told pg_convert to return one * that is valid as text in the current database encoding. */ ! PG_RETURN_TEXT_P(result); } /* --- 340,346 * in this case it will be because we've told pg_convert to return one * that is valid as text in the current database encoding. */ ! PG_RETURN_TEXT_P(DatumGetPointer(result)); } /* -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] datum passed to macro which expects a pointer
On Sat, Apr 12, 2008 at 06:02:39PM -0400, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: This may seem a little pedantic but I noticed a few places where we pass a datum to a macro which treats the datum as a pointer. This works now but might not in the future (if, say, Datum were to be 8 bytes). Yeah, definitely something to fix. I think though that the cases like this: ! PG_RETURN_TEXT_P(DatumGetPointer(result)); might as well just use PG_RETURN_DATUM instead of casting twice. Oh of course. Updated patch attached. Was this just eyeball inspection or did you find a compiler that would complain about this? I wish. It was actually thrown up when we (Greenplum) changed the macros to be inline functions as part of changing Datum to be 8 bytes. By using inline functions we get proper type checking from the compiler and since we have only a small number of target platforms and architectures, inlining isn't an issue. Thanks, Gavin Index: src/backend/utils/adt/varlena.c === RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/utils/adt/varlena.c,v retrieving revision 1.164 diff -c -p -c -r1.164 varlena.c *** src/backend/utils/adt/varlena.c 25 Mar 2008 22:42:44 - 1.164 --- src/backend/utils/adt/varlena.c 12 Apr 2008 21:10:01 - *** text_substring(Datum str, int32 start, i *** 754,760 * If we're working with an untoasted source, no need to do an extra * copying step. */ ! if (VARATT_IS_COMPRESSED(str) || VARATT_IS_EXTERNAL(str)) slice = DatumGetTextPSlice(str, slice_start, slice_size); else slice = (text *) DatumGetPointer(str); --- 754,761 * If we're working with an untoasted source, no need to do an extra * copying step. */ ! if (VARATT_IS_COMPRESSED(DatumGetPointer(str)) || ! VARATT_IS_EXTERNAL(DatumGetPointer(str))) slice = DatumGetTextPSlice(str, slice_start, slice_size); else slice = (text *) DatumGetPointer(str); Index: src/backend/utils/mb/mbutils.c === RCS file: /Users/swm/pgsql-cvs/pgsql/src/backend/utils/mb/mbutils.c,v retrieving revision 1.69 diff -c -p -c -r1.69 mbutils.c *** src/backend/utils/mb/mbutils.c 9 Jan 2008 23:43:54 - 1.69 --- src/backend/utils/mb/mbutils.c 12 Apr 2008 22:55:49 - *** pg_convert_to(PG_FUNCTION_ARGS) *** 313,319 result = DirectFunctionCall3(pg_convert, string, src_encoding_name, dest_encoding_name); ! PG_RETURN_BYTEA_P(result); } /* --- 313,319 result = DirectFunctionCall3(pg_convert, string, src_encoding_name, dest_encoding_name); ! PG_RETURN_DATUM(result); } /* *** pg_convert_from(PG_FUNCTION_ARGS) *** 340,346 * in this case it will be because we've told pg_convert to return one * that is valid as text in the current database encoding. */ ! PG_RETURN_TEXT_P(result); } /* --- 340,346 * in this case it will be because we've told pg_convert to return one * that is valid as text in the current database encoding. */ ! PG_RETURN_DATUM(result); } /* -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] datum passed to macro which expects a pointer
On Sat, Apr 12, 2008 at 07:07:48PM -0400, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: I wish. It was actually thrown up when we (Greenplum) changed the macros to be inline functions as part of changing Datum to be 8 bytes. Hmmm ... Datum has been 8 bytes for many years, on 64-bit machines. What is it you're trying to accomplish by making it wider on 32-bitters? I miss stated there. This was actually about making key 64 bit types pass by value instead of pass by reference. Thanks, Gavin -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] datum passed to macro which expects a pointer
On Sun, Apr 13, 2008 at 01:42:02AM +0100, Gregory Stark wrote: Gavin Sherry [EMAIL PROTECTED] writes: On Sat, Apr 12, 2008 at 07:07:48PM -0400, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: I wish. It was actually thrown up when we (Greenplum) changed the macros to be inline functions as part of changing Datum to be 8 bytes. Hmmm ... Datum has been 8 bytes for many years, on 64-bit machines. What is it you're trying to accomplish by making it wider on 32-bitters? I miss stated there. This was actually about making key 64 bit types pass by value instead of pass by reference. There was a patch to do this posted recently here as well. http://archives.postgresql.org/pgsql-patches/2008-03/msg00335.php Hm. I suppose it's true that you could make Datum 64-bit even on 32-bit machines and make int8 and float8 pass-by-value. Seems unlikely to be a net win though. A very quick scan showed me that one bet is missed in this patch which we learned about the hard way: write_auth_file() assumes timestamptz is pass by reference. I'm also not sure if endianness is completely covered in the patch but it looks fairly accurate. I think PointerGetDatum() may need the union trick (it's late where I am). There were other places in the code which were assuming Datums were equivalent to pointers. I'll dig them up. Also, it means we can clean up parts of numeric.c which special case calls from aggregates. Seems like a pretty clean patch though. Thanks, Gavin -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Bulk Insert tuning
On Tue, Feb 26, 2008 at 02:43:51PM +, Simon Riggs wrote: Following patch implements a simple mechanism to keep a buffer pinned while we are bulk loading. CK Tan and I worked on something similar but the problem we discovered was self locking. Consider a primary key: we insert a tuple into a buffer and do not release the exclusive lock. The btree code fetches the buffer and tries to share lock it, but we've already exclusive locked it. Oops. The performance improvement, though, makes it worth seeing if there is a solution. Thanks, Gavin -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
[PATCHES] Updated bitmap index patch
Hi all, I've updated the bitmap index patch. It can be found here: http://www.alcove.com.au/~swm/bitmap-2007-03-12.diff This fixes some bugs introduced in the last patch, catches up to HEAD and tidies up the executor code. I want to do a little more tidying, such as reverting the name changes we made. multiscan still sounds alright. I've been thinking about vacuum as well. Something along the lines of what Heikki mentioned earlier -- namely, the ability to iterate the bitmap setbit by setbit is in order. What I have in mind, though, is that when we find a reaped setbit, we mark the position and continue to iterate until we find a non reaped setbit. When, we update the underlying bitmap vector to reflect the non-set bits. Thoughts? Thanks, Gavin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Updated bitmap index patch
Teodor, On Mon, 12 Mar 2007, Teodor Sigaev wrote: I don't very like GiST changes: gistgetbitmap will lock/unlock page for every tuple. It seems to me taht is better to change gistnext function to use some sort callback, for example. Or have static array for tids in gistgetbitmap... Good point. I'll look at it. Thanks Gavin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] - WIP Patch Updatable Cursor
On Wed, 28 Feb 2007, John Bartlett wrote: Hi, A list of ctids is stored in the file. I would have thought these would be stored in memory. If the set got large, you'd use a temporary file the way other systems which overflow to disk do? The file is used to store the ctids during an updatable cursor transaction. It is set up as a permanent file as it has a potential lifetime of preserving data between crashes of the backend. Temporary files tend to be used for data that is defined within a single command. In this case the file needs to exist within a transaction and across backend processes. It does not. Cursors are implicitly closed when a session is closed. A backend crash or system restart closes all open sessions. The file gram.y has been corrected in my version. The files ctidListStore.c and ctidListStore.h were pasted into the patch file, as the diff -N command produced a file of several hundred thousand lines. Edit the file with a text editor. If you know which files should be excluded (like tags files), use diff --exclude=pattern. Thanks, Gavin ---(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: [pgsql-patches] pg_get_domaindef
On Thu, 25 Jan 2007, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: ... convincing use-case that will justify the maintenance load we are setting up for ourselves. Somebody might want this is not adequate. I realize it is problem to have the function in two places in our code, but if we don't make a user-accessible version, every application has to roll their own version and update it for our system catalog changes. Nope, wrong, you are assuming the conclusion. Exactly which apps have to have this? Well, the alternative interfaces like pgadmin and ppa. That said, I prefer the idea of breaking out the queries in pg_dump and psql into a library. Like you say up thread, that's a big project and it's an all or nothing proposition. Thanks, Gavin ---(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: [pgsql-patches] pg_get_domaindef
On Wed, 24 Jan 2007, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: FAST PostgreSQL wrote: Please find attached the patch with modifications are you proposing to implement the other functions in this TODO item (pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_functiondef() ) ? I haven't entirely understood the use case for any of these. It's not pg_dump, for a number of reasons: one being that pg_dump still has to support older backend versions, and another being that every time we let backend SnapshotNow functions get involved, we take another hit to pg_dump's claim to produce a consistent MVCC snapshot. I was talking to AndrewSN on irc about this. He proposed that we supply two versions (yes I hear the collective groan) of the SQL functions: a fast one (SnapshotNow) and an accurate one (which doesn't use SnapshotNow). The accurate version is important not just for pg_dump but for a host of people who interact with the system catalogs. If anyone's wondering why people are interacting with system catalogs in the first place, they need look know further than a monitoring application which checks system health and sanity on a regular basis. Combine that with some of the SnapshotNow based get def functions and common enough DDL (like temp table creation) and you start getting errors which look much more serious than what they are. Implementing the accurate version might be done via SPI. This is a headache though. It's starting to look like pulling the guts out of pg_dump and putting it in a library :-). Maybe the read place for this is actually pgfoundry? Thanks, Gavin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-patches] pg_get_domaindef
On Thu, 25 Jan 2007, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: I was talking to AndrewSN on irc about this. He proposed that we supply two versions (yes I hear the collective groan) of the SQL functions: a fast one (SnapshotNow) and an accurate one (which doesn't use SnapshotNow). Um, that's such a fundamental misconception that it's got to be nipped in the bud. The reason the backend tends to operate on SnapshotNow is Oops. Poor choice of words. Thanks, Gavin ---(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: [pgsql-patches] WIP: splitting EquivalenceClasses out from
I accidentally forgot to copy pgsql-patches earlier... On Fri, 19 Jan 2007, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: Also, some of the equivalence class support code is O(n^2). Yeah, at least :-(. But I find it hard to conceive of real-world queries that would generate more than a few entries per EC; can you? The old code had similar performance issues, and I don't recall seeing any complaints that could be traced to that. I can't think of any real world examples... and the fact that the existing code would have similar performance issues and I'm not aware of anyone bringing a problem up, seems like reason enough to not worry. Thanks, Gavin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Building libpq/psql with Borland BCC5
On Tue, 9 Jan 2007, L Bayuk wrote: The attached patch against PostgreSQL-8.2.1 was discussed on [INTERFACES]. It fixes bcc32.mak makefiles for the Borland BCC compiler to build libpq and psql*. There are also changes to some header files to hide some things BCC doesn't like. *Note: psql compiles with bcc after the patch, but it does not run very well. Define does not run very well :-) Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] SGML index build fix
On Sun, 7 Jan 2007, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: Perhaps even more to the point, what makes you think that someone will notice the warning? If the docs build is one step in an automated build process, this seems unlikely. Taking a closer look, it's pretty much guaranteed that no one will see them, because the targets they are attached to are intermediate, normally followed by latex runs. If we think this is a problem, ISTM the correct answer is to just force a repeat jade run when doing make all. The only objection to that AFAICS is that when you're doing docs work and only need a draft to look at, you'd rather it not run twice. But perhaps we could address that by providing a separate target, make draft say, that runs jade but once. That's a nice approach. Those working on the docs will know about the draft target and those just wanting to build the docs for publication will get the result. Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] On-disk bitmap index implementation
On Wed, 27 Dec 2006, Heikki Linnakangas wrote: Gavin Sherry wrote: There are still some things Jie and I have not gotten to yet: ... o Test WAL replay more thoroughly. Found one WAL related bug: postgres=# CREATE TABLE test (i int); CREATE TABLE postgres=# INSERT INTO test SELECT a FROM generate_series(1,10) a; INSERT 0 10 postgres=# CREATE INDEX mdx ON test USING bitmap(i); CREATE INDEX postgres=# INSERT INTO test VALUES (11); INSERT 0 1 postgres=# \q killall -9 postgres, and restart. Redo fails with: PANIC: bm_insert_redo: LOV item is not inserted in pos 2(requested 12) CONTEXT: xlog redo insert a new LOV item: rel 1663/10817/16388 I haven't dug deeper yet. Yes, there were a bunch of WAL issues we wanted to address. Jie has been working on this too. Thanks for the feedback, we can use this as a test. Thanks, Gavin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] On-disk bitmap index implementation
On Tue, 5 Dec 2006, Heikki Linnakangas wrote: Gavin Sherry wrote: o Improving VACUUM support -- currently, VACUUM FULL means REINDEX for bitmaps. Heikki Linnakangas offered to work on this. Heikki, are you still interested? BTW vacuuming seems quite broken as it is: [EMAIL PROTECTED]:~/pgsql.bitmap$ ~/pgsql.bitmap/bin/psql -a postgres vacuumtest.sql drop table if exists test; DROP TABLE create table test (key int); CREATE TABLE create index test_bm on test using bitmap (key); CREATE INDEX insert into test values (1); INSERT 0 1 delete from test; DELETE 1 vacuum test; VACUUM insert into test values (2); INSERT 0 1 select * from test where key = 1; key - 2 (1 row) Oops :-). Thanks for pointing it out. I think I might have busted something merging with HEAD. Don't you hate that? Thanks, Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] elog(FATAL)ing non-existent roles during client
On Tue, 5 Dec 2006, Gavin Sherry wrote:
On Thu, 30 Nov 2006, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] writes:
I wonder if we should check if the role exists for the other
authentication methods too? get_role_line() should be very cheap and it
would prevent unnecessary authentication work if we did it before
contacting, for example, the client ident server. Even with trust, it
would save work because otherwise we do not check if the user exists until
InitializeSessionUserId(), at which time we're set up our proc entry etc.
This only saves work if the supplied ID is in fact invalid, which one
would surely think isn't the normal case; otherwise it costs more.
Yes.
I could see doing this in the ident path, because contacting a remote
ident server is certainly expensive on both sides. I doubt it's a good
idea in the trust case.
Agreed. How about Kerberos too, applying the same logic?
Attached is a patch check adds the checks.
GavinIndex: src/backend/libpq/auth.c
===
RCS file: /usr/local/cvsroot/pgsql/src/backend/libpq/auth.c,v
retrieving revision 1.146
diff -c -p -r1.146 auth.c
*** src/backend/libpq/auth.c6 Nov 2006 01:27:52 - 1.146
--- src/backend/libpq/auth.c4 Dec 2006 13:47:05 -
*** pg_krb5_recvauth(Port *port)
*** 216,221
--- 217,225
krb5_ticket *ticket;
char *kusername;
+ if (get_role_line(port-user_name) == NULL)
+ return STATUS_ERROR;
+
ret = pg_krb5_init();
if (ret != STATUS_OK)
return ret;
Index: src/backend/libpq/hba.c
===
RCS file: /usr/local/cvsroot/pgsql/src/backend/libpq/hba.c,v
retrieving revision 1.157
diff -c -p -r1.157 hba.c
*** src/backend/libpq/hba.c 5 Nov 2006 22:42:08 - 1.157
--- src/backend/libpq/hba.c 4 Dec 2006 13:47:05 -
*** authident(hbaPort *port)
*** 1589,1594
--- 1589,1597
{
charident_user[IDENT_USERNAME_MAX + 1];
+ if (get_role_line(port-user_name) == NULL)
+ return STATUS_ERROR;
+
switch (port-raddr.addr.ss_family)
{
case AF_INET:
---(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: [PATCHES] On-disk bitmap index implementation
On Mon, 4 Dec 2006, Simon Riggs wrote: On Tue, 2006-12-05 at 00:18 +1100, Gavin Sherry wrote: o Determine if we need to provide anything for rm_startup, rm_cleanup, rm_safe_restartpoint RmgrData function pointers. safe_restartpoint gives true/false based upon whether there are multi-record WAL states that have only been partially received. For example, a btree index split needs multiple WAL records as it recurses up the index tree. If you've got one record but not the others yet you have an incomplete state and so cannot safely write a restartpoint. I'll document that if you/anyone might suggest where the best place is? transam/README ? o Look into adding an AM option such that the user can determine word size at index creation time. For higher-cardinality data (above 1000 distinct values), 16 bit word sizes can really help with performance. Although the word size is not just assumed to be a certain size across the code, macros are used extensively to interact with the word size. Making it different for each index might be a little messy. ...and is is it a typical case to have a bitmap with less than 1000 distinct values?? Surely we want that as the sole assumption? Nearly unique bitmaps can suffer a little I think, if it makes the most common case faster. But I'd like to see the perf results first, I guess. I'll put together some performance data on different word sizes. Thanks, Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] On-disk bitmap index implementation
On Mon, 4 Dec 2006, Heikki Linnakangas wrote: o Test WAL replay more thoroughly. I've had that problem too with a lot of things I've hacked. I've used a shell script that does the operation under test, runs a select, kills and restarts postmaster, and reruns the select. If the select after crash returns the same result as before, presumably WAL code works. But you need to watch out for full page writes that might mask bugs in the redo code. Anyone have a more sophisticated method? Well, I've done a combination of what you did and replaying a bunch of operations using PITR. Thanks, Gavin ---(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: [PATCHES] Latest Bitmap Index patch
On Mon, 18 Sep 2006, Jie Zhang wrote: Hi all, It seems that my previous email to pgsql-hackers about the latest bitmap index patch did not go through. Please find the latest patch in the attachment. Any suggestions and comments are appreciated. This patch is generated against the PostgreSQL CVS HEAD. This patch includes: (Gavin, if I miss anything, please let me know.) (1) Code style changes. (2) Fixed bugs on the multi-column support, the race condition problem if two backends try to insert the same new value (Thank Heikki for reporting this), and some others. (3) Added the stream bitmap implementation. (4) Added performance improvements for creating a bitmap index. (5) Added documentation. Other bugs to do with page layout and WAL recovery. Other TODOs are to add a bitmap specific cost estimate and to take another pass over the code to push things into the usual backend style. Gavin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
On Thu, 3 Aug 2006, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: Docs and regression tests attached. I've applied the regression tests (with a few additions), but I'm feeling dissatisfied with this approach to documenting VALUES. It seems to be mostly missing the point about VALUES being usable whereever SELECT is. I'm not at all sure what I'd do instead though. Should we give VALUES its own reference page? That doesn't quite seem helpful either. cc'ing to pgsql-docs for ideas. Good point. One question: are we happy calling this a 'VALUES list'? It's better than a 'table value constructor'. I took the lead from a comment in the source. Thanks, gavin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Docs and regression tests attached.
One slightly annoying thing is this:
---
regression=# declare foo cursor with hold for VALUES(1,2), (3, 4);
DECLARE CURSOR
regression=# declare foo2 cursor with hold for (VALUES(1,2), (3, 4)) as
foo(i, j);
ERROR: syntax error at or near as
LINE 1: ...e foo2 cursor with hold for (VALUES(1,2), (3, 4)) as foo(i, ...
---
Now, we can just rewrite the second query as:
---
declare foo2 cursor with hold for select * from (VALUES(1,2), (3, 4)) as
foo(i, j);
---
but it's not immediately obvious. Not worth busting up the grammar for it,
though. And, it's not spec.
GavinIndex: doc/src/sgml/ref/declare.sgml
===
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v
retrieving revision 1.37
diff -c -p -r1.37 declare.sgml
*** doc/src/sgml/ref/declare.sgml 26 Feb 2006 03:20:46 - 1.37
--- doc/src/sgml/ref/declare.sgml 3 Aug 2006 04:18:28 -
*** DECLARE liahona CURSOR FOR SELECT * FROM
*** 275,280
--- 275,288
See xref linkend=sql-fetch endterm=sql-fetch-title for more
examples of cursor usage.
/para
+
+ para
+The cursor replaceable class=parameterquery/ clause can also
+be a literalVALUES/ list:
+ programlisting
+ DECLARE cols CURSOR FOR VALUES(1,2), (3,4);
+ /programlisting
+ /para
/refsect1
refsect1
Index: doc/src/sgml/ref/delete.sgml
===
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/delete.sgml,v
retrieving revision 1.26
diff -c -p -r1.26 delete.sgml
*** doc/src/sgml/ref/delete.sgml22 Jan 2006 05:20:33 - 1.26
--- doc/src/sgml/ref/delete.sgml3 Aug 2006 03:26:58 -
*** DELETE FROM [ ONLY ] replaceable class=
*** 117,122
--- 117,128
in the replaceable class=PARAMETERusinglist/replaceable,
unless you wish to set up a self-join.
/para
+
+ para
+ The replaceable class=PARAMETERusinglist/ may also contain a
+ literalVALUES/ list, evaluating to one or more rows. These
+ rows may also be referenced in the literalWHERE/ clause.
+/para
/listitem
/varlistentry
*** DELETE FROM films WHERE kind lt;gt; 'M
*** 191,196
--- 197,213
DELETE FROM films;
/programlisting
/para
+
+ para
+Delete films made after 1990 which are 'Horror' and films made
+after 2000 which are 'Crime'. To do this, we use a literalVALUES/
+list in the literalUSING/ clause.
+ programlisting
+ DELETE FROM films USING (VALUES('1990-01-01, 'Horror'), ('2000-01-01',
'Crime))
+ AS det (year, kind) WHERE films.date_prod = det.year AND
+ films.kind = det.kind;
+ /programlisting
+ /para
/refsect1
refsect1
Index: doc/src/sgml/ref/insert.sgml
===
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v
retrieving revision 1.30
diff -c -p -r1.30 insert.sgml
*** doc/src/sgml/ref/insert.sgml17 Nov 2005 22:14:51 - 1.30
--- doc/src/sgml/ref/insert.sgml2 Aug 2006 22:40:14 -
*** PostgreSQL documentation
*** 21,27
refsynopsisdiv
synopsis
INSERT INTO replaceable class=PARAMETERtable/replaceable [ (
replaceable class=PARAMETERcolumn/replaceable [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { replaceable
class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) | replaceable
class=PARAMETERquery/replaceable }
/synopsis
/refsynopsisdiv
--- 21,27
refsynopsisdiv
synopsis
INSERT INTO replaceable class=PARAMETERtable/replaceable [ (
replaceable class=PARAMETERcolumn/replaceable [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { replaceable
class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ( ... ) ] |
replaceable class=PARAMETERquery/replaceable }
/synopsis
/refsynopsisdiv
*** INSERT INTO replaceable class=PARAMETE
*** 30,37
para
commandINSERT/command inserts new rows into a table.
!One can insert a single row specified by value expressions,
!or several rows as a result of a query.
/para
para
--- 30,37
para
commandINSERT/command inserts new rows into a table.
!One can insert one or more rows specified by value expressions,
!or zero or more rows resulting from a query.
/para
para
*** INSERT INTO films VALUES
*** 162,167
--- 162,177
/para
para
+Insert multiple rows into a table literalfilms/:
+
+ programlisting
+ INSERT INTO films VALUES
+ ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
+ ('HG120', 'The Dinner Game', 140, '1998-10-12', 'Comedy');
+ /programlisting
+ /para
+
+ para
In this example, the literallen/literal column is
omitted and therefore it will have the default value:
Index: doc/src/sgml/ref/select.sgml
[PATCHES] WIP: bitmap indexes (fwd)
I sent the following through earlier but I think the attachment was too large (thought that was limit was greatly increased?). You can download the patch here: http://www.alcove.com.au/~swm/bitmap-2.diff Thanks, Gavin -- Hi all, Attached is an update to the patch implementing bitmap indexes Jie sent last week. This patch tidies up some coding style issues, the system catalogs, adds some basic docs and regression tests, as well as additional functionality. There are still outstanding bugs and problems. These are: a) The planner doesn't really know about bitmaps. The code cheats. As such, bitmap index access is not costed correctly. b) There is, as Tom pointed out, a lot of code duplication around BitmapHeapNext(), MultiExecBitmapIndexScan() and related routines. This needs to be tidied up and would probably benefit from Tom's proposal to change the behaviour of amgetmulti. c) Related to this is the fact that the current on-disk bitmap cannot handle the ScalarArrayOpExpr optimisation that normal bitmap scans can. (The patch introduces some regression tests for bitmaps and one of these fails with an invalid row count. This displays the problem that needs to be solved). d) Also related to this, in() subqueries are causing us to hit some uninitialised memory. I haven't had time to explore this but it is related to the architectural issue above. e) Jie is hunting down a bug in multi-column support. f) I haven't tested concurrency I will continue to send in matches as we we make progress on these issues. Feed back, in particular on (a) and (b), are most welcome. Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom,
Is this intentional:
template1=# values(1), (2);
column1
-
1
2
(2 rows)
This is legal because of:
simple_select:
/* ... */
| values_clause { $$ = $2; }
Also, I am working out some docs and regression tests.
Gavin
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
On Tue, 1 Aug 2006, Joe Conway wrote:
Gavin Sherry wrote:
Is this intentional:
template1=# values(1), (2);
column1
-
1
2
(2 rows)
This is legal because of:
simple_select:
/* ... */
| values_clause { $$ = $2; }
hmm, not sure about that...
Also, I am working out some docs and regression tests.
Oh, cool. I was going to start working on that myself tonight, but if
you're already working on it, don't let me stand in the way ;-)
Actually, if you want me to finish up whatever you have started, I'm
happy to do that too.
I've got to go out but I'll send a complete patch when I get back.
Gavin
---(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
[PATCHES] READ ONLY transaction documentation error
The docs say: When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY TO Actually, COPY FROM is the command we disallow. This must have been overlooked when the read-only error was fixed in the COPY code. Pointed out by Arul Shaji at Fujitsu. Thanks GavinIndex: doc/src/sgml/ref/set_transaction.sgml === --- doc/src/sgml/ref/set_transaction.sgml (revision 15) +++ doc/src/sgml/ref/set_transaction.sgml (working copy) @@ -95,7 +95,7 @@ read/write or read-only. Read/write is the default. When a transaction is read-only, the following SQL commands are disallowed: literalINSERT/literal, literalUPDATE/literal, - literalDELETE/literal, and literalCOPY TO/literal if the + literalDELETE/literal, and literalCOPY FROM/literal if the table they would write to is not a temporary table; all literalCREATE/literal, literalALTER/literal, and literalDROP/literal commands; literalCOMMENT/literal, ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] READ ONLY transaction documentation error
On Sun, 30 Jul 2006, Alvaro Herrera wrote: Gavin Sherry wrote: The docs say: When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY TO Actually, COPY FROM is the command we disallow. This must have been overlooked when the read-only error was fixed in the COPY code. Applied -- sorry I typo'ed your name in the commit message though :-( No problem, Alhero ;-) Thanks, gavin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Proposed patch for sequence-renaming problems
On Wed, 28 Sep 2005, Tom Lane wrote:
I wrote:
The only other thing that's been discussed is the SQL2003 syntax
NEXT VALUE FOR sequencename
but this is in fact just syntactic sugar for something functionally
equivalent to nextval('sequencename'::regclass).
I have to take that back. It's not just syntactic sugar for nextval(),
because the SQL2003 spec says
: If there are multiple instances of next value expressions specifying
: the same sequence generator within a single SQL-statement, all those
: instances return the same value for a given row processed by that
: SQL-statement.
So it's really sort of a magic combination of nextval() and currval().
To meet the spec semantics, we'd need some sort of layer over nextval()
that would keep track of whether a new value should be obtained or not.
I don't think we should use the spec syntax until we're prepared to
meet the spec semantics, so NEXT VALUE FOR as part of the current patch
seems out.
Well, AFAICT, the only part of the spec we cannot implement is what you
quote above. Therefore, why can't we support NEXT VALUE FOR seqname and
reject table creation/alteration which would add more than one reference
to the same sequence. That will allow us to avoid an intermediate step
in getting to the SQL2003 syntax. Having to support three different
sequence incrementation mechanisms for three flavours of PostgreSQL is
going to be a real PITA.
Thanks,
Gavin
---(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: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Attached is a patch adding regression tests for this code. Thanks, Gavin On Tue, 23 Aug 2005, Bruce Momjian wrote: Thanks, modified patch applied by Tom, with the addition of a USER triggers only mode. --- Satoshi Nagayasu wrote: The message format for elog() report is cleaned up. -- NAGAYASU Satoshi [EMAIL PROTECTED] diff -cr pgsql.orig/src/backend/commands/tablecmds.c pgsql/src/backend/commands/tablecmds.c *** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.0 +0900 --- pgsql/src/backend/commands/tablecmds.c 2005-08-08 13:46:44.0 +0900 *** *** 236,241 --- 236,243 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Index: src/test/regress/expected/triggers.out === RCS file: /usr/local/cvsroot/pgsql/src/test/regress/expected/triggers.out,v retrieving revision 1.18 diff -c -p -r1.18 triggers.out *** src/test/regress/expected/triggers.out 13 Oct 2004 01:22:31 - 1.18 --- src/test/regress/expected/triggers.out 25 Aug 2005 01:07:08 - *** SELECT * FROM main_table ORDER BY a, b; *** 322,324 --- 322,388 | (8 rows) + -- Test enable/disable triggers + create table trigtest (i serial primary key); + NOTICE: CREATE TABLE will create implicit sequence trigtest_i_seq for serial column trigtest.i + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index trigtest_pkey for table trigtest + -- test that disabling RI triggers works + create table trigtest2 (i int references trigtest(i) on delete cascade); + create function trigtest() returns trigger as $$ + begin + raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL; + return new; + end;$$ language plpgsql; + create trigger trigtest_b_row_tg before insert or update or delete on trigtest + for each row execute procedure trigtest(); + create trigger trigtest_a_row_tg after insert or update or delete on trigtest + for each row execute procedure trigtest(); + create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest + for each statement execute procedure trigtest(); + create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest + for each statement execute procedure trigtest(); + insert into trigtest default values; + NOTICE: trigtest INSERT BEFORE STATEMENT + NOTICE: trigtest INSERT BEFORE ROW + NOTICE: trigtest INSERT AFTER ROW + NOTICE: trigtest INSERT AFTER STATEMENT + alter table trigtest disable trigger trigtest_b_row_tg; + insert into trigtest default values; + NOTICE: trigtest INSERT BEFORE STATEMENT + NOTICE: trigtest INSERT AFTER ROW + NOTICE: trigtest INSERT AFTER STATEMENT + alter table trigtest disable trigger user; + insert into trigtest default values; + alter table trigtest enable trigger trigtest_a_stmt_tg; + insert into trigtest default values; + NOTICE: trigtest INSERT AFTER STATEMENT + insert into trigtest2 values(1); + insert into trigtest2 values(2); + delete from trigtest where i=2; + NOTICE: trigtest DELETE AFTER STATEMENT + select * from trigtest2; + i + --- + 1 + (1 row) + + alter table trigtest disable trigger all; + delete from trigtest where i=1; + select * from trigtest2; + i + --- + 1 + (1 row) + + -- ensure we still insert, even when all triggers are disabled + insert into trigtest default values; + select * from trigtest; + i + --- + 3 + 4 + 5 + (3 rows) + + drop table trigtest2; + drop table trigtest; Index: src/test/regress/sql/triggers.sql === RCS file: /usr/local/cvsroot/pgsql/src/test/regress/sql/triggers.sql,v retrieving revision 1.8 diff -c -p -r1.8 triggers.sql *** src/test/regress/sql/triggers.sql 21 Nov 2003 22:32:49 - 1.8 --- src/test/regress/sql/triggers.sql 25 Aug 2005 01:04:38 - *** COPY main_table (a, b) FROM stdin; *** 253,255 --- 253,296 \. SELECT * FROM main_table ORDER BY a, b; + + -- Test enable/disable triggers + + create table trigtest (i serial primary key); + -- test that disabling RI triggers works + create table trigtest2 (i int references trigtest(i) on delete cascade); + + create function trigtest() returns trigger as $$ + begin + raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL; + return new; + end;$$ language plpgsql; + + create trigger trigtest_b_row_tg before insert or update or delete on trigtest + for each row execute
Re: [PATCHES] remove BufferBlockPointers for speed and space
On Thu, 11 Aug 2005, Andrew Dunstan wrote: Gavin Sherry wrote: Or more than one hardware architecture (which you didn't even say what you tested...) Well, he tested on SunOS (!) and Linux -- I presume that's two architectures. Sun still calls Solaris SunOs - try doing uname -s on a Solaris box (or look at a buildfarm solaris build info) True. But my previous experience in university environments is that SunOS usually refers to SunOS 2.6 -- and the performance indicates old hardware. The thing is, compilser optimised versions of the test reveal very little difference in performance. This may be because the compiler is very good at optimising sequential annd predictable access to the array. Instead, we should mimic what we see in the real world: random access. Gavin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] remove BufferBlockPointers for speed and space
On Thu, 11 Aug 2005, Qingqing Zhou wrote: It is said that the BufferBlockPointers is used to speedup the BufferGetBlock() macro. I compared three ways of getting block pointers. I.e., existing method (arrary method), calculating block pointer by adding base addr and offset*blockid method (mul method) and optimizing mul method by using bit shift (shift method). All of them calculate the block pointer 8 times (i.e., the BufferBlockPointers array is of size 8), and each take 3 rounds. The result is: SunOS/gcc 3.2 duration round 1 of array method: 4.179 ms duration round 2 of array method: 4.160 ms duration round 3 of array method: 4.143 ms duration round 1 of mul method: 3.311 ms duration round 2 of mul method: 3.233 ms duration round 3 of mul method: 3.233 ms duration round 1 of shift method: 3.554 ms duration round 2 of shift method: 3.235 ms duration round 3 of shift method: 3.233 ms Linux/gcc 3.2 duration round 1 of array method: 0.422 ms duration round 2 of array method: 0.324 ms duration round 3 of array method: 0.354 ms duration round 1 of mul method: 0.271 ms duration round 2 of mul method: 0.248 ms duration round 3 of mul method: 0.304 ms duration round 1 of shift method: 0.322 ms duration round 2 of shift method: 0.239 ms duration round 3 of shift method: 0.265 ms We can conclude that: (1) mul or shift are definitely better than array method; (2) mul and shift are comparable; Do you have results for more recent gcc releases? Thanks, Gavin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
On Fri, 1 Jul 2005, Satoshi Nagayasu wrote:
Hi all,
Here is a first patch to allow these commands.
ALTER TABLE table ENABLE TRIGGER trigname
ALTER TABLE table DISABLE TRIGGER trigname
There are three other areas which are worth looking at:
a) We may defer the execution of some triggers to the end of the
transaction. Do we execute those if a they were later disabled?
b) There is a bug in how we execute triggers. For example, in
ExecDelete():
booldodelete;
dodelete = ExecBRDeleteTriggers(estate, resultRelInfo, tupleid,
estate-es_snapshot-curcid);
if (!dodelete) /* do nothing */
return;
This means that if a before trigger returned NULL, we short circuit and do
not delete the tuple. Consider the following in ExecBRDeleteTriggers()
HeapTuple newtuple = NULL;
...
for (i = 0; i ntrigs; i++)
{
Trigger*trigger = trigdesc-triggers[tgindx[i]];
if (!trigger-tgenabled)
continue;
LocTriggerData.tg_trigtuple = trigtuple;
LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
LocTriggerData.tg_trigger = trigger;
newtuple = ExecCallTriggerFunc(LocTriggerData,
tgindx[i],
relinfo-ri_TrigFunctions,
relinfo-ri_TrigInstrument,
GetPerTupleMemoryContext(estate));
if (newtuple == NULL)
break;
if (newtuple != trigtuple)
heap_freetuple(newtuple);
}
...
return (newtuple == NULL) ? false : true;
This means that if all triggers on a table are disabled, we tell the
caller that a trigger returned NULL and that we should short circuit. This
does not seem to be the case for the other DML statements.
c) There has been a push over previous releases to make dumps generated by
pg_dump look like ANSI SQL. Now, ALTER TABLE ... DISABLE trigger is useful
for pg_dump but not compliant. Others have suggested something like:
SET enable_triggers = off
This would turn all triggers off in the current session. It has the added
benefit that it does not affect other sessions. It does introduce some
issues with permissions -- I wouldn't want users turning off data
validation before triggers in my database, but that's me. I'm not
enamoured of the idea but it is worth discussing, I believe.
Also, a final patch will also need documentation and regression tests :-)
Thanks,
Gavin
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
On Fri, 1 Jul 2005, Satoshi Nagayasu wrote: Hi all, Here is a first patch to allow these commands. ALTER TABLE table ENABLE TRIGGER trigname ALTER TABLE table DISABLE TRIGGER trigname Hmmm.. just thinking about it for a second. I wonder if we should also support: ALTER TABLE DISABLE TRIGGERS which would disable all triggers on the table. We would have a complimentary ENABLE TRIGGERS as well, obviously. The reason I say this is that the common case will be that people are doing a bulk load and want to disable all triggers. However, this will be very useful for debugging interactions between triggers on a table so a user might want to disable only one of many triggers -- as your current grammar does. Perhaps a way of making the grammar a little less ambiguous would be to have the following to disable all triggers: ALTER TABLE table DISABLE TRIGGERS and the following to disable one: ALTER TRIGGER trigger DISABLE Just an idea. Thanks, Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
On Fri, 1 Jul 2005, Satoshi Nagayasu wrote: Hi, Gavin Sherry wrote: Hmmm.. just thinking about it for a second. I wonder if we should also support: ALTER TABLE DISABLE TRIGGERS I found some RDBMSes are supporting 'DISABLE TRIGGER ALL TRIGGERS' command. Does anyone know about the SQL99 spec? The spec says nothing about disabling triggers. Gavin ---(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: [PATCHES] ALTER SCHEMA ... SET TABLESPACE
On Sat, 4 Jun 2005, Bruce Momjian wrote: I am thinking some day we will need: ALTER SCHEMA ... SET NEW TABLESPACE and ALTER SCHEMA ... SET CURRENT TABLESPACE to specify if existing objects are moved, but at this point we aren't going to get the later in 8.1, so I guess we will just go with an unadorned stynax. I must have missed something (likely, as I've been away from a computer for 6 weeks) but didn't we scrap the idea of schemas having a default tablespace? Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Developer's FAQ update
Diff to the HTML version attached. I'm assuming that you have a script to dump the text version, so I haven't sent a diff against the text version. Note that I have made a few more changes since the last diff. Thanks, Gavin--- doc/src/FAQ/FAQ_DEV.html +++ doc/src/FAQ/FAQ_DEV.html @@ -1,9 +1,9 @@ !DOCTYPE html PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META name=generator content=HTML Tidy, see www.w3.org - + meta http-equiv=Content-Type content=text/html; charset=utf-8 TITLEPostgreSQL Developers FAQ/TITLE /HEAD @@ -12,57 +12,63 @@ H1Developer's Frequently Asked Questions (FAQ) for PostgreSQL/H1 -PLast updated: Fri Oct 15 12:26:50 EDT 2004/P +PLast updated: Wed Dec 1 16:11:11 EST 2004/P PCurrent maintainer: Bruce Momjian (A href= -mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/A)BR +mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/A)br / /P PThe most recent version of this document can be viewed at A href= http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html;http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html/A./P HR -BR +br / CENTER H2General Questions/H2 /CENTER - A href=#1.11.1/A) How do I get involved in PostgreSQL -development?BR - A href=#1.21.2/A) How do I add a feature or fix a bug?BR - A href=#1.31.3/A) How do I download/update the current source -tree?BR - A href=#1.41.4/A) How do I test my changes?BR -A href=#1.51.5/A) What tools are available for developers?BR - A href=#1.61.6/A) What books are good for developers?BR - A href=#1.71.7/A) What is configure all about?BR - A href=#1.81.8/A) How do I add a new port?BR - A href=#1.91.9/A) Why don't you use threads/raw + A href=#1.11.1/A) How do I get involved in PostgreSQL + development?br / + a href=#1.21.2/a) What development environment is required to + develop code?br / +a href=#1.31.3/a) What areas need work?br / +a href=#1.41.4/a) What do I do after choosing an item to + work on?br / + a href=#1.51.5/a) Where can I learn more about the code?br / + a href=#1.61.6/a) I've developed a patch, what next?br / + A href=#1.71.7/A) How do I download/update the current source + tree?br / + A href=#1.81.8/A) How do I test my changes?br / + A href=#1.91.9/A) What tools are available for developers?br / + A href=#1.101.10/A) What books are good for developers?br / + A href=#1.111.11/A) What is configure all about?br / + A href=#1.121.12/A) How do I add a new port?br / + A href=#1.131.13/A) Why don't you use threads/raw devices/async-I/O, lt;insert your favorite wizz-bang feature - heregt;?BR - A href=#1.101.10/A) How are RPM's packaged?BR - A href=#1.111.11/A) How are CVS branches handled?BR - A href=#1.121.12/A) Where can I get a copy of the SQL - standards?BR - + heregt;?br / + A href=#1.141.14/A) How are RPM's packaged?br / + A href=#1.151.15/A) How are CVS branches handled?br / + A href=#1.161.16/A) Where can I get a copy of the SQL + standards?br / + a href=#1.171.17/a) Where can I get technical assistance?br / CENTER H2Technical Questions/H2 /CENTER A href=#2.12.1/A) How do I efficiently access information in -tables from the backend code?BR +tables from the backend code?br / A href=#2.22.2/A) Why are table, column, type, function, view names sometimes referenced as IName/I or INameData,/I and -sometimes as Ichar *?/IBR +sometimes as Ichar *?/Ibr / A href=#2.32.3/A) Why do we use INode/I and IList/I to -make data structures?BR +make data structures?br / A href=#2.42.4/A) I just added a field to a structure. What else -should I do?BR +should I do?br / A href=#2.52.5/A) Why do we use Ipalloc/I() and -Ipfree/I() to allocate memory?BR - A href=#2.62.6/A) What is ereport()?BR - A href=#2.72.7/A) What is CommandCounterIncrement()?BR - BR +Ipfree/I() to allocate memory?br / + A href=#2.62.6/A) What is ereport()?br / + A href=#2.72.7/A) What is CommandCounterIncrement()?br / + br / HR @@ -73,171 +79,174 @@ H3A name=1.11.1/A) How go I get involved in PostgreSQL development?/H3 -PThis was written by Lamar Owen:/P + pDownload the code and have a look around. See a href=#1.71.7/a. -P2001-06-22/P + pSubscribe to and read the + a href=http://archives.posrgresql.org/pgsql-hackers;pgsql-hackers/a + mailing list (often termed 'hackers'). This is where the major +contributors and core members of the project discuss development./p -BWhat open source development process is used by the PostgreSQL -team?/B +h3a name=1.21.2/a) What development environment is required + to develop code?/h3 -PRead HACKERS for six months (or a full release cycle, whichever -is longer). Really. HACKERS
Re: [PATCHES] Developer's FAQ update
Oops. I'll have a look at that and all try to enhance the document a little further as well. Gavin On Tue, 30 Nov 2004, Bruce Momjian wrote: Gavin, any chance of getting this patch against the HTML version, rather than the generated text version? It is in doc/src/FAQ/. --- Gavin Sherry wrote: Hi all, An initial look at bring the developer's FAQ up to date and reworking some text to make it more useful. Thanks, Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Give the TODO list a little more verbose explanation
Robert, I think there are some mistakes with your addition. Here's a reworked paragraph: --- This is a list of items which have been put to or discussed by contributors to the project. Many items have been discussed extensively on the mailing lists, the archives of which can be found here: http://archives.postgresql.org. Appearance on this list means that at least one major contributor considered the idea worth further investigation or implementation. As such, an item's appearance on this list does not mean it is being actively developed: look for items with a name following them to see items that have been claimed for active development If you are interested in contributing code for a specific item, first consult the developer's FAQ at http://developer.postgresql.org/readtext.php?src/FAQ/FAQ_DEV.html+Developers-FAQ If an item has brackets [], there is more detail available, so be sure to read that before posting. Items with a hyphen (-) mark changes that have completed and will appear in the 8.1 release. --- Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] Developer's FAQ update
Hi all, An initial look at bring the developer's FAQ up to date and reworking some text to make it more useful. Thanks, Gavin# Old manifest: 2910f7d05f74e086c097595fe8cc78ad728474e4 # New manifest: f4a4fb1871edfc75efcfa80ade602e36463909f0 # Summary of changes: # # patch doc/FAQ_DEV #from 6ea09a083520481b8896b30432a85af841655f31 # to 4b017fc05c35d88807fc61a5d12767ac5838eecb # # patch src/backend/access/transam/xlog.c #from 44af82f3c379aad3d842fc2b903d0211ecdd1e30 # to 08dcb5592d1c8a9501dd6e8845c32b96710b987c # --- doc/FAQ_DEV +++ doc/FAQ_DEV @@ -12,18 +12,18 @@ General Questions 1.1) How do I get involved in PostgreSQL development? - 1.2) How do I add a feature or fix a bug? - 1.3) How do I download/update the current source tree? - 1.4) How do I test my changes? - 1.5) What tools are available for developers? - 1.6) What books are good for developers? - 1.7) What is configure all about? - 1.8) How do I add a new port? - 1.9) Why don't you use threads/raw devices/async-I/O, insert your + 1.2) How do I download/update the current source tree? + 1.3) How do I test my changes? + 1.4) What tools are available for developers? + 1.5) What books are good for developers? + 1.6) What is configure all about? + 1.7) How do I add a new port? + 1.8) Why don't you use threads/raw devices/async-I/O, insert your favorite wizz-bang feature here? - 1.10) How are RPM's packaged? - 1.11) How are CVS branches handled? - 1.12) Where can I get a copy of the SQL standards? + 1.9) How are RPMs packaged? + 1.10) How are CVS branches handled? + 1.11) Where can I get a copy of the SQL standards? + 1.12) Where can I get technical assistance? Technical Questions @@ -42,154 +42,144 @@ 1.1) How go I get involved in PostgreSQL development? - This was written by Lamar Owen: + 1.1.1) Where do I start? + + Download the code and have a look around. See 1.2. - 2001-06-22 - What open source development process is used by the PostgreSQL team? - - Read HACKERS for six months (or a full release cycle, whichever is - longer). Really. HACKERS _is_the process. The process is not well - documented (AFAIK -- it may be somewhere that I am not aware of) -- - and it changes continually. - What development environment (OS, system, compilers, etc) is required + Subscribe to and read the pgsql-hackers mailing list (often termed + 'hackers'). This is where the major contributors and core members of the + project discuss development. + + 1.1.2) What development environment (OS, system, compilers, etc) is required to develop code? - Developers Corner on the website has links to this information. The - distribution tarball itself includes all the extra tools and documents - that go beyond a good Unix-like development environment. In general, a - modern unix with a modern gcc, GNU make or equivalent, autoconf (of a - particular version), and good working knowledge of those tools are - required. - What areas need support? + PostgreSQL is developed mostly in the C programming language. It also + makes use of Yacc and Lex. + + The source code is targeted at most of the popular Unix platforms and + the Windows environment (XP, Windows 2000, and up). + + Most developers make use of the open source development tool chain which + you are probably familiar with if you've compiled or contributed to + open source software before. Developers using this tool chain on Windows + make use of MingW (see http://www.mingw.org/). + + 1.1.3) What areas need work? - The TODO list. + Outstanding features are detailed in the TODO list. This is located in + doc/TODO in the source distribution or at + http://developer.postgresql.org/todo.php. + + You can learn more about these features by consulting the archives, the + SQL standards and the recommend texts (see 1.6). + + 1.1.4) What do I do after choosing an item to work on? + + Send an email to pgsql-hackers with a proposal for what you want to do + (assuming your contribution is not trivial). Working in isolation is not + advisable: others may be working on the same TODO item; you may have + misunderstood the TODO item; your approach may benefit from the review of + others. + + 1.1.5) Where can I learn more about the code? + + Other than documentation in the source tree itself, you can find some + papers/presentations discussing the code at http://developers.postgresql.org + + 1.1.6) I've developed a patch, what next? + + Generate the patch in contextual diff format. If you are unfamiliar with + this, you may find the script src/tools/makediff/difforig useful. + + Ensure that your patch is generated against the most recent version of the + code.
[PATCHES] PITR docs enhancements
Just some docs enhancements based on feedback I received from a few recent
talks on PITR.
Thanks,
Gavin--- doc/src/sgml/backup.sgml
+++ doc/src/sgml/backup.sgml
@@ -379,7 +379,7 @@
/sect1
sect1 id=backup-online
- titleOn-line backup and point-in-time recovery/title
+ titleOn-line backup and point-in-time recovery (PITR)/title
indexterm zone=backup
primaryon-line backup/primary
@@ -389,9 +389,14 @@
primarypoint-in-time recovery/primary
/indexterm
+ indexterm zone=backup
+ primaryPITR/primary
+ /indexterm
+
para
At all times, productnamePostgreSQL/ maintains a firsttermwrite ahead
- log/ (WAL) that shows details of every change made to the database's data
+ log/ (WAL) in the filenamepg_xlog// directory under the cluster data
+ directory. The log shows details of every change made to the database's data
files. This log exists primarily for crash-safety purposes: if the system
crashes, the database can be restored to consistency by quotereplaying/
the log entries made since the last checkpoint. However, the existence
@@ -481,7 +486,8 @@
file is recycled for reuse. Depending on the application and the
available hardware, there could be many different ways of quotesaving
the data somewhere/: we could copy the segment files to an NFS-mounted
-directory on another machine, or write them onto a tape drive, or batch
+directory on another machine, write them onto a tape drive (ensuring that
+you have a way of restoring the file with its original file name), or batch
them together and burn them onto CDs, or something else entirely. To
provide the database administrator with as much flexibility as possible,
productnamePostgreSQL/ tries not to make any assumptions about how
@@ -600,6 +601,14 @@
remember the original full path (literal%p/) but it is necessary to
remember the file name (literal%f/).
/para
+
+ para
+Note that although WAL archiving will allow you to restore any
+modifications made to the data in your productnamePostgreSQL/ database
+it will not restore changes made to configuration files (that is,
+filenamepostgresql.conf/, filenamepg_hba.conf/ and
+filenamepg_ident.conf/) after the initial base backup.
+ /para
/sect2
sect2 id=backup-base-backup
@@ -620,10 +628,16 @@
SELECT pg_start_backup('label');
/programlisting
where literallabel/ is any string you want to use to uniquely
- identify this backup operation. (One good practice is to use the
- full path where you intend to put the backup dump file.) It does
- not matter which database within the cluster you connect to to issue
- this command. You can ignore the result returned by the function;
+ identify this backup operation. functionpg_start_backup/ creates
+ a firsttermbackup label/ file, called filenamebackup_label/,
+ in the cluster directory with information about your backup.
+ One good practice is to use the full path where you intend to put the
+ backup dump file as.
+/para
+
+para
+ It does not matter which database within the cluster you connect to to
+ issue this command. You can ignore the result returned by the function;
but if it reports an error, deal with that before proceeding.
/para
/listitem
@@ -738,13 +744,20 @@
orderedlist
listitem
para
- Stop the postmaster, if it's running, and clean out all existing files
- under the cluster data directory and under the root directories of any
- tablespaces you are using.
- (If there are recent, unarchived WAL segment files in
- filenamepg_xlog// that you want to use during restore, move these
aside
- instead of removing them.)
+ Stop the postmaster, if it's running. If you have the space to do so,
+ copy the cluster data directory and any tablespacecs to a temporary
+ location so that you can reference them later. Note that this will
+ require that you have enough free space on your system to hold two
+ copies of your existing database. If you do not have enough space,
+ you need at the least to backup the filenamepg_xlog/ directory in
+ the cluster data directory as it may contain logs which were not archived
+ before the system went down.
/para
+
+para
+ Next, clean out all existing files under the cluster data directory and
+ under the root directories of any tablespaces you are using.
+/para
/listitem
listitem
para
@@ -766,7 +773,7 @@
/listitem
listitem
para
- If you had unarchived WAL segment files that you saved aside in step 1,
+ If you had unarchived WAL segment files that you saved in step 1,
copy them into filenamepg_xlog//. (It's best to copy them, not move
them back in, so that you still have the unmodified files if the worst
happens and you have to start over.)
@@ -775,9 +775,9 @@
listitem
para
Re: [PATCHES] [HACKERS] Possible make_oidjoins_check Security Issue
On Wed, 3 Nov 2004, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I think Tom's fix adequately addresses the security concerns. Exactly
what is wrong with writing to the current working directory?
Because it could be run from a directory where others have write
permission.
In which case, they could also change the findoidjoins script itself.
I think your fix is *less* secure than what you replaced.
However, I've already wasted more than enough time on this issue...
I'm done arguing about it.
As far as I know, my method is the only secure method. If I am wrong I
would like to know.
I think the problem can really be solved by just removing it from the
distribution. However, one thing I noticed with Bruce's script is that it
does not respect $TMPDIR -- which security conscious admins may be
setting. Solution would be to set TMP=${TMPDIR:-/tmp} before defining the
path to the temporary sub directory.
Thanks,
Gavin
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] dbsize contrib
On Mon, 30 Aug 2004, Andreas Pflug wrote: Gavin Sherry wrote: The attached patch contributes: - database_size(name) - relation_size(text) I sent in a dbsize patch to make these functions tablespace aware... AFAIR your patch was applied, but it misses tables in non-default tablespaces. Ahh. There is a thoughto in my patch. The ifdef should have been HAVE_SYMLINK not SYMLINK. With HAVE_SYMLINK the current code works fine. That being said, your patch seems to clean up the code some whilst adding more functionality. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] ALTER INDEX
On Fri, 20 Aug 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: No, but it is a missing capability many will complain about. I can easily remove it. I saw no one comment when I added it to the patches queue. I hadn't seen you add it to the patches queue ... I did see Gavin's submission but did not yet have time to look at the details. What does it *do* exactly --- simply allow INDEX as a substitute for TABLE in the syntax, or more? I'm not thrilled at the idea of adding a lot of duplicate coding for this. I tried to avoid any duplication. The patch still uses all the ALTER TABLE code. Its just a grammar modification and some setting of completion tags. That being said, I felt obliged to provide at patch when I started hearing noise about ALTER TABLE index name being a bit of a hack -- which it is. Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] ALTER SCHEMA ... SET TABLESPACE
This patch implements ALTER SCHEMA ... SET TABLESPACE.
This changes the default schema tablespace but does not attempted to move
objects within the schema to the new tablespace.
Docs, regression test and tab-completion included.
There were a few places which conflicted with my alter index patch and
I've tried to edit the diff where possible to allow for easy applying.
GavinIndex: doc/src/sgml/ref/alter_schema.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/alter_schema.sgml,v
retrieving revision 1.7
diff -2 -c -r1.7 alter_schema.sgml
*** doc/src/sgml/ref/alter_schema.sgml 25 Jun 2004 21:55:50 - 1.7
--- doc/src/sgml/ref/alter_schema.sgml 15 Aug 2004 10:43:10 -
***
*** 23,26
--- 23,27
ALTER SCHEMA replaceablename/replaceable RENAME TO
replaceablenewname/replaceable
ALTER SCHEMA replaceablename/replaceable OWNER TO
replaceablenewowner/replaceable
+ ALTER SCHEMA replaceablename/replaceable SET TABLESPACE
replaceabletablespace_name/replaceable
/synopsis
/refsynopsisdiv
***
*** 69,73
/listitem
/varlistentry
! /variablelist
/refsect1
--- 70,87
/listitem
/varlistentry
!
!varlistentry
! termreplaceable class=parametertablespace_name/replaceable/term
! listitem
!para
! The name of a new default tablespace for the schema. Tables and indexes
! created underneath this schema which are not explicitly created in
! a different tablespace will be created in this tablespace. Existing
! tables and indexes are not affected: they will remain in their
! existing tablespaces.
!/para
! /listitem
!/varlistentry
! /variablelist
/refsect1
***
*** 87,90
--- 101,105
memberxref linkend=sql-createschema
endterm=sql-createschema-title/member
memberxref linkend=sql-dropschema endterm=sql-dropschema-title/member
+memberxref linkend=sql-createtablespace
endterm=sql-createtablespace-title/member
/simplelist
/refsect1
Index: src/backend/commands/schemacmds.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/schemacmds.c,v
retrieving revision 1.22
diff -2 -c -r1.22 schemacmds.c
*** src/backend/commands/schemacmds.c 2 Aug 2004 01:30:40 - 1.22
--- src/backend/commands/schemacmds.c 15 Aug 2004 10:24:55 -
***
*** 382,383
--- 382,455
heap_close(rel, NoLock);
}
+
+ /*
+ * ALTER SCHEMA
+ *
+ * ALTER SCHEMA [ RENAME | OWNER TO ] are handled seperately
+ * This is currently coded pretty specifically to ALTER SCHEMA SET TABLESPACE
+ * but there's no other use for alter schema on the radar at the moment.
+ */
+
+ void
+ AlterSchema(AlterSchemaStmt *stmt)
+ {
+ HeapTuple tup;
+ Relationrel;
+ char *name = stmt-name;
+ List *cmds = stmt-cmds;
+ ListCell *tcmd;
+ boolnew_tablespace = false;
+
+ rel = heap_openr(NamespaceRelationName, RowExclusiveLock);
+
+ tup = SearchSysCacheCopy(NAMESPACENAME,
+CStringGetDatum(name),
+0, 0, 0);
+ if (!HeapTupleIsValid(tup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+errmsg(schema \%s\ does not exist, name)));
+
+ /* must be owner */
+ if (!pg_namespace_ownercheck(HeapTupleGetOid(tup), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE,
+ name);
+
+ if (!allowSystemTableMods IsReservedName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+errmsg(reserved schema \%s\ cannot be modified,
name)));
+
+
+ foreach(tcmd, cmds)
+ {
+ AlterSchemaCmd *n = lfirst(tcmd);
+
+ if(n-subtype == AS_SetTableSpace)
+ {
+ Oid new_tablespaceid;
+
+ /* Have we parsed this option already? */
+ if(new_tablespace)
+ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(conflicting or redundant options)));
+ new_tablespace = true;
+ new_tablespaceid = get_tablespace_oid(n-name);
+ if(!OidIsValid(new_tablespaceid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+errmsg(tablespace \%s\ does not exist,
n-name)));
+
+
Re: [PATCHES] ALTER INDEX
This patch has a fix for a 'thought-o' in the docs. Gavin Index: doc/src/sgml/ref/alter_index.sgml === RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/alter_index.sgml,v retrieving revision 1.1 diff -2 -c -r1.1 alter_index.sgml *** doc/src/sgml/ref/alter_index.sgml 13 Aug 2004 04:32:27 - 1.1 --- doc/src/sgml/ref/alter_index.sgml 13 Aug 2004 06:35:17 - *** *** 0 --- 1,188 + !-- + $PostgreSQL$ + PostgreSQL documentation + -- + + refentry id=SQL-ALTERINDEX + refmeta + refentrytitle id=sql-alterindex-titleALTER INDEX/refentrytitle + refmiscinfoSQL - Language Statements/refmiscinfo + /refmeta + + refnamediv + refnameALTER INDEX/refname + refpurposechange the definition of an index/refpurpose + /refnamediv + + indexterm zone=sql-alterindex + primaryALTER INDEX/primary + /indexterm + + refsynopsisdiv + synopsis + ALTER INDEX replaceable class=PARAMETERname/replaceable + replaceable class=PARAMETERaction/replaceable [, ... ] + ALTER INDEX replaceable class=PARAMETERname/replaceable + RENAME TO replaceable class=PARAMETERnew_name/replaceable + + where replaceable class=PARAMETERaction/replaceable is one of: + + OWNER TO replaceable class=PARAMETERnew_owner/replaceable + SET INDEXSPACE replaceable class=PARAMETERindexspace_name/replaceable + /synopsis + /refsynopsisdiv + + refsect1 + titleDescription/title + + para +commandALTER INDEX/command changes the definition of an existing index. +There are several subforms: + + variablelist + +varlistentry + termliteralOWNER/literal/term + listitem + para + This form changes the owner of the index to the + specified user. + /para + /listitem +/varlistentry + +varlistentry + termliteralSET TABLESPACE/literal/term + listitem + para + This form changes the index's tablespace to the specified tablespace and + moves the data file(s) associated with the index to the new tablespace. + See also + xref linkend=SQL-CREATETABLESPACE endterm=sql-createtablespace-title. + /para + /listitem +/varlistentry + +varlistentry + termliteralRENAME/literal/term + listitem + para + The literalRENAME/literal forms change the name of the index. + There is no effect on the stored data. + /para + /listitem +/varlistentry + + /variablelist + /para + + para +All the actions except literalRENAME/literal can be combined into +a list of multiple alterations to apply in parallel. + /para + + /refsect1 + + refsect1 + titleParameters/title + + variablelist + + varlistentry + termreplaceable class=PARAMETERname/replaceable/term + listitem +para + The name (possibly schema-qualified) of an existing index to + alter. +/para + /listitem + /varlistentry + + + varlistentry + termreplaceable class=PARAMETERnew_name/replaceable/term + listitem +para + New name for the index. +/para + /listitem + /varlistentry + + + varlistentry + termreplaceable class=PARAMETERnew_owner/replaceable/term + listitem +para + The user name of the new owner of the index. +/para + /listitem + /varlistentry + + varlistentry + termreplaceable class=PARAMETERtablespace_name/replaceable/term + listitem +para + The tablespace name to which the index will be moved. +/para + /listitem + /varlistentry + + /variablelist + /refsect1 + + refsect1 + titleNotes/title + +para + This same operations are supported by literalALTER TABLE/. See also + xref linkend=SQL-ALTERTABLE endterm=SQL-ALTERTABLE-TITLE. +/para + +para + Changing any part of a system catalog index is not permitted. +/para + /refsect1 + + refsect1 + titleExamples/title + para +To rename an existing index: + programlisting + ALTER INDEX distributors RENAME TO suppliers; + /programlisting + /para + + para + To move a index to a different tablespace: + programlisting + ALTER INDEX distributors SET TABLESPACE fasttablespace; + /programlisting + /para + + /refsect1 + + refsect1 + titleCompatibility/title + + para + literalALTER INDEX/ is a PostgreSQL extension. + /para + /refsect1 + /refentry + + !-- Keep this comment at the end of the file + Local variables: + mode: sgml + sgml-omittag:nil + sgml-shorttag:t + sgml-minimize-attributes:nil + sgml-always-quote-attributes:t + sgml-indent-step:1 + sgml-indent-data:t + sgml-parent-document:nil + sgml-default-dtd-file:../reference.ced + sgml-exposed-tags:nil + sgml-local-catalogs:/usr/lib/sgml/catalog + sgml-local-ecat-files:nil + End: + -- Index: src/backend/parser/gram.y
Re: [PATCHES] ALTER INDEX
Oops.
Too much with the ol' cut and paste.
I'm happy to send an updated patch but perhaps the committer, assuming the
patch is accepted, would be kind enough to update for me.
Thanks for reviewing.
Gavin
On Fri, 13 Aug 2004, Stefan Kaltenbrunner wrote:
Gavin Sherry wrote:
Index: src/bin/psql/tab-complete.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.109
diff -2 -c -r1.109 tab-complete.c
*** src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 - 1.109
--- src/bin/psql/tab-complete.c 13 Aug 2004 06:34:55 -
***
*** 633,637
{
static const char *const list_ALTER[] =
! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, NULL};
COMPLETE_WITH_LIST(list_ALTER);
--- 633,638
{
static const char *const list_ALTER[] =
! {DATABASE, GROUP, SCHEMA, TABLE, TRIGGER, USER, INDEX,
!NULL};
COMPLETE_WITH_LIST(list_ALTER);
***
*** 647,650
--- 648,661
COMPLETE_WITH_LIST(list_ALTERDATABASE);
}
+ /* ALTER INDEX name */
+ else if (pg_strcasecmp(prev3_wd, ALTER) == 0
+ pg_strcasecmp(prev2_wd, INDEX) == 0)
+ {
+ static const char *const list_ALTERDATABASE[] =
+ {SET TABLESPACE, OWNER TO, RENAME TO, NULL};
+
+ COMPLETE_WITH_LIST(list_ALTERDATABASE);
minor issue/nit(?): reusing list_ALTERDATABASE for the ALTER INDEX part
looks a little strange ...
Stefan(who could really need some feedback on his own tab-complete patch
*g*)
!DSPAM:411c802d169118747610806!
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] Minor BEFORE DELETE trigger fix
On Thu, 12 Aug 2004, Bruce Momjian wrote: Did this get resolved? --- Gavin Sherry wrote: Attached is a minor patch to make BEFORE DELETE triggers honour tgenabled properly. I know that we cannot, currently, use this feature through a DDL command but just in case someone is updating the catalogs to do it and since it is necessary in order to implement disabling of triggers, I thought I'd send it in. Gavin After taking a proper look, I agree with Tom that my patch was not the proper solution to the problem. What we really need for the BEFORE ROW triggers is the ability to say that there were no triggers executeed. At the moment, if no triggers are executed (ie, they're all disabled), then the executor thinks that a trigger returned NULL. I'll try to find some time to fix this soon. As I noted, though, its not critical because there's no DDL to disable a trigger. Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PATCHES] CREATE DATABASE with tablespace fix
Attached is a patch resolving the issue raised here:
http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackershl=enlr=ie=UTF-8group=comp.databases.postgresql.hackersscoring=dselm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.aurnum=4
When I was testing this, I noticed the following:
template1=# create tablespace blah location '/home/gavins/pgsql/blah';
CREATE TABLESPACE
template1=# create table foo(i int) tablespace blah;
CREATE TABLE
template1=# create database bar tablespace blah;
ERROR: template database template1 is already using tablespace blah
DETAIL: The default tablespace for a database cannot be in use by the
template
database
template1=# drop table foo;
DROP TABLE
template1=# create database bar tablespace blah;
ERROR: template database template1 is already using tablespace blah
DETAIL: The default tablespace for a database cannot be in use by the
template database
This happens because even though we drop the only entry in the tablespace
we keep the empty database directory around.
Should be test if the directory is empty and if so, not copy it (perhaps
only if it is in the tablespace which will be the default tablespace of
the new database?)
GavinIndex: doc/src/sgml/ref/create_database.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/create_database.sgml,v
retrieving revision 1.41
diff -2 -c -r1.41 create_database.sgml
*** doc/src/sgml/ref/create_database.sgml 17 Jul 2004 16:33:31 - 1.41
--- doc/src/sgml/ref/create_database.sgml 8 Aug 2004 08:21:45 -
***
*** 115,118
--- 115,121
para
Specifies the default tablespace for the new database.
+ The tablespace specified must not be in use by the template database.
+ /para
+para
If not specified, the same tablespace that is default for
the template database is used. See
Index: src/backend/commands/dbcommands.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/dbcommands.c,v
retrieving revision 1.139
diff -2 -c -r1.139 dbcommands.c
*** src/backend/commands/dbcommands.c 1 Aug 2004 20:30:48 - 1.139
--- src/backend/commands/dbcommands.c 8 Aug 2004 08:25:41 -
***
*** 266,271
{
char *tablespacename;
! AclResult aclresult;
!
tablespacename = strVal(dtablespacename-arg);
dst_deftablespace = get_tablespace_oid(tablespacename);
--- 266,272
{
char *tablespacename;
! AclResult aclresult;
! char *srcpath;
! struct stat st;
tablespacename = strVal(dtablespacename-arg);
dst_deftablespace = get_tablespace_oid(tablespacename);
***
*** 276,284
tablespacename)));
/* check permissions */
! aclresult = pg_tablespace_aclcheck(dst_deftablespace, GetUserId(),
ACL_CREATE);
! if (aclresult != ACLCHECK_OK)
! aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
!tablespacename);
}
else
--- 277,305
tablespacename)));
/* check permissions */
! aclresult = pg_tablespace_aclcheck(dst_deftablespace, GetUserId(),
ACL_CREATE);
! if (aclresult != ACLCHECK_OK)
! aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
! tablespacename);
!
! /* If we are trying to change the default tablespace of the template,
!* we require that the template not have any files in the new default
!* tablespace. This avoids the need to merge two subdirectories.
!* We can deal with the default tablespace, however.
!*
!* This could probably be improved later.
!*/
!
! if(dst_deftablespace != DEFAULTTABLESPACE_OID)
! {
! srcpath = GetDatabasePath(src_dboid, dst_deftablespace);
!
! if(stat(srcpath, st) == 0 || errno != ENOENT)
! ereport(ERROR,
! (errmsg(template database \%s\ is already
using tablespace \%s\,
! dbtemplate, tablespacename),
! (errdetail(The default tablespace for a
database cannot be in use by the template database;
!
Re: [PATCHES] Minor BEFORE DELETE trigger fix
On Sat, 7 Aug 2004, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] writes:
Attached in the usual format this time.
AFAICS this patch makes exactly zero change in behavior. What was
the point again?
With BEFORE DELETE triggers, if the trigger returns NULL, then the DELETE
will not take place. The following is the existing code:
for (i = 0; i ntrigs; i++)
{
Trigger*trigger = trigdesc-triggers[tgindx[i]];
if (!trigger-tgenabled)
continue;
LocTriggerData.tg_trigtuple = trigtuple;
LocTriggerData.tg_trigger = trigger;
newtuple = ExecCallTriggerFunc(LocTriggerData,
relinfo-ri_TrigFunctions + tgindx[i],
GetPerTupleMemoryContext(estate));
if (newtuple == NULL)
break;
if (newtuple != trigtuple)
heap_freetuple(newtuple);
}
heap_freetuple(trigtuple);
return (newtuple == NULL) ? false : true;
Now, if for all the triggers on the base relation, !trigger-tgenabled is
true, then newtuple will always be NULL.
At the moment, this situation shouldn't come up. But it will when we
support DISABLE trigger. Arul, from Fujitsu, is planning to implement that
for 8.1 so I thought I'd ease the way.
Also, if there is a point, why are we changing only one of the
several ExecFOOTriggers functions?
Because only BEFORE DELETE worries about trigger procedures returning
NULL, from memory.
Thanks,
Gavin
---(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
[PATCHES] dbsize modification to support tablespaces
Attached.
GavinIndex: contrib/dbsize/dbsize.c
===
RCS file: /usr/local/cvsroot/pgsql-server/contrib/dbsize/dbsize.c,v
retrieving revision 1.10
diff -2 -c -r1.10 dbsize.c
*** contrib/dbsize/dbsize.c 23 Feb 2004 23:03:10 - 1.10
--- contrib/dbsize/dbsize.c 6 Aug 2004 11:24:48 -
***
*** 7,11
--- 7,13
#include access/heapam.h
#include catalog/catalog.h
+ #include catalog/catname.h
#include catalog/namespace.h
+ #include catalog/pg_tablespace.h
#include commands/dbcommands.h
#include fmgr.h
***
*** 14,17
--- 16,22
+ static int64
+ get_tablespace_size(Oid dbid, Oid spcid, bool baddirOK);
+
static char *
psnprintf(size_t len, const char *fmt,...)
***
*** 45,52
Oid dbid;
- char *dbpath;
- DIR*dirdesc;
- struct dirent *direntry;
int64 totalsize;
dbid = get_database_oid(NameStr(*dbname));
--- 50,59
Oid dbid;
int64 totalsize;
+ #ifdef SYMLINK
+ Relationdbrel;
+ HeapScanDescscan;
+ HeapTuple tuple;
+ #endif
dbid = get_database_oid(NameStr(*dbname));
***
*** 56,67
errmsg(database \%s\ does not exist, NameStr(*dbname;
! dbpath = GetDatabasePath(dbid);
dirdesc = AllocateDir(dbpath);
if (!dirdesc)
! ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not open directory \%s\: %m,
dbpath)));
!
totalsize = 0;
for (;;)
--- 63,115
errmsg(database \%s\ does not exist, NameStr(*dbname;
! #ifdef SYMLINK
!
! dbrel = heap_openr(TableSpaceRelationName, AccessShareLock);
! scan = heap_beginscan(dbrel, SnapshotNow, 0, (ScanKey) NULL);
!
! totalsize = 0;
!
! while((tuple = heap_getnext(scan, ForwardScanDirection)))
! {
! Oid spcid = HeapTupleGetOid(tuple);
! if(spcid != GLOBALTABLESPACE_OID)
! totalsize += get_tablespace_size(dbid, spcid, true);
! }
! heap_endscan(scan);
! heap_close(dbrel, AccessShareLock);
! #else
! /* Same as always */
! totalsize = get_tablespace_size(dbid, DEFAULTTABLESPACE_OID, false);
! #endif
!
! /*
!* We need to keep in mind that we may not be called from the database
!* whose size we're reporting so, we need to look in every tablespace
!* to see if our database has data in there
!*/
!
! PG_RETURN_INT64(totalsize);
! }
!
! static int64
! get_tablespace_size(Oid dbid, Oid spcid, bool baddirOK)
! {
! char*dbpath;
! DIR *dirdesc;
! struct dirent *direntry;
! int64 totalsize;
!
! dbpath = GetDatabasePath(dbid, spcid);
dirdesc = AllocateDir(dbpath);
if (!dirdesc)
! {
! if(baddirOK)
! return 0;
! else
! ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not open directory \%s\: %m,
dbpath)));
! }
totalsize = 0;
for (;;)
***
*** 88,92
(errcode_for_file_access(),
errmsg(could not stat \%s\: %m,
fullname)));
-
totalsize += statbuf.st_size;
pfree(fullname);
--- 136,139
***
*** 94,102
FreeDir(dirdesc);
!
! PG_RETURN_INT64(totalsize);
}
-
-
/*
--- 141,146
FreeDir(dirdesc);
! return (totalsize);
}
/*
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] Minor savepoint doc addition
Included is an example of using savepoints in a non-trivial example.
Giving examples in the SQL command reference is hard because we don't have
conditionals at the SQL level.
Tom mentioned nested transactions being mentioned in places but I couldn't
find them (with grep). Suggestions?
GavinIndex: doc/src/sgml/ref/begin.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/begin.sgml,v
retrieving revision 1.31
diff -2 -c -r1.31 begin.sgml
*** doc/src/sgml/ref/begin.sgml 1 Aug 2004 17:32:13 - 1.31
--- doc/src/sgml/ref/begin.sgml 6 Aug 2004 11:35:03 -
***
*** 101,104
--- 101,107
Issuing commandBEGIN/ when already inside a transaction block will
provoke a warning message. The state of the transaction is not affected.
+To nest transactions within a transaction block, use savepoints
+(See xref linkend=sql-start-transaction endterm=sql-start-transaction-title
+for more information).
/para
/refsect1
Index: doc/src/sgml/ref/update.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.29
diff -2 -c -r1.29 update.sgml
*** doc/src/sgml/ref/update.sgml9 Jun 2004 19:08:13 - 1.29
--- doc/src/sgml/ref/update.sgml6 Aug 2004 11:54:39 -
***
*** 189,192
--- 189,205
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
/programlisting
+
+Attempt to insert a new stock item along with the quantity of stock. If
+the item exists, update the stock count of the existing item. To do this,
+use savepoints.
+ programlisting
+ BEGIN;
+ SAVEPOINT sp1;
+ INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
+ -- Check for unique violation on name
+ ROLLBACK TO sp1;
+ UPDATE wines SET stock = stock + 24 WHERE winename='Chateau Lafite 2003';
+ COMMIT;
+ /programlisting
/para
/refsect1
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] Minor BEFORE DELETE trigger fix
Oops.
Attached in the usual format this time.
Gavin
On Fri, 6 Aug 2004, Bruce Momjian wrote:
Can I get a context diff please?
---
Gavin Sherry wrote:
Attached is a minor patch to make BEFORE DELETE triggers honour tgenabled
properly.
I know that we cannot, currently, use this feature through a DDL command
but just in case someone is updating the catalogs to do it and since it is
necessary in order to implement disabling of triggers, I thought I'd send
it in.
Gavin
Content-Description:
[ Attachment, skipping... ]
---(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
--
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 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
!DSPAM:41144fb520531574347913!
Index: src/backend/commands/trigger.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/trigger.c,v
retrieving revision 1.166
diff -2 -c -r1.166 trigger.c
*** src/backend/commands/trigger.c 1 Jul 2004 00:50:11 - 1.166
--- src/backend/commands/trigger.c 5 Aug 2004 01:25:46 -
***
*** 1350,1353
--- 1350,1354
TupleTableSlot *newSlot;
int i;
+ boolret;
trigtuple = GetTupleForTrigger(estate, relinfo, tupleid, cid, newSlot);
***
*** 1366,1369
--- 1367,1371
LocTriggerData.tg_relation = relinfo-ri_RelationDesc;
LocTriggerData.tg_newtuple = NULL;
+ ret = true;
for (i = 0; i ntrigs; i++)
{
***
*** 1378,1382
--- 1380,1387
GetPerTupleMemoryContext(estate));
if (newtuple == NULL)
+ {
+ ret = false;
break;
+ }
if (newtuple != trigtuple)
heap_freetuple(newtuple);
***
*** 1384,1388
heap_freetuple(trigtuple);
! return (newtuple == NULL) ? false : true;
}
--- 1389,1393
heap_freetuple(trigtuple);
! return (ret);
}
---(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: [PATCHES] Patch for pg_dump: Multiple -t options and new -T
On Wed, 21 Jul 2004, Christopher Kings-Lynne wrote: No, it doesn't. I can look into that if you like. The patch was entirely to satisfy a need some of our customers have. The -T switch does fill a real need for our customers; our product has a couple of tables that aren't critical if they aren't backed up, but as the product evolves, we occasionally add more tables. So it's easier to use a -T switch to say what *not* to back up, than multiple -t switches to say what to back up. Well, since you wrote the patch, you'd be better off munging it. Read Tom's comments and see what you can come up with. There's been no decision made yet though on what changes to make however. I'd also move the should_dump.c file into an existing file and make sure the patch is against CVS HEAD, not 7.4.3. Also, there's a copyright statement at the top, retaining copyright with the author. Does anyone have an issue with that? Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Include tablespace information in psql \d footers
On Mon, 12 Jul 2004, Bruce Momjian wrote: Bruce Momjian wrote: Patch applied. Thanks. I removed the display of tablespaces for sequences and toast tables: test= \d test Table public.test Column | Type | Modifiers +-+--- x | integer | test= \d test2 Table public.test2 Column | Type | Modifiers +-+--- x | integer | Tablespace: tmp Why is the tablespace name printed on a separate line? OK, I moved the tablespace name up on to the same line just like Has oids: Works for me. Thanks, Gavin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
On Mon, 12 Jul 2004, Christopher Kings-Lynne wrote: I had forgotten that the original patch allowed that. Personally I'd vote for taking it out, for the above-stated reasons --- any objections? I vote for taking it out. Pull it. I added it intentionally but now I'm questioning my reasoning (which was, sequences might be accessed lots, people might want to put them somewhere intentionally). Chris !DSPAM:40f1f2de259842510072165! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] pg_tablespace_databases
On Tue, 6 Jul 2004, Bruce Momjian wrote: Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Joe Conway wrote: 2) This allocation size was a bit ambigous and I think based on a once longer tablespace directory name: This size calculation originated (copy/paste) from commands/tablespace.c, Yeah --- Bruce did not adjust the string length calculations when he editorialized on the directory name. I'd been meaning to go back and make them match. should be clarified there too (and pg_tblspc is hardcoded in strings, could be extracted to a macro definition). [ shrug... ] The name is not going to change again. I have never cared for the practice of writing strlen(foo) as if it were a compile-time constant. But certainly it would be entirely pointless to define such a macro and then use it in only one place. I think with gcc strlen(foo) is a compile-time constant. At least I remember that as a gcc optimization. What do you prefer? sizeof(foo)-1? Certainly +3 is poorly documented, no? You're right about the gcc optimisation: int i = strlen(foo); 8048304: c7 45 fc 03 00 00 00movl $0x3,0xfffc(%ebp) It does look messy thought. Can't this be cleared by a comment? Thanks, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] Docs additions: tablespace examples
Tablespace examples for CREATE TABLE/INDEX/SCHEMA/DATABASE as well as some other examples for CREATE DATABASE. Gavin tablespace_examples.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] Example for create function using argument names
Small addition.Index: doc/src/sgml/ref/create_function.sgml === RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/create_function.sgml,v retrieving revision 1.57 diff -2 -c -r1.57 create_function.sgml *** doc/src/sgml/ref/create_function.sgml 25 Jun 2004 07:05:34 - 1.57 --- doc/src/sgml/ref/create_function.sgml 3 Jul 2004 07:21:16 - *** *** 401,404 --- 401,415 /programlisting /para + + para +Increment an integer, making use of an argument name, in PL/PgSQL: + + programlisting + CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS ' + BEGIN + RETURN i + 1; + END;' LANGUAGE plpgsql; + /programlisting + /para /refsect1 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Hi all,
Attached is an updated ALTER TABLE ... SET TABLESPACE patch.
It uses the block by block copy mechanism proposed by Tom and handles i)
ALTER TABLE index and ii) Copying of TOAST tables and the TOAST
table's index.
It doesn't handle copying of system tables (pg_largeobject) and, in the
interests of code reuse, the patch fiddles with the code used by CLUSTER.
This isn't great but I wanted to get a patch in before 1 July since I
think the feature is very important -- even for the first release.
Thanks,
GavinIndex: doc/src/sgml/ref/alter_table.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.72
diff -2 -c -r1.72 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 2 Jun 2004 21:04:40 - 1.72
--- doc/src/sgml/ref/alter_table.sgml 20 Jun 2004 02:54:30 -
***
*** 44,47
--- 44,48
CLUSTER ON replaceable class=PARAMETERindex_name/replaceable
SET WITHOUT CLUSTER
+ SET TABLESPACE replaceable class=PARAMETERtablespace_name/replaceable
/synopsis
/refsynopsisdiv
***
*** 233,237
/listitem
/varlistentry
!
varlistentry
termliteralRENAME/literal/term
--- 234,250
/listitem
/varlistentry
!
!varlistentry
! termliteralSET TABLESPACE/literal/term
! listitem
! para
! This form changes the table's tablespace to the specified tablespace and
! moves the data file(s) associated with the table to the new tablespace.
! See also
! xref linkend=SQL-CREATETABLESPACE endterm=sql-createtablespace-title.
! /para
! /listitem
!/varlistentry
!
varlistentry
termliteralRENAME/literal/term
***
*** 358,361
--- 371,382
varlistentry
+ termreplaceable class=PARAMETERtablespace_name/replaceable/term
+ listitem
+para
+ The tablespace name to which the table will be moved.
+/para
+ /listitem
+ /varlistentry
+ varlistentry
termliteralCASCADE/literal/term
listitem
***
*** 552,555
--- 573,584
/programlisting
/para
+
+ para
+ To move a table to a different tablespace:
+ programlisting
+ ALTER TABLE distributors SET TABLESPACE fasttablespace;
+ /programlisting
+ /para
+
/refsect1
Index: src/backend/commands/cluster.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/cluster.c,v
retrieving revision 1.126
diff -2 -c -r1.126 cluster.c
*** src/backend/commands/cluster.c 18 Jun 2004 06:13:22 - 1.126
--- src/backend/commands/cluster.c 30 Jun 2004 14:48:06 -
***
*** 506,510
snprintf(NewHeapName, sizeof(NewHeapName), pg_temp_%u, tableOid);
! OIDNewHeap = make_new_heap(tableOid, NewHeapName);
/*
--- 506,512
snprintf(NewHeapName, sizeof(NewHeapName), pg_temp_%u, tableOid);
! OIDNewHeap = make_new_heap(tableOid, NewHeapName,
! OldHeap-rd_rel-reltablespace, allowSystemTableMods,
! true);
/*
***
*** 522,526
/* Swap the relfilenodes of the old and new heaps. */
! swap_relfilenodes(tableOid, OIDNewHeap);
CommandCounterIncrement();
--- 524,528
/* Swap the relfilenodes of the old and new heaps. */
! swap_relfilenodes(tableOid, OIDNewHeap, true);
CommandCounterIncrement();
***
*** 551,555
*/
Oid
! make_new_heap(Oid OIDOldHeap, const char *NewName)
{
TupleDesc OldHeapDesc,
--- 553,558
*/
Oid
! make_new_heap(Oid OIDOldHeap, const char *NewName, Oid newtablespaceId,
! bool allow_system_table_mods, bool create_toast)
{
TupleDesc OldHeapDesc,
***
*** 558,562
RelationOldHeap;
! OldHeap = heap_open(OIDOldHeap, AccessExclusiveLock);
OldHeapDesc = RelationGetDescr(OldHeap);
--- 561,565
RelationOldHeap;
! OldHeap = relation_open(OIDOldHeap, AccessExclusiveLock);
OldHeapDesc = RelationGetDescr(OldHeap);
***
*** 568,580
OIDNewHeap = heap_create_with_catalog(NewName,
!
RelationGetNamespace(OldHeap),
! OldHeap-rd_rel-reltablespace,
!
tupdesc,
!
OldHeap-rd_rel-relkind,
!
OldHeap-rd_rel-relisshared,
!
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
On Thu, 1 Jul 2004, Gavin Sherry wrote: Hi all, Attached is an updated ALTER TABLE ... SET TABLESPACE patch. It uses the block by block copy mechanism proposed by Tom and handles i) ALTER TABLE index and ii) Copying of TOAST tables and the TOAST table's index. It doesn't handle copying of system tables (pg_largeobject) and, in the interests of code reuse, the patch fiddles with the code used by CLUSTER. This isn't great but I wanted to get a patch in before 1 July since I think the feature is very important -- even for the first release. Oh, and it doesn't handle WAL. Tom mentioned/agreed that btree's method of dumping whole pages into WAL would be the best/most efficient way to journaling this and that the btree code should be generalised. I haven't had time to look at this yet. Thanks, Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Include tablespace information in psql \d footers
On Fri, 25 Jun 2004, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] writes:
Attached.
Couple of problems with this:
1. Don't #ifndef WIN32 it. In the first place, we might have tablespace
support on Windows later, and in the second place, even if psql is
running on Windows that doesn't mean the server it's talking to is.
Oops.
2. Printing pg_default when reltblspace is zero is wrong. Get the
database's default tablespace and print that. Or perhaps better,
don't print anything at all; that would avoid cluttering the display
for people who don't use tablespaces.
Ahhh yes of course. Attached is a patch with docs which excludes reporting
of the default tablespace.
GavinIndex: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.116
diff -2 -c -r1.116 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 18 Jun 2004 06:13:05 - 1.116
--- doc/src/sgml/ref/psql-ref.sgml 28 Jun 2004 17:59:31 -
***
*** 785,789
For each relation (table, view, index, or sequence) matching the
replaceable class=parameterpattern/replaceable, show all
! columns, their types, and any special
attributes such as literalNOT NULL/literal or defaults, if
any. Associated indexes, constraints, rules, and triggers are
--- 785,790
For each relation (table, view, index, or sequence) matching the
replaceable class=parameterpattern/replaceable, show all
! columns, their types, the tablespace they reside in (if this differs
! from the database default) and any special
attributes such as literalNOT NULL/literal or defaults, if
any. Associated indexes, constraints, rules, and triggers are
Index: src/bin/psql/common.h
===
RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/common.h,v
retrieving revision 1.35
diff -2 -c -r1.35 common.h
*** src/bin/psql/common.h 19 Apr 2004 17:42:58 - 1.35
--- src/bin/psql/common.h 24 Jun 2004 03:16:02 -
***
*** 21,24
--- 21,27
#endif
+ #define atooid(x) ((Oid) strtoul((x), NULL, 10))
+
+
/*
* Safer versions of some standard C library functions. If an
Index: src/bin/psql/describe.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.99
diff -2 -c -r1.99 describe.c
*** src/bin/psql/describe.c 18 Jun 2004 06:14:04 - 1.99
--- src/bin/psql/describe.c 28 Jun 2004 18:02:08 -
***
*** 40,43
--- 40,46
const char *altnamevar, const char *visibilityrule);
+ static void add_tablespace_footer(char relkind, Oid tablespace,
+ char **footers, int *count, PQExpBufferData buf);
+
/*
* Handlers for various slash commands displaying some sort of list
***
*** 683,686
--- 686,690
boolhasrules;
boolhasoids;
+ Oid tablespace;
} tableinfo;
boolshow_modifiers = false;
***
*** 695,699
/* Get general table info */
printfPQExpBuffer(buf,
!SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids\n
FROM pg_catalog.pg_class WHERE oid = '%s',
oid);
--- 699,704
/* Get general table info */
printfPQExpBuffer(buf,
!SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n
! relhasoids, reltablespace \n
FROM pg_catalog.pg_class WHERE oid = '%s',
oid);
***
*** 718,721
--- 723,727
tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), t) == 0;
tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), t) == 0;
+ tableinfo.tablespace = atooid(PQgetvalue(res, 0, 6));
PQclear(res);
***
*** 869,873
/* Make footers */
! if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
--- 875,893
/* Make footers */
! if(tableinfo.relkind == 'S' || tableinfo.relkind == 't')
! {
! /*
!* We can handle TOAST and sequences together, as the only
!* footer is tablespace
!*/
! int count_footers = 0;
!
! footers = pg_malloc_zero(3 * sizeof(*footers));
!
! add_tablespace_footer(tableinfo.relkind
[PATCHES] Include tablespace information in psql \d footers
Attached.
There is no other way at the moment to get the tablespace name of an
object in psql.
We get information for tables, index, sequences and toast tables.
We should probably do this for schemas too...
GavinIndex: src/bin/psql/common.h
===
RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/common.h,v
retrieving revision 1.35
diff -2 -c -r1.35 common.h
*** src/bin/psql/common.h 19 Apr 2004 17:42:58 - 1.35
--- src/bin/psql/common.h 24 Jun 2004 03:16:02 -
***
*** 21,24
--- 21,27
#endif
+ #define atooid(x) ((Oid) strtoul((x), NULL, 10))
+
+
/*
* Safer versions of some standard C library functions. If an
Index: src/bin/psql/describe.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.99
diff -2 -c -r1.99 describe.c
*** src/bin/psql/describe.c 18 Jun 2004 06:14:04 - 1.99
--- src/bin/psql/describe.c 24 Jun 2004 10:59:32 -
***
*** 40,43
--- 40,46
const char *altnamevar, const char *visibilityrule);
+ static void add_tablespace_footer(char relkind, Oid tablespace,
+ char **footers, int *count, PQExpBufferData buf);
+
/*
* Handlers for various slash commands displaying some sort of list
***
*** 683,686
--- 686,690
boolhasrules;
boolhasoids;
+ Oid tablespace;
} tableinfo;
boolshow_modifiers = false;
***
*** 695,699
/* Get general table info */
printfPQExpBuffer(buf,
!SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids\n
FROM pg_catalog.pg_class WHERE oid = '%s',
oid);
--- 699,704
/* Get general table info */
printfPQExpBuffer(buf,
!SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n
! relhasoids, reltablespace \n
FROM pg_catalog.pg_class WHERE oid = '%s',
oid);
***
*** 718,721
--- 723,727
tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), t) == 0;
tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), t) == 0;
+ tableinfo.tablespace = atooid(PQgetvalue(res, 0, 6));
PQclear(res);
***
*** 869,873
/* Make footers */
! if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
--- 875,895
/* Make footers */
! if(tableinfo.relkind == 'S' || tableinfo.relkind == 't')
! {
! /*
!* We can handle TOAST and sequences together, as the only
!* footer is tablespace
!*/
! int count_footers = 0;
!
! footers = pg_malloc_zero(3 * sizeof(*footers));
!
! #ifndef WIN32
! add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
! footers[0], count_footers, buf);
! #endif
! footers[count_footers] = NULL;
! }
! else if (tableinfo.relkind == 'i')
{
/* Footer information about an index */
***
*** 898,901
--- 920,924
char *indtable = PQgetvalue(result, 0, 4);
char *indpred = PQgetvalue(result, 0, 5);
+ int count_footers = 0;
if (strcmp(indisprimary, t) == 0)
***
*** 917,923
appendPQExpBuffer(tmpbuf, _(, CLUSTER));
! footers = pg_malloc_zero(2 * sizeof(*footers));
! footers[0] = pg_strdup(tmpbuf.data);
! footers[1] = NULL;
}
--- 940,951
appendPQExpBuffer(tmpbuf, _(, CLUSTER));
! footers = pg_malloc_zero(4 * sizeof(*footers));
! footers[count_footers++] = pg_strdup(tmpbuf.data);
! #ifndef WIN32
! add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
! footers, count_footers, tmpbuf);
! #endif
! footers[count_footers] = NULL;
!
}
***
*** 1104,1108
inherits_count = PQntuples(result6);
! footers = pg_malloc_zero((index_count + check_count + rule_count +
trigger_count + foreignkey_count + inherits_count + 6)
*
[PATCHES] make_ctags change
Exuberant ctags adds data at the beginning of the tags file. It also sorts the tags file by default. This means, if we sort the file generated by Exuberant ctags, it doesn't work in VIM and possibly other editors. I didn't notice this until recently because I was using my own script. Its possible that this only affects recent version of Exuberant ctags though I tested a version from 2001 which exhibited the same behaviour. GavinIndex: src/tools/make_ctags === RCS file: /usr/local/cvsroot/pgsql-server/src/tools/make_ctags,v retrieving revision 1.9 diff -2 -c -r1.9 make_ctags *** src/tools/make_ctags2 Feb 2004 17:29:32 - 1.9 --- src/tools/make_ctags23 Jun 2004 08:25:01 - *** *** 3,9 rm -f ./tags ! if ctags --version 21 | grep Exuberant /dev/null ! then FLAGS=--c-types=+dfmstuv ! else FLAGS=-dt fi --- 3,11 rm -f ./tags ! cv=`ctags --version 21 | grep Exuberant` ! ! if [ -z $cv ] ! then FLAGS=-dt ! else FLAGS=--c-types=+dfmstuv fi *** *** 11,17 -type f -name '*.[chyl]' -print|xargs ctags $FLAGS -a -f tags ! LC_ALL=C ! export LC_ALL ! sort tags /tmp/$$ mv /tmp/$$ tags find . -name 'CVS' -prune -o -type d -print |while read DIR --- 13,22 -type f -name '*.[chyl]' -print|xargs ctags $FLAGS -a -f tags ! if [ -z $cv ] ! then ! LC_ALL=C ! export LC_ALL ! sort tags /tmp/$$ mv /tmp/$$ tags ! fi find . -name 'CVS' -prune -o -type d -print |while read DIR ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Tablespaces
On Sun, 20 Jun 2004, Simon Riggs wrote: On Thu, 2004-05-27 at 07:59, Gavin Sherry wrote: Attached is my latest patch implementing tablespaces. This has all the functionality I was planning for 7.5. Most of the information about the patch is contained in the patch/documentation, previous submissions and the archives. Testing, review, comments would be greatly appreciated. I've reviewed your patch by eye, but can't see anything in your patch about relocating the pg_xlog directory. I didn't intend on looking at that in this patch. pg_xlog is only referred to in 4 lines in the code (incl. PITR patch): - xlog.c - pgarch.c (PITR patch) - initdb.c - pgresetxlog.c Each time it is simply setting a string to the location of the xlog directory. If we could work out a way of... i) letting the pg_xlog be created by default ii) then transferring this to another tablespace later? That would give us maximum flexibility, since you may wish to change location later when workload changes/increases. Sounds reasonable. Perhaps adding a GUC...for wal_tablespace (pls suggest name!) defaults to the pg_xlog directory, when not listed? Changeable only at postmaster startup... This could be done independently of tablespaces, but I think any directory flexibility/change should work using the tablespace infrastructure, not in addition to it. If the change is as simple as you propose, it has nothing to do with the tablespace code. Also, I don't see any situation where we would want to make use of the tablespace code. Could we discuss this? it sounds like a change we could make happen fairly quickly now your code is in place. Again, I don't think my code really has any affect on the location of xlog. Of course, I accept that many may say that such a change is not really needed, but then... Comments anyone? Gavin ---(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
[PATCHES] Admin guide tablespace docs
Attached. Thanks, Gavin tablespace-docs.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] ALTER TABLE ... SET TABLESPACE
Attached is a patch implementing this functionality. I've modified make_new_heap() as well as swap_relfilenodes() to not assume that tablespaces remain the same from old to new heap. I thought it better to go down this road than introduce a lot of duplicate code. Thanks Gavin altertable.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace patch review
On Fri, 18 Jun 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, I have reviewed the patch. I think Tom is doing the same, but I want to report the things I found. I just came up for air after about two solid days of working on this patch ... had not seen your message before committing it. The good news is that I think I did see all the stuff you found. Awesome. What facility is there for moving objects between tablespaces? None, as yet. Seems we should be consistent in having WIN32 defs or not. Probably. I removed #ifdefs whereever possible --- there are just a few left in tablespace.c and dbcommands.c now. I was contemplating replacing HAVE_SYMLINKS with a HAVE_TABLESPACES flag, but with the occurrences isolated to one file I'm not sure it's worth the trouble. Your code in tablespc.c calls realpath(). Do all OS's have that? It doesn't anymore --- I was concerned about the portability question too. The only point of that code AFAICS was to prevent creation of two pg_tablespace entries pointing at the same directory. I felt that the better way to handle this was to write a PG_VERSION file in the tablespace directory during CREATE TABLESPACE. A subsequent CREATE TABLESPACE on the same directory will fail because the directory isn't empty anymore. And the version file might come in handy someday... Yes. That's a better idea. [snip] Where do we need to add mention of tablespaces in the main non-reference-page docs? Clearly at least in the section on managing disk space. Yeah. The patch as committed covers the reference pages, but we desperately need a higher-level discussion of tablespaces for the administrator's guide. I'll look at this tomorrow. Thanks for your assistance. regards, tom lane Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
On Fri, 18 Jun 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Are we ripping out our initlocation code at the same time? Not done yet, but it's dead and should be removed as soon as a decent respect for the deceased permits ;-) You want me to do the honors? Nah, I'll get it. I want to do some other small cleanup on that patch, too. (But Gavin, you're on the hook for a rewrite of the admin guide section about alternate locations into something about tablespaces...) I can either replace the Alternative Locations section or make a higher level reference to tablespaces under Server Administration in the main index. What do people think? Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Tablespace patch review
On Fri, 18 Jun 2004, Bruce Momjian wrote:
[snip]
TODO. You sound like a man who's expecting a
several-generations-polished facility when we only just committed
the first version today. I do not feel a need to have any of these
features in 7.5 ...
I just need to know what to add to the TODO list, and so we can answer
people who are going to ask for this functionality. Added to TODO:
* Allow reporting of which objects are in which tablespaces
Do we need an information_schema.tablespaces view as well as an update to
information_schema.{tables|indexes|...} ?
Gavin
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Tablespace patch review
On Sat, 19 Jun 2004, Andreas Pflug wrote: [snip] I don't think we should try and show all objects for a tablespace in information_schema. Agreed, information_schema is database specific. I was thinking about a pg_tablespace_contents(..) function anyway. Being able to list all objects in a tablespace, including which databases they are in, is clearly useful, however (eg: hunting down use of a give tablespace that you want dropped). Sounds like a script in contrib (or the main source tree?) to me. You're suggesting the dblink way using a shell script. Imagine 20, 200, ... databases. This would be a costly thing (and has to be implemented differently in win32). I'd like to see an implementation that enables gui interfaces to show objects that depend on a tablespace, so you'd need to be aware of a user clicking on show what's in that tablespace and he probably wouldn't expect to wait an extended period of time for all databases to be scanned, or impose a 200-connection load on the server. I see this more as a script like Tom described in another email. We'd have a list of tablespacecs and databases and scan each database (on connection at a time) and get the information the user wants. IMHO checking objects in a tablespace is a routine administrative task, so it should be supported natively by the server without need of contribs. And for win user acceptance, a command line tool won't be sufficient either. I would debate that. Firstly, tablespaces aren't supported on windows yet. Secondly, I'd think that Unix users would be fine with a command line tool, especially one that can connect to a remote host. For those not used to command line tools, I can imagine extensions to pgadmin or phppgadmin. Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespaces
Hi Tom,
On Wed, 16 Jun 2004, Tom Lane wrote:
I'm starting to review this patch, and almost immediately came across
what seemed a spectacularly bad idea:
*** src/backend/storage/buffer/bufmgr.c8 May 2004 19:09:25 -1.165
--- src/backend/storage/buffer/bufmgr.c26 May 2004 06:21:01 -
***
*** 1148,1152
{
bufHdr = LocalBufferDescriptors[i];
! if (RelFileNodeEquals(bufHdr-tag.rnode, rnode))
{
bufHdr-flags = ~(BM_DIRTY | BM_JUST_DIRTIED);
--- 1148,1156
{
bufHdr = LocalBufferDescriptors[i];
! /* special case for default tblNode */
! if (RelFileNodeEquals(bufHdr-tag.rnode, rnode) ||
! (!OidIsValid(rnode.tblNode)
! bufHdr-tag.rnode.relNode == rnode.relNode
! bufHdr-tag.rnode.dbNode == rnode.dbNode))
{
bufHdr-flags = ~(BM_DIRTY | BM_JUST_DIRTIED);
There has got to be a better way than this. In the first place the
code seems able to seize on the wrong buffer if it's not checking
all three fields; in the second place, if the weak matching is correct
here why is it not needed everyplace else?
Ahh. This is a hang over from some tests I was doing. I must have missed
it when I send the patch in. The patch should certainly work without this
change. I will verify later today when I have access to my development
machine.
Gavin
---(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
[PATCHES] Tablespaces
Hi all, Attached is my latest patch implementing tablespaces. This has all the functionality I was planning for 7.5. Most of the information about the patch is contained in the patch/documentation, previous submissions and the archives. Testing, review, comments would be greatly appreciated. Gavin tablespace-18.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespaces
Attached is an updated patch, fixing a compile error which my compiler didn't seem to detect/suffer from and incorporating fixes to problems raised by Neil. Thanks, Gavin tablespace-20.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] psql documentation one liner
On Tue, 10 Feb 2004, Peter Eisentraut wrote: Gavin Sherry wrote: This just clears up one aspect of variable handling in psql. [ variable names are case-sensitive ] I honestly wonder why this is suddenly of so much interest. I hope no one is going to question the fact that the command names are case-sensitive. Do we need a note abou that, too? This arose because Neil and I were having a private conversation where psql variables were involved. A script was not behaving correctly because I assumed the variables were case insensitive. I'm not sure why -- perhaps because SQL is case insensitive. I thought I'd send in the note so that no one else could make the mistake. Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] psql documentation one liner
This just clears up one aspect of variable handling in psql. GavinIndex: doc/src/sgml/ref/psql-ref.sgml === RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.104 diff -2 -c -r1.104 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 20 Jan 2004 23:48:56 - 1.104 --- doc/src/sgml/ref/psql-ref.sgml 10 Feb 2004 03:48:54 - *** *** 1812,1815 --- 1812,1821 /note + note + para + Variable names are case sensitive. + /para + /note + para If you call command\set/command without a second argument, the ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] updateable cursors
Attached is a patch implementing updatable cursors in HEAD. Regression test and documentation are included. Updateable cursors are used as follows: begin; declare foo cursor for select * from bar for update; fetch foo; update bar set abc='def' where current of foo; fetch foo; delete from bar where current of foo; commit; Two points: i) The patch doesn't implement updateable cursors for cursors marked WITH HOLD. I have working code for this and will send it in soon. ii) I've implemented a new snapshot type since, AFAICT, updateable cursors have a type of tuple visibility. Namely, if the base table of a cursor is updated based on that cursor, the new and old tuples are removed from the cursor's set of visible tuples. Like wise, deleted tuples are also removed. Thanks, Gavin wherecur2.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 8: explain analyze is your friend
