Saturday, 24 March 2007
|
| Syntax Error Marcel Boscher 03:28:33 |
| | Hey there,
ERROR: function round(double precision, integer, integer) does not exist
Any suggestions
Purpose is to generate a 4 digit errorcode 1st letter alpha - rest numeric
my source code:
DECLARE
chars nchar(26);
charpos INTEGER;
character nchar(4);
nentries INTEGER;
key INTEGER;
BEGIN
chars := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
charpos := Round((random() * (length(chars)-1)), 0, 0);
character := SubString(chars, charpos, 1) ;
nentries := 1;
WHILE (nentries > 0) LOOP
key := trunc(random() * 10000);
SELECT eCode = character + key
SELECT
INTO nentries
count(*)
FROM se_errorcode
WHERE se_errorcode.entry = eCode;
END LOOP;
INSERT
INTO se_errorcode (entry)
VALUES (eCode);
RETURN(eCode);
END;
Thx in advance
Marcel
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| |  18 answers | Add comment |
Saturday, 17 March 2007
|
| Slony-I on Windows Ezequias Rodrigues da Rocha 00:05:44 |
| | Hi list,
It is possible to put slony to run on windows ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda И melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
|
| | 3 answer | Add comment |
Monday, 5 March 2007
|
| Corrupted DB - Help Mark Liberman 10:13:26 |
| | Running postgres 8.1.2 on gentoo linux 2.6.14
Server crashed (not sure if due to pg operation). Upon rebooting server and trying to start postgres, get the following log entries:
<4983:2006-07-27 18:04:24 PDT>LOG: database system was interrupted at 2006-07-27 17:58:51 PDT
<4983:2006-07-27 18:04:24 PDT>LOG: checkpoint record is at 2A4/2A23F328
<4983:2006-07-27 18:04:24 PDT>LOG: redo record is at 2A4/2A23F328; undo record is at 0/0; shutdown FALSE
<4983:2006-07-27 18:04:24 PDT>LOG: next transaction ID: 70649528; next OID: 345957
<4983:2006-07-27 18:04:24 PDT>LOG: next MultiXactId: 1742; next MultiXactOffset: 3498
<4983:2006-07-27 18:04:24 PDT>LOG: database system was not properly shut down; automatic recovery in progress
<4983:2006-07-27 18:04:24 PDT>LOG: redo starts at 2A4/2A23F36C
<4983:2006-07-27 18:04:24 PDT>WARNING: could not truncate relation 1663/128873/129263 to 398 blocks: Success
<4984:2006-07-27 18:04:24 PDT>LOG: connection received: host=[local]
<4984:2006-07-27 18:04:24 PDT>FATAL: the database system is starting up
<4981:2006-07-27 18:04:24 PDT>DEBUG: forked new backend, pid=4984 socket=7
<4981:2006-07-27 18:04:24 PDT>DEBUG: server process (PID 4984) exited with exit code 0
<4985:2006-07-27 18:04:25 PDT>LOG: connection received: host=[local]
<4985:2006-07-27 18:04:25 PDT>FATAL: the database system is starting up
<4981:2006-07-27 18:04:25 PDT>DEBUG: forked new backend, pid=4985 socket=7
<4981:2006-07-27 18:04:25 PDT>DEBUG: server process (PID 4985) exited with exit code 0
<4983:2006-07-27 18:04:26 PDT>PANIC: could not open relation 1663/128873/129201: No such file or directory
<4981:2006-07-27 18:04:26 PDT>LOG: startup process (PID 4983) was terminated by signal 6
<4981:2006-07-27 18:04:26 PDT>LOG: aborting startup due to startup process failure
<4982:2006-07-27 18:04:26 PDT>LOG: logger shutting down
The last commmand that I did right before the crash was a vacuum full on a very small table. After it completed, I exited psql, tried a non-pg command and it hung up. Here are the last log entries before the crash:
<1054:2006-07-27 18:00:18 PDT>LOG: statement: vacuum full verbose port_etr_state_offline;
<1054:2006-07-27 18:00:18 PDT>INFO: vacuuming "public.port_etr_state_offline"
<1054:2006-07-27 18:00:18 PDT>INFO: "port_etr_state_offline": found 0 removable, 213 nonremovable row versions in 16 pages
<1054:2006-07-27 18:00:18 PDT>DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 45 to 45 bytes long.
There were 2445 unused item pointers.
Total free space (including removable row versions) is 109896 bytes.
12 pages are or will become empty, including 0 at the end of the table.
16 pages containing 109896 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
<1054:2006-07-27 18:00:18 PDT>INFO: "port_etr_state_offline": moved 213 row versions, truncated 16 to 2 pages
<1054:2006-07-27 18:00:18 PDT>DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
<1054:2006-07-27 18:00:18 PDT>LOG: duration: 16.078 ms
Any way to salvage this db?
Thanks,
Mark
|
| | 2 answer | Add comment |
Sunday, 28 January 2007
|
| initdb failure John Wilcher 00:38:30 |
| | Hi!
I received the following error after attempting to initialize the database
within Cygwin.
selecting default shared_buffers... Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
Signal 12
50
creating configuration files... ok
creating template1 database in /user/local/pgsql/data/ba
initdb: failed
initdb: removing data directory "/user/local/pgsql/data"
Any help is appreciated. Here is my configuration dump:
$ cygcheck -s
Cygwin Configuration Diagnostics
Current System Time: Fri Aug 06 10:48:18 2004
Windows XP Home Edition Ver 5.1 Build 2600
Path: C:\cygwin\usr\local\bin
C:\cygwin\bin
C:\cygwin\bin
C:\cygwin\usr\X11R6\bin
c:\WINDOWS\system32
c:\WINDOWS
c:\WINDOWS\System32\Wbem
c:\Program Files\PuTTY
c:\java\apache-ant-1.6.1\bin
c:\java\j2sdk1.4.2_04
.\
Output from C:\cygwin\bin\id.exe (nontsec)
UID: 1006(John) GID: 513(None)
513(None)
Output from C:\cygwin\bin\id.exe (ntsec)
UID: 1006(John) GID: 513(None)
0(root) 513(None)
544(Administrators) 545(Users)
SysDir: C:\WINDOWS\System32
WinDir: C:\WINDOWS
HOME = `C:\cygwin\home\John'
MAKE_MODE = `unix'
PWD = `/home/John'
USER = `John'
Use `-r' to scan registry
c: hd NTFS 28576Mb 28% CP CS UN PA FC
d: hd FAT 62Mb 67% CP UN FLASH DISK
e: cd N/A N/A
C:\cygwin / system binmode
C:\cygwin/bin /usr/bin system binmode
C:\cygwin/lib /usr/lib system binmode
. /cygdrive system binmode,cygdrive
Found: C:\cygwin\bin\awk.exe
Found: C:\cygwin\bin\bash.exe
Found: C:\cygwin\bin\cat.exe
Found: C:\cygwin\bin\cp.exe
Not Found: cpp (good!)
Found: C:\cygwin\bin\find.exe
Not Found: gcc
Not Found: gdb
Found: C:\cygwin\bin\grep.exe
Not Found: ld
Found: C:\cygwin\bin\ls.exe
Not Found: make
Found: C:\cygwin\bin\mv.exe
Found: C:\cygwin\bin\rm.exe
Found: C:\cygwin\bin\sed.exe
Found: C:\cygwin\bin\sh.exe
Found: C:\cygwin\bin\tar.exe
61k 2003/08/09 C:\cygwin\bin\cygbz2-1.dll
7k 2003/10/19 C:\cygwin\bin\cygcrypt-0.dll
841k 2004/03/17 C:\cygwin\bin\cygcrypto-0.9.7.dll
45k 2001/04/25 C:\cygwin\bin\cygform5.dll
35k 2002/01/09 C:\cygwin\bin\cygform6.dll
48k 2003/08/09 C:\cygwin\bin\cygform7.dll
28k 2003/07/20 C:\cygwin\bin\cyggdbm-3.dll
30k 2003/08/11 C:\cygwin\bin\cyggdbm-4.dll
19k 2003/03/22 C:\cygwin\bin\cyggdbm.dll
15k 2003/07/20 C:\cygwin\bin\cyggdbm_compat-3.dll
15k 2003/08/11 C:\cygwin\bin\cyggdbm_compat-4.dll
107k 2004/07/06 C:\cygwin\bin\cyggettextlib-0-14-1.dll
17k 2004/07/06 C:\cygwin\bin\cyggettextpo-0.dll
190k 2004/07/06 C:\cygwin\bin\cyggettextsrc-0-14-1.dll
17k 2001/06/28 C:\cygwin\bin\cyghistory4.dll
29k 2003/08/10 C:\cygwin\bin\cyghistory5.dll
991k 2004/07/06 C:\cygwin\bin\cygiconv-2.dll
22k 2001/12/13 C:\cygwin\bin\cygintl-1.dll
37k 2003/08/10 C:\cygwin\bin\cygintl-2.dll
54k 2004/07/06 C:\cygwin\bin\cygintl-3.dll
26k 2001/04/25 C:\cygwin\bin\cygmenu5.dll
20k 2002/01/09 C:\cygwin\bin\cygmenu6.dll
29k 2003/08/09 C:\cygwin\bin\cygmenu7.dll
156k 2001/04/25 C:\cygwin\bin\cygncurses++5.dll
175k 2002/01/09 C:\cygwin\bin\cygncurses++6.dll
226k 2001/04/25 C:\cygwin\bin\cygncurses5.dll
202k 2002/01/09 C:\cygwin\bin\cygncurses6.dll
224k 2003/08/09 C:\cygwin\bin\cygncurses7.dll
15k 2001/04/25 C:\cygwin\bin\cygpanel5.dll
12k 2002/01/09 C:\cygwin\bin\cygpanel6.dll
19k 2003/08/09 C:\cygwin\bin\cygpanel7.dll
62k 2003/12/11 C:\cygwin\bin\cygpcre-0.dll
63k 2003/04/11 C:\cygwin\bin\cygpcre.dll
9k 2003/12/11 C:\cygwin\bin\cygpcreposix-0.dll
61k 2003/04/11 C:\cygwin\bin\cygpcreposix.dll
108k 2001/06/28 C:\cygwin\bin\cygreadline4.dll
148k 2003/08/10 C:\cygwin\bin\cygreadline5.dll
171k 2004/03/17 C:\cygwin\bin\cygssl-0.9.7.dll
61k 2003/12/04 C:\cygwin\bin\cygz.dll
1126k 2004/05/26 C:\cygwin\bin\cygwin1.dll
Cygwin DLL version info:
DLL version: 1.5.10
DLL epoch: 19
DLL bad signal mask: 19005
DLL old termios: 5
DLL malloc env: 28
API major: 0
API minor: 116
Shared data: 4
DLL identifier: cygwin1
Mount registry: 2
Cygnus registry name: Cygnus Solutions
Cygwin registry name: Cygwin
Program options name: Program Options
Cygwin mount registry name: mounts v2
Cygdrive flags: cygdrive flags
Cygdrive prefix: cygdrive prefix
Cygdrive default prefix:
Build date: Tue May 25 22:07:00 EDT 2004
CVS tag: cr-0x5e6
Shared id: cygwin1S4
Cygwin Package Information
Package Version
_update-info-dir 00227-1
ash 20040127-1
base-files 2.6-1
base-passwd 1.1-1
bash 2.05b-16
bzip2 1.0.2-5
crypt 1.1-1
cygrunsrv 1.0-1
cygwin 1.5.10-3
diffutils 2.8.7-1
editrights 1.01-1
fileutils 4.1-2
findutils 4.1.7-4
gawk 3.1.4-3
gdbm 1.8.3-7
grep 2.5-1
groff 1.18.1-2
gzip 1.3.5-1
less 381-1
libbz2_1 1.0.2-5
libgdbm 1.8.0-5
libgdbm-devel 1.8.3-7
libgdbm3 1.8.3-3
libgdbm4 1.8.3-7
libgettextpo0 0.14.1-1
libiconv2 1.9.2-1
libintl1 0.10.40-1
libintl2 0.12.1-3
libintl3 0.14.1-1
libncurses5 5.2-1
libncurses6 5.2-8
libncurses7 5.3-4
libpcre 4.1-1
libpcre0 4.5-1
libreadline4 4.1-2
libreadline5 4.3-5
login 1.9-7
man 1.5k-3
mktemp 1.5-3
ncurses 5.3-4
openssl 0.9.7d-1
postgresql 7.4.3-1
readline 4.3-5
sed 4.0.9-2
sh-utils 2.0.15-4
tar 1.13.25-5
termcap 20021106-2
terminfo 5.3_20030726-1
texinfo 4.2-4
textutils 2.0.21-1
which 1.5-2
zlib 1.2.1-1
Use -h to see help about each section
Thanks!
John Wilcher
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
| | 5 answers | Add comment |
|
| DROP FUNCTION failure: cache lookup failed for relation X Michael Fuhr 00:38:30 |
| | I've found a situation that causes DROP FUNCTION to fail (tested
in 8.1.6, 8.2.1, and 8.3devel):
CREATE TABLE foo (id integer);
CREATE FUNCTION foofunc() RETURNS trigger AS $$
BEGIN
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Then in concurrent sessions:
A: BEGIN;
A: CREATE TRIGGER footrig BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foofunc();
B: DROP TABLE foo; -- blocks pending A's commit
A: COMMIT; -- B's DROP TABLE completes
A: SELECT tgrelid FROM pg_trigger WHERE tgname = 'footrig';
tgrelid
---------
66153
(1 row)
A: DROP FUNCTION foofunc();
ERROR: cache lookup failed for relation 66153
Apparently the row in pg_trigger that A committed wasn't deleted
by B's DROP TABLE, presumably because B didn't have visibility to
to the trigger when its DROP TABLE statement began. This case is
admittedly contrived but I did stumble across it in a test environment.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 2 answer | Add comment |
|
| Change draft gmake control Bruce Momjian 00:38:30 |
| | I had to change the draft flag for SGML from 'gmake draft html' to
'gmake DRAFT=Y html'. Internally the code used to recurse with DRAFT=Y,
but I found there is no way to exit the makefile after the recursion
returned, so I had to use this new syntax. Though more cumbersome, it
is more logical because DRAFT is really a modifier, not a rule itself.
For example, 'gmake html draft' would never have worked. Patch attached
and applied.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 8 answers | Add comment |
|
| [pgsql-patches] Ctid chain following enhancement Pavan Deolasee 00:17:11 |
| |
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 3 answer | Add comment |
|
| [pgsql-patches] minor regression test refactor Neil Conway 00:10:18 |
| | I noticed that the "bigcheck" and "bigtest" targets of
src/test/regress/GNUMakefile don't have the right dependencies:
$ make -C src/test/regress clean bigcheck
[...]
make: ./pg_regress: Command not found
make: *** [bigcheck] Error 127
Attached is a patch that fixes this, by making "bigcheck" and "bigtest"
depend on the "all" target. Along the way, I removed some old Makefile
targets that were just aliases for existing targets, and refactored the
code for setting up the tablespace directory to avoid repeating it five
times.
Barring any objections, I'll apply this tomorrow.
-Neil
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
|
| Re: crash on 8.2 and cvshead - failed to add item to the Stefan Kaltenbrunner 00:03:02 |
| | Gregory Stark wrote:> "Joe Conway" <mail@joeconway.com> writes:>
psql:/home/jconway/pgsql/das_data_load_failure2.sql:419: PANIC: failed>> to add item to the left sibling for "pk_status_log_2007_01_4_10"> Was this preceded by a WARNING? > Was the server running with a log_min_messages low enough to log WARNINGs?> I probably can't help find the bug but I can see that would be helpful as> there are three branches of the code that can result in this and two of them> log warnings before returning the invalid offset which causes the panic.
FWIW I can reproduce the crash on 8.2 and I don't get a WARNING either ...
Stefan
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 14 answers | Add comment |
Saturday, 27 January 2007
|
| [pgsql-patches] pltcl/plython fixes for spi_prepare types Andrew Dunstan 22:47:35 |
| |
Here's a patch along the same lines as the fix for plperl committed
earlier today, that allows passing type aliases to spi_prepare as well
as types named in pg_type. It also removes the mention of the previous
limitation in the pltcl docs. Unlike the plperl and pltcl cases, I
didn't use the simpler form that Tom suggested for plpython, as that
code wants to get hold of the HeapTuple. If anyone wants to tidy that up
some, feel free. Also, some regression tests from those with more tcl-fu
or python-fu that I have would be nice.
I'll apply this in a day or two unless there's an objection.
cheers
andrew
---------------------------(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
|
| | Add comment |
|
| Re: Predicted lifespan of different PostgreSQL Bruce Momjian 21:51:17 |
| | Devrim GUNDUZ wrote:
-- Start of PGP signed section.> Hi,>
On Sat, 2007-01-27 at 08:41 +0000, Dave Page wrote:> > What do people think about that? Does anyone think it would be an> > unreasonable policy? > I don't think so. You should build 8.0 binaries until the community> stops maintaining PostgreSQL 8.0.> This is what we do for RPMs -- I know it is really a lot of work to> maintain older releases but as a community packager, it is not my "job"> to force people to upgrade to new version by not supplying older> binaries.
Agreed, and I do believe we should be supporting releases as far back as
reasonably possible.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 2 answer | Add comment |
|
| windows/ecpg regression tests failing Andrew Dunstan 21:35:42 |
| |
We seem to have 2 ECPG regression failures on Windows - see below, taken
from buildfarm log. Can we either fix ecpg or fix the expected results?
cheers
andrew
*** expected/compat_informix-dec_test-MinGW32.stdoutSat Jan 27 02:34:46 2007
--- results/compat_informix-dec_test.stdoutSat Jan 27 02:59:35 2007
***************
*** 69,75 ****
dec[6,7]: 0.00 (r: 0 - cmp: 1)
dec[6,8]: 0 (r: 0)
dec[6,9]: 0.00 (r: 0 - cmp: 1)
- (errno == PGTYPES_NUM_UNDERFLOW) - dec[6,10]: 0 (r: -1)
dec[7,1]: r: 0, 0.001
dec[7,2]: r: 0, 0
--- 69,74 ----
*** expected/pgtypeslib-num_test2-MinGW32.stdoutSat Jan 27 02:34:46 2007
--- results/pgtypeslib-num_test2.stdoutSat Jan 27 02:59:43 2007
***************
*** 88,94 ****
num[6,7]: 0.00 (r: 0 - cmp: 1)
num[6,8]: 0 (r: 0)
num[6,9]: 0.00 (r: 0 - cmp: 1)
- (errno == PGTYPES_NUM_UNDERFLOW) - num[6,10]: 0 (r: -1)
num[6,11]: - (r: 0)
num[6,12]: 0.00 (r: 0 - cmp: 0)
--- 88,93 ----
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
|
| Stats collector frozen? Jeremy Haile 21:35:06 |
| | I've noticed that my tables are not being auto vacuumed or analyzed
regularly, even though I have very aggressive autovacuum settings.
The stats collector appears to still be running, since I can see a
postgres.exe process with -forkcol. However, I never notice it using
I/O or CPU usage. Also, querying the pg_stat_user_tables view shows no
change in table stats even for tables that change very frequently.
I see error such as these in the log every now and then - not sure if
they are related. These have been discussed at length in other posts
and seems to have something to do with PG holding onto old file handles
(Windows specific):
2007-01-24 06:24:16 ERROR: could not open relation 1663/16404/333779:
Permission denied
2007-01-24 06:24:17 ERROR: could not open relation 1663/16404/333779:
Permission denied
2007-01-24 06:24:18 ERROR: could not open relation 1663/16404/333779:
Permission denied
2007-01-24 06:24:19 ERROR: could not open relation 1663/16404/333779:
Permission denied
I'm running PG 8.2.1 on Windows. Here is some of the output from "show
all":
"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.02"
"autovacuum_analyze_threshold";"250"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_naptime";"1min"
"autovacuum_vacuum_cost_delay";"-1"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.08"
"autovacuum_vacuum_threshold";"500"
"stats_block_level";"on"
"stats_command_string";"on"
"stats_reset_on_server_start";"off"
"stats_row_level";"on"
"stats_start_collector";"on"
"vacuum_cost_delay";"20ms"
"vacuum_cost_limit";"200"
"vacuum_cost_page_dirty";"20"
"vacuum_cost_page_hit";"1"
"vacuum_cost_page_miss";"10"
"vacuum_freeze_min_age";"100000000"
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 36 answers | Add comment |
|
| Re: How to configure Postgres to make it not to use (load) Joshua D. Drake 21:02:33 |
| | Tom Dong wrote:> Sorry for the spam. I am not sure if the email I sent earlier went> though as it was before I signed up for this email list.>
Hi,> I am looking for a way via configuration to make Postgres> not to use the openssl lib libeay32.dll as I need to delete that> library. I basically need to remove any encryption (hash is fine)> features (buid-in functions are ok) from my Postgres (8.x) installation.> This is quite urgent for me. I would be very grateful if someone in> this communicate can reply to this email and help me.
You can download the source and recompile without the ssl flag.
Sincerely,
Joshua D. Drake
Thanks!>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | Add comment |
|
| How to configure Postgres to make it not to use (load) opensll crypto libraries. Tom Dong 20:57:13 |
| | Sorry for the spam. I am not sure if the email I sent earlier went
though as it was before I signed up for this email list.
Hi,
I am looking for a way via configuration to make Postgres
not to use the openssl lib libeay32.dll as I need to delete that
library. I basically need to remove any encryption (hash is fine)
features (buid-in functions are ok) from my Postgres (8.x) installation.
This is quite urgent for me. I would be very grateful if someone in
this communicate can reply to this email and help me.
Thanks!
Tom
|
| | Add comment |
|
| Modifying and solidifying contrib Joshua D. Drake 20:34:12 |
| | Hello,
With all the recent discussion on contrib modules etc.. I would like to
offer the following suggestion. I am willing to do a good portion of the
work myself and I can get it done before feature freeze. I will need
help with the global make file stuff however so that is one dependency.
Add directory /modules
Modules are compiled and installed by default but not enabled.
Modules in 8.3 currently are:
pgrowlocks
pg_freespacemap (to be renameed pgfreespacemap to be consistent)
pgstattuple
pgcrypto
xml2
pgbuffercache
initagg
Requirements for /modules
Must go through normal vetting process on -hackers
Must include patches to core documentation in Docbook
Must include test cases? I don't recall if we have regress for all
contrib stuff.
Keep directory contrib
Contrib is not compiled or installed by default
Contrib in 8.3 would be:
start-scripts
pgbench (which I think really should be a foundry project)
vacuumlo (is this even required anymore?)
adminpack
btree_gist
etc...
Requirements for /contrib
Must go through normal vetting process on -hackers
Must includes README
Must include test cases? Same questions for modules
Thoughts, flames?
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 2 answer | Add comment |
|
| Enable/Disable Trigger per single transaction Derrick Betts 20:15:45 |
| | Assuming we have well over 1000 users accessing the database through a web
application.
We have a row-level trigger set up on table "alpha" that gets executed after
updates.
I was wondering if there is a way to temporarily disable a trigger on table
"alpha" for a single user, even though there may be 999 other users sending
transactions to the database that need the trigger to remain in place? For
example, I only want the trigger disabled for one single transaction (using
commands inside that transaction) while the trigger still gets fired
properly for the other 999 transactions that hit the database
simultaneously.
Is this possible at present?
Thank you for your insights,
Derrick
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 1 answer | Add comment |
|
| Predicted lifespan of different PostgreSQL branches Bill Moran 20:14:47 |
| |
I spend some time googling this and searching the Postgresql.org site, but
I'm either not good enough with the search strings, or it's not to be found.
I'm trying to plan upgrades so that we don't upgrade needlessly, but also
don't get caught using stuff that nobody's supporting any more.
The FreeBSD project keeps this schedule:
http://www.freebsd.org/security/#adv
which is _really_ nice when talking to managers and similar people about
when upgrades need to be scheduled.
Does the PostgreSQL project have any similar policy about EoLs? Even just
a simple statement like, "it is our goal to support major branches for 2
years after release" or some such?
--
Bill Moran
Collaborative Fusion Inc.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 9 answers | Add comment |
|
| Postgresql 8.1: plperl code works with LATIN1, fails with UTF8 Philippe Lang 19:34:08 |
| | Hi,
I've got plperl code that works just fine when the database is encoded using LATIN1, but fails as soon as I switch to UTF8.
I've been testing PG 8.1.4 under Linux, and PG 8.1.6 under FreeBSD, both behave exactly the save.
I'm sorry I'm not able to strip down the code, and show you a small test, but if anyone need the full script, feel free to ask me per email.
The code is made up of plperl routines, all structured in the same way, but only one of them fails in UTF8. It is:
#----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.volets_fiche_fab_1 (
INid_commandeint4,
OUT posint4,
OUT quantint4,
OUT nbre_vtxint4,
OUT nbre_vtx_totalint4,
OUT larg_maconnerieint4,
OUT haut_maconnerieint4,
OUT larg_vtx varchar(20),
OUT haut_vtxint4,
OUT ouv int4,
OUT couvre_joints text,
OUT coupe_verticale text,
OUT vide_interieur varchar(20),
OUT typvarchar(20)
)
RETURNS SETOF record
AS
$$
BEGIN { strict->import(); }
#----------------------------------------------------------------------------
#-- Lexical variables
#----------------------------------------------------------------------------
my @i;
my @io;
my @o;
my $i;
my $io;
my $o;
my %input;
my %output;
my $fab;
my $fab_nrows;
my $lignes_query;
my $lignes;
my $lignes_nrows;
my $lignes_rn;
my $c;
my $j;
my $key;
my $value;
my $ordre;
my $vtxg;
my $vtxd;
#----------------------------------------------------------------------------
#-- Helper functions
#----------------------------------------------------------------------------
my $init = sub
{
$c = 0;
foreach $i (@i) {$input{$i} = @_[$c++]};
foreach $io (@io) {$input{$io} = @_[$c]; $output{$io} = @_[$c++]};
foreach $o (@o) {$output{$o} = @_[$c++]};
};
my $start_sub = sub
{
&$init(@_);
};
my $end_sub = sub
{
return undef;
};
my $ret = sub
{
while (($key, $value) = each %output) {if (!defined($value)) {elog(ERROR, 'Valeur ind finie pour ' . $key)}};
return_next \%output;
&$init(@_);
};
#----------------------------------------------------------------------------
#-- Configuration des param tres de la fonction
#----------------------------------------------------------------------------
@i = ( 'id_commande'
);
@io = ();
@o = ( 'pos',
'quant',
'nbre_vtx',
'nbre_vtx_total',
'larg_maconnerie',
'haut_maconnerie',
'larg_vtx',
'haut_vtx',
'ouv',
'couvre_joints',
'coupe_verticale',
'vide_interieur',
'typ'
);
#----------------------------------------------------------------------------
#-- Pr paration des param tres de la fonction
#----------------------------------------------------------------------------
&$start_sub(@_);
#----------------------------------------------------------------------------
#-- Cr ation de la fiche de fabrication
#----------------------------------------------------------------------------
$lignes_query = 'SELECT * FROM lignes WHERE id_commande = ' . $input{'id_commande'} . ' ORDER BY pos;';
$lignes = spi_exec_query($lignes_query);
$lignes_nrows = $lignes->{processed};
foreach $lignes_rn (0 .. $lignes_nrows - 1)
{
# Fabrication de la ligne
$fab = spi_exec_query('SELECT * FROM volets_fab(' . $lignes->{rows}[$lignes_rn]->{'id'} . ');');
$fab_nrows = $fab->{processed};
# Recherches des ventuels vantaux de gauche et droite
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail gauche') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $vtxg = $fab->{rows}[$j]->{'larg'}; }
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail droite') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $vtxd = $fab->{rows}[$j]->{'larg'}; }
# Position
$output{'pos'} = $lignes->{rows}[$lignes_rn]->{'pos'};
# Quantit
$output{'quant'} = $lignes->{rows}[$lignes_rn]->{'quant'};
# Nombre de vantaux
$output{'nbre_vtx'} = $lignes->{rows}[$lignes_rn]->{'nbre_vtx'};
# Nombre de vantaux total
$output{'nbre_vtx_total'} = $lignes->{rows}[$lignes_rn]->{'nbre_vtx'} * $lignes->{rows}[$lignes_rn]->{'quant'};
# Largeur de ma onnerie
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de ma onnerie') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'larg_maconnerie'} = $fab->{rows}[$j]->{'larg'}; }
else { $output{'larg_maconnerie'} = ''; };
# Hauteur de ma onnerie
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de ma onnerie') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'haut_maconnerie'} = $fab->{rows}[$j]->{'haut'}; }
else { $output{'haut_maconnerie'} = ''; };
# Largeur de vantail
if (defined($vtxg) and defined($vtxd))
{
# Vantaux asym triques
$output{'larg_vtx'} = $vtxg . " / " . $vtxd;
}
else
{
# Vantaux sym triques
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'larg_vtx'} = $fab->{rows}[$j]->{'larg'}; }
else { $output{'larg_vtx'} = ''; };
}
# Hauteur de vantail
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de vantail') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'haut_vtx'} = $fab->{rows}[$j]->{'haut'}; }
else { $output{'haut_vtx'} = ''; };
# Type d'ouverture
$output{'ouv'} = $lignes->{rows}[$lignes_rn]->{'ouv'};
# Image des couvre-joints
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Couvre-joints') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'couvre_joints'} = $fab->{rows}[$j]->{'image'}; }
else { $output{'couvre_joints'} = ''; };
# Image de la coupe verticape
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Coupe verticale') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'coupe_verticale'} = $fab->{rows}[$j]->{'image'}; }
else { $output{'coupe_verticale'} = ''; };
# Vide int rieur
if (defined($vtxg) and defined($vtxd))
{
# Vantaux asym triques
$output{'vide_interieur'} = ($vtxg - 106) . " / " . ($vtxd - 106);
}
else
{
# Vantaux sym triques
for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail') and ($j < $fab_nrows); $j = $j + 1) {};
if ($j < $fab_nrows) { $output{'vide_interieur'} = $fab->{rows}[$j]->{'larg'} - 106; }
else { $output{'vide_interieur'} = ''; };
}
# Type de volet
$output{'typ'} = $lignes->{rows}[$lignes_rn]->{'typ'};
# Sortie
&$ret(@_);
}
#----------------------------------------------------------------------------
#-- Fin de la fonction
#----------------------------------------------------------------------------
&$end_sub(@_);
$$
LANGUAGE 'plperl' VOLATILE;
#----------------------------------------------------------------------------
When running:
-------------
select * from volets_fiche_fab_1(1)
Database replies:
-----------------
ERROR: error from Perl function: invalid input syntax for integer: "" at line 54.
SQL state: XX000
Does anyone have a small idea where to search?
Thanks
Philippe Lang
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 1 answer | Add comment |
|
| Converting 7.x to 8.x Carlos 18:45:16 |
| | What would be the faster way to convert a 7.4.x database into an 8.x
database? A dump of the database takes over 20 hours so we want to convert
the database without having to do a dump and resptore.
|
| | 6 answers | Add comment |
|
| Proposal: Commit timestamp Jan Wieck 17:14:02 |
| | For a future multimaster replication system, I will need a couple of
features in the PostgreSQL server itself. I will submit separate
proposals per feature so that discussions can be kept focused on one
feature per thread.
For conflict resolution purposes in an asynchronous multimaster system,
the "last update" definition often comes into play. For this to work,
the system must provide a monotonically increasing timestamp taken at
the commit of a transaction. During replication, the replication process
must be able to provide the remote nodes timestamp so that the
replicated data will be "as of the time it was written on the remote
node", and not the current local time of the replica, which is by
definition of "asynchronous" later.
To provide this data, I would like to add another "log" directory,
pg_tslog. The files in this directory will be similar to the clog, but
contain arrays of timestamptz values. On commit, the current system time
will be taken. As long as this time is lower or equal to the last taken
time in this PostgreSQL instance, the value will be increased by one
microsecond. The resulting time will be added to the commit WAL record
and written into the pg_tslog file.
If a per database configurable tslog_priority is given, the timestamp
will be truncated to milliseconds and the increment logic is done on
milliseconds. The priority is added to the timestamp. This guarantees
that no two timestamps for commits will ever be exactly identical, even
across different servers.
The COMMIT syntax will get extended to
COMMIT [TRANSACTION] [WITH TIMESTAMP <timestamptz>];
The extension is limited to superusers and will override the normally
generated commit timestamp. This will be used to give the replicating
transaction on the replica the exact same timestamp it got on the
originating master node.
The pg_tslog segments will be purged like the clog segments, after all
transactions belonging to them have been stamped frozen. A frozen xid by
definition has a timestamp of epoch. To ensure a system using this
timestamp feature has enough time to perform its work, a new GUC
variable defining an interval will prevent vacuum from freezing xid's
that are younger than that.
A function get_commit_timestamp(xid) returning timpstamptz will return
the commit time of a transaction as recorded by this feature.
Comments, changes, additions?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 22 answer | Add comment |
|
| Re: Proposal: Change of pg_trigger.tg_enabled and adding Jan Wieck 16:27:00 |
| | On 1/25/2007 6:55 PM, Tom Lane wrote:> Jan Wieck <JanWieck@Yahoo.com> writes:>> The value definitions of tg_enabled would be>
A fires always>> N fires never>> O fires on transaction origin only>> R fires on replica only> A new per session GUC variable, restricted to superusers, will define if >> the session is in origin or replica mode.> Are you sure two states are enough?
Good question. I don't know. I'd rather error on the safe side and make
it multiple states, for now I only have Normal and Replica mode.
No particular objection, but now would be the time to think if a boolean> is sufficient.> Likewise the system catalog pg_rewrite is extended with an attribute >> ev_enabled. It will have the same possible values and a new command, > I assume there'd be no intention of supporting on-the-fly changes of> this setting (ie, you'd set the GUC variable once at session startup> and not change thereafter)? Otherwise you'd have a problem with cached> plans.
This is indeed the intended use pattern. Since it is restricted to
superusers, I don't see a particular reason why to enforce it in the
system though.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
---------------------------(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
|
| | 11 answers | Add comment |
|
| autovacuum process handling Alvaro Herrera 16:13:21 |
| | Hi,
This is how I think autovacuum should change with an eye towards being
able to run multiple vacuums simultaneously:
1. There will be two kinds of processes, "autovacuum launcher" and
"autovacuum worker".
2. The launcher will be in charge of scheduling and will tell workers
what to do
3. The workers will be similar to what autovacuum does today: start when
somebody else tells it to start, process a single item (be it a table or
a database) and terminate
4. Launcher will be a continuously-running process, akin to bgwriter;
connected to shared memory
5. Workers will be direct postmaster children; so postmaster will get
SIGCHLD when worker dies
6. Launcher will start a worker using the following protocol:
- Set up information on what to run on shared memory
- invoke SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER)
- Postmaster will react by starting a worker, and registering it very
similarly to a regular backend, so it can be shut down easily when
appropriate.
(Thus launcher will not be informed right away when worker dies)
- Worker will examine shared memory to know what to do, clear the
request, and send a signal to Launcher
- Launcher wakes up and can start another one if appropriate
Does this raise some red flags? It seems straightforward enough to me;
I'll submit a patch implementing this, so that scheduling will continue
to be as it is today. Thus the scheduling discussions are being
deferred until they can be actually useful and implementable.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 9 answers | Add comment |
|
| implementing (something like) UNIQUE constraint using PL/pgSQL Tomas Vondra 14:58:24 |
| | Hello,
in our application we need to implement a constraint that enforces 'at
most N rows with this value', that is we have a table with 'flag' column
and for each value there should be at most 10 rows (for example, the
exact number does not matter).
I'm trying to implement a PL/pgSQL trigger to enforce this constraint,
but I'm not sure my solution is 'the best one possible'. The first
solution I came up with is this:
=====================================================================
CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$
DECLARE
v_cnt INTEGER;
p_cnt INTEGER;
BEGIN
IF TG_NARGS >= 1 THEN
p_cnt := TG_ARGV[0]::integer;
ELSE
p_cnt := 1;
END IF;
SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag;
IF v_cnt > p_cnt THEN
RAISE EXCEPTION 'Too many rows with this flag!'
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE at_most(10);
=====================================================================
But that obviously does not work as two sessions can reach the SELECT
INTO statement at the same time (or until one of them commits). Thus
there could be more than 'cnt' rows with the same value.
Then I used a 'FOR UPDATE' lock on a separate 'lock table' that already
contains all possible values of 'flag' (in reality the trigger tries to
insert that value and catches the 'duplicate key' exception but that's
not important here). The trigger is then
=====================================================================
CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$
DECLARE
v_cnt INTEGER;
p_cnt INTEGER;
BEGIN
IF TG_NARGS >= 1 THEN
p_cnt := TG_ARGV[0]::integer;
ELSE
p_cnt := 1;
END IF;
PERFORM flag FROM lock_table WHERE flag = NEW.flag FOR UPDATE;
SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag;
IF v_cnt > p_cnt THEN
RAISE EXCEPTION 'Too many rows with this flag!';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE at_most(10);
=====================================================================
This works (af far as I know), but I'm not sure it's the best possible
solution - for example I'd like to remove the lock table. Is there some
'more elegant' way to do this?
Tomas
PS: Is there some up to date 'best practices' book related to PL/pgSQL?
All books I've found on Amazon are pretty old (about 5 years) or are
related to 'beginners' or different areas of development (PHP, etc.)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 2 answer | Add comment |
|
| Re: 10 weeks to feature freeze (Pending Work) Jonah H. Harris 12:31:25 |
| | On 1/22/07, Joshua D. Drake <jd@commandprompt.com> wrote:> Jonah Harris: WITH/Recursive Queries?
Yup, just talked with Bruce about this last week. Working on the design now.
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 21 answer | Add comment |
|
