Re: [HACKERS] anyelement2 pseudotype
adding an anyelement2 pseudotype ... The context was a compatibility SQL function to support Oracle's DECODE function. The reason it's not in there already is we didn't seem to have quite enough use-case to justify it. Do you have more? No. Even this case, for me, is more an expedient than a necessity. I could just rewrite my Oracle code to use CASE, but I've a lot of code to convert, and the transformation is a bit error prone. I'm also looking at a scripted code edit to rewrite the Oracle stuff, and comparing this to the cost a PG compatibility function. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] anyelement2 pseudotype
A few months ago at http://archives.postgresql.org/pgsql-general/2006-11/msg01770.php the notion of adding an anyelement2 pseudotype was discussed. The context was a compatibility SQL function to support Oracle's DECODE function. Assuming this new pseudotype has not been added yet, I'm ready to look into doing this myself, and I'd like a bit of shove in the right direction. ---(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
[HACKERS] 8.2.0 Tarball vs. REL8_2_0 vs. REL8_2_STABLE (was: [GENERAL] pgcluster-1.7.0rc1-patch)
When I apply pgcluster-1.7.0rc1-patch to Postgres REL8_2_STABLE I get a handful of rejects. The patch applies to the 8.2.0 tarball without rejects and without fuzz. That's good. Now on to some fun with pgcluster... However, the patch will not apply to cvs branch REL8_2_0. This all raises the question: what's the difference between REL8_2_STABLE, REL8_2_0, and the 8.2.0 tarball? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2.0 Tarball vs. REL8_2_0 vs. REL8_2_STABLE
difference between REL8_2_STABLE, REL8_2_0 STABLE doesn't mean static. It's the branch for what will be the 8.1.x series. Okay, and this is all different from HEAD, which will presumably become 8.3, correct? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.2.0 Tarball vs. REL8_2_0 vs. REL8_2_STABLE
The [pgcluster-1.7.0rc1-patch] patch applies to the 8.2.0 tarball ... However, the patch will not apply to cvs branch REL8_2_0. I've been told that the pgcluster patch patches some generated files (parse.h and other apparently). Yes, I could not at first apply to REL8_2_0 because the patch file wanted to patch src/backend/parser/gram.c. At that point I started over with a fresh REL8_2_0, ran ./configure; make, and tried the patch again. That's when I got a bunch of failures and fuzz. The problem files are: src/backend/parser/gram.c src/backend/parser/parse.h src/interfaces/libpq/libpq.rc So, I suppose libpq.rc is a derived file, also? Now I have two questions. First, why does pgcluster patch derived files? Is this just sloppy/lazy technique, or could there be some deeper reason? I realize this is properly to be posed to the pgcluster folks, but they don't seem to be too responsive, at least not to their pgfoundry forums. Second, does it make sense that the derived files that rejected the patch would be so different between the 8.2.0 tarball and my REL8_2_0 build? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [GENERAL] Allowing SYSDATE to Work
Can't keywords share code
the way to do what you want I think is
like this:
foo: bar_or_baz
{ code block }
;
bar_or_baz: bar | baz ;
I'll try that, thanks.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Allowing SYSDATE to Work
I suggest you to contribute this kind of code to orafce project [1] Thanks, I'll go play over there for a while. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Allowing SYSDATE to Work
I found it interesting that gram.c and parse.h already supported SYSDATE. Only after you ran bison ;-). They're derived files. Well, so much for my conspiracy theory. Thanks for the bison lesson. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [GENERAL] Allowing SYSDATE to Work
Why should we add this Oraclism to PostgreSQL? I doesn't add any new feature. Certainly, this feature falls well within the class of completely gratuitous proprietary extensions that we typically reject. I now agree completely. My purpose is to migrate Oracle databases to Posgres, and I had thought that Oracle didn't support CURRENT_DATE, CURRENT_TIMESTAMP, and so on. However, I've just learned otherwise. So, I think the proper migration process for a production database would be to first change the Oracle DB to use CURRENT_DATE (or some other standard psuedo column), since that will work properly under both Oracle and Postgres. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Allowing SYSDATE to Work
Redirecting from -general.
I'd like SYSDATE to work syntactically and semantically the same as
CURRENT_TIMESTAMP
current_time and the like are hardcoded in the grammar. You'd have to
do the same for sysdate.
Okay, I patched. The patch follows. Please comment. In particular,
I've just copied the CURRENT_TIMESTAMP code block in gram.y. Is this
the best approach? I saw similar code copying between a couple of the
other time-related functions in gram.y. Can't keywords share code
blocks in bison?
I found it interesting that gram.c and parse.h already supported SYSDATE.
I patched only gram.y and keywords.c
I'd question the hassle of having to patch all the Postgres
installations you're going to want to run your code on.
Yeah, and I don't expect that they'll be a rush to commit this to head
anytime soon. I'll be happy enough tracking this locally. I think it's
a win for my situation.
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.568
diff -c -r2.568 gram.y
*** gram.y 5 Nov 2006 22:42:09 - 2.568
--- gram.y 17 Nov 2006 23:36:35 -
***
*** 419,425
SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
! SYSID SYSTEM_P
TABLE TABLESPACE TEMP TEMPLATE TEMPORARY THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
--- 419,425
SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
! SYSDATE SYSID SYSTEM_P
TABLE TABLESPACE TEMP TEMPLATE TEMPORARY THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
***
*** 7540,7545
--- 7540,7559
n-location = @1;
$$ = (Node *)n;
}
+ | SYSDATE
+ {
+ /*
+* Translate as now(), since we have
a function that
+* does exactly what is needed.
+*/
+ FuncCall *n = makeNode(FuncCall);
+ n-funcname = SystemFuncName(now);
+ n-args = NIL;
+ n-agg_star = FALSE;
+ n-agg_distinct = FALSE;
+ n-location = @1;
+ $$ = (Node *)n;
+ }
| CURRENT_TIMESTAMP '(' Iconst ')'
{
/*
***
*** 8893,8898
--- 8907,8913
| SESSION_USER
| SOME
| SYMMETRIC
+ | SYSDATE
| TABLE
| THEN
| TO
Index: keywords.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.177
diff -c -r1.177 keywords.c
*** keywords.c 7 Oct 2006 21:51:02 - 1.177
--- keywords.c 17 Nov 2006 23:36:35 -
***
*** 324,329
--- 324,330
{substring, SUBSTRING},
{superuser, SUPERUSER_P},
{symmetric, SYMMETRIC},
+ {sysdate, SYSDATE},
{sysid, SYSID},
{system, SYSTEM_P},
{table, TABLE},
---(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
[HACKERS] cvs 'initdb' -- vacuuming database template1 ... FATAL: could not identify a comparison function for type aclitem
head does this to me when I try to initdb: [EMAIL PROTECTED]:~$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... FATAL: could not identify a comparison function for type aclitem child process exited with exit code 1 initdb: removing contents of data directory /usr/local/pgsql/data ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] cvs 'initdb' -- vacuuming database template1 ... FATAL:
head does this to me when I try to initdb: I bet you didn't do a full recompile after cvs update. If you're not using --enable-depend then you really have to do make clean or even make distclean. I am using --enable-depend, but I'll make clean and give it another shot. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] cvs 'initdb' -- vacuuming database template1 ... FATAL:
head does this to me when I try to initdb: ... do make clean or even make distclean. I am using --enable-depend, but I'll make clean and give it another shot. All better. Thanks. I guess I be suspicious of --enable-depend for a while. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] cvs 'initdb' -- vacuuming database template1 ... FATAL:
head does this to me when I try to initdb: I bet you didn't do a full recompile after cvs update. If you're not using --enable-depend then you really have to do make clean or even make distclean. I am using --enable-depend, but I'll 'make clean' and give it another shot. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple tester for MVCC in PostgreSQL
On Tue, 2005-08-30 at 00:56 +0200, Martijn van Oosterhout wrote: I saw the discussion about an tester for MVCC. Since I'd never done anything with asyncronous queries before, I figured I'd try to write something useful with it. The result is at: http://svana.org/kleptog/pgsql/mvcctest.tar.gz I've started using it in some simple cases and it seems to be a good tool. The feature set looks to me to be a pretty solid core on which to build. It uses Perl and the Pg module from CPAN This dependency seems easy enough to live with. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
Rewriting all my Oracle code function-by-function could be painful ... I'm still trying to hold on to my fantasy that I can hack Postgres (and contrib/ora2pg) into submission. Why don't you just use EnterpriseDB? I looked at EnterpriseDB a few months ago. The installation errored. It left stuff in /var/opt, which I consider non-standard for a Red Hat machine. The whole product just didn't feel clean to me. I admit that's a pretty limited and subjective evaluation, especially for a beta product, but I was in the mode of broadly evaluating alternatives, so I moved on. Maybe I need to look at it again. Basically I feel more secure tracking the core project, even if I need to maintain some of my own patches. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Ora2Pg (was PL/pgSQL: EXCEPTION NOSAVEPOINT)
On Fri, 2005-09-02 at 12:29 -0700, Josh Berkus wrote:
still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission.
I'm happy to work with you on ora2pg
Cool.
It looks like I should have referred to contrib/oracle, not
contrib/ora2pg, but you got my point.
The latest version I found of ora2pg is at
http://www.samse.fr/GPL/ora2pg/ora2pg-3.3.tar.gz This seems to be more
recent than the version at contrib/oracle. For example, this newer
version has tablespace support. Given this as a starting point, I've
made the attached changes. Mostly I've added a few new config options,
but I also made a correction to the existing EXCLUDE option, and I
corrected a couple spelling/English errors along the way.
A big thing that's lacking is conversion for stored procedures and
functions. My initial approach to this was to use Perl to post-process
the PL/SQL code dumped by the export, making it look more like proper
Pl/pgSQL (e.g. VARCHAR2-VARCHAR). I'm no Perl hacker, and when I came
across significant PL/SQL -- PL/pgSQL differences (e.g. PL/pgSQL
exception == rollback), I added to my approach the idea of hacking
PL/pgSQL to make it look more like PL/SQL. Attacking the problem from
both ends like this, I imagined that Nirvana would be reached somewhere
in the middle.
The beginning of my Perl-based attempt to convert PL/SQL into PL/pgSQL
is a pretty simple stand-alone script. I can send it if you like, but
I'm a Perl newbie, so you can probably do much better. My attempts to
make PL/pgSQL look like PL/SQL have been posted to -hackers and -patches
over the last couple months.
diff -c ora2pg_3.3/ora2pg.conf ora2pg/ora2pg.conf
*** ora2pg_3.3/ora2pg.conf 2004-12-24 16:05:40.0 +
--- ora2pg/ora2pg.conf 2005-09-02 20:38:48.900376220 +
***
*** 56,61
--- 56,68
# Value must be a list of table name separated by space.
#EXCLUDE OTHER_TABLES
+ # Set whether to include invalid functions, procedures, and packages.
+ # Under Oracle's on-the-fly invalidation/recompilation model there
+ # may be any number of objects that have status of 'INVALID' but that
+ # are actually viable.
+ INCLUDE_INVALID 1
+
+
# Display table indice and exit program (do not perform any export)
SHOWTABLEID 0
***
*** 139,148
# Constraints will be checked at the end of each transaction.
DEFER_FKEY 0
! # If set to 1 replace portable numeric type into PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to smallint,
# integer, bigint, real and float PostgreSQL data type. If you have monetary
# fields you should preserve the numeric(p,s) PostgreSQL data type if you need
! # very good precision. NUMBER without precision are set to float.
! PG_NUMERIC_TYPE 1
--- 146,171
# Constraints will be checked at the end of each transaction.
DEFER_FKEY 0
! # If set to 1 replace portable numeric type with PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to smallint,
# integer, bigint, real and float PostgreSQL data type. If you have monetary
# fields you should preserve the numeric(p,s) PostgreSQL data type if you need
! # very good precision (see PG_INTEGER_TYPE). NUMBER without precision are set to
! # float.
! PG_NUMERIC_TYPE 0
!
! # If set to 1 replace portable numeric type with PostgreSQL internal type,
! # for integers only. This behaves as PG_NUMERIC_TYPE with respect to
! # Oracle data type NUMBER(p), but preserves exact arithmetic on NUMBER(p,s)
! # columns by converting to PostgreSQL numeric(p,s). NUMBER without precision
! # maps to numeric without precision.
! PG_INTEGER_TYPE 1
!
! # If set to 1 map Oracle's DATE type to PostgreSQL DATE type. Oracle DATE type
! # can contain time information, so PostgreSQL timestamp should, in general, be
! # used to hold Oracle DATEs. However, Oracle also supports TIMESTAMP. Setting
! # PG_DATE_TYPE indicates that Oracle TIMESTAMPs are the only incoming date columns
! # with a time portion that needs to be preserved, and that incoming Oracle DATEs
! # effectively contain only a date portion.
! PG_DATE_TYPE 1
diff -c ora2pg_3.3/ora2pg.pl ora2pg/ora2pg.pl
*** ora2pg_3.3/ora2pg.pl 2004-12-24 16:05:40.0 +
--- ora2pg/ora2pg.pl 2005-07-07 18:01:53.0 +
***
*** 40,45
--- 40,46
#tables = [EMAIL PROTECTED]'TABLES'}},
tables = $Config{'TABLES'},
exclude = $Config{'EXCLUDE'},
+ include_invalid = $Config{'INCLUDE_INVALID'} || 0,
showtableid = $Config{'SHOWTABLEID'} || 0,
min = $Config{'MIN'} || 0,
max = $Config{'MAX'} || 0,
***
*** 56,66
fkey_deferrable = $Config{'FKEY_DEFERRABLE'} || 0,
defer_fkey = $Config{'DEFER_FKEY'} || 0,
pg_numeric_type = $Config{'PG_NUMERIC_TYPE'} || 0,
);
exit 0 if ($Config{'SHOWTABLEID'});
! # Mofify export structure if required
if ($Config{'TYPE'} =~ /^(DATA|COPY)$/) {
for my $t
[HACKERS] PG_PAGE_LAYOUT_VERSION - Should be Documented as 3?
doc/src/sgml/storage.sgml says: The last 2 bytes of the page header, structfieldpd_pagesize_version/structfield, store both the page size and a version indicator. Beginning with productnamePostgreSQL/productname 8.0 the version number is 2; productnamePostgreSQL/productname 7.3 and 7.4 used version number 1; prior releases used version number 0. But src/include/storage/bufpage.h says: /* * Page layout version number 0 is for pre-7.3 Postgres releases. * Releases 7.3 and 7.4 use 1, denoting a new HeapTupleHeader layout. * Release 8.0 changed the HeapTupleHeader layout again. * Release 8.1 redefined HeapTupleHeader infomask bits. */ #define PG_PAGE_LAYOUT_VERSION 3 So, should the attached be applied? Index: storage.sgml === RCS file: /var/local/pgcvs/pgsql/doc/src/sgml/storage.sgml,v retrieving revision 1.6 diff -c -r1.6 storage.sgml *** storage.sgml 28 Apr 2005 21:47:09 - 1.6 --- storage.sgml 1 Sep 2005 15:32:35 - *** *** 437,443 The last 2 bytes of the page header, structfieldpd_pagesize_version/structfield, store both the page size and a version indicator. Beginning with ! productnamePostgreSQL/productname 8.0 the version number is 2; productnamePostgreSQL/productname 7.3 and 7.4 used version number 1; prior releases used version number 0. (The basic page layout and header format has not changed in these versions, --- 437,444 The last 2 bytes of the page header, structfieldpd_pagesize_version/structfield, store both the page size and a version indicator. Beginning with ! productnamePostgreSQL/productname 8.1 the version number is 3; ! productnamePostgreSQL/productname 8.0 used version number 2; productnamePostgreSQL/productname 7.3 and 7.4 used version number 1; prior releases used version number 0. (The basic page layout and header format has not changed in these versions, ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
On Thu, 2005-09-01 at 18:28 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: Basically I'd like my Pl/pgSQL code to be able to utilize the try/catch paradigm of error handling without the overhead of subtransactions [Pl/pgSQL] can't even do 2+2 without calling the main executor --- and recovering from elog(ERROR) without a transaction rollback is not part of the executor's contract. Okay, so that's the crux regarding PL/pgSQL. You might take a look at the other PLs such as plperl That would defeat my goal of not rewriting all my Oracle code. If I were fool enough to plan an attack on the main executor's exception handling to try and disarm it of its subtransaction semantics, where would I start? Where would I end? What would I do in between? Can New Orleans be rebuilt above sea level? Seriously, though, I'm willing to devote considerable time to this. Rewriting all my Oracle code function-by-function could be painful, and I would end up dragging other people around this company into it. I'm still trying to hold on to my fantasy that I can hack Postgres (and contrib/ora2pg) into submission. In the end I'm hoping that the move from Oracle will be made easier for others. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, 2005-08-31 at 13:13 -0500, Tony Caduto wrote: the function below also raises no errors at create, but at run time it does. ... CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out lastname varchar) RETURNS SETOF pg_catalog.record AS $BODY$ Declare row record44; BEGIN asfdfdfdfafdsfsdfsdf sdf bla bla sdf yada yada s df sd fsd END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; When I execute this CREATE statement I get: ERROR: type record44 does not exist CONTEXT: compile of PL/pgSQL function test_func9 near line 2 So, it does seem to be working as advertised. I'm running HEAD as of a few hours ago. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote: If a table does not exist, we don't want to check for that and bounce the function; possibly the function will only be called in a context where the table does exist. The Pl/pgSQL compiler should be able to dive into SQL statements, hit the catalog, and bounce a function because of invalid database object references. Ideally this capability could be turned off on demand. I am thankful that Oracle's PL/SQL compiler checks these things for me. I don't remember the last time I intended to write code that referenced something that did not exist in the database. I agree,though, that some developers might rely on such a capability in some circumstances. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: I don't remember the last time I intended to write code that referenced something that did not exist in the database. Almost every day, people try to write stuff like CREATE TEMP TABLE foo ... ; INSERT INTO foo ... ; etc etc DROP TABLE foo ; Point taken. PL/SQL requires all DDL to be dynamic SQL. For example: execute immediate 'drop table foo'; The stuff inside the string is pretty-much ignored at compile time. Maybe, then, my idealized PL/pgSQL compiler always allows DDL to reference any object, but DML is checked against the catalog. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
On Fri, 2005-08-26 at 13:13 -0400, Nicholas Walker wrote: You can't use savepoints, you can trap errors which is implemented using savepoints. You still might want to write code like this: BEGIN SAVEPOINT foo; IF SOME_ERROR_CODE = 1234 THEN ROLLBACK TO SAVEPOINT foo; END ... I agree, and I think savepoints would be much more usefull if you could call them from pl/pgsql... Maybe if PL/pgSQL had user-defined exceptions then the language's identity of savepoints and exception blocks would be a little easier to work with. Is anything happening with the patch for user-defined exceptions, posted at http://archives.postgresql.org/pgsql-patches/2005-06/msg00475.php (and also discussed elsewhere)? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO list comments
On Thu, 2005-08-25 at 15:50 +0900, Michael Glaesemann wrote:
* %Remove CREATE CONSTRAINT TRIGGER
Do we really want to remove it,
Also, I believe CONSTRAINT TRIGGERS are the only way to provide
transaction level (rather than statement level) referential
integrity.
Don't deferrable foreign keys give you transaction-level referential
integrity? From the SET CONSTRAINTS doc:
Synopsis
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
Description
SET CONSTRAINTS sets the behavior of constraint checking within the
current transaction. IMMEDIATE constraints are checked at the end of
each statement. DEFERRED constraints are not checked until transaction
commit.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Testing of MVCC
On Mon, 2005-08-08 at 16:59 -0400, Tom Lane wrote:
Matt Miller [EMAIL PROTECTED] writes:
I want to write some regression tests that confirm the behavior of
multiple connections simultaneously going at the same tables/rows. Is
there something like this already, e.g. in src/test/regress?
No. ... but surely we need one.
The attached patch allows src/test/regress/pg_regress.sh to recognize
lines that begin with curr_test: in the schedule file. Tests named on
such a line are run concurrently across multiple connections. To make
use of this facility each test in the group must begin with the line:
select * from concurrency_test where key = 'test_name' for update;
where test_name is replace by the name of that test. This will enable
pg_regress to start this test at the same time as the other tests in the
group.
Is this a reasonable starting point for a concurrent testing framework?
This does not address the issue of how to interpret the test output.
Maybe the simplest solution is to force test writers to generate output
that does not depend on the relative progress of any concurrent tests.
Or, maybe the ignore: directive in the schedule file could be employed
somehow.
Index: pg_regress.sh
===
RCS file: /var/local/pgcvs/pgsql/src/test/regress/pg_regress.sh,v
retrieving revision 1.59
diff -c -r1.59 pg_regress.sh
*** pg_regress.sh 17 Jul 2005 18:28:45 - 1.59
--- pg_regress.sh 15 Aug 2005 21:20:03 -
***
*** 623,628
--- 623,632
do
# Count line numbers
lno=`expr $lno + 1`
+
+ # Init concurrency flag
+ concurrent=
+
[ -z $line ] continue
set X $line; shift
***
*** 631,636
--- 635,647
shift
ignore_list=$ignore_list $@
continue
+ elif [ x$1 = xcurr_test: ]; then
+ # init support for concurrent test group
+ concurrent=1
+ cat /dev/null $inputdir/sql/concurrency_test_init.sql
+ echo create table concurrency_test (key varchar primary key); $inputdir/sql/concurrency_test_init.sql
+ ( $PSQL -d $dbname $inputdir/sql/concurrency_test_init.sql $outputdir/results/concurrency_test_init.out 21 )
+ wait
elif [ x$1 != xtest: ]; then
echo $me:$schedule:$lno: syntax error
(exit 2); exit
***
*** 649,671
( $PSQL -d $dbname $inputdir/sql/$1.sql $outputdir/results/$1.out 21 )
wait
else
! # Start a parallel group
! $ECHO_N parallel group ($# tests): $ECHO_C
! if [ $maxconnections -gt 0 ] ; then
! connnum=0
! test $# -gt $maxconnections $ECHO_N (in groups of $maxconnections) $ECHO_C
! fi
! for name do
! (
! $PSQL -d $dbname $inputdir/sql/$name.sql $outputdir/results/$name.out 21
! $ECHO_N $name$ECHO_C
! )
if [ $maxconnections -gt 0 ] ; then
! connnum=`expr \( $connnum + 1 \) % $maxconnections`
! test $connnum -eq 0 wait
fi
! done
! wait
echo
fi
--- 660,717
( $PSQL -d $dbname $inputdir/sql/$1.sql $outputdir/results/$1.out 21 )
wait
else
! # --
! # If this is a concurrent test group then write the script concurrent_test.sql
! # which will spawn and synchronize each test in the group.
! #
! # Concurrent test groups do not respect $maxconnections.
! #
! # If this is not a concurrent test group then just run each test directly.
! # --
!
! if [ $concurrent = 1 ]; then
! $ECHO_N concurrent group ($# tests): $ECHO_C
!
! # insert a lock record for each test
! cat /dev/null $inputdir/sql/concurrency_test.sql
! echo BEGIN; $inputdir/sql/concurrency_test.sql
! for name do
! echo insert into concurrency_test values ('$name'); $inputdir/sql/concurrency_test.sql
! done
! echo COMMIT; $inputdir/sql/concurrency_test.sql
!
! # for each test, acquire the lock and then spawn the test
! echo BEGIN; $inputdir/sql/concurrency_test.sql
! for name do
! echo select * from concurrency_test where key = '$name' for update; $inputdir/sql/concurrency_test.sql
! echo \! $PSQL -d \$dbname\ \$inputdir/sql/$name.sql\ \$outputdir/results/$name.out\ 21 $inputdir/sql/concurrency_test.sql
! done
!
! # release all locks, concurrently launching all tests
! echo ROLLBACK; $inputdir/sql/concurrency_test.sql
!
! # done writing the script. fire it.
! ( $PSQL -d $dbname $inputdir/sql/concurrency_test.sql $outputdir/results/concurrency_test.out 21 )
! wait
! else
! $ECHO_N
Re: [HACKERS] Testing of MVCC
What we really need is a test program that can issue a command on one connection (perhaps waiting for it to finish, perhaps not) and then issue other commands on other connections, all according to a script. It seems to me that this is what contrib/dblink could allow, but when I presented that idea earlier you replied: I doubt it would be very useful, since a script based on that still doesn't let you issue concurrent queries. So, I guess I'm not clear on what you're thinking. Perhaps we should look at Expect or something similar. Where can I get more info on Expect? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Testing of MVCC
Perhaps we should look at Expect or something similar. Where can I get more info on Expect? I think I found it: http://expect.nist.gov/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Testing of MVCC
On Wed, 2005-08-10 at 16:41 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: It seems to me that contrib/dblink could greatly simplify the design and coding of multi-user regression tests. I doubt it would be very useful, since a script based on that still doesn't let you issue concurrent queries. I think it would be useful to allow a test script to first create a set of committed and uncommitted transactions, and to then issue some queries on another connection to confirm that the other connection has a proper view of the database at that point. This type of test is serialized, but I think it would be a useful multi-user test. Also, the output from such a test is probably pretty easy to fit into the diff-based validation of make check. I realize that we also need to have tests that spawn several connections and run scripts concurrently across those connections. I agree that this type of test would probably not benefit fundamentally from contrib/dblink. However, I was grasping a bit to see how the output from such a concurrent test would be diff'ed with an expected output in a meaningful way. So, to continue to progress on this problem, I figured that a contrib/dblink dependency would at least allow me to start coding something... Is there objection to a portion of src/test/regress depending on contrib/dblink? Yes. Understood. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Testing of MVCC
On Mon, 2005-08-08 at 16:59 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: I want to write some regression tests that confirm the behavior of multiple connections simultaneously going at the same tables/rows. Is there something like this already, e.g. in src/test/regress? No. ... but surely we need one. It seems to me that contrib/dblink could greatly simplify the design and coding of multi-user regression tests. Is there objection to a portion of src/test/regress depending on contrib/dblink? I'm not sure yet how that dependency would look, but I'm mainly wondering if there are objections in principle to depending on contrib/. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
