Re: [PATCHES] TODO item -- Improve psql's handling of multi-line queries
Sergey E. Koposov writes: I'm proposing the small patch for the TODO item -- Improve psql's handling of multi-line queries. With this patch the multi-line queries are saved by readline as whole and not line by line. I like it already! This is my first patch for Postgres but it seems to work and to not break anything. I'm waiting for review, comments, objections, etc... Did you consider its interaction with \e? Editing the query_buffer with \e will leave that query prefixed with \e in the history. That wasn't the case before your patch. Also, using \e several times on a query without sending it (i.e. without a semicolon) will yield a history entry of a concatenation of old query buffers. Thanks, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
On Mon, 2005-11-14 at 23:40 -0300, Alvaro Herrera wrote: The whole thing is pretty fragile is somebody manually updates a catalog. But we tell people not to do that, so it should be their fault, right? H...sounds scary. Cool ideas in the patch though. I discovered one problem with the whole approach. Per this patch, we only store normal Xids in relminxid/relvacuumxid. So if a table is completely frozen, we will store RecentXmin. We do this because it would actually be unsafe to store, say, FrozenXid: if another transaction stores/changes a tuple while we are vacuuming it, the Frozen mark wouldn't be correct and thus the table could be corrupted if a Xid wraparound happens (which is why we use RecentXmin in the first place: to cope with the possibility of someone else using the table during the vacuum.) Yep. And because VACUUM FULL FREEZE is no longer possible. The problem comes when this is done to template1, and it is copied to another database after some millions of transactions have come and go -- it will seem like the database has suffered wraparound. We would need to vacuum it completely after copied for the stats to be accurate. I don't understand the issue, can you explain more? I see no problem. If an identical copy gives a problem then surely template1 should also. I'm not sure what to do about that. I think storing FrozenXid may not actually be a totally bad idea. Comments? Its not a totally bad idea, but it has some risk, which where transactions are concerned is not really acceptable. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] pl/pgSQL doco patch
Hi, I supplied a minor doco patch relating to porting pl/SQL to pl/pgSQL: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01295.php. Also attached here. Could someone please review and apply this for me? Regards, Philip. -- Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. - Brian W. Kernighan - Utiba Pty Ltd This message has been scanned for viruses and dangerous content by Utiba mail server and is believed to be clean. Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.79 diff -c -r1.79 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 21 Oct 2005 05:11:23 - 1.79 --- doc/src/sgml/plpgsql.sgml 28 Oct 2005 05:20:54 - *** *** 3132,3137 --- 3132,3144 state in temporary tables, instead. /para /listitem + listitem + para +You cannot use parameter names that are the same as columns +that are referenced in the function. Oracle does allow you to do this +if you qualify the parameter name as function_name.paramater_name + /para + /listitem /itemizedlist /para ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Add missing const qualifier in ECPG
On Sun, 13 Nov 2005, Peter Eisentraut wrote:
! (*stmt)-command = (char *)query;
(*stmt)-connection = connection;
(*stmt)-lineno = lineno;
(*stmt)-compat = compat;
This sort of cheating should be avoided.
According to Peter's comment, revised patch is attached. It duplicates the
values of the query string and free it at the end of the query. There is
another question -- seems we allow strdup() fails silently in various
places. Shall we do something about it?
Regards,
Qingqing
---
Index: ecpglib/descriptor.c
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/ecpglib/descriptor.c,v
retrieving revision 1.12
diff -c -r1.12 descriptor.c
*** ecpglib/descriptor.c29 Aug 2004 05:06:59 - 1.12
--- ecpglib/descriptor.c16 Nov 2005 00:40:57 -
***
*** 49,55
}
bool
! ECPGget_desc_header(int lineno, char *desc_name, int *count)
{
PGresult *ECPGresult;
struct sqlca_t *sqlca = ECPGget_sqlca();
--- 49,55
}
bool
! ECPGget_desc_header(int lineno, const char *desc_name, int *count)
{
PGresult *ECPGresult;
struct sqlca_t *sqlca = ECPGget_sqlca();
***
*** 188,194
}
bool
! ECPGget_desc(int lineno, char *desc_name, int index,...)
{
va_list args;
PGresult *ECPGresult;
--- 188,194
}
bool
! ECPGget_desc(int lineno, const char *desc_name, int index,...)
{
va_list args;
PGresult *ECPGresult;
***
*** 431,437
}
bool
! ECPGset_desc_header(int lineno, char *desc_name, int count)
{
struct descriptor *desc;
--- 431,437
}
bool
! ECPGset_desc_header(int lineno, const char *desc_name, int count)
{
struct descriptor *desc;
***
*** 452,458
}
bool
! ECPGset_desc(int lineno, char *desc_name, int index,...)
{
va_list args;
struct descriptor *desc;
--- 452,458
}
bool
! ECPGset_desc(int lineno, const char *desc_name, int index,...)
{
va_list args;
struct descriptor *desc;
Index: ecpglib/execute.c
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/ecpglib/execute.c,v
retrieving revision 1.43
diff -c -r1.43 execute.c
*** ecpglib/execute.c 15 Oct 2005 02:49:47 - 1.43
--- ecpglib/execute.c 16 Nov 2005 00:40:57 -
***
*** 141,147
* ind_offset - indicator offset
*/
static bool
! create_statement(int lineno, int compat, int force_indicator, struct
connection * connection, struct statement ** stmt, char *query, va_list ap)
{
struct variable **list = ((*stmt)-inlist);
enum ECPGttype type;
--- 141,148
* ind_offset - indicator offset
*/
static bool
! create_statement(int lineno, int compat, int force_indicator, struct
connection * connection, struct statement ** stmt, const char
! *query, va_list ap)
{
struct variable **list = ((*stmt)-inlist);
enum ECPGttype type;
***
*** 149,155
if (!(*stmt = (struct statement *) ECPGalloc(sizeof(struct statement),
lineno)))
return false;
! (*stmt)-command = query;
(*stmt)-connection = connection;
(*stmt)-lineno = lineno;
(*stmt)-compat = compat;
--- 150,156
if (!(*stmt = (struct statement *) ECPGalloc(sizeof(struct statement),
lineno)))
return false;
! (*stmt)-command = strdup(query);
(*stmt)-connection = connection;
(*stmt)-lineno = lineno;
(*stmt)-compat = compat;
***
*** 224,229
--- 225,231
return;
free_variable(stmt-inlist);
free_variable(stmt-outlist);
+ ECPGfree(stmt-command);
ECPGfree(stmt);
}
***
*** 1359,1365
}
bool
! ECPGdo(int lineno, int compat, int force_indicator, const char
*connection_name, char *query,...)
{
va_list args;
struct statement *stmt;
--- 1361,1367
}
bool
! ECPGdo(int lineno, int compat, int force_indicator, const char
*connection_name, const char *query,...)
{
va_list args;
struct statement *stmt;
***
*** 1417,1423
ECPGdo_descriptor(int line, const char *connection,
const char *descriptor, const char *query)
{
! return ECPGdo(line, ECPG_COMPAT_PGSQL, true, connection, (char *)
query, ECPGt_EOIT,
ECPGt_descriptor, descriptor, 0L, 0L, 0L,
ECPGt_NO_INDICATOR, NULL, 0L, 0L, 0L,
ECPGt_EORT);
}
--- 1419,1425
ECPGdo_descriptor(int line, const char *connection,
const char *descriptor, const char *query)
{
! return ECPGdo(line, ECPG_COMPAT_PGSQL, true,
Re: [PATCHES] [HACKERS] Per-table freeze limit proposal
Simon Riggs wrote: On Mon, 2005-11-14 at 23:40 -0300, Alvaro Herrera wrote: The whole thing is pretty fragile is somebody manually updates a catalog. But we tell people not to do that, so it should be their fault, right? H...sounds scary. Cool ideas in the patch though. Yeah, well, actually the problem is solved very easily by setting the pg_database tuple manually, either to InvalidTransactionId or to the minimum computed from pg_class. The problem comes when this is done to template1, and it is copied to another database after some millions of transactions have come and go -- it will seem like the database has suffered wraparound. We would need to vacuum it completely after copied for the stats to be accurate. I don't understand the issue, can you explain more? I see no problem. If an identical copy gives a problem then surely template1 should also. Actually, template1 has the problem too. The scenario is this: - template1 is freezed. datminxid - X - a long time passes, say INT_MAX * 0.75 transactions - a new database D is created, which coming from template1 has datminxid=X - the Xid counter is past the vacuum horizon for D.datminxid, so the system determines that the Xid counter could be wrapped already. - The system automatically decides to stop accepting new transactions. In fact there's no problem because in D, just like in template1, all tuples are frozen. How should we mark this on the catalogs? I don't see any way. Note that setting relminxid = FrozenTransactionId is bogus in any case, because even if we correctly lock and freeze the table, the next transaction after the vacuum could insert a new tuple into the table. But we don't want INSERT to be checking pg_class.relminxid! (Or do we?) Now, restating the problem, certainly template1 has the problem too. In fact we have a bigger problem: we are forcing all tables to be vacuumed every so often, even if they have been completely frozen before! This is because setting relminxid = Frozen is really bogus. I'm not sure what to do about that. I think storing FrozenXid may not actually be a totally bad idea. Comments? Its not a totally bad idea, but it has some risk, which where transactions are concerned is not really acceptable. Perhaps we should reinstate VACUUM FULL FREEZE to do just a FREEZE with a table lock and skip all that moving data around. Doesn't work either because of the argument above. What about assuming that if somebody executes a database-wide FREEZE, he knows what he is doing and thus we can mark datminxid as FrozenTransactionId? Sadly, I see all this as proof that the whole idea doesn't work. It seems better than the current state of the system, where we rely on the user to do certain things, or on pgstat which is inherently inexact. But there is a big hole in the whole reasoning which hasn't been filled yet. Any ideas welcome. The idea of any insert/delete/update operation checking a bit in the Relation and resetting relminxid to TopTransactionId if it's marked Frozen is the only one I have right now. What do people think about it? -- Alvaro Herrerahttp://www.PlanetPostgreSQL.org No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra(Malucha Pinto) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [DOCS] pl/pgSQL doco patch
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Philip Yarra wrote: Hi, I supplied a minor doco patch relating to porting pl/SQL to pl/pgSQL: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01295.php. Also attached here. Could someone please review and apply this for me? Regards, Philip. -- Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. - Brian W. Kernighan - Utiba Pty Ltd This message has been scanned for viruses and dangerous content by Utiba mail server and is believed to be clean. [ Attachment, skipping... ] ---(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 -- 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 2: Don't 'kill -9' the postmaster
