Re: [PATCHES] pg_regress: paths in largeobject test
Tom Lane wrote: Jorgen Austvik - Sun Norway [EMAIL PROTECTED] writes: I believe the results paths in line 139 and 144 are missing the @abs_builddir@ qualifier. I'd put it the other way around: likely we should get rid of the one use of @[EMAIL PROTECTED] He, he. Generally I prefer explicit over implicit (having the full paths make troubleshooting easier), but in this case you have the additional aspect of the lo_import operating relative to the client, while lo_export operates relative to the server. If you remove @abs_builddir@ on the first one, you might e.g. get problems like this: SELECT lo_export(loid, 'results/lotest.txt') FROM lotest_stash_values; ERROR: could not create server file results/lotest.txt: No such file or directory -J -- Jørgen Austvik, Software Engineering - QA Sun Microsystems Database Technology Group begin:vcard fn;quoted-printable:J=C3=B8rgen Austvik n;quoted-printable:Austvik;J=C3=B8rgen org:Sun Microsystems;Database Technology Group adr:;;Haakon VIII gt. 7b;Trondheim;;NO-7485;Norway email;internet:[EMAIL PROTECTED] title:Senior Engineer tel;work:+47 73 84 21 10 tel;fax:+47 73 84 21 01 tel;cell:+47 901 97 886 x-mozilla-html:FALSE url:http://www.sun.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [GENERAL] Empty arrays with ARRAY[]
On Nov 30, 2007 9:09 PM, Gregory Stark [EMAIL PROTECTED] wrote: I'm sorry to suggest anything at this point, but... would it be less invasive if instead of requiring the immediate cast you created a special case in the array code to allow a placeholder object for empty array of unknown type. The only operation which would be allowed on it would be to cast it to some specific array type. That way things like UPDATE foo SET col = array[]; INSERT INTO foo (col) VALUES (array[]); could be allowed if they could be contrived to introduce an assignment cast. Hi Gregory. Not sure it would be less invasive, but I do like the outcome of being able to create an empty array pending assignment. In addition to your examples, it might also make it possible to do things like this in plpgsql DECLARE a text[] := array[]; Whereas my patch requires you to write a text[]: =array[]::text[]; ... which seems pretty stupid. So, I like your idea a lot from a usability point of view. But I really, really hate it from a just spent half a week on this patch point of view =/ Any suggestions about how you would enforce the only allow casts to array types restriction on the empty array? Cheers BJ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] pt_BR FAQ update
Hi, I updated the brazilian FAQ; it's in [1]. I'm attaching a small patch that adds a forgotten question and change to lt;. [1] http://timbira.com/tmp/FAQ_brazilian.html -- Euler Taveira de Oliveira http://www.timbira.com/ *** ./doc/src/FAQ/FAQ.html.orig 2007-11-30 22:39:02.0 -0200 --- ./doc/src/FAQ/FAQ.html 2007-11-30 22:40:06.0 -0200 *** *** 127,133 H2 align=centerGeneral Questions/H2 ! H3 id=item1.11.1) What is PostgreSQL? How is it pronounced?/H3 PPostgreSQL is pronounced IPost-Gres-Q-L/I. (For those curious about how to say PostgreSQL, an a --- 127,133 H2 align=centerGeneral Questions/H2 ! H3 id=item1.11.1) What is PostgreSQL? How is it pronounced? What is Postgres?/H3 PPostgreSQL is pronounced IPost-Gres-Q-L/I. (For those curious about how to say PostgreSQL, an a *** *** 1051,1057 does not exist errors when accessing temporary tables in PL/PgSQL functions?/H3 ! PIn PostgreSQL versions 8.3, PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached --- 1051,1057 does not exist errors when accessing temporary tables in PL/PgSQL functions?/H3 ! PIn PostgreSQL versions lt; 8.3, PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Fri, Nov 30, 2007 at 12:34:05PM +0530, NikhilS wrote: Hi, Another reason to go along with triggers is that COPY honors triggers, but does not honor rules. While trying to do bulk inserts into a parent of partitioned tables where rules are being employed, the COPY operation will not be so straightforward. Folks, Does my latest patch attached address this well enough? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 1 Dec 2007 04:55:46 - *** *** 2510,2564 listitem para If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! programlisting ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); /programlisting We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: programlisting ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate gt;= DATE '2004-02-01' AND logdate lt; DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate gt;= DATE '2005-12-01' AND logdate lt; DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate gt;= DATE '2006-01-01' AND logdate lt; DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! /programlisting ! ! Note that the literalWHERE/literal clause in each rule ! exactly matches the literalCHECK/literal ! constraint for its partition. /para /listitem /orderedlist --- 2510,2589 listitem para If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! programlisting ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! /programlisting ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! programlisting ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); /programlisting We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex trigger function as shown ! below: programlisting ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate gt;= DATE '2004-02-01' AND logdate lt; DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, !
Re: [PATCHES] [GENERAL] Empty arrays with ARRAY[]
Brendan Jurd [EMAIL PROTECTED] writes: The patch is very invasive (at least compared to any of my previous patches), but so far I haven't managed to find any broken behaviour. I'm sorry to suggest anything at this point, but... would it be less invasive if instead of requiring the immediate cast you created a special case in the array code to allow a placeholder object for empty array of unknown type. The only operation which would be allowed on it would be to cast it to some specific array type. That way things like UPDATE foo SET col = array[]; INSERT INTO foo (col) VALUES (array[]); could be allowed if they could be contrived to introduce an assignment cast. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [GENERAL] Empty arrays with ARRAY[]
As discussed on -hackers, this patch allows the construction of an
empty array if an explicit cast to an array type is given (as in,
ARRAY[]::int[]).
postgres=# select array[]::int[];
array
---
{}
postgres=# select array[];
ERROR: no target type for empty array
HINT: Empty arrays must be explictly cast to the desired array type,
e.g. ARRAY[]::int[]
A few notes on the implementation:
* The syntax now allows an ARRAY constructor with an empty expression
list (array_expr_list may be empty).
* I've added a new parsenode for arrays, A_ArrayExpr (previously the
parser would create ArrayExpr primnodes).
* transformArrayExpr() now takes two extra arguments, a type oid and
a typmod. When transforming a typecast which casts an A_ArrayExpr to
an array type, transformExpr passes these type details down to
transformArrayExpr, and skips the typecast.
* transformArrayExpr() behaves slightly differently when passed type
information. The overall type of the array is set to the given type,
and all elements are explictly coerced to the equivalent element type.
If it was not passed a type, then the behaviour is as previous; the
function looks for a common type among the elements, and coerces them
to that type. The overall type of the array is derived from the
common element type.
The patch is very invasive (at least compared to any of my previous
patches), but so far I haven't managed to find any broken behaviour.
All regression tests pass, and the regression tests for arrays seem to
be quite comprehensive. I did add a couple of new tests for the empty
array behaviours, but the rest I've left alone.
I look forward to your comments -- although given the length of the
8.4 patch review queue, that will probably be an exercise in extreme
patience!
Major thanks go out to Tom for all his guidance on -hackers while I
developed the patch.
Regards,
BJ
*** ./doc/src/sgml/syntax.sgml.orig Fri Nov 30 19:31:29 2007
--- ./doc/src/sgml/syntax.sgml Fri Nov 30 19:32:11 2007
***
*** 1497,1503
array value from values for its member elements. A simple array
constructor
consists of the key word literalARRAY/literal, a left square bracket
! literal[/, one or more expressions (separated by commas) for the
array element values, and finally a right square bracket literal]/.
For example:
programlisting
--- 1497,1503
array value from values for its member elements. A simple array
constructor
consists of the key word literalARRAY/literal, a left square bracket
! literal[/, a list of expressions (separated by commas) for the
array element values, and finally a right square bracket literal]/.
For example:
programlisting
***
*** 1507,1515
{1,2,7}
(1 row)
/programlisting
! The array element type is the common type of the member expressions,
! determined using the same rules as for literalUNION/ or
! literalCASE/ constructs (see xref linkend=typeconv-union-case).
/para
para
--- 1507,1516
{1,2,7}
(1 row)
/programlisting
! If the array is not explictly cast to a particular type, the array
element
! type is the common type of the member expressions, determined using the
! same rules as for literalUNION/ or literalCASE/ constructs (see
! xref linkend=typeconv-union-case).
/para
para
***
*** 1554,1559
--- 1555,1573
/para
para
+You can construct an empty array, but since it's impossible to have an
array
+with no type, you must explictly cast your empty array to the desired
type. For example:
+ programlisting
+ SELECT ARRAY[]::int[];
+ int4
+ --
+ {}
+ (1 row)
+ /programlisting
+For more on casting, see xref linkend=sql-syntax-type-casts.
+ /para
+
+ para
It is also possible to construct an array from the results of a
subquery. In this form, the array constructor is written with the
key word literalARRAY/literal followed by a parenthesized (not
*** ./src/backend/nodes/copyfuncs.c.origFri Nov 30 19:29:16 2007
--- ./src/backend/nodes/copyfuncs.c Fri Nov 30 19:32:11 2007
***
*** 1704,1709
--- 1704,1719
return newnode;
}
+ static A_ArrayExpr *
+ _copyA_ArrayExpr(A_ArrayExpr *from)
+ {
+ A_ArrayExpr *newnode = makeNode(A_ArrayExpr);
+
+ COPY_NODE_FIELD(elements);
+
+ return newnode;
+ }
+
static ResTarget *
_copyResTarget(ResTarget *from)
{
***
*** 3538,3543
--- 3548,3556
case T_A_ArrayExpr:
retval = _copyA_ArrayExpr(from);
break;
+ case T_A_ArrayExpr:
+ retval = _copyA_ArrayExpr(from);
+ break;
case T_ResTarget:
retval = _copyResTarget(from);
break;
*** ./src/backend/nodes/outfuncs.c.orig
Re: [PATCHES] pg_regress: paths in largeobject test
Alvaro Herrera [EMAIL PROTECTED] writes: Jorgen Austvik - Sun Norway wrote: Tom Lane wrote: I'd put it the other way around: likely we should get rid of the one use of @[EMAIL PROTECTED] Generally I prefer explicit over implicit (having the full paths make troubleshooting easier), but in this case you have the additional aspect of the lo_import operating relative to the client, while lo_export operates relative to the server. I submit that the test is OK as it currently is. Yeah, I hadn't thought about the different-paths aspect at the time of making the above comment; but given that, it is correct as-is. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Thu, 2007-11-29 at 09:52 -0800, Joshua D. Drake wrote: In any of the above cases a trigger is going to work better than a rule with the exceptions of what TGL pointed out and in simpler partitioning environments where the number of partitions are very low. Agreed to this and in general to JD's points. Tom's point about Rules being statement-level is valid only when all rows from an INSERT SELECT go into one partition. If that were the case then it seems strange to spend lots of time designing a rules infrastructure anyway. If you try to do lots of partitions and RULEs then it sucks. I spoke against their inclusion originally and do so again now. My point then was you don't need this for most partitioning applications. What nobody has mentioned is that Triggers suck as well, so neither Triggers nor Rules should be given centre stage. COPY only makes sense running into the table you are loading and if you're trying to load large amounts of data using INSERTs + anything then you need a whack. Rob did a beautiful de-construction of all of this in Montreal, BTW, with humour too. Current PostgreSQL partitioning is not the same as Oracle's and papering over the cracks doesn't help anybody much. ISTM we should say to people to use COPY into a named partition for high speed, plus these other suggestions if you want some fancy logic, but go careful, cos they're slow. It would be nice to have an example of using CREATE TABLE LIKE + COPY in same transaction, then ALTER TABLE ... INHERITS to add the partition onto the main table. That is now the fastest way in 8.3. I'll leave it to y'all from here though. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pg_regress: paths in largeobject test
Jorgen Austvik - Sun Norway wrote: Tom Lane wrote: Jorgen Austvik - Sun Norway [EMAIL PROTECTED] writes: I believe the results paths in line 139 and 144 are missing the @abs_builddir@ qualifier. I'd put it the other way around: likely we should get rid of the one use of @[EMAIL PROTECTED] He, he. Generally I prefer explicit over implicit (having the full paths make troubleshooting easier), but in this case you have the additional aspect of the lo_import operating relative to the client, while lo_export operates relative to the server. I submit that the test is OK as it currently is. The lo_export() call is expanded by the server, which can be running anywhere -- hence the need to use an absolute path. Then we have \lo_import and \lo_export calls which are relative to the client. The client is already running in the regress builddir, so using relative paths works fine. If I try to run the client from another directory, it fails completely. Exactly what is the problem you are trying to fix? $ cd .. $ pwd /pgsql/build/00head/src/test $ regress/pg_regress largeobject (using postmaster on Unix socket, port 55432) == dropping database regression == DROP DATABASE == creating database regression == CREATE DATABASE ALTER DATABASE == running regression test queries== test largeobject ... /bin/sh: cannot open ./sql/largeobject.sql: No such file diff: ./expected/largeobject.out: No such file or directory diff: ./results/largeobject.out: No such file or directory diff command failed with status 512: diff -w ./expected/largeobject.out ./results/largeobject.out ./results/largeobject.out.diff -- Alvaro Herrerahttp://www.advogato.org/person/alvherre The Postgresql hackers have what I call a NASA space shot mentality. Quite refreshing in a world of weekend drag racer developers. (Scott Marlowe) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Re: [HACKERS] [GENERAL] plperl and regexps with accented characters - incompatible?
I wrote:
OK, I have a fairly ugly manual workaround, that I don't yet
understand, but seems to work for me.
In your session, run the following code before you do anything else:
CREATE OR REPLACE FUNCTION test(text) RETURNS bool LANGUAGE plperl as $$
return shift =~ /\xa9/i ? 'true' : 'false';
$$;
SELECT test('a');
DROP FUNCTION test(text);
After that we seem to be good to go with any old UTF8 chars.
I'm looking at automating this so the workaround can be hidden, but
I'd rather understand it first.
(Core guys: If we can hold RC1 for a bit while I get this fixed that
would be good.)
The attached patch works for me to eliminate the errors. Please test
ASAP.
Given our time constraints I intend to apply this to HEAD and backpatch
it to 8.2 and 8.1, unless there's a strenuous objection. That will give
us some buildfarm coverage on it, although we don't seem to have any
perl 5.6.x on the buildfarm that I could see. We've had a positive test
report, no negative reports, and I'm fairly sure the patch is at worst
harmless.
cheers
andrew
---(end of broadcast)---
TIP 6: explain analyze is your friend
