Re: [PATCHES] per user/database connections limit again
Bruce Momjian wrote: I removed your use of the pg_auth flat file. By the time you have the PROC entry to do your lookups, you might as well just use the system cache. There is a race condition in the code because we set our PROC entry before we check for other entries. If there is one connection left and two backends do this at the same time, they would both fail, while one should fail and the other succeed. Without a lock, I see no way to avoid it so I just commented it in the code. Yeah my working version was doing this too but I wanted to avoid lock on PROC array and that race condition and because pg_auth is loaded anyway I used it. Also, I felt that zero should mean allow no/zero connections, rather than representing unlimited connections. I used -1 for unlimited. We can either document the use of -1, or add syntax to allow NO CONNECTION LIMIT, or something like that. Right, maybe we could remove datallowconn from pg_database (in future) if we can achieve same thing using datconnlimit = 0 ? The patch requires a catalog version update when applied. Yes, thanks for your work on this patch, I will write documentation for it in next few days. -- Regards Petr Jelinek (PJMODOS) ---(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] per user/database connections limit again
Petr Jelinek wrote: Bruce Momjian wrote: I removed your use of the pg_auth flat file. By the time you have the PROC entry to do your lookups, you might as well just use the system cache. There is a race condition in the code because we set our PROC entry before we check for other entries. If there is one connection left and two backends do this at the same time, they would both fail, while one should fail and the other succeed. Without a lock, I see no way to avoid it so I just commented it in the code. Yeah my working version was doing this too but I wanted to avoid lock on PROC array and that race condition and because pg_auth is loaded anyway I used it. Well, we are locking the PROC array for the db scan as well, so I don't see a difference for user. Also, I don't see how it would avoid the race condition. We could scan PROC and then set our user value, but that would allow possibly too many connections rather than too few. Also, I felt that zero should mean allow no/zero connections, rather than representing unlimited connections. I used -1 for unlimited. We can either document the use of -1, or add syntax to allow NO CONNECTION LIMIT, or something like that. Right, maybe we could remove datallowconn from pg_database (in future) if we can achieve same thing using datconnlimit = 0 ? Yes, we certainly could, but I am betting we would need both because if someone wanted to close down a database, _but_ keep the existing limit so it could be resetored later, they would still use datallowconn. The patch requires a catalog version update when applied. Yes, thanks for your work on this patch, I will write documentation for it in next few days. Thanks. -- 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 6: explain analyze is your friend
Re: [PATCHES] small SPI docs correction
Patch applied. Thanks. --- Alvaro Herrera wrote: I've had this small patch in my local tree for a while. It documents new commands which may make an SPI call fail. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Maybe there's lots of data loss but the records of data loss are also lost. (Lincoln Yeoh) [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- 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 6: explain analyze is your friend
Re: [PATCHES] [pgsql-hackers-win32] patch for win32 dynloader
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. --- Magnus Hagander wrote: 3) I restarted the postmaster both times. I got this error both times. :25: ERROR: could not load library C:/Program Files/PostgreSQL/8.0/lib/testtrigfuncs.dll: dynamic load error Yes. We really need to look at fixing that error message. I had forgotten it completely :-( Bruce, you think we can sneak that in after feature freeze? I would call it a bugfix :-) Me too. That's been on the radar for awhile --- please do send in a patch. Here we go, that wasn't too hard :-) Apart from adding the error handling, it does one more thing: it changes the errormode when loading the DLLs. Previously if a DLL was broken, or referenced other DLLs that couldn't be found, a popup dialog box would appear on the screen. Which had to be clicked before the backend could continue. This patch also disables the popup error message for DLL loads. I think this is something we should consider doing for the entire backend - disable those popups, and say we deal with it ourselves. What do you other win32 hackers thinnk about this? In the meantime, this patch fixes the error msgs. Please apply for 8.1 and please consider a backpatch to 8.0. //Magnus Content-Description: win32_dynloader_err.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- 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 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 OBJECT SET SCHEMA
Bernd Helmle wrote: --On Donnerstag, Juli 28, 2005 23:12:37 -0400 Bruce Momjian pgman@candle.pha.pa.us wrote: Here is an updated version of your patch. Would you supply SGML documentation updates to match the code changes? Thanks. Bruce, is there any requirement to add some regression tests, too? It would be nice, yes. -- 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
Re: [PATCHES] ALTER OBJECT SET SCHEMA
--On Donnerstag, Juli 28, 2005 23:12:37 -0400 Bruce Momjian pgman@candle.pha.pa.us wrote: Here is an updated version of your patch. Would you supply SGML documentation updates to match the code changes? Thanks. Bruce, is there any requirement to add some regression tests, too? -- Bernd ---(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] [PATCHES] Dbsize backend integration
Patch applied. Thanks. /contrib/dbsize removed. New functions: pg_tablespace_size pg_database_size pg_relation_size pg_complete_relation_size pg_size_pretty --- Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 06 July 2005 04:11 To: Tom Lane Cc: Dave Page; Christopher Kings-Lynne; Robert Treat; Dawid Kuroczko; Andreas Pflug; PostgreSQL-patches; PostgreSQL-development Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration Tom Lane wrote: pg_relation_size plus pg_complete_relation_size is fine. Ship it... OK. Updated version attached. Regards, Dave. Content-Description: dbsize.c [ Attachment, skipping... ] Content-Description: dbsize.patch [ Attachment, skipping... ] -- 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 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] Implement support for TCP_KEEPCNT, TCP_KEEPIDLE, TCP_KEEPINTVL
Is this the functionality we agreed we wanted?
---
Oliver Jowett wrote:
Bruce Momjian wrote:
Is this patch being worked on?
Here's an updated version. It compiles and appears to work as expected
under Linux (supports TCP_KEEPIDLE etc) and Solaris 9 (no support).
Main changes:
- removed the tcp_keepalives GUC, SO_KEEPALIVE is now always on (as in
current CVS)
- {get,set}sockopt calls are only done when absolutely necessary (no
extra syscalls during backend startup in a default configuration).
I still haven't had a chance to glue in support for the TCP_KEEPALIVE
(Solaris-style) option, but that should be fairly painless to add later.
-O
? postgresql-8.1devel.tar.gz
Index: doc/src/sgml/runtime.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.335
diff -u -c -r1.335 runtime.sgml
*** doc/src/sgml/runtime.sgml 2 Jul 2005 19:16:36 - 1.335
--- doc/src/sgml/runtime.sgml 4 Jul 2005 10:41:33 -
***
*** 894,899
--- 894,946
/listitem
/varlistentry
+ varlistentry id=guc-tcp-keepalives-idle
xreflabel=tcp_keepalives_idle
+ termvarnametcp_keepalives_idle/varname
(typeinteger/type)/term
+ indexterm
+primaryvarnametcp_keepalives_idle/ configuration
parameter/primary
+ /indexterm
+ listitem
+para
+ On systems that support the TCP_KEEPIDLE socket option, specifies
the
+ number of seconds between sending keepalives on an otherwise idle
+ connection. A value of 0 uses the system default. If TCP_KEEPIDLE is
+ not supported, this parameter must be 0. This option is ignored for
+ connections made via a Unix-domain socket.
+/para
+ /listitem
+ /varlistentry
+
+ varlistentry id=guc-tcp-keepalives-interval
xreflabel=tcp_keepalives_interval
+ termvarnametcp_keepalives_interval/varname
(typeinteger/type)/term
+ indexterm
+primaryvarnametcp_keepalives_interval/ configuration
parameter/primary
+ /indexterm
+ listitem
+para
+ On systems that support the TCP_KEEPINTVL socket option, specifies
how
+ long, in seconds, to wait for a response to a keepalive before
+ retransmitting. A value of 0 uses the system default. If
TCP_KEEPINTVL
+ is not supported, this parameter must be 0. This option is ignored
+ for connections made via a Unix-domain socket.
+/para
+ /listitem
+ /varlistentry
+
+ varlistentry id=guc-tcp-keepalives-count
xreflabel=tcp_keepalives_count
+ termvarnametcp_keepalives_count/varname
(typeinteger/type)/term
+ indexterm
+primaryvarnametcp_keepalives_count/ configuration
parameter/primary
+ /indexterm
+ listitem
+para
+ On systems that support the TCP_KEEPCNT socket option, specifies how
+ many keepalives may be lost before the connection is considered
dead.
+ A value of 0 uses the system default. If TCP_KEEPINTVL is not
+ supported, this parameter must be 0.
+/para
+ /listitem
+ /varlistentry
+
/variablelist
/sect3
sect3 id=runtime-config-connection-security
Index: src/backend/libpq/pqcomm.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/libpq/pqcomm.c,v
retrieving revision 1.176
diff -u -c -r1.176 pqcomm.c
*** src/backend/libpq/pqcomm.c22 Feb 2005 04:35:57 - 1.176
--- src/backend/libpq/pqcomm.c4 Jul 2005 10:41:33 -
***
*** 87,93
#include libpq/libpq.h
#include miscadmin.h
#include storage/ipc.h
!
/*
* Configuration options
--- 87,93
#include libpq/libpq.h
#include miscadmin.h
#include storage/ipc.h
! #include utils/guc.h
/*
* Configuration options
***
*** 594,599
--- 594,612
elog(LOG, setsockopt(SO_KEEPALIVE) failed: %m);
return STATUS_ERROR;
}
+
+ /* Set default keepalive parameters. This should also catch
+ * misconfigurations (non-zero values when socket options aren't
+ * supported)
+ */
+ if (pq_setkeepalivesidle(tcp_keepalives_idle, port) !=
STATUS_OK)
+ return STATUS_ERROR;
+
+ if (pq_setkeepalivesinterval(tcp_keepalives_interval, port) !=
STATUS_OK)
+ return STATUS_ERROR;
+
+ if (pq_setkeepalivescount(tcp_keepalives_count, port) !=
STATUS_OK)
+ return STATUS_ERROR;
}
return STATUS_OK;
Re: [PATCHES] [HACKERS] Autovacuum loose ends
BTW, is there still any reason not to remove the contrib/pg_autovacuum directory from CVS? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] reindexdb into scripts
Patch applied. Thanks. reindexdb removed from /contrib. --- Euler Taveira de Oliveira wrote: Hi, Sorry for the delay. I'm too busy the last days. Based in the discussions [1], here is a patch that translate the reindexdb shell script to a C program. Like the other scripts, this is a wrapper for REINDEX. Docs are include but need some improvement. Hope that it could be included in 8.1. http://archives.postgresql.org/pgsql-hackers/2005-06/msg01578.php Euler Taveira de Oliveira euler[at]yahoo_com_br ___ Yahoo! Acesso Gr?tis - Internet r?pida e gr?tis. Instale o discador agora! http://br.acesso.yahoo.com/ Content-Description: 431253405-reindexdb.tgz [ Attachment, skipping... ] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- 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] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
I am waiting for pg_dump support for this patch.
---
Satoshi Nagayasu wrote:
Bruce Momjian wrote:
I am not sure what to do with this patch. It is missing dump
capability, there is no clause to disable all triggers on a table, and
it uses a table owner check when a super user check is required (because
of referential integrity).
Satoshi, are you still working on it? If not does someone else want to
complete it? I realized you just started on it but the deadline is
soon.
I've already implemented 'ENABLE/DISABLE TRIGGER ALL',
and the superuser check has been added. Please check it.
And, I'm going to have a business trip to Sydney this weekend,
so I'll complete pg_dump stuffs while my flight.
Thank you.
--
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.c2005-07-01 15:50:27.0
+0900
***
*** 236,241
--- 236,243
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
***
*** 1993,1998
--- 1995,2005
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
***
*** 2155,2160
--- 2162,2173
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
***
*** 5465,5470
--- 5478,5492
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+ static void
+ ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+ {
+ EnableDisableTrigger(rel, trigname, enable);
+ }
+
+ /*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -cr pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
*** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
--- pgsql/src/backend/commands/trigger.c 2005-07-04 10:40:27.0
+0900
***
*** 3063,3065
--- 3063,3158
afterTriggerAddEvent(new_event);
}
}
+
+ /* --
+ * EnableDisableTrigger()
+ *
+ * Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+ void
+ EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+ {
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+ int nkeys;
+ int changed;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg(permission denied: \%s\ is a system
catalog,
+ RelationGetRelationName(rel;
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ nkeys = 2;
+ changed = 0;
+ if ( strcmp(tgname, *)==0 )
+ {
+ if ( !superuser() )
+ elog(ERROR,
Re: [PATCHES] WIP XLog Switch
Simon Riggs wrote: On Sat, 2005-05-21 at 18:38 +0100, Simon Riggs wrote: On Fri, 2005-05-20 at 23:16 -0400, Bruce Momjian wrote: Any farther on this? No, but it will be in by deadline. I need to make first base with partitioning before I can do this. Hence my (otherwise rude) ignoring of your sterling work on the exec logging patch. If I were to complete this patch now over next few days, would anybody object to its inclusion in 8.1 beta? I don't like to leave things incomplete... Please send us what you have when you are done and we will review it. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Fri, Jul 29, 2005 at 11:19:34AM -0400, Tom Lane wrote: BTW, is there still any reason not to remove the contrib/pg_autovacuum directory from CVS? I still haven't added custom cost-based delays, but I don't see that as a showstopper for removing it. I just went through the CVS log and I don't see anything else that applies. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Voy a acabar con todos los humanos / con los humanos yo acabaré voy a acabar con todos / con todos los humanos acabaré (Bender) ---(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] Patch to mention cost-based delay in vacuum reference
Hackers, Subject says it all. Please review the grammar ... -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Et put se mouve (Galileo Galilei) Index: ref/vacuum.sgml === RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/ref/vacuum.sgml,v retrieving revision 1.36 diff -c -r1.36 vacuum.sgml *** ref/vacuum.sgml 2 Dec 2004 19:28:48 - 1.36 --- ref/vacuum.sgml 29 Jul 2005 17:03:36 - *** *** 174,179 --- 174,188 to occupy less disk space. commandVACUUM FULL/command will usually shrink the table more than a plain commandVACUUM/command would. /para + +para + During commandVACUUM/command execution, there can be a substantial + increase in I/O traffic, which can lead to a performance drop for the + rest of the system. Therefore, it is sometimes advisable to activate + the cost-based vacuum delay feature. See xref + linkend=runtime endterm=runtime-config-resource-vacuum-cost for more + details. +/para /refsect1 refsect1 *** *** 229,234 --- 238,244 simplelist type=inline memberxref linkend=app-vacuumdb endterm=app-vacuumdb-title/member +memberxref linkend=runtime endterm=runtime-config-resource-vacuum-cost/member /simplelist /refsect1 /refentry ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera [EMAIL PROTECTED] writes: Updated this patch: - The stat collector is modified so as to keep shared relations separate from regular ones. Autovacuum understands this. [etc] Applied with some fixes --- you had broken the reporting of statistics for shared tables, for one thing. Also the patch seemed to be missing diffs for header files? It occurs to me that vacuuming to prevent XID wraparound is not the only reason to do DB-wide vacuums: we also need to keep pg_database.datvacuumxid from getting too old, else we will have problems with clog bloat. We may need to rethink the test used. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Updated this patch: - The stat collector is modified so as to keep shared relations separate from regular ones. Autovacuum understands this. [etc] Applied with some fixes --- you had broken the reporting of statistics for shared tables, for one thing. Oops :-( Didn't notice that. Also the patch seemed to be missing diffs for header files? Damn, I generated the diff from within src/backend instead of the root :-( Sorry for the inconvenience. It occurs to me that vacuuming to prevent XID wraparound is not the only reason to do DB-wide vacuums: we also need to keep pg_database.datvacuumxid from getting too old, else we will have problems with clog bloat. We may need to rethink the test used. Hmm. Will see about it. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) La Primavera ha venido. Nadie sabe como ha sido (A. Machado) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] PL/pgSQL: SELECT INTO EXACT
This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. I also feel that this EXACT behavior is overall a bit cleaner than the current PL/pgSQL behavior. Maybe I've just been brainwashed by years of Oracle'ing. Here are three excerpts from the patched PL/pgSQL documentation: If the EXACT option is specified, then target will not be set unless the query returns exactly one row You can check the special FOUND variable after a SELECT INTO to determine whether the statement was successful. ... an EXACT query is successful only if exactly 1 row is returned. ...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2, indicating no matching rows, exactly one matching row, or greater than one matching row, respectively. Index: doc/src/sgml/plpgsql.sgml === RCS file: /var/local/pgcvs/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.75 diff -c -r1.75 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 2 Jul 2005 08:59:47 - 1.75 --- doc/src/sgml/plpgsql.sgml 29 Jul 2005 19:19:56 - *** *** 1067,1073 variable, or list of scalar variables. This is done by: synopsis ! SELECT INTO replaceabletarget/replaceable replaceableselect_expressions/replaceable FROM ...; /synopsis where replaceabletarget/replaceable can be a record variable, a row --- 1067,1073 variable, or list of scalar variables. This is done by: synopsis ! SELECT INTO optionalEXACT/optional replaceabletarget/replaceable replaceableselect_expressions/replaceable FROM ...; /synopsis where replaceabletarget/replaceable can be a record variable, a row *** *** 1108,1126 /para para ! If the query returns zero rows, null values are assigned to the ! target(s). If the query returns multiple rows, the first ! row is assigned to the target(s) and the rest are discarded. ! (Note that quotethe first row/ is not well-defined unless you've ! used literalORDER BY/.) /para para ! You can check the special literalFOUND/literal variable (see ! xref linkend=plpgsql-statements-diagnostics) after a ! commandSELECT INTO/command statement to determine whether the ! assignment was successful, that is, at least one row was was returned by ! the query. For example: programlisting SELECT INTO myrec * FROM emp WHERE empname = myname; --- 1108,1130 /para para ! If the literalEXACT/literal option is specified, then ! replaceabletarget/replaceable will not be set unless the query ! returns exactly one row. If literalEXACT/literal is not ! specified then replaceabletarget/replaceable will be set ! regardless of the number of rows returned by the query. In the ! non-literalEXACT/literal case, null values are assigned if the ! query returns zero rows, and the first row is assigned if the query ! returns more than 1 row. (Note that quotethe first row/ is not ! well-defined unless you've used literalORDER BY/.) /para para ! You can check the special literalFOUND/literal variable after a ! commandSELECT INTO/command to determine whether the statement was ! successful. A non-literalEXACT/literal query is considered successful ! if any rows are returned, and an literalEXACT/literal query is ! successful only if exactly 1 row is returned. For example: programlisting SELECT INTO myrec * FROM emp WHERE empname = myname; *** *** 1128,1141 RAISE EXCEPTION 'employee % not found', myname; END IF; /programlisting /para para ! To test for whether a record/row result is null, you can use the ! literalIS NULL/literal conditional. There is, however, no ! way to tell whether any additional rows might have been ! discarded. Here is an example that handles the case where no ! rows have been returned: programlisting DECLARE users_rec RECORD; --- 1132,1196 RAISE EXCEPTION 'employee % not found', myname; END IF; /programlisting + + programlisting + SELECT INTO EXACT myrec * FROM emp WHERE empname = myname; + IF NOT FOUND THEN + RAISE EXCEPTION 'employee % not found or not unique', myname; + END IF; + /programlisting + /para + + para + When using the literalEXACT/literal option you can distinguish the + not-found case from the not-unique case by using + commandGET DIAGNOSTICS/command (see + xref
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera [EMAIL PROTECTED] writes: Also the patch seemed to be missing diffs for header files? Damn, I generated the diff from within src/backend instead of the root :-( Sorry for the inconvenience. No problem --- reverse-engineering the changes to function declarations was simple enough. But did you have any other changes outside src/backend? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
Matt Miller [EMAIL PROTECTED] writes: This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. Uh, what's the point of being only sort-of compatible? Why not throw the exception? I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway. Not sure about a better word though ... anyone? regards, tom lane ---(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] PL/pgSQL: SELECT INTO EXACT
On 7/29/05, Tom Lane [EMAIL PROTECTED] wrote: Matt Miller [EMAIL PROTECTED] writes: This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. Uh, what's the point of being only sort-of compatible? Why not throw the exception? I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway. Not sure about a better word though ... anyone? regards, tom lane just wonder, why that is not the default behavior of the SELECT INTO? at least, the first time i think the function was right until i found that the first row of a set of rows was assigned... i mean, when you do that code you are expecting just one row from your query, doesn't you? -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Fri, Jul 29, 2005 at 05:46:11PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Also the patch seemed to be missing diffs for header files? Damn, I generated the diff from within src/backend instead of the root :-( Sorry for the inconvenience. No problem --- reverse-engineering the changes to function declarations was simple enough. But did you have any other changes outside src/backend? Nope, that was it. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo (G. Lama) ---(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] PL/pgSQL: SELECT INTO EXACT
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception Uh, what's the point of being only sort-of compatible? Why not throw the exception? I guess my hesitation is that the PL/SQL notion of the exception as a program flow control technique is a bit at odds with the PL/pgSQL notion of the exception as a transaction control mechanism. Maybe these notions could be reconciled by a new NOSAVE option to the EXCEPTION block definition, to suppress the savepoint and the exception-induced rollback for that BEGIN ... END block. Then an automatically-thrown exception would not be so expensive. I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway The motivation is that EXACTly one row must be returned. Maybe UNIQUE instead of EXACT? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. why that is not the default behavior of the SELECT INTO? ... i mean, when you do that code you are expecting just one row from your query I agree. I suppose I was fearful of breaking existing stuff, so I added a new keyword. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] INSERT ... RETURNING
Are you still working on completing this? --- [EMAIL PROTECTED] wrote: Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani [ 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 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
Re: [PATCHES] INSERT ... RETURNING
Here here on this one. With the deprecation of oids on the horizon insert returning is to be extremely important. It's use with the uniqueidentifier mod would be really really helpful. On a similar note, is anyone working on the ability to have a column default as the product of a function on another column of the same row. I know this can be done as a trigger but something like CREATE TABLE abc (name text not null unique, hash not null default somehashfunc(name)); would be very convenient, and of course with the ability to get the product back with insert returning. Kevin McArthur - Original Message - From: Bruce Momjian pgman@candle.pha.pa.us To: [EMAIL PROTECTED] Cc: pgsql-patches@postgresql.org Sent: Friday, July 29, 2005 7:26 PM Subject: Re: [PATCHES] INSERT ... RETURNING Are you still working on completing this? --- [EMAIL PROTECTED] wrote: Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani [ 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 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Matt Miller wrote: This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. I also feel that this EXACT behavior is overall a bit cleaner than the current PL/pgSQL behavior. Maybe I've just been brainwashed by years of Oracle'ing. Here are three excerpts from the patched PL/pgSQL documentation: If the EXACT option is specified, then target will not be set unless the query returns exactly one row You can check the special FOUND variable after a SELECT INTO to determine whether the statement was successful. ... an EXACT query is successful only if exactly 1 row is returned. ...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2, indicating no matching rows, exactly one matching row, or greater than one matching row, respectively. [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- 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 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] PL/pgSQL: SELECT INTO EXACT
Sorry, patch removed from the queue. I now see the later discussion. --- Matt Miller wrote: This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. I also feel that this EXACT behavior is overall a bit cleaner than the current PL/pgSQL behavior. Maybe I've just been brainwashed by years of Oracle'ing. Here are three excerpts from the patched PL/pgSQL documentation: If the EXACT option is specified, then target will not be set unless the query returns exactly one row You can check the special FOUND variable after a SELECT INTO to determine whether the statement was successful. ... an EXACT query is successful only if exactly 1 row is returned. ...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2, indicating no matching rows, exactly one matching row, or greater than one matching row, respectively. [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- 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
Re: [PATCHES] Implement support for TCP_KEEPCNT, TCP_KEEPIDLE, TCP_KEEPINTVL
Bruce Momjian wrote: Is this the functionality we agreed we wanted? I think it covers Tom's comments on the first version: - a GUC to turn off SO_KEEPALIVE isn't particularly useful - don't do redundant get/setsockopt calls on backend startup -O ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
On Fri, Jul 29, 2005 at 11:24:37PM -0400, Bruce Momjian wrote:
Would someone who knows perl update plperl.sgml and send me a patch?
Also, is this still true in 8.1:
In the current implementation, if you are fetching or returning
very large data sets, you should be aware that these will all go
into memory.
That's no longer true. Please find enclosed a new patch :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.42
diff -c -r2.42 plperl.sgml
*** doc/src/sgml/plperl.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml30 Jul 2005 05:42:56 -
***
*** 46,52
para
To create a function in the PL/Perl language, use the standard
xref linkend=sql-createfunction endterm=sql-createfunction-title
!syntax:
programlisting
CREATE FUNCTION replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-type/replaceable AS $$
# PL/Perl function body
--- 46,57
para
To create a function in the PL/Perl language, use the standard
xref linkend=sql-createfunction endterm=sql-createfunction-title
!syntax. A PL/Perl function must always return a scalar value. You
!can return more complex structures (arrays, records, and sets)
!in the appropriate context by returning a reference.
!Never return a list. Here follows an example of a PL/Perl
!function.
!
programlisting
CREATE FUNCTION replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-type/replaceable AS $$
# PL/Perl function body
***
*** 282,288
/para
para
!PL/Perl provides two additional Perl commands:
variablelist
varlistentry
--- 287,293
/para
para
!PL/Perl provides three additional Perl commands:
variablelist
varlistentry
***
*** 293,303
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
listitem
para
!Executes an SQL command. Here is an example of a query
!(commandSELECT/command command) with the optional maximum
!number of rows:
programlisting
$rv = spi_exec_query('SELECT * FROM my_table', 5);
/programlisting
--- 298,315
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_fetchrow/(replaceablecommand/replaceable)/literal/term
+
listitem
para
!literalspi_exec_query/literal executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. emphasisYou should only use this command when you know
! that the result set will be relatively small./emphasis Here is an
! example of a query (commandSELECT/command command) with the
! optional maximum number of rows:
!
programlisting
$rv = spi_exec_query('SELECT * FROM my_table', 5);
/programlisting
***
*** 345,351
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv-gt;{status};
my $nrows = $rv-gt;{processed};
--- 357,363
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv-gt;{status};
my $nrows = $rv-gt;{processed};
***
*** 360,366
SELECT * FROM test_munge();
/programlisting
! /para
/listitem
/varlistentry
--- 372,416
SELECT * FROM test_munge();
/programlisting
! /para
! para
! literalspi_query/literal and literalspi_fetchrow/literal
! work together as a pair for rowsets which may be large, or for cases
! where you wish to return rows as they arrive.
! literalspi_fetchrow/literal works emphasisonly/emphasis with
! literalspi_query/literal. The following example illustrates how
! you use them together:
!
! programlisting
! CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
!
! CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
! use Digest::MD5 qw(md5_hex);
