[PATCHES] TODO-Item: TRUNCATE ... CASCADE
The proposed patch implements TRUNCATE ... CASCADE:
* %Allow TRUNCATE ... CASCADE/RESTRICT
This is like DELETE CASCADE, but truncates.
The patch also adds a function makeRangeVarFromRelId() to namespace.c that I
thought would be useful. I hope I didn't overlook something similar that
exists already.
Joachim
diff -cr cvs/pgsql/doc/src/sgml/ref/truncate.sgml
cvs.build/pgsql/doc/src/sgml/ref/truncate.sgml
*** cvs/pgsql/doc/src/sgml/ref/truncate.sgml2005-02-22 20:06:18.0
+0100
--- cvs.build/pgsql/doc/src/sgml/ref/truncate.sgml 2006-02-02
08:54:29.0 +0100
***
*** 20,26
refsynopsisdiv
synopsis
! TRUNCATE [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...]
/synopsis
/refsynopsisdiv
--- 20,26
refsynopsisdiv
synopsis
! TRUNCATE [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...]
[ CASCADE | RESTRICT ]
/synopsis
/refsynopsisdiv
***
*** 59,67
para
commandTRUNCATE/ cannot be used on a table that has foreign-key
!references from other tables, unless all such tables are also truncated
!in the same command. Checking validity in such cases would require table
!scans, and the whole point is not to do one.
/para
para
--- 59,68
para
commandTRUNCATE/ cannot be used on a table that has foreign-key
!references from other tables, unless either all such tables are also
!truncated in the same command or the literalCASCADE/ keyword is
!specified. Checking validity in such cases would require table scans,
!and the whole point is not to do one.
/para
para
***
*** 80,87
TRUNCATE TABLE bigtable, fattable;
/programlisting
/para
/refsect1
!
refsect1
titleCompatibility/title
--- 81,97
TRUNCATE TABLE bigtable, fattable;
/programlisting
/para
+
+ para
+Truncate the table literalothertable/literal and cascade to tables that
+are referencing literalothertable/literal via foreign-key constraints:
+
+ programlisting
+ TRUNCATE othertable CASCADE;
+ /programlisting
+ /para
/refsect1
!
refsect1
titleCompatibility/title
diff -cr cvs/pgsql/src/backend/catalog/heap.c
cvs.build/pgsql/src/backend/catalog/heap.c
*** cvs/pgsql/src/backend/catalog/heap.c2005-11-22 19:17:08.0
+0100
--- cvs.build/pgsql/src/backend/catalog/heap.c 2006-02-02 08:54:29.0
+0100
***
*** 2066,2072
get_rel_name(con-conrelid),
get_rel_name(con-confrelid),
NameStr(con-conname)),
!errhint(Truncate table \%s\
at the same time.,
get_rel_name(con-conrelid;
}
}
--- 2066,2072
get_rel_name(con-conrelid),
get_rel_name(con-confrelid),
NameStr(con-conname)),
!errhint(Truncate table \%s\
at the same time or use TRUNCATE ... CASCADE.,
get_rel_name(con-conrelid;
}
}
diff -cr cvs/pgsql/src/backend/catalog/namespace.c
cvs.build/pgsql/src/backend/catalog/namespace.c
*** cvs/pgsql/src/backend/catalog/namespace.c 2005-11-22 19:17:08.0
+0100
--- cvs.build/pgsql/src/backend/catalog/namespace.c 2006-02-02
09:21:20.0 +0100
***
*** 1379,1384
--- 1379,1409
return rel;
}
+ /* makeRangeVarFromRelId
+ *Utility routine to get a RangeVar variable from a RelId Oid
+ *
+ *If the relation is not found, return NULL if failOK = true,
+ *otherwise raise an error.
+ */
+ RangeVar *
+ makeRangeVarFromRelId(Oid relId, bool failOK)
+ {
+ char *namespaceName;
+ char *relName;
+ Oid namespaceId = get_rel_namespace(relId);
+
+ if (!OidIsValid(namespaceId))
+ if (failOK)
+ return NULL;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+errmsg(relation with OID %d does not
exist, relId)));
+ namespaceName = get_namespace_name(namespaceId);
+ relName = get_rel_name(relId);
+ return makeRangeVar(namespaceName, relName);
+ }
+
/*
* NameListToString
*Utility routine to convert a qualified-name list into a string.
diff -cr cvs/pgsql/src/backend/commands/tablecmds.c
Re: [PATCHES] TODO-Item: TRUNCATE ... CASCADE
Joachim Wieland wrote: The proposed patch implements TRUNCATE ... CASCADE: * %Allow TRUNCATE ... CASCADE/RESTRICT This is like DELETE CASCADE, but truncates. The patch also adds a function makeRangeVarFromRelId() to namespace.c that I thought would be useful. I hope I didn't overlook something similar that exists already. That's the wrong way to go about it -- better refactor the code so that a function gets a list of Oids instead of RangeVars, and truncates them. ExecuteTruncate should build the list and pass it down. Also I think all the involved relations should be opened and locked before any of them is touched (so maybe instead of passing Oids you should be passing Relations). + static + List* BuildReferencingRelationList(List* oids, List* found_earlier) Minor stylistic gripe: this should be static List * BuildReferencingRelationList(List *oids, List *found_earlier) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [BUGS] BUG #2171: Differences compiling plpgsql in
Andrew Dunstan [EMAIL PROTECTED] writes: As a matter of curiosity, why does ecpg handle dollar quoting like that? psql, for example, happily just passes a dollar quoted string through to the backend, without any need to convert it to a conventionally quoted string. Doesn't ecpg have to convert string literals into C string constants? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [BUGS] BUG #2171: Differences compiling plpgsql in
On Thu, 2006-02-02 at 16:41 -0500, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: As a matter of curiosity, why does ecpg handle dollar quoting like that? psql, for example, happily just passes a dollar quoted string through to the backend, without any need to convert it to a conventionally quoted string. Doesn't ecpg have to convert string literals into C string constants? I believe so. But doesn't it do that by surrounding them with double quotes, and escaping embedded double quotes (and now newlines) ? The backend still needs to see what it will accept as a quote delimiter, surely. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] pg_restore COPY error handling
* Tom Lane ([EMAIL PROTECTED]) wrote:
I agree. I wonder if it wouldn't be cleaner to pass the information in
the other direction, ie, send a boolean down to PrintTocData saying you
are sending SQL commands or you are sending COPY data. Then, instead
of depending only on the libpq state to decide what to do in
ExecuteSqlCommandBuf, we could cross-check: if we're sending SQL data
and the libpq state is wrong, just discard the line.
I believe the attached patch does this now. Under my test case it
correctly handled things. I'm certainly happier with it this way and
apologize for not realizing this better approach sooner. Please
comment.
Thanks!
Stephen
Index: src/bin/pg_dump/pg_backup_archiver.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.119
diff -c -r1.119 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c21 Jan 2006 02:16:20 -
1.119
--- src/bin/pg_dump/pg_backup_archiver.c3 Feb 2006 02:47:33 -
***
*** 330,339
--- 330,345
* with libpq.
*/
if (te-copyStmt
strlen(te-copyStmt) 0)
+ {
ahprintf(AH, %s,
te-copyStmt);
+ AH-writingCopy = 1;
+ }
(*AH-PrintTocDataPtr) (AH, te,
ropt);
+ if (te-copyStmt
strlen(te-copyStmt) 0)
+ AH-writingCopy = 0;
+
_enableTriggersIfNecessary(AH,
te, ropt);
}
}
***
*** 1590,1595
--- 1596,1602
AH-compression = compression;
AH-pgCopyBuf = createPQExpBuffer();
+ AH-writingCopy = 0;
AH-sqlBuf = createPQExpBuffer();
/* Open stdout with no compression for AH output handle */
Index: src/bin/pg_dump/pg_backup_archiver.h
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.h,v
retrieving revision 1.68
diff -c -r1.68 pg_backup_archiver.h
*** src/bin/pg_dump/pg_backup_archiver.h15 Oct 2005 02:49:38 -
1.68
--- src/bin/pg_dump/pg_backup_archiver.h3 Feb 2006 02:47:33 -
***
*** 245,250
--- 245,251
int loFd; /* BLOB fd */
int writingBlob;/* Flag */
+ int writingCopy;/* Flag to indicate if we are
in COPY mode */
int blobCount; /* # of blobs restored
*/
char *fSpec; /* Archive File Spec */
Index: src/bin/pg_dump/pg_backup_db.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_db.c,v
retrieving revision 1.66
diff -c -r1.66 pg_backup_db.c
*** src/bin/pg_dump/pg_backup_db.c 15 Oct 2005 02:49:38 - 1.66
--- src/bin/pg_dump/pg_backup_db.c 3 Feb 2006 02:47:33 -
***
*** 389,395
*-
*/
! if (PQputline(AH-connection, AH-pgCopyBuf-data) != 0)
die_horribly(AH, modulename, error returned by PQputline\n);
resetPQExpBuffer(AH-pgCopyBuf);
--- 389,395
*-
*/
! if (AH-pgCopyIn PQputline(AH-connection, AH-pgCopyBuf-data) != 0)
die_horribly(AH, modulename, error returned by PQputline\n);
resetPQExpBuffer(AH-pgCopyBuf);
***
*** 400,406
if (isEnd)
{
! if (PQendcopy(AH-connection) != 0)
die_horribly(AH, modulename, error returned by
PQendcopy\n);
AH-pgCopyIn = 0;
--- 400,406
if (isEnd)
{
! if (AH-pgCopyIn PQendcopy(AH-connection) != 0)
die_horribly(AH, modulename, error returned by
PQendcopy\n);
AH-pgCopyIn = 0;
***
*** 615,621
/* Could switch between command and COPY IN mode at each line */
while (qry eos)
{
! if (AH-pgCopyIn)
qry = _sendCopyLine(AH, qry, eos);
else
qry = _sendSQLLine(AH, qry, eos);
--- 615,624
/* Could switch between command and COPY IN mode at each line */
while (qry eos)
{
! /* If we are in CopyIn mode *or*
Re: [PATCHES] [BUGS] BUG #2171: Differences compiling plpgsql in ecpg
Andrew Dunstan wrote: On Wed, 2006-02-01 at 23:48 -0500, Bruce Momjian wrote: I have researched your report, and you are right, there are two ecpg bugs here. First, dollar quoting uses single-quotes internally to do the quoting, but it does not double any single-quotes in the dollar-quoted string. As a matter of curiosity, why does ecpg handle dollar quoting like that? psql, for example, happily just passes a dollar quoted string through to the backend, without any need to convert it to a conventionally quoted string. ecpg actually parses the statements so it can do things like make variable substitutions. The $$ and '' strings are actually passed to preproc.y as SCONST. parser/scan.l does the same thing, though it doesn't need to single-quote it and pass it up to eventually be output in C. -- 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 5: don't forget to increase your free space map settings
Re: [PATCHES] [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql
Here is an updated patch. It fixes things in the places it should be
fixed by handling \n at the time we double-quote, and double single
quotes at the place we are building the $$ string.
I am going to apply this to HEAD. It is a fix, but I am worried it
might affect ecpg users who might have worked-around these problems and
this might break their work-around. I am looking for opinions on
applying this to back releases.
---
Andrew Klosterman wrote:
On Wed, 1 Feb 2006, Bruce Momjian wrote:
I have researched your report, and you are right, there are two ecpg
bugs here. First, dollar quoting uses single-quotes internally to do
the quoting, but it does not double any single-quotes in the
dollar-quoted string.
Second, when a dollar quoted string or single-quoted string spans
multiple lines, ecpg does not escape the newline that is part of the
string. Some compilers will accept an unescaped newline in a string,
while others will not:
$ gcc -pedantic -c -g -Wall tst1.c
tst1.c:5: warning: string constant runs past end of line
It isn't standard so I think we need to replace newline in a string with
\n\.
Attached is a patch which fixes both of these issues. This changes ecpg
behavior so I am thinking this patch would only appear in 8.2.
I am unclear if I fixed the \r case properly.
---
[EMAIL PROTECTED] wrote:
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.2
Operating system: Linux (Debian)
Description:Differences compiling plpgsql in ecpg and psql
Details:
There appear to be parsing problems with ecpg. The following example
program shows code snippets that allow for the successful creation of a
function (CREATE FUNCTION) only using two different syntaxes: one when
entered through psql, and another when compiling with ecpg.
The expectation (and hints from the documentation) indicate that the exact
same method of defining a function should succeed in both cases, but such
is
not the case.
Different quoting and line-wrap behavior is observed between psql and
ecpg.
(Thanks for the attention, I hope this is useful!)
BEGIN CODE---
/* This file is bug.pgc. */
/* Compile as shown:
ecpg bug.pgc -o bug.c
gcc -c -g -std=c99 -I/usr/local/pgsql/include -L/usr/local/pgsql/lib
bug.c -o bug.o
gcc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lecpg bug.o -o
bug
*/
/* Run as: ./bug */
#include stdio.h
#include stdlib.h
#include string.h
int main(int argc, char* argv[]) {
EXEC SQL CONNECT TO DEFAULT;
EXEC SQL SET AUTOCOMMIT TO ON;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CREATE TABLE My_Table ( Item1 int, Item2 text );
/* Documentation appears to indicate that only single quotes (') are
needed, but this will not ecpg-compile without double-single ('')
quotes. When entered through psql, only the single quotes (')
are needed. */
/* doc/html/sql-syntax.html#SQL-SYNTAX-DOLLAR-QUOTING: It is
particularly useful when representing string constants inside
other constants, as is often needed in procedural function
definitions. */
/* doc/html/sql-createfunction.html: Without dollar quoting, any
single quotes or backslashes in the function definition must be
escaped by doubling them. */
/* Documentation appears to indicate that the body of the funtion
can be extended across multiple lines in the input file (this
file) but it will not compile (ecpg) without keeping the function
body on one line. Multiple line input works through psql, but
not here.*/
//bad ecpg,good psql: EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS
trigger
//bad ecpg,good psql: AS $My_Table_Check$
//bad ecpg,good psql: BEGIN RAISE NOTICE 'TG_NAME=%, TG WHEN=%',
TG_NAME,
TG_WHEN;
//bad ecpg,good psql: RETURN NEW;
//bad ecpg,good psql: END;
//bad ecpg,good psql: $My_Table_Check$
//bad ecpg,good psql: LANGUAGE 'plpgsql';
EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS trigger
AS $My_Table_Check$ BEGIN RAISE NOTICE ''TG_NAME=%, TG WHEN=%'',
TG_NAME, TG_WHEN; RETURN NEW; END; $My_Table_Check$
LANGUAGE 'plpgsql';
EXEC SQL CREATE TRIGGER My_Table_Check_Trigger
BEFORE INSERT
ON My_Table
FOR EACH ROW
EXECUTE PROCEDURE My_Table_Check();
EXEC SQL INSERT INTO My_Table VALUES (1234, 'Some random text');
EXEC SQL INSERT INTO My_Table VALUES (5678, 'The Quick Brown');
EXEC SQL DROP TRIGGER
