[PATCHES] Blob .getBytes position should start at 1

2005-05-06 Thread Emmanuel Bernard
From the Javadoc of java.sql.Blob byte[] getBytes(long pos, int length) 
throws SQLException;

  * @param pos the ordinal position of the first byte in the
  *codeBLOB/code value to be extracted; the first byte is at
  *position 1
pqsql driver assumes the position starts from 0
Patch attached
--
Emmanuel Bernard
http://www.hibernate.org
callto://emmanuelbernard
Index: org/postgresql/jdbc2/AbstractJdbc2BlobClob.java
===
RCS file: 
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2BlobClob.java,v
retrieving revision 1.1
diff -u -r1.1 AbstractJdbc2BlobClob.java
--- org/postgresql/jdbc2/AbstractJdbc2BlobClob.java 28 Mar 2005 08:52:35 
-  1.1
+++ org/postgresql/jdbc2/AbstractJdbc2BlobClob.java 6 May 2005 09:59:42 
-
@@ -46,7 +46,8 @@
 
 public byte[] getBytes(long pos, int length) throws SQLException
 {
-lo.seek((int)pos, LargeObject.SEEK_SET);
+//throw an appropriate exception if pos  1
+lo.seek((int)pos-1, LargeObject.SEEK_SET);
 return lo.read(length);
 }
 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


[PATCHES] Nameless IPC on POSIX systems

2005-05-06 Thread Dag-Erling Smørgrav
The attached patch implements new semaphore and shared memory
mechanisms for POSIX systems.

Semaphores are implemented using unnamed pipes.  A semaphore is
incremented by writing a single character to the pipe, and decremented
by reading a single character.  The only semaphore operation we can't
reliably simulate in this manner is sem_getvalue(), but PostgreSQL
doesn't use it.

Shared memory is implemented using file-less (swap-backed) mmap(),
either with MAP_ANON on systems which support it, or with /dev/zero
(SysV-style).  Note that I've only tested this on systems which
support MAP_ANON, so there may be bugs in the /dev/zero code.

One system which will definitely benefit from this is FreeBSD.
FreeBSD has both SysV and POSIX semaphores and shared memory, but
unnamed POSIX semaphores can't be shared between processes, and POSIX
shared memory is implemented using plain files, so the POSIX
primitives can't be used.  The SysV primitives use a global namespace,
which causes problems when multiple PostgreSQL instances run in
separate jails (they can't run on the same port, and a compromised
postmaster in one jail can be used to crash postmasters in other
jails)

