Re: [HACKERS] Re: Format of the Money field
On Sat, 3 Feb 2001, Dave Mertens wrote:
On Sat, Feb 03, 2001 at 11:39:29AM -0500, Mitch Vincent wrote:
What's the standard on Money type (if there is one) and if it doesn't
include the $ (of course that would change based on what currency you were
using) then is it any different than numeric(9,2)? numeric(9,2) is what I
use for all fields that need to hold a dollar amount so I'm curious.. I
remember reading in the documentation that money was numeric(9,2) with the
dollar sign added but I wanted to check with the man :-)
This is possible if you use for formatting to_char()/to_number() with
full locale support (here for example with de_DE locale):
test=# select to_char(1000.12, 'L9G999D99');
to_char
-
DM 1.000,12
(1 row)
test=# select to_number('DM 1.000,12', 'L9G999D99');
to_number
---
1000.12
(1 row)
IMHO use numeric and some formatting routine is good idea (better than
current money datetype..)
Oh, never heard of currency?? NOT every country is using dollars. In a few
months we in Europe are going to use the Euro. A money-type is normaly a
BTW, the other day I read nice paper about some US-banks that not known
Euro yet, and have problems with cheque in Euro... :-)
How locale is needful for Euro currency symbol? :-)
floating type with a precision of 5 (float(5)). A money field is just like
A float for money? Not sure that it is normaly. See archive (already
discussed), here is a part or old Jan's letter:
On Mon, 13 Dec 1999, Jan Wieck wrote:
In some countries (Germany at least) storage of financial
booking information is not permitted to use floats. And you
aren't allowed to use it for calculation of taxes etc.,
instead you must use some datatype with a fixable number of
digits after the decimal point.
an float, only less precies. By the way, storing money values with an
decimal precision is a (mostly) a bad thing. We Save currency amounts in
the smallest unit. We save every amount in Eurocents. Our programs format
the amount to the proper format (US-format (35,673.56) or EuropeannFormat
(35.673,56). Currency signs are bad things in databases. Most database are
international, so most amounts also!
In PostgreSQL it is output thing, a currency symbol is not inside DB.
Karel
[HACKERS] Stuck Spinlock (fwd) - m68k architecture, 7.0.3
Has anyone got PostgreSQL 7.0.3 working on m68k architecture? Russell is trying to install it on m68k and is consistently getting a stuck spinlock in initdb. He used to have 6.3.2 working. Both 6.5.3 and 7.0.3 fail. His message shows that the first attempt to set a lock fails. --- Forwarded Message Date:Mon, 05 Feb 2001 09:03:21 -0500 From:Russell Hires [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Stuck Spinlock Hey, here is the spinlock test results... Thanks! Russell rusty@smurfette:~/postgresql-7.0.3/src/backend/storage/buffer$ make s_lock_test gcc -I../../../include -I../../../backend -O2 -g -g3 -Wall - -Wmissing-prototypes -Wmissing-declarations -I../.. -DS_LOCK_TEST=1 s_lock.c - -o s_lock_test s_lock.c:251: warning: return type of `main' is not `int' ./s_lock_test FATAL: s_lock(80002974) at s_lock.c:260, stuck spinlock. Aborting. FATAL: s_lock(80002974) at s_lock.c:260, stuck spinlock. Aborting. make: *** [s_lock_test] Aborted make: *** Deleting file `s_lock_test' --- End of Forwarded Message -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Lift up your heads, O ye gates; and be ye lift up, ye everlasting doors; and the King of glory shall come in. Who is this King of glory? The LORD strong and mighty, the LORD mighty in battle." Psalms 24:7,8
Re: [HACKERS] Ungraceful handling of fatal flex errors
scan.c:2145: warning: `yy_fatal_error' defined but not used I have a sneakier idea to avoid the warning. [...] #define fprintf(file,fmt,msg) elog(FATAL, "%s", (msg)) Meaning no disrespect : yuck... IMHO this is asking for trouble whenever someone decides to use another yacc. One should never ever use the preprocessor to do what it was originally intended for :-). Why not just make a useless statement calling yy_fatal_error ? --- scan.l.orig Mon Jan 29 11:36:56 2001 +++ scan.l Mon Jan 29 11:27:28 2001 @@ -532,6 +534,9 @@ because input()/myinput() checks the non-nullness of parseCh to know when to pass the string to lex/flex */ parseCh = NULL; + + /* Make a bogus use of yy_fatal_error to avoid spurious warning */ + (void) yy_fatal_error; /* initialize literal buffer to a reasonable but expansible size */ literalalloc = 128; -- Tout n'y est pas parfait, mais on y honore certainement les jardiniers Dominique Quatravaux [EMAIL PROTECTED]
Re: [HACKERS] 1024 limits??
I just do a select on a user table with the username and password pretty upfront and normal query you would like me to use PQtrace??? it doesnt say much if I do bt on gdb it says: 0x4b3003d in ?? () On Mon, 05 Feb 2001, you wrote: Mathieu Dube [EMAIL PROTECTED] writes: If I remove the function where I authenticate my server can handle as much clients as I want. Which authentication method do you use? If I authenticate every clients with connections to the database it crashes on PQconnectdb at th 1024th client without returning an error(it just segfaults). Please provide a backtrace from the segfault ... regards, tom lane -- Mathieu Dube Mondo-Live www.flipr.com
Re: [HACKERS] 1024 limits??
Mathieu Dube [EMAIL PROTECTED] writes: If I remove the function where I authenticate my server can handle as much clients as I want. Which authentication method do you use? If I authenticate every clients with connections to the database it crashes on PQconnectdb at th 1024th client without returning an error(it just segfaults). Please provide a backtrace from the segfault ... regards, tom lane
Re: [HACKERS] 1024 limits??
Im currently using rpms is there a way to just remove an rpm(the API one) and compile only that?? where can I find libpq sources? my app is already compiled with -g On Mon, 05 Feb 2001, you wrote: Mathieu Dube [EMAIL PROTECTED] writes: if I do bt on gdb it says: 0x4b3003d in ?? () You'll need to recompile libpq and your application with -g in CFLAGS to get a more useful backtrace from gdb. regards, tom lane -- Mathieu Dube Mondo-Live www.flipr.com
[HACKERS] timestamp in pg_dump
I'd like to have pg_dump for 7.1 produce "timestamp with time zone" when dealing with timestamp type(s). That will prepare us for introducing a timestamp type without time zones, while allowing reasonable upgrades to 7.2. Comments? Would a pg_dump guru be willing to take this on? Should be "easy", since afaik we already map some internal types to external SQL-standard names... - Thomas
Re: [HACKERS] pg_ctl wish list
Tom Lane writes:
"fast" shutdown should be the default, otherwise you may get surprises on
system shutdown when users are still connected.
No, I don't think so. During a system shutdown, init will deliver
SIGTERM to all the backends as well as the postmaster, so the backends
will die quite handily without the postmaster needing to give them any
additional push.
During a system shutdown on my system, '{script} stop' will be called,
which will (at present) send SIGTERM to the postmaster. This will hang
indefinitely at worst. Assume that we don't configure it to wait
(although we agreed on the opposite), and the global SIGTERM is sent out,
is there some sort of ordering guarantee?
We should not change to a less-safe default behavior when there is no
need to.
Is it really less safe? In my mind, the "fast" shutdown behaviour is
appropriate for a system shutdown. After all, the system shutdown doesn't
wait for all users to log out either, it just says "See ya". An
ergonomical "fast" postmaster shutdown would send some message before
disconnecting all clients as well, but the fact that is does forcibly
disconnect seems required to me.
There should be an option to put the server log somewhere, either a file
or maybe a pipe, e.g.,
pg_ctl -l logfile
pg_ctl -P 'magic-log-rotator -x -y -z'
Or we could just switch over to syslog as the standard log
destination...
Not as long as all the good stuff goes to stderr.
Perhaps the postmaster should have a '-u userid' option and do a
setuid() call ... though I don't know whether this will be materially
more portable than invoking su from a script.
Given that we do have pg_ctl, we can probably avoid a lot of headaches by
avoiding the setuid() business. There's no good solution for initdb in
that area anyway.
I think the -w thing and the log output thing should be fixed before 7.1
goes out the door. Any comments on the particulars?
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] build WIN32 ODBC using VC++, not Cygwin/gcc
To build the PostgreSQL ODBC driver from CVS (or a recent snapshot/beta, I presume): the following steps worked for me: + Make sure that the psqlodbc.def file in interfaces/odbc is exactly as distributed or obtained from CVS, not the result of some prior build. + Run 'configure' _without_ '--with-odbc' to build PostgreSQL. + Follow the steps in interfaces/odbc/readme.txt to build the ODBC DLL using MS Visual C++. Building with '--with-odbc' will appear to work, but the resulting DLL (created by Cygwin gcc/tools) completely fails to work for me once installed. (It would have been nice if the documentation gave a hint about this. Or did I miss it? The presense of the readme.txt file does not suffice to make it clear that VC++ is the _only_ correct way to build the module for WIN32.) I also got confused when first building the DLL from VC++, because my prior efforts using '--with-odbc' left behind a psqlodbc.def file that is incorrect. My thanks to Hiroshi Inoue for helping me get past this. -- Fred Yankowski [EMAIL PROTECTED] tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
[HACKERS] command-line option for logfile
A command-line option for specifying the logfile would be very helpful on WIN32. When setting up PostgreSQL to run as an NT service, the only way I can see to redirect stdout/stderr to a file is to invoke postmaster via a wrapper sh/bash process. I suspect that this wrapper process would have to hang around as long as postmaster is running, although it might be possible to do an exec to overlay it. It would be nice to avoid this wrapper process. On Mon, Feb 05, 2001 at 06:01:10PM +0100, Peter Eisentraut wrote: There should be an option to put the server log somewhere, either a file or maybe a pipe, e.g., pg_ctl -l logfile pg_ctl -P 'magic-log-rotator -x -y -z' Or we could just switch over to syslog as the standard log destination... Not as long as all the good stuff goes to stderr. -- Fred Yankowski [EMAIL PROTECTED] tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
Re: [HACKERS] Re: 1024 limits??
cc -o therver therver.c -g -lflipr -lpq -L. -lpthread -D_REENTRANT ./libpq.a(fe-auth.o): In function `pg_password_sendauth': /usr/local/postgresql-7.0.3/src/interfaces/libpq/fe-auth.c:465: undefined reference to `crypt' collect2: ld returned 1 exit status make: *** [therver] Error 1 this is what I get should I copy other files? On Mon, 05 Feb 2001, you wrote: "Mitch Vincent" [EMAIL PROTECTED] writes: You need to compile PostgreSQL from source with -g .. ftp.postgresql.org is the main FTP, there are many mirrors. Look for the source tarball there.. Im currently using rpms is there a way to just remove an rpm(the API one) and compile only that?? If you don't want to mess with removing the RPMs, you don't have to. Build the source with -g, but don't install it. Instead, copy the static libpq.a library (look in src/interfaces/libpq after building) to your application directory, and force your app to link against it instead of against the libpq.so that's in /usr/lib (or whereever the RPMs installed it). regards, tom lane -- Mathieu Dube Mondo-Live www.flipr.com
Re: [HACKERS] Foreign Key Columns And Indices
On Mon, 5 Feb 2001, Philip Warner wrote: PGSQL implements PK/FK Unique constraints by using indexes (and rules) at the moment. There is no guarantee that this will always be the case - in fact, one path to rationalizing the constraints system is to implement most features as SQL CHECK constraints: PK: Check( (Select Count(*) from Table Where PKCOLS=PKCOLS) = 1) FK: Check( (Select Count(*) from PK_Table Where PKCOLS=FKCOLS) = 1) There are a couple of problems with this for the fk case. The biggest is that check constraints with subselects won't currently do the correct thing (even if it allowed you to specify them). We could replace the current trigger on insert/update of fk table with a check constraint assuming we made it possible to defer check constraints, but all of the stuff on the pk table won't work that way due to the referential actions and the fact that you need to check after delete on pk rows (which AFAIK we don't currently do for check constraints). The spec gives the check version of the foreign key constraint but it was unfortunate that they also added functionality which then made that insufficient. :(
Re: [HACKERS] pg_ctl wish list
Peter Eisentraut wrote:
Tom Lane writes:
"fast" shutdown should be the default, otherwise you may get surprises on
system shutdown when users are still connected.
No, I don't think so. During a system shutdown, init will deliver
SIGTERM to all the backends as well as the postmaster, so the backends
will die quite handily without the postmaster needing to give them any
additional push.
During a system shutdown on my system, '{script} stop' will be called,
which will (at present) send SIGTERM to the postmaster. This will hang
indefinitely at worst. Assume that we don't configure it to wait
(although we agreed on the opposite), and the global SIGTERM is sent out,
is there some sort of ordering guarantee?
Hmmm. On RedHat, the appropriate runlevel is set -- 6 for reboot, 0 for
halt. The SysV-init walks the rc.d subdir (rc6.d for reboot, rc0.d for
halt), finding a list of scripts. The scripts are executed in ASCII
collation order, but, by convention, the scripts are symlinks to the
real scripts in /etc/rc.d/init.d. Scripts starting with K are given a
parameter of 'stop', and ones starting with S are given a parameter of
'start'. The K scripts (K85postgresql for example) are executed in
order, then the S scripts are executed. The last two scripts to execute
are S00killall and S01reboot.
S00killall (symlink to ../init.d/killall) combs /var/lock/subsys for
services that were started but not stopped by their own initscript, and
invokes their script with the stop parameter if so. This is a sanity
check.
S01reboot is a symlink to ../init.d/halt, and does the following:
1.) Checks its invocation name. If reboot, set up for reboot, if halt,
set up for halt.
2.) Sends SIGTERM to all running processes, using /sbin/killall5, which
is a binary shipped with the SysVinit package to send a kill to all
processes. The kills are likely sent in PID order, but a look at the
source to killall5 would be required to guarantee that.
3.) halt -w to force a write to wtmp before /var is umounted.
4.) Save mixer settings.
5.) Turn off swap.
6.) Unmount filesystems, using a sophisticated scheme that kills off any
remaining processes with open files on the filesystems to be unmounted
with SIGKILL.
7.) Turn off RAID.
8.) Unmount /proc.
9.) Remount remaining filesystems (/ should be the only one) read-only.
10.)Eval the command setup -- reboot for 'reboot', and halt for 'halt'.
But, in the context of the RedHat system, the postgresql initscript is
where the backend reaping should occur. Recommendations as to algorithm
are welcome, as you correctly state that the script currently only
directly signals postmaster processes.
Is it really less safe? In my mind, the "fast" shutdown behaviour is
appropriate for a system shutdown. After all, the system shutdown doesn't
wait for all users to log out either, it just says "See ya". An
A system shutdown will forcibly disconnect all clients anyway, as NICs
are shutdown, etc. So a fast shutdown for system shutdown is entirely
appropriate, IMHO.
Or we could just switch over to syslog as the standard log
destination...
Not as long as all the good stuff goes to stderr.
I think the -w thing and the log output thing should be fixed before 7.1
goes out the door. Any comments on the particulars?
Where can elog() not be safely used? I'll volunteer to grep and
replace, subbing appropriate elog parameters, but I'm not sure I can do
it in time for 7.1's release.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
Re: [HACKERS] timestamp in pg_dump
Thomas Lockhart writes: I'd like to have pg_dump for 7.1 produce "timestamp with time zone" when dealing with timestamp type(s). That will prepare us for introducing a timestamp type without time zones, while allowing reasonable upgrades to 7.2. Comments? Would a pg_dump guru be willing to take this on? Should be "easy", since afaik we already map some internal types to external SQL-standard names... Did that. But given that 'timestamp' is really 'timestamp with time zone' and there is no 'timestamp without time zone', isn't there something wrong with the documentation? See User's Guide, table 3-6: http://www.postgresql.org/devel-corner/docs/postgres/datatype-datetime.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] Foreign Key issue - pg_shadow
I suppose the below reference isn't intended to be done. But, I'd like to extend the pg_shadow table to store information about the database users and some of their actions (a history of changes). Is it safe to add a UNIQUE constraint to the usesysid or usename columns? temp=# \d pg_shadow Table "pg_shadow" Attribute | Type | Modifier -+-+-- usename | name | usesysid | integer | usecreatedb | boolean | usetrace | boolean | usesuper | boolean | usecatupd | boolean | passwd | text | valuntil | abstime | temp=# temp=# CREATE TABLE users ( temp(# pg_username name NOT NULL temp(# REFERENCES pg_shadow(usesysid) temp(# ON UPDATE CASCADE temp(# ON DELETE RESTRICT temp(# ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "pg_shadow" not found temp=# -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. BEGIN:VCARD VERSION:2.1 N:Taylor;Rod;B FN:Taylor, Rod B ORG:BarChord Entertainment Inc.;System Operation and Development TITLE:Chief Technical Officer ADR;WORK:;;;Toronto;Ontario;;Canada LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Toronto, Ontario=0D=0ACanada X-WAB-GENDER:2 URL: URL:http://www.barchord.com BDAY:19790401 EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20010129T201530Z END:VCARD
Re: [HACKERS] Like vs '='
Hi there, I am compiling postgresql 7.1beta4. How would i change the default 8k row limit? -- Manny C. Cabido e-mail:[EMAIL PROTECTED] [EMAIL PROTECTED] =
[HACKERS] OID from insert has extra letter
This is very odd. I have a database app failing sometimes with an error
when I try to get back my inserted row. I do this to get all the fields
that were adjusted for defaults, triggers and rules. On an insert query
I expect to get the OID of the just inserted tuple. Once in a while I
get the wrong value. Here are two examples.
Get: 31930584, should be 3193058
Get: 31943386, should be 3194338
As you can see, there is an extra digit added. I am using PQoidStatus()
to get the OID. Is this the right way? Is it possible that there is
a buffer/terminating problem in the insert code? I assume that most
people are not examining this value so it may be that something slipped
through. Anyone else ever seen this?
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
[HACKERS] PostgreSQL - PHP problem
This is the debug output for the last query that seems to be throwing PHP
into a fit (a fit that somehow closes the backend connection - note, it
doesn't crash, it just closes)..
I don't think anything is going on here that shouldn't be, it looks the same
as any other query that succeeds.. I just wanted someone that could actually
read and understand this to take a look..
Thanks!
Debug output follows ---
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT * FROM app_degrees
parser outputs:
{ QUERY :command 1 :utility :resultRelation 0 :into :isPortal false
:isBinary false :isTemp false :unionall false :distinctClause :sortClause
:rtable ({ RTE :relname app_degrees :ref { ATTR :relname app_degrees
:attrs } :relid 660864 :inh false :inFromCl true :inJoinSet true :skipAcl
false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname degree_id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
:resno 2 :restype 1043 :restypmod 14 :resname abbr :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 14 :varlevelsup 0 :varnoold 1 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname
description :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0
:varnoold 1 :varoattno 3}}) :qual :groupClause :havingQual :hasAggs
false :hasSubLinks false :unionClause :intersectClause :limitOffset
:limitCount :rowMark }
after rewriting:
{ QUERY
:command 1
:utility
:resultRelation 0
:into
:isPortal false
:isBinary false
:isTemp false
:unionall false
:distinctClause
:sortClause
:rtable (
{ RTE
:relname app_degrees
:ref
{ ATTR
:relname app_degrees
:attrs
}
:relid 660864
:inh false
:inFromCl true
:inJoinSet true
:skipAcl false
}
)
:targetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 23
:restypmod -1
:resname degree_id
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 1043
:restypmod 14
:resname abbr
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 2
:vartype 1043
:vartypmod 14
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1043
:restypmod 54
:resname description
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 3
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
)
:qual
:groupClause
:havingQual
:hasAggs false
:hasSubLinks false
:unionClause
:intersectClause
:limitOffset
:limitCount
:rowMark
}
plan:
{ SEQSCAN :startup_cost 0.00 :total_cost 20.00 :rows 1000 :width 28 :state
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname degree_id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
:resno 2 :restype 1043 :restypmod 14 :resname abbr :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 14 :varlevelsup 0 :varnoold 1 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname
description :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0
:varnoold 1 :varoattno 3}}) :qpqual :lefttree :righttree :extprm ()
:locprm () :initplan :nprm 0 :scanrelid 1 }
ProcessQuery
CommitTransactionCommand
proc_exit(0)
shmem_exit(0)
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 45155 exited with status 0
[HACKERS] 1024 limit?
Hi, Im building a server that uses libpq to connect to a database and authenticate the users that connect. I do PQfinish for the conn and PQclear for the result so there cant be a memory leak there. If I remove the function where I authenticate my server can handle as much clients as I want. If I authenticate every clients with connections to the database it crashes on PQconnectdb at th 1024th client without returning an error(it just segfaults). My ulimit -n is more than 1024 btw. Did anybody encounter such problem?? I've been trying to fix this one for a long time... Thanks -Mat -- Mathieu Dube Mondo-Live www.flipr.com
[HACKERS] Parallel queries
Title: Parallel queries All, How hard would it be to get a back end to hand off parts of a query tree to other back ends (which it created), and then collate the results, and take it from there? Obviously, it would only do this under certain conditions, specifically, if it was compiled in to the server, and the query tree in question had a particular profile, i.e.: lent itself to parallel processing. But surely it couldn't be that difficult to get a back end to hand off, say, a sub-select sub-tree, or UNION sub-trees. Under heavy loads with the right hardware, this could gain significant performance improvement. Michael Ansley ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
[HACKERS] 1024 limit??
Hi, Im building a server that uses libpq to connect to a database and authenticate the users that connect. I do PQfinish for the conn and PQclear for the result so there cant be a memory leak there. If I remove the function where I authenticate my server can handle as much clients as I want. If I authenticate every clients with connections to the database it crashes on PQconnectdb at th 1024th client without returning an error(it just segfaults). My ulimit -n is more than 1024 btw. Did anybody encounter such problem?? I've been trying to fix this one for a long time... Thanks -Mat -- Mathieu Dube Mondo-Live www.flipr.com
[HACKERS] ODBC Problem v7.1 beta4
Developers, Pgsql v7.1 beta4 ODBC v6.50.00.00 RedHat v6.2 I upgraded from 7.03 to 7.1 beta4 yesterday see the following problem. After the upgrade, applications like Crystal Reports, MS Query, Brio, etc. now do not see the catalog of tables and fields in the database, login is through user postgres. SQL statements sent directly through the ODBC work correctly. The pgAdmin utility CAN see the catalog of all of the tables fields OK through the same login. I played with all of the options of the ODBC driver postgres security could not find a solution. Also, searched the site for any similar problems found no posts. Can anyone verify this problem? Any ideas? Thanks for the help, Steve Shaffer ([EMAIL PROTECTED])
[HACKERS] Re: Like vs '='
There isn't any row or query size limit in 7.1 thanks to TOAST! -Mitch - Original Message - From: "Manuel Cabido" [EMAIL PROTECTED] To: "m w" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 29, 2001 10:18 PM Subject: Re: Like vs '=' Hi there, I am compiling postgresql 7.1beta4. How would i change the default 8k row limit? -- Manny C. Cabido e-mail:[EMAIL PROTECTED] [EMAIL PROTECTED] =
Re: [HACKERS] Re: 1024 limits??
From which file should I load the symbol for gdb?? Thanks -Mat On Mon, 05 Feb 2001, you wrote: "Mitch Vincent" [EMAIL PROTECTED] writes: You need to compile PostgreSQL from source with -g .. ftp.postgresql.org is the main FTP, there are many mirrors. Look for the source tarball there.. Im currently using rpms is there a way to just remove an rpm(the API one) and compile only that?? If you don't want to mess with removing the RPMs, you don't have to. Build the source with -g, but don't install it. Instead, copy the static libpq.a library (look in src/interfaces/libpq after building) to your application directory, and force your app to link against it instead of against the libpq.so that's in /usr/lib (or whereever the RPMs installed it). regards, tom lane -- Mathieu Dube Mondo-Live www.flipr.com
Re: [HACKERS] Re: [BUGS] syslog logging setup broken?
Bruce Momjian [EMAIL PROTECTED] 04-Feb-01 10:07:40 PM The bottom line is that, IMHO, writing a portable init.d style (or any other such concept) startup file that is ready for blind use is beyond practicality. It might be better to collect a few of the ones that are being used now (Red Hat-style, SuSE-style, Debian, *BSD-style) and ship them. This should be coordinated with the packagers, though. Should I remove init.d from /contrib? I'm just a postgres user but I don't agree with Peter. I think the file is valuable. The file is valuable for people not using a distribution such as Debian, etc... and also is usefull to people developing packages for distributions. I don't use a packaged postgres and it was certainly valuable to me because it served as an example of what I had to do to get postgres going quickly in the way that I wanted. I sent Peter an updated file that IMHO irons out some problems which may cause Peter to consider the file broken: - ouptut was being piped to the logger if "syslog" was on It's not necessary to do that because postgres handles the decision about syslog depending on the conf file. - the postmaster was being started without nohup - the system for setting options wasn't very usefull the system that I've replaced it with isn't terribly usefull either but it works. So anyway, my view as a user is that it's usefull and that a package specific version would come with the package anyway. Nic Ferrier Tapsell-Ferrier Limited
[HACKERS] Re: PostgreSQL - PHP problem
In the PHP bugs I see... ===[PostgreSQL related]=== 5862 Open Consecutive pg_open statements cause second statement to fail 6525 Open Connection problem 7007 Open The pg_close function doesn't close the connection. 7236 Open 1 is not a valid PostgreSQL link resource 7264 Open 1 is not a valid PostgreSQL link 7298 Open ... not a valid link resource... after pg_connect 7312 Open Problems with pg_connect() i pg_fetch_row() 7333 Open Connection fault in circled query 7529 Open pg_connect() returns invalid connection id 7536 Open Warning: is not a valid PostgreSQL link resource 7931 Feedback Undefined symbol "_PQconnectdb" 8053 Open PGSQL doesn't detects on FBSD4 8225 Open Suddenly doesnt allow multiple psql connections from one php page 8317 Open postgresql table uppercase field name 8689 Open pg_Connect() seems to do some type of caching that doesn't quite work 8769 Open Persistent connections aren't closed when using dynamically loaded module 8907 Open pg_Close on multiple connections to same host 9048 Open problem to open several connections on 4.0.4pl1 that worked on 4.0.2 Ouch. It looks like this is exactly what is happening to me. pg_open gets called several times in these scripts.. It looks like I'll have to install an old version of PHP.. Son of a er nevermind.. Thanks guys.. -Mitch
Re: [HACKERS] src/bin/psql/tab-complete.c ported to emacs lisp
Michal Maruka writes: I have started trying to make the emacs' psql experience more comfortable. I transformed (with sed(1) manually) the C-source to elisp program. Now I want to improve it. Interesting. Is the reason that you need this the fact that psql doesn't use readline when run in emacs because it thinks it's connected to a non-terminal? Maybe a better approach would be to make psql (or readline (or emacs)) think differently in this situation. My question: What is the licence of this file. Good question. We like to think that all files that are not labeled otherwise are under the BSD-style license that is shown in the COPYRIGHT file, with appropriate copyright holders replaced. Can I distribute is under GPL? Personally, (I wrote the file originally) I would prefer it if you kept the BSD license on it, as it will allow us to steal code back from you, and it's not like somebody's going to distribute binary-only versions, get rich, and cut you out. Some people think it's not okay to relicense BSD stuff as GPL without getting into some kind of jam, to which I personally agree, but if you don't care then I won't sue you either. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] build WIN32 ODBC using VC++, not Cygwin/gcc
Fred Yankowski writes: To build the PostgreSQL ODBC driver from CVS (or a recent snapshot/beta, I presume): the following steps worked for me: [snip] + Run 'configure' _without_ '--with-odbc' to build PostgreSQL. I doubt you need that, since you don't use the makefiles. [snip] Building with '--with-odbc' will appear to work, but the resulting DLL (created by Cygwin gcc/tools) completely fails to work for me once installed. (It would have been nice if the documentation gave a hint about this. Or did I miss it? Something more detailed thatn "completely fails to work" might shed some light onto it. The presense of the readme.txt file does not suffice to make it clear that VC++ is the _only_ correct way to build the module for WIN32.) True, it is not a very prominent place to hide such a thing. Maybe it would be appropriate to mention it in the "Installation on Windows" chapter in the Administrator's Guide or alternatively in the ODBC chapter in the Programmer's Guide. Can somebody explain to me what this means, from said readme.txt: "In order to use row versioning, you must overload the int4eq function for use with the xid type." -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Getting Software
On Sun, 4 Feb 2001, Kajetan Hofman wrote: Hi Guys, please tell me how to get software from you. I found no way to download any software. Did you try the "Software" link on the website? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
[HACKERS] pg_dump data integrity java
We are currently developing a java RMI Server to manage database backup on FreeBSD 4.1 running Postgres 7.0.2 (we plan to upgrade to 7.1 when it is released). The server will pg_dump individual tables and entire databases at specified intervals and manage backup verification and backup file maintenance. The current postgres documentation indicates that pg_dump can either backup one file at a time or an entire database within a single transaction in order to maintain data integrity. My questions are: If I pg_dump a single table which has a foreign key constraint, and subsequently backup the constraint table, won't the data be out of sync? Each pg_dump will run in it's own transaction, right? Is there a way to dump multiple tables within a single transaction without having to dump the entire database? If not, is it plausible for me to add this option i.e. pg_dump -t array_of_tables ? What happens when I restore if the data is out of sync? As an aside, the current documentation does not reflect all of the pg_dump options, specifically the "-f" option which will cause pg_dump to output to a file instead of stdout. This information is very useful to java developers who need to execute external commands and direct the output to somewhere other than java... Is there anything I can do to help update documentation for postgres configuration options in general? Thanks for all comments. Peter Schmidt Prismedia Networks [EMAIL PROTECTED]
Re: [HACKERS] build WIN32 ODBC using VC++, not Cygwin/gcc
On Mon, Feb 05, 2001 at 08:06:28PM +0100, Peter Eisentraut wrote: + Run 'configure' _without_ '--with-odbc' to build PostgreSQL. I doubt you need that, since you don't use the makefiles. I stated that point for the benefit of those, like me, who are building all of PG from source, not just building ODBC. Using '--with-odbc' in such a case has two problems: 1) it builds a useless psqlodbc.dll; 2) it updates (AFAICT) psqlodbc.def to new data that will cause a later, independent, build of psqlodbc.dll via VC++ to fail. Building with '--with-odbc' will appear to work, but the resulting DLL (created by Cygwin gcc/tools) completely fails to work for me once installed. (It would have been nice if the documentation gave a hint about this. Or did I miss it? Something more detailed thatn "completely fails to work" might shed some light onto it. I had posted earlier messages with more details (which got no response other than from Mr. Hiroshi Inoue, who got me past the problem) so I didn't repeat them in that message. Anyway, here's what happens when I build psqlodbc.dll as part of a complete pgsql build, having configured with '--with-odbc': + The psqlodbc.dll file appears to build OK, with no warnings (that I recall). + When I overwrite my systemroot/system32/psqlodbc.dll with the one I just built, I then can no longer access any ODBC functions for PostgreSQL. In particular: + The 'PostgreSQL' driver does appear in the list of drivers in the ODBC control panel. However, its version company information is not available. + When I select an existing System DSN using the PostgreSQL driver and click Configure, I get the message "The setup routines for the PostgreSQL ODBC driver could not be accessed. Please reinstall the driver." + When I try to Add a new DSN using the PostgreSQL DSN, the same message results. + When I try to bring up my Zope website, which employs several ODBC connections (and is my only application using PG ODBC), accessing that site results in an application error from Zope/python saying "The instruction at '0x64eca3e5' referenced memory at '0x014f3e54'. The memory could not be 'read'.". Bringing up the VC++ debugger at that point gives me the message "Unhandled exception in python.exe (PSQLODBC.DLL): 0xC005: Access Violation". -- Fred Yankowski [EMAIL PROTECTED] tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
Re: [HACKERS] Re: Re: grant privileges to a database [URGENT]
Hello A few weeks ago I was interested in this question. My results were: - Yes, this is a sorrowful but true fact that if you enable access to someone to a database, she is automatically enabled to create objects in it. - Yes, the developers know it, and they said: there is a patch existing to workaround it. - No, they don't include it in 7.1. The reason: if you use that patch, pg_dumpall will not work. If somebody will have the strength in him to fix it, than it will be considered to include it in the base. After collecting these informations from more experienced people, I calmed down. Since I am in the beginning of creating my project, I think for the time when I will need it, it will be ready. Anyway, I do not know where this patch is. If you don't bother about pg_dumpall, ask a developer (a am only a wannabe developer) about it. If anyone detects that I wrote silly things, please do correct me. Bye, Baldvin
Re: [HACKERS] build WIN32 ODBC using VC++, not Cygwin/gcc
Peter Eisentraut [EMAIL PROTECTED] writes: Can somebody explain to me what this means, from said readme.txt: "In order to use row versioning, you must overload the int4eq function for use with the xid type." Apparently, there is something in either the ODBC driver itself or MS Access that expects to be able to do "WHERE xmin = integer". Since we supply no such operator in the standard distribution, one has to kluge up the operator as illustrated. Of course, this code will break if we go to 8-byte XIDs ... regards, tom lane
[HACKERS] with -g and libpq.a ...
even after compiling with -g and using the libpq.a it still outputs that on the bt after the crash... (gdb) bt #0 0x40050768 in ?? () #1 0x4004ff25 in ?? () #2 0x804a3cd in main (argc=3, argv=0xb8e4) at therver.c:430 #3 0x400739ee in ?? () -- Mathieu Dube Mondo-Live www.flipr.com
[HACKERS] Can we modify 'text *' passed to a C function?
If I write a function that does a 'tolower()' on each character of a parameter passed into my function, do I have to allocate a new text object and initialize it and copy the data, or can I modify that object in place and return it? Modifying it in place will save the overhead of an alloc call, and on a large database, that could add up. right now, I am allocating a new data buffer of exactly the same size and it seems like a waste. Secondly, if I modify the data length of an object, as: VARDATA(field) = nn, making sure that 'nn' is smaller than the origial length, with this affect the memory management? In short, can we modify text * objects passed to a C function, and can we shrink the length field without any ill effects? (Doing so will reduce quite a bit of memory allocation overhead I am doing.) __ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [HACKERS] pg_ctl wish list
Tom Lane writes: Peter Eisentraut [EMAIL PROTECTED] writes: To alleviate the need for a start up script somewhat we could perhaps fix up pg_ctl to serve that purpose better. -w should be the default, as has been suggested. Agreed. But we need to make up a better way to wait for postmaster startup. The current 'psql -l' is no good, as we have discussed, but if this thing is going into system startup scripts mainstream now we can't have that kind of random behaviour. Actually, trying to connect is wrong-headed anyway. If the user has a peculiar setup to disallow connections from the local host (virtual host maybe), even if it is only a temporary misconfiguration, we should still be able to start up in good faith. How is "successfully started up" constrained anyway? Even if we had a way to check that, the postmaster could still choose to abort briefly after our check, e.g., during the backend reaping phase. Maybe waiting on startup isn't so important to worry about it. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] RE: Index grows huge, possible leakage?
* Mikheev, Vadim [EMAIL PROTECTED] [010202 10:39] wrote: After several weeks our idicies grow very large (in one case to 4-5 gigabytes) After droppping and recreating the indecies they shrink back to something more reasonable (500megs same case). We are currently using Vadim's vacuum patches for VLAZY and MMNB, against 7.0.3. We are using a LAZY vacuum on these tables However a normal (non-lazy) vacuum doesn't shrink the index, the only thing that helps reduce the size is dropping and recreating. Is this a bug in 7.0.3? A possible bug in Vadim's patches? Or is this somewhat expected behavior that we have to cope with? When index is created its pages are filled in full = any insert into such pages results in page split - ie in additional page. So, it's very easy to get 4Gb from 500Mb. Well that certainly stinks. :( Vacuum was never able to shrink indices - it just removes dead index tuples and so allows to re-use space ... if you'll insert the same keys. This doesn't make sense to me, seriously, if the table is locked during a normal vacuum (not VLAZY), why not have vaccum make a new index by copying valid index entries into a new index instead of just vacating slots that aren't used? To know does VLAZY work properly or not I would need in vacuum debug messages. Did you run vacuum with verbose option or do you have postmaster' logs? With LAZY vacuum writes messages like Index _name_: deleted XXX unfound YYY YYY supposed to be 0... With what you explained (indecies normally growing) I don't think VLAZY is the problem here. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
[HACKERS] Should I look elsewhere??
So... If after recompiling postgres with -g in the CFLAGS and still getting 0x40050768 in ?? () in gdb, should I look elsewhere??? Thanks -Mat -- Mathieu Dube Mondo-Live www.flipr.com
Re: [HACKERS] How to modify type in table?
Hi there, I am compiling postgresql 7.1beta4. How would i change the 8k row limit? -- Manny C. Cabido e-mail:[EMAIL PROTECTED] [EMAIL PROTECTED] =
[GENERAL] Re: [HACKERS] Re: Re: grant privileges to a database [URGENT]
Hrm- I'd love to know where this patch is. I don't see how that quite breaks PG_DUMPALL though. Really if your logged in as a superuser (postgres) you should be able to use all the databases and dump all of the data. Am I the only one that doesn't see where the problem is? How about a patch that says 'if the user that created the database is not the current user, then reject- otherwise accept'. I could go for that. Though access control would be nice, I could log in as a superuser, make a user with the ability to make databases, login as that user, make the databases I need, then login as postgres and revoke the privilages of creating databases. Suddenly you can only access databases you created and its as easy as that (a few PHP lines if you ask me) to make new databases. Wouldn't it just be a simple IF statement to see if the current user is the database owner [or if they have the superuser ID set]? Am I not seeing the big picture? -- Mike From: Kovacs Baldvin [EMAIL PROTECTED] To: Mike Miller [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: [HACKERS] Re: Re: grant privileges to a database [URGENT] Date: Mon, 5 Feb 2001 20:13:38 +0100 (MET) Hello A few weeks ago I was interested in this question. My results were: - Yes, this is a sorrowful but true fact that if you enable access to someone to a database, she is automatically enabled to create objects in it. - Yes, the developers know it, and they said: there is a patch existing to workaround it. - No, they don't include it in 7.1. The reason: if you use that patch, pg_dumpall will not work. If somebody will have the strength in him to fix it, than it will be considered to include it in the base. After collecting these informations from more experienced people, I calmed down. Since I am in the beginning of creating my project, I think for the time when I will need it, it will be ready. Anyway, I do not know where this patch is. If you don't bother about pg_dumpall, ask a developer (a am only a wannabe developer) about it. If anyone detects that I wrote silly things, please do correct me. Bye, Baldvin _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
[HACKERS] building libpgtcl for pgaccess under win32, and other stories
I wanted to get pgaccess working under win32 with MSVC 6. It relies on the libpgtcl library, which didn't have a win32.mak. This turned out to be (I think) because Tcl_CreateFileChannel function isn't portable to win32 systems. So I grabbed the relevant files from CVS (no tags), and hacked about a bit and now I have a mostly working libpgtcl.dll. The problem is that the implementation of pg_listen relies on Tcl_CreateFileChannel, so NOTIFY's aren't picked up under win32 until, say, a pg_exec is issued. I had a quick look at finding out how to get incoming data on the socket to trigger Tcl events, and it looks like it might be possible to build a Tcl event source to fetch the NOTIFY events from the backed. That however is not something I could do in a day or two. So now pgaccess works fine - it doesn't use pg_listen. The other change I made was to move dll exports from the .def file into the source code using declspec. A clunky system but IMHO slightly easier to work with than the .def files. I found that the Tcl, libpq, and libpgtcl source code all use the DLLIMPORT define. This causes some annoying conflicts, so I changed each library to have it's own declspec define (XXX_STORAGE_CLASS), and each library now needs a define ( BUILDING_XXX_DLL) to indicate whether you're building the dll (defined) or importing it (undefined). XXX_STORAGE_CLASS would be defined to '' for build environments that do shared libraries properly. This means that for each dll export, for example in pgsql/src/interfaces/libpq/libpq-fe.h I have something like PQ_STORAGE_CLASS extern PGconn *PQconnectStart(const char *conninfo); instead of extern PGconn *PQconnectStart(const char *conninfo); There were one or two other minor changes like defining NO_GDI in strategic places to prevent a 'macro ERROR redefined' warning, and adding some defines to pgsql/src/interfaces/libpq/win32.h, as well as including win32.h at the top of pgsql/src/interfaces/libpq/libpq-fe.h. This may need to be in a config.h file, or something like that. I fought with the win32.mak files for a while, but it turned out to be easier to use an MSVC 6 Workspace to build libpq, libpgtcl, psql as projects. I put it in pgsql/src/win32 - it seemed like a good place. That way you don't end up with Debug and Release directories all over the place, and you can put your .dlls and .exes and .libs in one place. Oh and there's an amusing free() assertion failure in the notify handler if you don't build with multithread dll runtime, but you guys got that one already? So, to summarise: 1) get libpgtcl working under win32, but with slighly broken pg_listen support 2) export DLL functions differently 3) make win32 builds slightly less noisy 4) create MSVC projects (in src/win32) Where do I send the diffs and new files? pgsql-patches? bye John
[HACKERS] Re: Should I look elsewhere??
On Mon, 05 Feb 2001, you wrote: You're not backtracing the same core file as before, are you? ??? I dont see what you mean I build postgresql with -g and then put libpq.a in my project's dir all my files are compiled with -g too the only other thing I use is posix threads How do I know what function is supposed to be in the place of ?? that gdb outputs Thanks -Mat The core file produced by something compiled with debug symbols should be much nicer to read.. -Mitch - Original Message - From: "Mathieu Dube" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 05, 2001 5:03 PM Subject: Should I look elsewhere?? So... If after recompiling postgres with -g in the CFLAGS and still getting 0x40050768 in ?? () in gdb, should I look elsewhere??? Thanks -Mat -- Mathieu Dube Mondo-Live www.flipr.com -- Mathieu Dube Mondo-Live www.flipr.com
Re: [HACKERS] Should I look elsewhere??
post the rest of the traceback. 0x40* is the address inside some shared library, most likely libc. full traceback will show what happened before it got to libc -alex On Mon, 5 Feb 2001, Mathieu Dube wrote: So... If after recompiling postgres with -g in the CFLAGS and still getting 0x40050768 in ?? () in gdb, should I look elsewhere??? Thanks -Mat
[HACKERS] Foreign keys and deferrable
Well, I'm not sure about the spec precisely, but I found out how Oracle seems to handle the delete/insert, etc cases for at least on delete cascade. It seems that they do the action (other than no action obviously) immediately upon the statement, but still defer the check for validity until commit time. So, given: create table a (a int unique); create table b (b int references a(a) on delete cascade deferrable initially deferred); insert into b values (3); insert into a values (3); select * from a; -- gives 1 row with 3 select * from b; -- gives 1 row with 3 delete from a; select * from b; -- gives no rows insert into a values (3); commit; select * from b; -- gives no rows select * from a; -- gives 1 row with 3 This is related part 2 of how Hiroshi broke up the issues with the deferred FK. If Oracle's right, then we'd probably want to: Make all non-No Action triggers not deferrable Add a check to the no action triggers to basically do a select from pk table where each key value is the same as the old values and if we get a row we've inserted a row with this same value so the constraint will be satisfied. The latter fails for MATCH PARTIAL, but that's a completely different animal entirely...
Re: [HACKERS] pg_dump data integrity java
At 11:09 5/02/01 -0800, Peter Schmidt wrote: If I pg_dump a single table which has a foreign key constraint, and subsequently backup the constraint table, won't the data be out of sync? Each pg_dump will run in it's own transaction, right? Yes. Is there a way to dump multiple tables within a single transaction without having to dump the entire database? If not, is it plausible for me to add this option i.e. pg_dump -t array_of_tables ? The version in 7.1 should allow '-t *' or '--tables' which will dump all tables. The ability to dump selected tables was not seen as a high priority. What happens when I restore if the data is out of sync? If it is dumped as inserts you will get RI errors. If done via copy then some but not all constraints will be evaluated - I think that COPY does not fire triggers. As an aside, the current documentation does not reflect all of the pg_dump options, specifically the "-f" option which will cause pg_dump to output to Current docs do cover -f. Maybe you are looking at 7.0 docs? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Re: [BUGS] syslog logging setup broken?
"Oliver Elphick" [EMAIL PROTECTED] writes: "Nic Ferrier" wrote: - the postmaster was being started without nohup If postmaster is being started by init, it should not need nohup, because init never exits and postmaster is not going to get shutdown unexpectedly. On the other hand, when pg_ctl is invoked by hand, it probably is a bug that it fails to use nohup. Perhaps this is a reason why pg_ctl should not be made into a substitute for a startup script? regards, tom lane
Re: [HACKERS] Can we modify 'text *' passed to a C function?
m w [EMAIL PROTECTED] writes: If I write a function that does a 'tolower()' on each character of a parameter passed into my function, do I have to allocate a new text object and initialize it and copy the data, or can I modify that object in place and return it? You must NEVER alter a pass-by-reference parameter, of text or any other data type. If you do, you are probably altering data in a disk buffer, thus changing the content of a tuple. Secondly, if I modify the data length of an object, as: VARDATA(field) = nn, making sure that 'nn' is smaller than the origial length, with this affect the memory management? In a palloc'd temp object it's OK if the allocated size is more than what you end up using, so yes you can set VARSIZE() to less than what you allocated. Some of the built-in functions do things this way to simplify calculation of the palloc request size, IIRC. Again, you cannot scribble on a source object. In 7.1, for toastable data types there are GETARG macros that guarantee to return a writable copy, see PG_GETARG_TEXT_P_COPY etc. I believe tolower() and similar functions use this already. regards, tom lane
Re: [HACKERS] pg_dump data integrity java
Philip Warner [EMAIL PROTECTED] writes: If it is dumped as inserts you will get RI errors. If done via copy then some but not all constraints will be evaluated - I think that COPY does not fire triggers. Say what? COPY certainly does fire triggers ... regards, tom lane
Re: [HACKERS] pg_dump data integrity java
At 20:16 5/02/01 -0500, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: If it is dumped as inserts you will get RI errors. If done via copy then some but not all constraints will be evaluated - I think that COPY does not fire triggers. Say what? COPY certainly does fire triggers ... Is there any difference between COPY and INSERT other than speed? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] pg_dump data integrity java
Philip Warner [EMAIL PROTECTED] writes: Say what? COPY certainly does fire triggers ... Is there any difference between COPY and INSERT other than speed? COPY does not fire ON INSERT rules. Other than that, AFAIR they're the same from an integrity point of view. regards, tom lane
Re: [HACKERS] Can we modify 'text *' passed to a C function?
--- Tom Lane [EMAIL PROTECTED] wrote: m w [EMAIL PROTECTED] writes: If I write a function that does a 'tolower()' on each character of a parameter passed into my function, do I have to allocate a new text object and initialize it and copy the data, or can I modify that object in place and return it? You must NEVER alter a pass-by-reference parameter, of text or any other data type. If you do, you are probably altering data in a disk buffer, thus changing the content of a tuple. Thanks, I was concerned that may be the case, but I could never find a definitive answer on this. __ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
RE: [HACKERS] Re: PostgreSQL - PHP problem
I tell you what I'd like to see in PHP. If you're using a Postgres persistent connection, and it detects a 'BEGIN TRANSACTION' going thru, once that script has finished, the connection should not be returned to the connection pool. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mitch Vincent Sent: Tuesday, February 06, 2001 2:29 AM To: [EMAIL PROTECTED] Subject: [HACKERS] Re: PostgreSQL - PHP problem In the PHP bugs I see... ===[PostgreSQL related]=== 5862 Open Consecutive pg_open statements cause second statement to fail 6525 Open Connection problem 7007 Open The pg_close function doesn't close the connection. 7236 Open 1 is not a valid PostgreSQL link resource 7264 Open 1 is not a valid PostgreSQL link 7298 Open ... not a valid link resource... after pg_connect 7312 Open Problems with pg_connect() i pg_fetch_row() 7333 Open Connection fault in circled query 7529 Open pg_connect() returns invalid connection id 7536 Open Warning: is not a valid PostgreSQL link resource 7931 Feedback Undefined symbol "_PQconnectdb" 8053 Open PGSQL doesn't detects on FBSD4 8225 Open Suddenly doesnt allow multiple psql connections from one php page 8317 Open postgresql table uppercase field name 8689 Open pg_Connect() seems to do some type of caching that doesn't quite work 8769 Open Persistent connections aren't closed when using dynamically loaded module 8907 Open pg_Close on multiple connections to same host 9048 Open problem to open several connections on 4.0.4pl1 that worked on 4.0.2 Ouch. It looks like this is exactly what is happening to me. pg_open gets called several times in these scripts.. It looks like I'll have to install an old version of PHP.. Son of a er nevermind.. Thanks guys.. -Mitch
[HACKERS] Re: Re: PostgreSQL - PHP problem
Bruce said he and Rasmus (from PHP devel) were fixing this. That'll be great! -Mitch - Original Message - From: "Christopher Kings-Lynne" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 05, 2001 8:55 PM Subject: RE: Re: PostgreSQL - PHP problem I tell you what I'd like to see in PHP. If you're using a Postgres persistent connection, and it detects a 'BEGIN TRANSACTION' going thru, once that script has finished, the connection should not be returned to the connection pool. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mitch Vincent Sent: Tuesday, February 06, 2001 2:29 AM To: [EMAIL PROTECTED] Subject: [HACKERS] Re: PostgreSQL - PHP problem In the PHP bugs I see... ===[PostgreSQL related]=== 5862 Open Consecutive pg_open statements cause second statement to fail 6525 Open Connection problem 7007 Open The pg_close function doesn't close the connection. 7236 Open 1 is not a valid PostgreSQL link resource 7264 Open 1 is not a valid PostgreSQL link 7298 Open ... not a valid link resource... after pg_connect 7312 Open Problems with pg_connect() i pg_fetch_row() 7333 Open Connection fault in circled query 7529 Open pg_connect() returns invalid connection id 7536 Open Warning: is not a valid PostgreSQL link resource 7931 Feedback Undefined symbol "_PQconnectdb" 8053 Open PGSQL doesn't detects on FBSD4 8225 Open Suddenly doesnt allow multiple psql connections from one php page 8317 Open postgresql table uppercase field name 8689 Open pg_Connect() seems to do some type of caching that doesn't quite work 8769 Open Persistent connections aren't closed when using dynamically loaded module 8907 Open pg_Close on multiple connections to same host 9048 Open problem to open several connections on 4.0.4pl1 that worked on 4.0.2 Ouch. It looks like this is exactly what is happening to me. pg_open gets called several times in these scripts.. It looks like I'll have to install an old version of PHP.. Son of a er nevermind.. Thanks guys.. -Mitch
Re: [HACKERS] OID from insert has extra letter
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: Get: 31930584, should be 3193058 Get: 31943386, should be 3194338 As you can see, there is an extra digit added. I am using PQoidStatus() to get the OID. Is this the right way? Is it possible that there is a buffer/terminating problem in the insert code? Yes, on looking at it I see that someone broke PQoidStatus() in 7.0. If you want to fix your copy, the patch (line numbers are for current CVS) is Index: fe-exec.c === RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v retrieving revision 1.98 retrieving revision 1.100 diff -c -r1.98 -r1.100 *** fe-exec.c 2001/01/24 19:43:30 1.98 --- fe-exec.c 2001/02/06 02:02:27 1.100 *** *** 2035,2041 if (len 23) len = 23; strncpy(buf, res-cmdStatus + 7, len); ! buf[23] = '\0'; return buf; } --- 2035,2041 if (len 23) len = 23; strncpy(buf, res-cmdStatus + 7, len); ! buf[len] = '\0'; return buf; } However, really I'd recommend using PQoidValue() and not PQoidStatus() at all. regards, tom lane
Re: [HACKERS] with -g and libpq.a ...
Mathieu Dube [EMAIL PROTECTED] writes: even after compiling with -g and using the libpq.a it still outputs that on the bt after the crash... (gdb) bt #0 0x40050768 in ?? () #1 0x4004ff25 in ?? () #2 0x804a3cd in main (argc=3, argv=0xb8e4) at therver.c:430 #3 0x400739ee in ?? () So what's the code in and around line 430 of therver.c? Probably you are looking at a crash in some libc routine, perhaps we can guess which by seeing what was called. regards, tom lane
Re: [HACKERS] Foreign Key issue - pg_shadow
"Rod Taylor" [EMAIL PROTECTED] writes: I suppose the below reference isn't intended to be done. But, I'd like to extend the pg_shadow table to store information about the database users and some of their actions (a history of changes). Is it safe to add a UNIQUE constraint to the usesysid or usename columns? You can't do either of those things, at least not without modifying code in the backend. The code that manipulates pg_shadow entries knows exactly what indexes exist on the table, so you can't just go and create more. Also, that code will not fire triggers, so even if the correct unique index existed, you would not get correct referential integrity behavior. However, it does seem like an oversight that we don't use unique indexes to enforce uniqueness of usename and usesysid. Too late to fix it for 7.1 (unless we want to force another initdb), but it ought to get fixed. Bruce, a TODO item please? regards, tom lane
Re: [HACKERS] Using Threads?
I have put a new version of my multi-threaded postgresql experiment at http://www.sacadia.com/mtpg.html This one actually works. I have added a server based on omniORB, a CORBA 2.3 ORB from ATT. It is much smaller than TAO and uses the thread per connection model. I haven't added the java side of the JNI interface yet but the C++ side is there. It's still not stable but it is much better than the last. Myron Scott [EMAIL PROTECTED]
Re: [HACKERS] 7.1 beta 3 CHANGES FOR QNX
"Maurizio" [EMAIL PROTECTED] writes: ECHO is defined in the following QNX gcc include files : termio.h termios.h If ECHO was not redefined in pgc.l you can't compile in embedded SQL C. Well, my question still stands: why aren't the other four flex outputs also broken? They all use ECHO. In any case, I'd prefer to see this fixed by not including termios.h rather than hacking up the .l files. Surely it doesn't need to be included everywhere, as src/include/port/qnx4.h is now causing to happen. In fact, it looks to me like qnx4.h probably includes and defines a lot more than it needs to; would you experiment with stripping it down? I am also checking for another problem. I have some errors if I compile pgsql without change the typedef Size in c.h. To succesfully compile pgsql I have changed typedef Size in int insteed size_t. That strikes me as *horribly* dangerous. There is too much code whose behavior might change in unpleasant ways if Size becomes a signed type. Please explain what problems you are seeing that make you think this is a good idea. Andreas, the QNX port is largely your work IIRC. What do you think of these issues? Have you tried 7.1beta on QNX? regards, tom lane
Re: [HACKERS] Re: [BUGS] syslog logging setup broken?
If postmaster is being started by init, it should not need nohup, because init never exits and postmaster is not going to get shutdown unexpectedly. On the other hand, when pg_ctl is invoked by hand, it probably is a bug that it fails to use nohup. Perhaps this is a reason why pg_ctl should not be made into a substitute for a startup script? If pg_ctl unconditionally use nohup, it might be a performance penalty as Oliver mentioned. nohup has a performance cost, in that (at least on Linux) it automatically nices (lowers the priority of) the process. You may not want the priority lowered... Moreover if postmaster detaches itself to be a deamon, nohup is not necessary at all. BTW, for the startup script, I don't think we need to use pg_ctl. Invoking postmaster directry seems enough for me. The only reason for using pg_ctl to start postmaster is waiting for postmaster up and running. In most cases the time to recover DB would not be so long. And if the recovery took too long time, we would not want to be blocked in the middle of the boot sequence anyway. Comments? -- Tatsuo Ishii
Re: [HACKERS] Foreign Key issue - pg_shadow
"Rod Taylor" [EMAIL PROTECTED] writes: I suppose the below reference isn't intended to be done. But, I'd like to extend the pg_shadow table to store information about the database users and some of their actions (a history of changes). Is it safe to add a UNIQUE constraint to the usesysid or usename columns? You can't do either of those things, at least not without modifying code in the backend. The code that manipulates pg_shadow entries knows exactly what indexes exist on the table, so you can't just go and create more. Also, that code will not fire triggers, so even if the correct unique index existed, you would not get correct referential integrity behavior. However, it does seem like an oversight that we don't use unique indexes to enforce uniqueness of usename and usesysid. Too late to fix it for 7.1 (unless we want to force another initdb), but it ought to get fixed. Bruce, a TODO item please? Added to TODO: * Add unique indexes to pg_shadow.usename and pg_shadow.usesysid -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] timestamp in pg_dump
I'd like to have pg_dump for 7.1 produce "timestamp with time zone" when dealing with timestamp type(s). That will prepare us for introducing a timestamp type without time zones, while allowing reasonable upgrades to 7.2. Did that. Great! But given that 'timestamp' is really 'timestamp with time zone' and there is no 'timestamp without time zone', isn't there something wrong with the documentation? See User's Guide, table 3-6: Hmm. Not sure *where* some of that info came from; perhaps it is a holdover from when timestamp was implemented as a 4 byte integer. Or it was space aliens ;) Will update asap. Thanks for catching it... - Thomas
Re: [HACKERS] alter the column type
i would like to alter the column type which has exist in the table, i can't find anything about that, can anybody help me? To effectively alter the type of a column, you must do a "create table"/"insert into"/"create table as" combination to make an intermediate temporary table with the correct type (of course, other permutations of commands can give the desired result also). - Thomas
Re: [HACKERS] Parallel queries
How hard would it be to get a back end to hand off parts of a query tree to other back ends (which it created), and then collate the results, and take it from there? Obviously, it would only do this under certain conditions, specifically, if it was compiled in to the server, and the query tree in question had a particular profile, i.e.: lent itself to parallel processing. But surely it couldn't be that difficult to get a back end to hand off, say, a sub-select sub-tree, or UNION sub-trees. Under heavy loads with the right hardware, this could gain significant performance improvement. Right. This would help speed up some single-client queries on SMP machines (we already scale will with multiple clients on SMP machines). This could perhaps be solved by threading each client backend, or, as you suggest, by spawning entirely new processes (which would easily ?? generalize into distributed database support). Bring it up after 7.1 is released (though there may be enough hoops to jump through that it would not be in 7.2; not certain yet). All imho of course, though we've had a few discussions off-list to help get us thinking about it for planning purposes. I would assume that it will take a few folks working together to get this to fly, but feel free to give it a go on your own. - Thomas
[HACKERS] Re: [PATCHES] A Sparc/Linux patch (for 7.1), and a Linux rc.d/init.dscript....
On Sun, 4 Feb 2001, Peter Eisentraut wrote: Ryan Kirkpatrick writes: postgresql - This is a Linux distribution independent (or so I hope) init.d/rc.d script that makes use of pg_ctl. There is currently a few in ./contrib/linux of the pgsql source tree, but they are RedHat specific. This one is simple and self contained. Might be worth adding to the other scripts. I don't see how this can be more independent if it uses DAEMON=/home/postgres/bin/pg_ctl Ooops That is my mistake... Should have been /usr/local/pgsql/bin/pg_ctl. I have /usr/local/pgsql/ symlinked to /home (where there is more, faster disk space). I can submit a patch, or can some one just fix it? LOG="/usr/local/pgsql/server.log" What is wrong with that? There really is no standard on where to put the log file, so it is either here or /var/log. su - postgres sh -c "$DAEMON stop /dev/null" Hmm... What is wrong here, besides the ''? The '' can be replaced with '21 ' if that is more standard. an appropriate way to stop the server in a system shutdown situation. Uh... Isn't that the way you are supposed to stop it? pg_ctl stop? IMHO, rather than accumulating a bunch of versions that somebody liked better than the existing ones, why not provide actual scripts from actual distributions? Generic scripts will just lead to generic problems. Fine by me... I just put mine up as a suggestion, as the only other one in contrib was very RH specific. The one included with the debian package is very debian specific (and over complex in IMHO). I just submitted mine as a possible generic Linux version that should work with bash on most installation. If some one has a better idea, then by all means post it. Also, from the way I understand contrib, they are user submissions and are in no way assured to work on any other machine than the user's who submitted it. I take it as given anything in contrib might need a bit of editing to fit my needs. Anyway, just my response as one who has used Slackware, RedHat, and now Debian over the years, but who in no way claims to be an uber-Linux or Unix hacker or sysadmin. :) PS. I do play a part time Linux sysadmin in real though... --- | "For to me to live is Christ, and to die is gain."| |--- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | ---
Re: [HACKERS] WAL Crash during index vacuuming 7.1beta4
during the nightly vacuum pgsql closed and do not start any more. Attached the log. Seems the problem was rebuilding an Index, There is a way to force wal to ignore indexes ? The problem was in redoing tuple movement in *table*. Can I delete it ? ... DEBUG: redo starts at (6, 4144414796) NOTICE: PageAddItem: tried overwrite of used ItemId ^^^ FATAL 2: heap_update_redo: failed to add tuple I think that I've just fixed this problem (must not check itemid' flag in PageAddItem in overwrite mode when offset number == maxoff + 1). I hope that Giuseppe will check new code soon. Thanks to Giuseppe for help! Vadim