The patch was developed and tested on FreeBSD 6, and has also been
tested cursorily on SuSE Linux 9.2.  It passes 'make check', and osdb
(for what it's worth) shows no difference in performance between
patched and unpatched postmasters built from the same source.

Remember to run autoconf and configure before testing, as the patch
modifies configure.in and the FreeBSD and Linux templates.

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

Index: configure.in
===
RCS file: /home/pqcvs/pgsql/configure.in,v
retrieving revision 1.409
diff -u -u -r1.409 configure.in
--- configure.in	5 May 2005 19:15:54 -	1.409
+++ configure.in	6 May 2005 12:03:26 -
@@ -1240,20 +1240,26 @@
 if test x$USE_NAMED_POSIX_SEMAPHORES = x1 ; then
   AC_DEFINE(USE_NAMED_POSIX_SEMAPHORES, 1, [Define to select named POSIX semaphores.])
   SEMA_IMPLEMENTATION=src/backend/port/posix_sema.c
+elif test x$USE_UNNAMED_POSIX_SEMAPHORES = x1 ; then
+  AC_DEFINE(USE_UNNAMED_POSIX_SEMAPHORES, 1, [Define to select unnamed POSIX semaphores.])
+  SEMA_IMPLEMENTATION=src/backend/port/posix_sema.c
+elif test x$USE_PIPE_SEMAPHORES = x1 ; then
+  AC_DEFINE(USE_PIPE_SEMAPHORES, 1, [Define to select pipe()-based semaphores.])
+  SEMA_IMPLEMENTATION=src/backend/port/pipe_sema.c
 else
-  if test x$USE_UNNAMED_POSIX_SEMAPHORES = x1 ; then
-AC_DEFINE(USE_UNNAMED_POSIX_SEMAPHORES, 1, [Define to select unnamed POSIX semaphores.])
-SEMA_IMPLEMENTATION=src/backend/port/posix_sema.c
-  else
-AC_DEFINE(USE_SYSV_SEMAPHORES, 1, [Define to select SysV-style semaphores.])
-SEMA_IMPLEMENTATION=src/backend/port/sysv_sema.c
-  fi
+  AC_DEFINE(USE_SYSV_SEMAPHORES, 1, [Define to select SysV-style semaphores.])
+  SEMA_IMPLEMENTATION=src/backend/port/sysv_sema.c
 fi
 
 
 # Select shared-memory implementation type.
-AC_DEFINE(USE_SYSV_SHARED_MEMORY, 1, [Define to select SysV-style shared memory.])
-SHMEM_IMPLEMENTATION=src/backend/port/sysv_shmem.c
+if test x$USE_MMAP_SHARED_MEMORY = x1 ; then
+  AC_DEFINE(USE_MMAP_SHARED_MEMORY, 1, [Define to select mmap()-based shared memory.])
+  SHMEM_IMPLEMENTATION=src/backend/port/mmap_shmem.c
+else
+  AC_DEFINE(USE_SYSV_SHARED_MEMORY, 1, [Define to select SysV-style shared memory.])
+  SHMEM_IMPLEMENTATION=src/backend/port/sysv_shmem.c
+fi
 
 
 if test $enable_nls = yes ; then
Index: src/backend/port/mmap_shmem.c
===
RCS file: src/backend/port/mmap_shmem.c
diff -N src/backend/port/mmap_shmem.c
--- /dev/null	1 Jan 1970 00:00:00 -
+++ src/backend/port/mmap_shmem.c	6 May 2005 12:09:45 -
@@ -0,0 +1,138 @@
+/*-
+ *
+ * mmap_shmem.c
+ *	  Implement shared memory using mmap()
+ *
+ * Portions Copyright (c) 2005 Dag-Erling Coïdan Smørgrav
+ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  $PostgreSQL$
+ *
+ *-
+ */
+#include postgres.h
+
+#include sys/mman.h
+
+#include errno.h
+#include fcntl.h
+#include unistd.h
+
+#include miscadmin.h
+#include storage/ipc.h
+#include storage/pg_shmem.h
+
+#ifdef EXEC_BACKEND
+/* there is no way to reattach to a segment after exec */
+#error mmap()-based shared memory can not be combined with EXEC_BACKEND
+#endif /* EXEC_BACKEND */
+
+#if !defined(MAP_ANON)
+#if defined(MAP_ANONYMOUS)
+#define MAP_ANON MAP_ANONYMOUS
+#else
+static const char  *DevZero = /dev/zero;
+#endif
+#endif
+
+static int			ShmemFileDescriptor = -1;
+static void		   *ShmemSegmentAddress = NULL;
+static size_t		ShmemSegmentSize = 0;
+
+bool

Re: [PATCHES] Nameless IPC on POSIX systems

2005-05-06 Thread Tom Lane
[EMAIL PROTECTED] (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
 The attached patch implements new semaphore and shared memory
 mechanisms for POSIX systems.

I'm afraid we'll have to reject this out of hand:

 +bool
 +PGSharedMemoryIsInUse(unsigned long id1, unsigned long id2)
 +{
 +/*
 + * This is never the case when using mmap(), since the segments will
 + * vanish into thin air when postmaster exits or crashes.
 + */
 + return false;
 +}

This is not acceptable in the slightest, because it offers no protection
against the situation where the old postmaster has crashed but there are
still live backends.  If a new postmaster and new backends are allowed
to start in that situation, using a new shared memory segment, you
*will* have major database corruption (eg, duplicate use of transaction
IDs).  We need the SysV ability to detect whether any backends are still
connected to the old shared memory segment in order to be safe against
this scenario.

The semaphore code may be functionally OK, but I'm not thrilled with the
fact that it requires two open file descriptors per semaphore, which
have to be passed down to each postmaster child process.  That's a lot
of files if MaxBackends is large; not only does it constrain the number
of file slots available for fd.c to use, but you run the risk of
overflowing what an fd_set can handle, which I notice breaks this code
:-(.  For comparison, the Darwin implementation needs one descriptor per
semaphore, and we have seen performance issues with that.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] libpq

2005-05-06 Thread Bruce Momjian

PostgreSQL 8.0.3, to be released in a few days, will have a bcc32.mak
that will compile using Borland, and a DLL fix for WSACleanup().  Would
you please test that version?

Thanks.

---

Gabor Berenyi wrote:
 Hello Bruce and others,
 
 Now I send you the example makefiles (makefiles.zip),
 PLUS 
 a bug report (maybe_a_dll_bug.zip). I think it's not a
 libpq.dll bug, but a related one at least. I would
 very much appreciate your help.
 
 maybe_a_dll_bug.zip contains a simple project that
 dynamically loads libpq.dll and doesn't work.
 Wine says (under Linux): validation failed for
 vsinit.dll
 Win98 and XP say: error in MSVCRT.DLL
 Can you compile and run this simple project (it
 contains no Borland-specific code, at least not the
 Windows part)? Do I have the wrong DLLs? If yes, could
 you send me the correct ones, please?
 
 Thank you.
 With love, Gabor
 
 Bruce Momjian pgman@candle.pha.pa.us írta:
 
  Berényi Gábor wrote:
   Hello Bruce,
   The output: 29 rows, each consists of the single
 message file not 
   found.
   Should I send you a bcc makefile that works?  have
 one for my project 
   that the IDE created automatically - if you think
 it helps to understand 
   the syntax.
  
  Great, sure, send it over.
  
  -- 
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
  
 
 
   
 __ 
 Do you Yahoo!? 
 Yahoo! Small Business - Try our new resources site!
 http://smallbusiness.yahoo.com/resources/ 

Content-Description: makefiles.zip

[ Attachment, skipping... ]

Content-Description: maybe_a_dll_bug.zip

[ 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 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] [patch 0/6] pgcrypto update

2005-05-06 Thread Bruce Momjian


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.

---

Marko Kreen wrote:
 Here are various updates for pgcrypto that
 I've been sitting on for some time now.
 
 They should be applied in order, but otherwise
 they stand alone - code should in working state
 after each one.
 
 Next update is hopefully pgp_encrypt, which I
 have already working, only some final polishing
 is missing.
 
 -- 
 marko
 
 
 ---(end of broadcast)---
 TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
 

-- 
  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: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] [patch 0/6] pgcrypto update

2005-05-06 Thread Tom Lane
Bruce Momjian pgman@candle.pha.pa.us writes:
 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.

Neil applied all those some time ago, no?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PATCHES] [patch 0/6] pgcrypto update

2005-05-06 Thread Marko Kreen
On Fri, May 06, 2005 at 11:49:43AM -0400, Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  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.
 
 Neil applied all those some time ago, no?

Yes.

-- 
marko


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PATCHES] [patch 0/6] pgcrypto update

2005-05-06 Thread Bruce Momjian
Marko Kreen wrote:
 On Fri, May 06, 2005 at 11:49:43AM -0400, Tom Lane wrote:
  Bruce Momjian pgman@candle.pha.pa.us writes:
   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.
  
  Neil applied all those some time ago, no?
 
 Yes.

Yes, I see now.  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 4: Don't 'kill -9' the postmaster


Re: [PATCHES] [patch 0/6] pgcrypto update

2005-05-06 Thread Bruce Momjian

Already applied.

---

Marko Kreen wrote:
 Here are various updates for pgcrypto that
 I've been sitting on for some time now.
 
 They should be applied in order, but otherwise
 they stand alone - code should in working state
 after each one.
 
 Next update is hopefully pgp_encrypt, which I
 have already working, only some final polishing
 is missing.
 
 -- 
 marko
 
 
 ---(end of broadcast)---
 TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
 

-- 
  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 7: don't forget to increase your free space map settings


Re: [PATCHES] Nameless IPC on POSIX systems

2005-05-06 Thread Dag-Erling Smørgrav
Tom Lane [EMAIL PROTECTED] writes:
 This is not acceptable in the slightest, because it offers no protection
 against the situation where the old postmaster has crashed but there are
 still live backends.  If a new postmaster and new backends are allowed
 to start in that situation, using a new shared memory segment, you
 *will* have major database corruption (eg, duplicate use of transaction
 IDs).

I assumed the backends would terminate if postmaster crashed, and that
reattach was only necessary for the EXEC_BACKEND case.

You can use file-backed shared memory instead.  You need a directory
which you know is writeable and unique to this instance, on a file
system with enough free space to accomodate the full size of the
shared memory segment.  DataDir is probably a good choice.  If the
file does not exist, you create it at startup.  If it does exist, you
map it in and perform the same checks as in the SysV case.

 The semaphore code may be functionally OK, but I'm not thrilled with the
 fact that it requires two open file descriptors per semaphore, which
 have to be passed down to each postmaster child process.  That's a lot
 of files if MaxBackends is large; not only does it constrain the number
 of file slots available for fd.c to use, but you run the risk of
 overflowing what an fd_set can handle, which I notice breaks this code
 :-(.

#define FD_SETSIZE BIG_NUMBER

Anyway, I'm not sure you fully understand the problem this patch
addresses.  It is currently impractical if not impossible to run
PostgreSQL in jails on FreeBSD, because:

 - SysV IPC is normally not allowed in jails, and must be explicitly
   enabled.

 - the namespace is global, not per-jail, so separate instances in
   separate jails risk collision (I believe there is a workaround for
   this in 8.0, but I haven't tested it)

 - even if collision is avoided, SysV IPC breaches the separation
   between jails, allowing anyone who manages to compromise one jail
   to crash or corrupt any process using SysV IPC in any other jail on
   the system.

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Nameless IPC on POSIX systems

2005-05-06 Thread Tom Lane
[EMAIL PROTECTED] (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
 You can use file-backed shared memory instead.  You need a directory
 which you know is writeable and unique to this instance, on a file
 system with enough free space to accomodate the full size of the
 shared memory segment.  DataDir is probably a good choice.  If the
 file does not exist, you create it at startup.  If it does exist, you
 map it in and perform the same checks as in the SysV case.

The check we need is are there any other processes (still) attached to
this shmem and AFAIK that is not available in the mmap API.  Do you
know how to get it?

 Anyway, I'm not sure you fully understand the problem this patch
 addresses.

Yes, I do.  I'm not interested in substituting a risk of data corruption
for them.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Nameless IPC on POSIX systems

2005-05-06 Thread Dag-Erling Smørgrav
Tom Lane [EMAIL PROTECTED] writes:
 The check we need is are there any other processes (still) attached to
 this shmem and AFAIK that is not available in the mmap API.  Do you
 know how to get it?

You can hack something up with fcntl() locks.  If a process has a
shared lock on the shm file, F_GETLK will get you its pid.  Then grab
your own shared lock.

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PATCHES] Nameless IPC on POSIX systems

2005-05-06 Thread Tom Lane
[EMAIL PROTECTED] (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
 Tom Lane [EMAIL PROTECTED] writes:
 The check we need is are there any other processes (still) attached to
 this shmem and AFAIK that is not available in the mmap API.  Do you
 know how to get it?

 You can hack something up with fcntl() locks.  If a process has a
 shared lock on the shm file, F_GETLK will get you its pid.  Then grab
 your own shared lock.

Seems fairly race-condition-prone: what about recently spawned child
processes that haven't yet taken their own locks?  If I read the fork()
page correctly, a forked child doesn't inherit any file locks.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PATCHES] Cleaning up unreferenced table files

2005-05-06 Thread Heikki Linnakangas
On Thu, 5 May 2005, Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Applied.
Now that I've had a chance to look at it, this patch is thoroughly
broken.  Problems observed in a quick review:
1. It doesn't work at all for non-default tablespaces: it will
claim that every file in such a tablespace is stale.  The fact
that it does that rather than failing entirely is accidental.
It tries to read the database's pg_class in the target tablespace
whether it's there or not.  Because the system is still in recovery
mode, the low-level routines allow the access to the nonexistent
pg_class table to pass --- in fact they think they should create
the file, so after it runs there's a bogus empty 1259 file in each
such tablespace (which of course it complains about, too).  The code
then proceeds to think that pg_class is empty so of course everything
draws a warning.
2. It's not robust against stale subdirectories of a tablespace
(ie, subdirs corresponding to a nonexistent database) --- again,
it'll try to read a nonexistent pg_class.  Then it'll produce a
bunch of off-target complaint messages.
3. It's assuming that relfilenode is unique database-wide, when no
such assumption is safe.  We only have a guarantee that it's unique
tablespace-wide.
4. It fails to examine table segment files (such as nnn.1).  These
should be complained of when the nnn doesn't match any hash entry.
5. It will load every relfilenode value in pg_class into the hashtable
whether it's meaningful or not.  There should be a check on relkind.
6. I don't think relying on strtol to decide if a filename is entirely
numeric is very safe.  Note all the extra defenses in pg_atoi against
various platform-specific misbehaviors of strtol.  Personally I'd use a
strspn test instead.
I'll fix 1-6 according to your suggestions, and send another patch.
It shows how little experience I have with multiple database 
and tablespace management.

7. There are no checks for readdir failure (compare any other readdir
loop in the backend).
I couldn't figure out what you meant. The readdir code is the same as 
anywhere else. Also, man page (Linux) says that readdir returns NULL on 
error, and that is checked.

See also Simon Riggs' complaints that the circumstances under which it's
done are pretty randomly selected.  (One particular thing that I think
is a bad idea is to do this in a standalone backend.  Any sort of
corruption in any db's pg_class would render it impossible to start up.)
I'd agree with Simons complaints if we actually deleted the files. But 
since we only report them, it's a good idea to report them on every 
startup, otherwise the DBA might think that the stale files are not there 
anymore since the system isn't complaining about them anymore.

The original patch only ran the check on crash recovery, but Bruce changed 
it to run on startup as well, for the above reason.

I agree, though, that it's a bad idea to do it in standalone mode. I'll 
add a check for that. Also it probably shouldn't stop the startup even if 
some pg_class is corrupt. Other databases could be fine.

To fix the first three problems, and also avoid the performance problem
of multiply rescanning a database's pg_class for each of its
tablespaces, I would suggest that the hashtable entries be widened to
RelFileNode structs (ie, db oid, tablespace oid, relfilenode oid).  Then
there should be one iteration over pg_database to learn the OIDs and
default tablespaces of each database; with that you can read pg_class
from its correct location for each database and load all the entries
into the hashtable.  Then you iterate through the tablespaces looking
for stuff not present in the hashtable.  You might also want to build a
list or hashtable of known database OIDs, so that you can recognize a
stale subdirectory immediately and issue a direct complaint about it
without even recursing into it.
regards, tom lane
- Heikki
---(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


Re: [PATCHES] [HACKERS] read-only database

2005-05-06 Thread Bruce Momjian
Satoshi Nagayasu wrote:
 
 Tom Lane wrote:
  I'd view this as a postmaster state that propagates to backends.
  Probably you'd enable it by means of a postmaster option, and the
  only way to get out of it is to shut down and restart the postmaster
  without the option.
 
 I've created a patch to make a postmaster read-only.
 (attached patch can be applied to 8.0.1)
 
 Read-only state can be enabled/disabled by the postmaster option,
 or the postgresql.conf option.
 
 If you start the postmaster with -r options,
 the cluster will go to read-only.
 
 % pg_ctl -o -i -r -D $PGDATA start
 
 Or if you set readonly_cluster = true in the postgresql.conf,
 the cluster will also become read-only.

Nice idea.  I have attached a new patch which has a few adjustments.

First, we are moving away from using postmaster flags, and instead
encouraging people to use postgresql.conf, so I removed the -r flag but
added an entry in postgresql.conf for this.  I can see why it might be
nice to have it as a postmaster flag, but at a certain point the number
of flags gets too confusing so postgresql.conf is better.  Second, I
changed it so it can be modified by a sighup to the postmaster, which
can't be done with a postmaster flag.

Also, I renamed it to server_read_only because that seems more
consistent than readonly_cluster.

Also, I added documentation for this postgresql.conf variable.

With this change, I see we now have three read_only options:

transaction_read_only
default_transaction_read_only
server_read_only

The first one is not documented (should it be?) and I assume allows you
to query and change the READ ONLY status of a single transaction, while
default_transaction_read_only affects all new transactions for the
session, and server_read_only is for all transactions on the server.

It seems server_read_only is the same as default_transaction_read_only
except it can't be changed.  It seems more like a secure version of
default_transaction_read_only rather than something new.

If we set default_transaction_read_only to true in postgresql.conf,
could we just prevent that from being changed by a session.  As I
remember we have abandoned the idea of trying to limit session changes
to postgresql.conf values so maybe this is the way we have to go.

-- 
  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
Index: doc/src/sgml/runtime.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.315
diff -c -c -r1.315 runtime.sgml
*** doc/src/sgml/runtime.sgml   23 Apr 2005 03:27:40 -  1.315
--- doc/src/sgml/runtime.sgml   6 May 2005 22:48:41 -
***
*** 3224,3229 
--- 3224,3247 
/listitem
   /varlistentry
   
+  varlistentry id=guc-server-read-only xreflabel=server_read_only
+   indexterm
+primaryforce read-only transaction/primary
+   /indexterm
+   indexterm
+primaryvarnameserver_read_only/ configuration parameter/primary
+   /indexterm
+ 
+   termvarnameserver_read_only/varname (typeboolean/type)/term
+   listitem
+para
+ This parameter behaves just like varnamedefault_read_only/
+ except it can only be set from filenamepostgresql.conf/ and is 
server-wide.
+ The default is false (read/write).
+/para
+   /listitem
+  /varlistentry
+  
   varlistentry id=guc-statement-timeout xreflabel=statement_timeout
termvarnamestatement_timeout/varname (typeinteger/type)/term
indexterm
Index: src/backend/executor/execMain.c
===
RCS file: /cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.248
diff -c -c -r1.248 execMain.c
*** src/backend/executor/execMain.c 6 May 2005 17:24:53 -   1.248
--- src/backend/executor/execMain.c 6 May 2005 22:48:42 -
***
*** 130,136 
 * If the transaction is read-only, we need to check if any writes are
 * planned to non-temporary tables.
 */
!   if (XactReadOnly  !explainOnly)
ExecCheckXactReadOnly(queryDesc-parsetree);
  
/*
--- 130,136 
 * If the transaction is read-only, we need to check if any writes are
 * planned to non-temporary tables.
 */
!   if ( (XactReadOnly || ServerReadOnly)  !explainOnly)
ExecCheckXactReadOnly(queryDesc-parsetree);
  
/*
Index: src/backend/tcop/utility.c
===
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.236
diff -c -c -r1.236 utility.c
*** src/backend/tcop/utility.c  28 Apr 2005 21:47:15 - 

Re: [PATCHES] [HACKERS] read-only database

2005-05-06 Thread Tom Lane
Bruce Momjian pgman@candle.pha.pa.us writes:
 It seems server_read_only is the same as default_transaction_read_only
 except it can't be changed.

I thought the TODO item was for a low-level read-only option, suitable
for trying to look at a corrupted database or run off a read-only volume.
This is very far from being that --- it allows temp table creation/use,
and it still eats transaction IDs so it is certainly not read-only to
xlog or clog.

I am not sure I see any use case for this implementation: it is
read-only enough to get in your way, without being read-only enough
to derive any real benefit.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] [HACKERS] read-only database

2005-05-06 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  It seems server_read_only is the same as default_transaction_read_only
  except it can't be changed.
 
 I thought the TODO item was for a low-level read-only option, suitable
 for trying to look at a corrupted database or run off a read-only volume.
 This is very far from being that --- it allows temp table creation/use,
 and it still eats transaction IDs so it is certainly not read-only to
 xlog or clog.
 
 I am not sure I see any use case for this implementation: it is
 read-only enough to get in your way, without being read-only enough
 to derive any real benefit.

I am not sure I see the use case either but I developed it so everyone
could look at it and decide if it is useful.  When true, it is basically
a unchangable default_transaction_read_only.

-- 
  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: Don't 'kill -9' the postmaster


Re: [PATCHES] COPY CSV header line feature

2005-05-06 Thread Bruce Momjian

Applied.

---

pgman wrote:
 
 Here is an updated version of this patch, with documentation changes.
 
 I have already updated the gram.y comment you suggested.
 
 ---
 
 Andrew Dunstan wrote:
  
  ammended patch attached. sorry for the oversight. I agree with Tom's
  remark - it's far too easy to miss this.
  
  cheers
  
  andrew
  
  Alvaro Herrera wrote:
  
  On Tue, Mar 15, 2005 at 08:55:36PM -0600, Andrew Dunstan wrote:

  
  Alvaro Herrera said:
  
  
  On Sun, Mar 13, 2005 at 06:32:20PM -0500, Andrew Dunstan wrote:
  

  
  The attached patch implements the previously discussed header line
  feature for CSV mode COPY. It is triggered by the keyword HEADER
  (blame Bruce - he chose it ;-) ).
  
  
  I think you should add the new reserved keyword to the
  unreserved_keywords list or some other.

  
  Please be more specific. I'll be happy to add in anything I've missed.
  
  
  
  The Postgres grammar classifies keywords in one of several lists, in
  order to make them available as names to users (column names, function
  names, etc).  So each time you create a new keyword and add it to the
  keywords.c list, you have to add it to one of the lists on gram.y too.
  See gram.y line 7669 ff.
  
  I'd add a comment on this on gram.y:
  
  Index: gram.y
  ===
  RCS file: /home/alvherre/cvs/pgsql/src/backend/parser/gram.y,v
  retrieving revision 2.484
  diff -c -w -b -B -c -r2.484 gram.y
  *** gram.y 14 Mar 2005 00:19:36 -  2.484
  --- gram.y 16 Mar 2005 03:12:48 -
  ***
  *** 327,333 
/*
 * If you make any token changes, update the keyword table in
 * parser/keywords.c and add new keywords to the appropriate one of
  !  * the reserved-or-not-so-reserved keyword lists, below.
 */

/* ordinary key words in alphabetical order */
  --- 327,334 
/*
 * If you make any token changes, update the keyword table in
 * parser/keywords.c and add new keywords to the appropriate one of
  !  * the reserved-or-not-so-reserved keyword lists, below; search this
  !  * file for Name classification hierarchy.
 */

/* ordinary key words in alphabetical order */
  

  
 
 
  
  ---(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

 Index: doc/src/sgml/ref/copy.sgml
 ===
 RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v
 retrieving revision 1.63
 diff -c -c -r1.63 copy.sgml
 *** doc/src/sgml/ref/copy.sgml4 Jan 2005 00:39:53 -   1.63
 --- doc/src/sgml/ref/copy.sgml6 May 2005 03:36:30 -
 ***
 *** 24,34 
   COPY replaceable class=parametertablename/replaceable [ ( 
 replaceable class=parametercolumn/replaceable [, ...] ) ]
   FROM { 'replaceable class=parameterfilename/replaceable' | STDIN }
   [ [ WITH ] 
 !   [ BINARY ] 
 [ OIDS ]
 [ DELIMITER [ AS ] 'replaceable 
 class=parameterdelimiter/replaceable' ]
 [ NULL [ AS ] 'replaceable class=parameternull 
 string/replaceable' ]
 !   [ CSV [ QUOTE [ AS ] 'replaceable 
 class=parameterquote/replaceable' ] 
   [ ESCAPE [ AS ] 'replaceable 
 class=parameterescape/replaceable' ]
   [ FORCE NOT NULL replaceable 
 class=parametercolumn/replaceable [, ...] ]
   
 --- 24,35 
   COPY replaceable class=parametertablename/replaceable [ ( 
 replaceable class=parametercolumn/replaceable [, ...] ) ]
   FROM { 'replaceable class=parameterfilename/replaceable' | STDIN }
   [ [ WITH ] 
 !   [ BINARY ]
 [ OIDS ]
 [ DELIMITER [ AS ] 'replaceable 
 class=parameterdelimiter/replaceable' ]
 [ NULL [ AS ] 'replaceable class=parameternull 
 string/replaceable' ]
 !   [ CSV [ HEADER ]
 ! [ QUOTE [ AS ] 'replaceable 
 class=parameterquote/replaceable' ] 
   [ ESCAPE [ AS ] 'replaceable 
 class=parameterescape/replaceable' ]
   [ FORCE NOT NULL replaceable 
 class=parametercolumn/replaceable [, ...] ]
   
 ***
 *** 36,45 
   TO { 'replaceable class=parameterfilename/replaceable' | STDOUT }
   [ [ WITH ] 
 [ BINARY ]
 [ OIDS ]
 [ DELIMITER [ AS ] 'replaceable 
 class=parameterdelimiter/replaceable' ]
 [ NULL [ AS ] 'replaceable class=parameternull 
 string/replaceable' ]
 !  

[PATCHES] Please define PQ_BUFFER_SIZE in interfaces/libpq/fe-misc.c

2005-05-06 Thread Hideyuki Kawashima
Hi,

I am accelerating the transfer of resultset 
from backend to client using the zlib library.
While coding, I was surprised that 
magic number 8192 (which is defined as PQ_BUFFER_SIZE in 
backend/libpq/pqcomm.c) are written into the code 
in interfaces/libpq/fe-misc.

I thought the magic number should be eliminated.
So I defined the magic number 8192 as PQ_BUFFER_SIZE in fe-misc.c.
The result is attached to this mail.
# it is just a slight modification

I am very happy if you accept my proposition.

Best regards,

-- Hideyuki Kawashima
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Hideyuki KAWASHIMA [EMAIL PROTECTED] Ph.D. 
Research Associate, Keio University
/*-
 *
 *   FILE
 *  fe-misc.c
 *
 *   DESCRIPTION
 *   miscellaneous useful functions
 *
 * The communication routines here are analogous to the ones in
 * backend/libpq/pqcomm.c and backend/libpq/pqcomprim.c, but operate
 * in the considerably different environment of the frontend libpq.
 * In particular, we work with a bare nonblock-mode socket, rather than
 * a stdio stream, so that we can avoid unwanted blocking of the application.
 *
 * XXX: MOVE DEBUG PRINTOUT TO HIGHER LEVEL.  As is, block and restart
 * will cause repeat printouts.
 *
 * We must speak the same transmitted data representations as the backend
 * routines.
 *
 *
 * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
 * Portions Copyright (c) 1994, Regents of the University of California
 *
 * IDENTIFICATION
 *$PostgreSQL: pgsql/src/interfaces/libpq/fe-misc.c,v 1.112 2004/12/31 
22:03:50 pgsql Exp $
 *
 *-
 */

#include postgres_fe.h

#include errno.h
#include signal.h
#include time.h

#ifndef WIN32_CLIENT_ONLY
#include netinet/in.h
#include arpa/inet.h
#endif

#ifdef WIN32
#include win32.h
#else
#include unistd.h
#include sys/time.h
#endif

#ifdef HAVE_POLL_H
#include poll.h
#endif
#ifdef HAVE_SYS_POLL_H
#include sys/poll.h
#endif
#ifdef HAVE_SYS_SELECT_H
#include sys/select.h
#endif

#include libpq-fe.h
#include libpq-int.h
#include pqsignal.h
#include mb/pg_wchar.h

#define PQ_BUFFER_SIZE 8192

static int  pqPutMsgBytes(const void *buf, size_t len, PGconn *conn);
static int  pqSendSome(PGconn *conn, int len);
static int pqSocketCheck(PGconn *conn, int forRead, int forWrite,
  time_t end_time);
static int  pqSocketPoll(int sock, int forRead, int forWrite, time_t 
end_time);


/*
 * pqGetc: get 1 character from the connection
 *
 *  All these routines return 0 on success, EOF on error.
 *  Note that for the Get routines, EOF only means there is not enough
 *  data in the buffer, not that there is necessarily a hard error.
 */
int
pqGetc(char *result, PGconn *conn)
{
if (conn-inCursor = conn-inEnd)
return EOF;

*result = conn-inBuffer[conn-inCursor++];

if (conn-Pfdebug)
fprintf(conn-Pfdebug, From backend %c\n, *result);

return 0;
}


/*
 * pqPutc: write 1 char to the current message
 */
int
pqPutc(char c, PGconn *conn)
{
if (pqPutMsgBytes(c, 1, conn))
return EOF;

if (conn-Pfdebug)
fprintf(conn-Pfdebug, To backend %c\n, c);

return 0;
}


/*
 * pqGets:
 * get a null-terminated string from the connection,
 * and store it in an expansible PQExpBuffer.
 * If we run out of memory, all of the string is still read,
 * but the excess characters are silently discarded.
 */
int
pqGets(PQExpBuffer buf, PGconn *conn)
{
/* Copy conn data to locals for faster search loop */
char   *inBuffer = conn-inBuffer;
int inCursor = conn-inCursor;
int inEnd = conn-inEnd;
int slen;

while (inCursor  inEnd  inBuffer[inCursor])
inCursor++;

if (inCursor = inEnd)
return EOF;

slen = inCursor - conn-inCursor;

resetPQExpBuffer(buf);
appendBinaryPQExpBuffer(buf, inBuffer + conn-inCursor, slen);

conn-inCursor = ++inCursor;

if (conn-Pfdebug)
fprintf(conn-Pfdebug, From backend \%s\\n,
buf-data);

return 0;
}


/*
 * pqPuts: write a null-terminated string to the current message
 */
int
pqPuts(const char *s, PGconn *conn)
{
if (pqPutMsgBytes(s, strlen(s) + 1, conn))
return EOF;

if (conn-Pfdebug)
fprintf(conn-Pfdebug, To backend '%s'\n, s);

return 0;
}

/*
 * pqGetnchar:
 *  get a string of exactly len bytes in buffer s, no null termination
 */
int
pqGetnchar(char *s, size_t len, PGconn *conn)
{
if (len  0 || len  (size_t) (conn-inEnd - conn-inCursor))
return EOF;

memcpy(s, conn-inBuffer + 

Re: [PATCHES] Please define PQ_BUFFER_SIZE in interfaces/libpq/fe-misc.c

2005-05-06 Thread Tom Lane
Hideyuki Kawashima [EMAIL PROTECTED] writes:
 While coding, I was surprised that 
 magic number 8192 (which is defined as PQ_BUFFER_SIZE in 
 backend/libpq/pqcomm.c) are written into the code 
 in interfaces/libpq/fe-misc.

AFAICS the uses of 8192 in fe-misc.c have no relationship to
PQ_BUFFER_SIZE or indeed much of anything.  They are just arbitrary
amounts by which to increase the I/O buffer size.

Of course they are not *completely* arbitrary --- they are essentially
guesses about the buffering behavior of the kernel and TCP stack.
But they have nothing to do with the server-side PQ_BUFFER_SIZE.

 The result is attached to this mail.

In future please submit proposed changes as diff -c patches.
A complete file is useless because it cannot be applied without
risking overwriting other people's changes.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] Please define PQ_BUFFER_SIZE in

2005-05-06 Thread Hideyuki Kawashima
Thanks for teaching me about 8192 in fe-misc.c.
I recognized the 8192 in fe-misc.c does not relate to PQ_BUFFER_SIZE
in pqcomm.c, but I am pleased if someone revises the magic number.
And I am sorry for my incorrect format patch.
From next time, I will submit my proposition as diff -c patches.

-- Hideyuki Kawashima

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster