Re: [HACKERS] Tablespace and cpu costs
Gavin Sherry wrote: On Wed, 18 Aug 2004, Gaetano Mendola wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, now that we have the tablespace support don't you think that each tablespace needs his own costs instead of a system wide one ? I searched in the archives and on the TODO list without success. Yep. I've looked at this but didn't think it necessary for the first stage of implementation. I definately plan to do it for 8.1 Do you have an Idea on how you want implement this ( usage side ) ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] SRPM for 8.0.0 beta?
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: BTW I have bitten recently on the attempt of change the default port. I did it as usual changing it in postgresql.conf but that parameter is overriden by: PGPORT=5432 present on the start up script. There is any reason to still pass this parameter to postmaster ? The init script needs to know the port number because it uses it in a couple places. Set the value you want in /etc/sysconfig/pgsql/postgresql ... I know I know, but I'm wondering if this value could be retrieved from another place instead: grep -e port /var/lib/pgsql/data/postgresql.conf | cut -d '=' -f 2 I know it's horrible. May be write some words inside postgresql.conf or write some warning in the logs for sure could help. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] vacuums and ctid
Hi all, looking at how ctid is used is clear to me: insert line (A) in a table; == ctid = (0, 1); rollback an update for the line (A); insert a line (B) in the table; == ctid = (0, 3); vacuum full; insert a line (C) in the table; == ctid = (0, 2); I was expecting this for a normal vacuum not for a vacuum full. for a vacuum full I was expecting: insert line (A) in a table; == ctid = (0, 1); rollback an update for the line (A); insert a line (B) in the table; == ctid = (0, 3); vacuum full; ( line B moved in ctid ( 0, 2) ) insert a line (C) in the table; == ctid = (0, 3); could this due the fact that moving that tuple will not save any storage space ? If not, am I missing something ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Does psql use nested transactions?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Added to TODO list: * Add an option to automatically use savepoints for each statement in a multi-statement transaction. When enabled, this would allow errors in multi-statement transactions to be automatically ignored. Like I said, my preference would be that it couldn't be turned on at all except when psql's input is interactive. I strongly vote for the same solution. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] will PITR in 8.0 be usable for hot spare/log
Eric Kerin wrote: On Sat, 2004-08-14 at 01:11, Tom Lane wrote: Eric Kerin [EMAIL PROTECTED] writes: The issues I've seen are: 1. Knowing when the master has finished the file transfer transfer to the backup. The standard solution to this is you write to a temporary file name (generated off your process PID, or some other convenient reasonably- unique random name) and rename() into place only after you've finished the transfer. Yup, much easier this way. Done. 2. Handling the meta-files, (.history, .backup) (eg: not sleeping if they don't exist) Yeah, this is an area that needs more thought. At the moment I believe both of these will only be asked for during the initial microseconds of slave-postmaster start. If they are not there I don't think you need to wait for them. It's only plain ol' WAL segments that you want to wait for. (Anyone see a hole in that analysis?) Seems to be working fine this way, I'm now just returning ENOENT if they don't exist. 3. Keeping the backup from coming online before the replay has fully finished in the event of a failure to copy a file, or other strange errors (out of memory, etc). Right, also an area that needs thought. Some other people opined that they want the switchover to occur only on manual command. I'd go with that too if you have anything close to 24x7 availability of admins. If you *must* have automatic switchover, what's the safest criterion? Dunno, but let's think ... I'm not even really talking about automatic startup on fail over. Right now, if the recovery_command returns anything but 0, the database will finish recovery, and come online. This would cause you to have to re-build your backup system from a copy of master unnecessarily. Sounds kinda messy to me, especially if it's a false trigger (temporary io error, out of memory) Well, this is the way most of HA cluster solution are working, in my experience the RH cluster solution rely on a common partition between the two nodes and on a serial connection between them. For sure for a 24x7 service is a compulsory requirement have an automatic procedure that handle the failures without uman intervention. Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] will PITR in 8.0 be usable for hot spare/log
Eric Kerin wrote: On Sun, 2004-08-15 at 16:22, Gaetano Mendola wrote: Eric Kerin wrote: On Sat, 2004-08-14 at 01:11, Tom Lane wrote: Eric Kerin [EMAIL PROTECTED] writes: The issues I've seen are: 1. Knowing when the master has finished the file transfer transfer to the backup. The standard solution to this is you write to a temporary file name (generated off your process PID, or some other convenient reasonably- unique random name) and rename() into place only after you've finished the transfer. Yup, much easier this way. Done. 2. Handling the meta-files, (.history, .backup) (eg: not sleeping if they don't exist) Yeah, this is an area that needs more thought. At the moment I believe both of these will only be asked for during the initial microseconds of slave-postmaster start. If they are not there I don't think you need to wait for them. It's only plain ol' WAL segments that you want to wait for. (Anyone see a hole in that analysis?) Seems to be working fine this way, I'm now just returning ENOENT if they don't exist. 3. Keeping the backup from coming online before the replay has fully finished in the event of a failure to copy a file, or other strange errors (out of memory, etc). Right, also an area that needs thought. Some other people opined that they want the switchover to occur only on manual command. I'd go with that too if you have anything close to 24x7 availability of admins. If you *must* have automatic switchover, what's the safest criterion? Dunno, but let's think ... I'm not even really talking about automatic startup on fail over. Right now, if the recovery_command returns anything but 0, the database will finish recovery, and come online. This would cause you to have to re-build your backup system from a copy of master unnecessarily. Sounds kinda messy to me, especially if it's a false trigger (temporary io error, out of memory) Well, this is the way most of HA cluster solution are working, in my experience the RH cluster solution rely on a common partition between the two nodes and on a serial connection between them. For sure for a 24x7 service is a compulsory requirement have an automatic procedure that handle the failures without uman intervention. Regards Gaetano Mendola Already sent this to Gaetano, didn't realize the mail was on list too: Redhat's HA stuff is a fail over cluster, not a log shipping cluster. Once the Backup detects a failure of the master, it powers the master off, and takes over all devices, and network names/IP addresses. We are using RH HA stuff since long time and is not necessary have the master powered off ( our setup don't ). In log shipping, you can't even be sure that both nodes will be close enough together to have multiple communication methods. At work, we have an Oracle log shipping setup where the backup cluster is a thousand or so miles away from the master cluster, separated by a T3 link. For a 24x7 zero-downtime type of system, you would have 2 Fail over clusters, separated by a few miles(or a few thousand). Then setup log shipping from the master to the backup. That keeps the system online incase of a single node hardware failure, without having to transfer to the backup log shipping system. The backup is there incase the master is completely destroyed (by fire, hardware corruption, etc) Hence the reason for the remote location. I totally agree with you but not all people can set up a RH HA cluster or equivalent solutions ( is needed very expensive SAN with double port ) and this software version could help in a low cost setup. The scripts that I posted do the failover between master and slave in automatic way delivering also the partial WAL ( I could increase the robusteness checking also a serial connection ) without need expensive HW. For sure this way to proceed ( the log shipping activity ) will increase the availability in case of total disaster ( actualy I transfer to another location a plain dump each 3 hours :-( ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PITR on Windows?
Bruce Momjian wrote: Andrew Dunstan wrote: [EMAIL PROTECTED] wrote: I notice that PITR doesn't function correctly on Windows. Has that been reported elsewhere? The archive_command parameter %p resolves to a full path containing slashes rather than backslashes. This is not a Windows file, so any attempt to copy it fails. There isn't any way to avoid that. I'm surely not the first to report that? Am I? This was fixed several days ago, after lengthy discussion on the -win32 list, and we have seen reported success, IIRC. Yes, the fix will be in beta2 or the next pginstaller release. Do you mean that the next pginstaller release will carry a 8.0beta1 with different behaviour that actual 8.0beta1 ? I hope I mistake you. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Calling PL functions with named parameters
Oliver Jowett wrote:
Tom Lane wrote:
On a related note, it would also be nice to have default
parameters and some way to say to use them.
That is fundamentally not ever going to happen, because it blows
overloaded-function resolution out of the water: there is no way to
choose whether foo(42, 2.5) matches foo(int, float) or
foo(int, float, something-with-a-default). Let's try to limit our
attention to something that might actually work.
C++ manages to solve this problem, although I can't remember the exact
mechanics (and C++ is usually not a good example to follow anyway ;)
Your're wrong:
try to compile this:
void foo( int a, float b ) { }
void foo( int a, float b, int c=0 ) { }
int main(char argc, char **argv)
{
foo( 42, 2.5 );
return 0;
}
you'll get:
a.cpp:6: error: call of overloaded `foo(int, double)' is ambiguous
a.cpp:1: error: candidates are: void foo(int, float)
a.cpp:2: error: void foo(int, float, int)
usualy C++ is not a good example as SQL is not :-)
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Calling PL functions with named parameters
Oliver Jowett wrote: Gaetano Mendola wrote: Oliver Jowett wrote: David Fetter wrote: Dennis has pointed out that mixing the call-with-named-parameter interface with call-by-order-of-parameters one would cause confusion, Python's equivalent syntax allows you to mix the two forms so long as all the by-position parameters come first: python don't have overloaded functions... It doesn't change how you'd handle overloaded functions; you still have a type for every parameter available. I think will be a mess that will break the minor surprise principle, even the bad C++ stays away from this field ( se explicit constructors, and automatic cast limited to only one level ). I know I know the Koenig Lookup is there as a Damocle's sword... Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] will PITR in 8.0 be usable for hot spare/log
Eric Kerin wrote: On Wed, 2004-08-11 at 16:43, Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: Tom Lane wrote: It should work; dunno if anyone has tried it yet. I was thinking about it but I soon realized that actually is impossible to do, postgres replay the log only if during the start the file recover.conf is present in $DATA directory :-( SNIP Somebody should hack this together and try it during beta. I don't have time myself. regards, tom lane I've wrote up a very quick, and insanely dirty hack to do log shipping. Actually, it's so poorly written I kinda feel ashamed to post the code. But so far the process looks very promising, with a few caveats. The issues I've seen are: 1. Knowing when the master has finished the file transfer transfer to the backup. 2. Handling the meta-files, (.history, .backup) (eg: not sleeping if they don't exist) 3. Keeping the backup from coming online before the replay has fully finished in the event of a failure to copy a file, or other strange errors (out of memory, etc). I did the same work and I had the same problems solved in the exact way you did, however my version was shell based ( wasted time ) :-( I guess is better mantain the C version, I will take a look at it and I will modify it if something doesn't work. Good work. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 8.0beta1 hot spare how to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
I seen that Eric Kerin did the work suggested by Tom about
how to use the PITR in order to have an hot spare postgres,
writing a C program.
I did the same writing 2 shell scripts, one of them perform
the restore the other one deliver the partial filled wal and
check if the postmaster is alive ( check if the pid process
still exist ).
With these two scripts I'm able to have an hot spare installation,
and the spare one go alive when the first postmaster dies.
How test it:
1) Master node:
modify postgresql.conf using:
~archive_command = 'cp %p /mnt/server/archivedir/%f'
~launch postgres and perform a backup as doc
~http://developer.postgresql.org/docs/postgres/backup-online.html
suggest to do
launch the script:
partial_wal_deliver.sh PID /mnt/server/partialdir pg_xlog path
~this script will delivery each 10 seconds the current wal file,
~and touch the alive file in order to notify the spare node that
~the master node is up and running
2) Spare node:
create a recovery.conf with the line:
~restore_command = 'restore.sh /mnt/server/archivedir/%f %p
/mnt/server/partialdir'
~replace the content of data directory with the backup performed at point 1,
~remove any file present in the pg_xlog directory ( leaving there the
archive_status
~directory ) and remove the postmaster.pid file ( this is necessary if you are
running
~the spare postgres on the same hw ).
~launch the postmaster, the restore will continue till the alive file
present in the
~/mnt/server/partialdir directory is not updated for 60 seconds ( you can
modify this
~values inside the restore.sh script ).
Be sure that restore.sh and all directories involved are accessible
Let me know.
This is a first step, of course, as Eric Kerin did, is better port these script
in C and make it more robust.
Postgres can help this process, as suggested by Tom creating a pg_current_wal()
or even better having two new GUC parameters: archive_current_wal_command and
archive_current_wal_delay.
I problem I discover during the tests is that if you shut down the spare node
and the restore_command is still waiting for a file then the postmaster will never
exit :-(
Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBHe+S7UpzwH2SGd4RAss1AJ0ZfnhSZWWvbRjvbgAlpLu0dtxKIwCg22B+
6bFDyutTVow6+mcTNfD3Cuc=
=Ot+H
-END PGP SIGNATURE-
#!/bin/bash
PID=$1
PARTIAL=$2
PGXLOG=$3
function copy_last_wal
{
FILE=$( ls -t1p $PGXLOG | grep -v / | head -1 )
echo Last Wal $FILE
cp ${PGXLOG}/${FILE} ${PARTIAL}/${FILE}.tmp
mv ${PARTIAL}/${FILE}.tmp ${PARTIAL}/${FILE}.partial
find ${PARTIAL} -name *.partial | grep -v ${FILE} | xargs -i rm -fr {}
}
while [ 1 ]
do
ps --pid $PID /dev/null 21
ALIVE=$?
if [ ${ALIVE} == 1 ]
then
#The process is dead
echo Process dead
copy_last_wal
exit 1
fi
#The process still exist
touch ${PARTIAL}/alive
copy_last_wal
sleep 10
done
#!/bin/bash
SOURCE=$1
TARGET=$2
PARTIAL=$3
SIZE_EXPECTED=16777216 #bytes 16 MB
DIED_TIME=60#seconds
function test_existence
{
if [ -f ${SOURCE} ]
then
COUNTER=0
#I have to check if the file is begin copied
#I assume that it will reach the right
#size in a few seconds
while [ $(stat -c '%s' ${SOURCE} ) -lt $SIZE_EXPECTED ]
do
sleep 1
let COUNTER+=1
if [ 20 -lt $COUNTER ]
then
exit 1# BAILING OUT
fi
done
cp $SOURCE $TARGET
exit 0
fi
echo ${SOURCE} not found
#if is looking for a history file and not exist
#I have suddenly exit
echo $SOURCE | grep history /dev/null 21 exit 1
}
while [ 1 ]
do
test_existence
#CHECK IF THE MASTER IS ALIVE
DELTA_TIME=$(( $( date +'%s' ) - $( stat -c '%Z' ${PARTIAL}/alive ) ))
if [ $DIED_TIME -lt $DELTA_TIME ]
then
echo Master is dead...
# Master is dead
CURRENT_WAL=$( basename $SOURCE )
echo Partial: ${PARTIAL}
echo Current wal: ${CURRENT_WAL}
echo Target: ${TARGET}
cp ${PARTIAL}/${CURRENT_WAL}.partial ${TARGET} /dev/null 21 exit 0
exit 1
fi
sleep 1
done
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Calling PL functions with named parameters
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Oliver Jowett [EMAIL PROTECTED] writes:
I think you just made my point for me. C++ allows default parameters
and resolves the ambiguity by disallowing ambiguous calls when they
happen.
I'm not sure why C++ doesn't disallow it at declaration time off the
top of my head -- perhaps because you'd get inconsistent behaviour if
the candidates were split across compilation units.
IIRC this was due to multiple unheritance. You could inherit methods
with the same name and parameter list from two different base classes.
Disallowing that at declaration time would mean disallowing
inheritance (even indirectly) from these two base classes, even though
the derived class didn't use the ambiguous methods.
You get the point, and with a linear hierarchy the last function hide
the previous one:
struct A { void foo(int) { } };
struct B : A { void foo(int, int a = 3) { } };
B b;
b.foo(3);
will call the B::foo.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] PITR on Windows?
Andrew Dunstan wrote: Gaetano Mendola wrote: Bruce Momjian wrote: Yes, the fix will be in beta2 or the next pginstaller release. Do you mean that the next pginstaller release will carry a 8.0beta1 with different behaviour that actual 8.0beta1 ? I hope I mistake you. What are you talking about? The installer project doesn't have to sit around waiting for the next beta before they include patches that have occurred since then. They can package whatever they like, in fact (and they have been doing a fantastic job, IMNSHO). After all, their releases are beta too. You can run against cvs HEAD and it will be marked 8.0beta1. So? I don't like it neither. Am I criticizing their job ? I think is better have a 8.0beta1 and a 8.0beta1devel, this just in order to understand which version the users are testing. But of course I'm the last in this list that can decide about it. BTW other projects are carrying also a build version in order to distinguish between two 8.0beta1 versions. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Calling PL functions with named parameters
Oliver Jowett wrote: Gaetano Mendola wrote: Oliver Jowett wrote: Gaetano Mendola wrote: Oliver Jowett wrote: David Fetter wrote: Dennis has pointed out that mixing the call-with-named-parameter interface with call-by-order-of-parameters one would cause confusion, Python's equivalent syntax allows you to mix the two forms so long as all the by-position parameters come first: python don't have overloaded functions... It doesn't change how you'd handle overloaded functions; you still have a type for every parameter available. I think will be a mess that will break the minor surprise principle, even the bad C++ stays away from this field ( se explicit constructors, and automatic cast limited to only one level ). I don't understand your argument. What is the surprising behaviour you are worried about? I'm worried about: (1) foo( integer, float); (2) foo( integer, integer, float a = 3 ); which one is called with: foo( 2, 2 )? the first one because have two parameters or the second one that better match the arguments ? Whatever policy we adopt someone could argue that the (2) have a signature with 3 parameters so the (1) shall be called, and someone can argue that (2) is equivalent to: (2a) foo(integer, integer) (2b) foo(integer, integer, float); so the (2) have to be called. BTW C++ adopt the latter. Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Calling PL functions with named parameters
Oliver Jowett wrote: Gaetano Mendola wrote: Oliver Jowett wrote: David Fetter wrote: Dennis has pointed out that mixing the call-with-named-parameter interface with call-by-order-of-parameters one would cause confusion, Python's equivalent syntax allows you to mix the two forms so long as all the by-position parameters come first: I'm worried about: (1) foo( integer, float); (2) foo( integer, integer, float a = 3 ); which one is called with: foo( 2, 2 )? This is a separate issue to the one I was discussing above. I am not talking about default arguments at all here; I am talking about mixing positional parameter syntax with named parameter syntax. I see. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] hot spare / log shipping work on
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm having some spare time and I'm testing what Tom Lane was suggesting: === Tom Lane wrote: 1. You set up WAL archiving on the master, and arrange to ship copies of completed segment files to the slave. 2. You take an on-line backup (ie, tar dump) on the master, and restore it on the slave. 3. You set up a recover.conf file with the restore_command being some kind of shell script that knows where to look for the shipped-over segment files, and also has a provision for being signaled to stop tracking the shipped-over segments and come alive. 4. You start the postmaster on the slave. It will try to recover. Each time it asks the restore_command script for another segment file, the script will sleep until that segment file is available, then return it. 5. When the master dies, you signal the restore_command script that it's time to come alive. It now returns no such file to the patiently waiting postmaster, and within seconds you have a live database on the slave. === How I'm expanding the point above: 1) This is the easy part and the task can be accomplished with a simple: cp %p /mnt/server/archivedir/%f 2) Easy task 3+4) I already wrote the restore_command that do the trick, it take 3 ~ parameters: source target partial_directory ~ The partial_directory will contain the partial_segment shipped each ~ minute, and a file alive that is touched periodically ~ The script when called perform these tasks: a) Check if the file requested exist a1) If exist check that is a 16MB file ( the request can ~arrive during the copy ), if is not 16MB sleep for ~1 second and retry. This is done for 20 try, after ~this time out the script exit with a nonzero return time. ~When the file reach a size of 16MB ( or is already a 16MB ~file then it's copied with: cp source target ~a2) If the file not exist this mean that is not yet recycled and ~is a partial file present on the partial directory, ~check if the alive file is older then 2 minutes. ~ a21) If the file is older than 2 minutes I assume that ~the master is dead: I move the partial WAL file ~present in the partial directory to the target ~directory, and I exit returning a 0 ( the asked file ~was the partial ). If the partial file do not exist ~this mean that in the previous call I already moved the ~partial file and then I have to exit with a nonzero value. a22) If the file is newer than 2 minutes I assume that ~the master is alive and I sleep for 5 seconds and I ~restart from the point a) 5) If the master dies the daemon ( a running shell script ) that is running on ~ the master will not touch the alive file. ~ If the master is alive the daemon copy the current WAL file in the partial ~ directory with the name current_name.tmp and after the copy: ~ mv current_name.tmp current_name.partial Do you see any pitfall on it ? I think in an hour I'll test it and I let you know. Regard Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBHNW+7UpzwH2SGd4RAsMBAJ9diSsgG3y6rnueWbZLOvjzko07OwCdGaxE f8mwC9A4sDJ8nN+XhcUKjP8= =9SrG -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] hot spare / log shipping work on
Manfred Spraul wrote: Gaetano Mendola wrote: a1) If exist check that is a 16MB file ( the request can ~arrive during the copy ), I think this will fail under windows: copy first sets the file size and then transfers the data. I wouldn't rule out that some Unices use the same implementation. I'm doing this work as proof of concept, after made it working on my platform we can even write them in C. ~a2) If the file not exist this mean that is not yet recycled and ~is a partial file present on the partial directory, ~check if the alive file is older then 2 minutes. ~ a21) If the file is older than 2 minutes I assume that ~the master is dead: I'd concentrate on cold failover: the user (or the OS) must call a script to cause a fail-over. The tricky thing are the various partial connection losses between master and spare: perhaps the alive file is not updated anymore due to a net split, but the master is still alive. Unless you are really careful both master and spare could run. I agree but as I said I'm doing it as proof of concept, we can even change later the way to discovery that master is dead. I'm facing however to the following problems: 1) Discovery the actual WAL file I'm supposing is the last modified file inside the pg_xlog directory. If this is not the good method may I know how I can know it ? 2) During the recovery phase postmaster ask me for file that will never be there like: 0001.history. Actualy if the file does not exist and contain the string history instead to wait for him I exit with 1 exit code Is this right for you ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] hot spare / log shipping work on
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I'm facing however to the following problems: 1) Discovery the actual WAL file I'm supposing is the last modified file inside the pg_xlog directory. If this is not the good method may I know how I can know it ? While that theoretically will work, it leaves a bad taste in my mouth. I have been thinking of proposing that we add a pg_current_wal_file() function, or some such name, to return the name of the active WAL file. Totally agree, this could help during the process. Actually I detect the current wal file in this way: ls -t1p $PGXLOGDIR | grep -v / | head 1 that is an almost empirical process, in the first phase I can live with it but for sure a more robust way is a must. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Calling PL functions with named parameters
Oliver Jowett wrote: David Fetter wrote: Dennis has pointed out that mixing the call-with-named-parameter interface with call-by-order-of-parameters one would cause confusion, and I think it would be OK to disallow this type mixing, so SELECT foo_func(name AS 'yet another name', 35); would be disallowed. Python's equivalent syntax allows you to mix the two forms so long as all the by-position parameters come first: def f(a,b,c,d): ... print a,b,c,d ... f(1,2,3,4) 1 2 3 4 f(1,2,c=3,d=4) 1 2 3 4 f(1,2,d=4,c=3) 1 2 3 4 f(1,d=4,2,c=3) SyntaxError: non-keyword arg after keyword arg python don't have overloaded function... Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] will PITR in 8.0 be usable for hot spare/log shipping type
Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: will PITR in 8.0 be usable for hot spare/log shipping type of replication or is it just for Point In Time RECOVERY ? It should work; dunno if anyone has tried it yet. I was thinking about it but I soon realized that actually is impossible to do, postgres replay the log only if during the start the file recover.conf is present in $DATA directory :-( Am I missing the point ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] will PITR in 8.0 be usable for hot spare/log shipping type
Tom Lane wrote: Somebody should hack this together and try it during beta. I don't have time myself. Will see, if I have spare time I will try. Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] VACUUM DELAY
Jan Wieck wrote: On 8/9/2004 7:41 PM, Gaetano Mendola wrote: If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; You got a point here. However, we don't have SELECT foo FROM bar WHERE baz = 'bumm' NOSEQSCAN; either, and I hope you don't suggest doing that next :-) Good idea indeed :-) This could be the first step to give some hints to the planner, Informix have it for sure and if I remember well Oracle have it, Sybase have it... Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Postgres development model (was Re: CVS comment)
Peter Eisentraut wrote: Tom Lane wrote: I haven't seen any particular reason why we should adopt another SCM. Perhaps BitKeeper or SubVersion would be better for our purposes than CVS, but are they enough better to justify the switchover costs? BitKeeper ist not open source, so it's out of the question for most people. Not for Linus Torvalds apparently. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] VACUUM DELAY
Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; this will permit to change easilly the delay in the maintainance scripts. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] VACUUM DELAY
Jan Wieck wrote: On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM WITH DELAY 100 [ ]; Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] VACUUM DELAY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alvaro Herrera wrote: | On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: | | |So the other parameter will inserted in the new sintax too, I think is |fundamental |the ability of override this values during the vacuum call: | |VACUUM WITH DELAY 100 [ ]; | | | What's wrong with | | SET vacuum_delat 100; | SET whatever_parameter 'value'; | VACUUM ...; Noting wrong but: 1) The parameters and new feature will be spotted out better to new users 2) My shell script will become less hugly :-) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBF8wv7UpzwH2SGd4RAnSHAJ0QI0Uu9ZVJiMFn3NY5jFT6omdkYwCfZ8pU BaVnYczZ9pGGTBXMurNtj30= =hP7Q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] VACUUM DELAY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jan Wieck wrote: | On 8/9/2004 1:19 PM, Gaetano Mendola wrote: | | Jan Wieck wrote: | | On 8/9/2004 7:19 AM, Gaetano Mendola wrote: | | Hi all, | I have seen the big debat about to have the delay | off or on by default. | | Why not enable it by default and introduce a new | parameter to vacuum command itself ? Something like: | | | VACUUM WITH DELAY 100; | | | | It's not just one parameter to tune here. It is a set of parameters | that all together need to be viewed as a whole. The slowdown will be | affected by the other parameters as well, so turning the millisecond | knob only is not even half of the story. | | | So the other parameter will inserted in the new sintax too, I think is | fundamental | the ability of override this values during the vacuum call: | | VACUUM WITH DELAY 100 [ ]; | | | You can do it right now. | | set vacuum_cost_delay = 100; | vacuum analyze; | No need to panic. No need to be smarty pants too. I know that it can be possible, after all 4 years for a dummy like I'm, are enough to understand that is possible to change some GUC for a given connection. :-) However I think is annoying to write: set vacuum_cost_delay = 100; vacuum table big_huge; set vacuum_cost_delay = 0; set whatelse; vacuum table night_table; or even better: psql -c set vacuum_cost_delay = 100; vacuum analyze; and what about the utility vacuumdb ? If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGAud7UpzwH2SGd4RAjR0AKDw8XLAI2Lo2uqRauwhWJWwGmwYtgCgmI7u WDZvqwUMzuwXN6Z1qqj91vs= =Wxpz -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] CVS comment
Alvaro Herrera wrote: On Sat, Aug 07, 2004 at 01:34:20AM +0200, Gaetano Mendola wrote: Alvaro Herrera wrote: Yeah. I included your tab-complete patch in the patch I sent to pgsql-patches, which later Tom reworked and applied. His CVS comment didn't mention the tab completion change. This isn't surprising at all, as minor changes go uncommented sometimes when they are surrounded by bigger changes (like the large object work). Understood. Why not comment each file separately too much work with CVS? People just doesn't feel it's important ... other projects have strict guidelines regarding CVS commit message formatting, but what I have seen is in most cases useless noise. Anyone can see the real diffs when there's need. I do not have experience with CVS ( at work I user Clearcase ) and for my personal purpose I use subversion ( any plans to migrate the CVS repository to subversion or even bitkeeper ? ). Subversion and arch have been mentioned, but so far there is no compelling reason to change. It'd take convincing at least a couple of core hackers to get the ball rolling ... Well, I think having seen what's happening at the 8.0 relase I think that committers are too overloaded and someone else have to be promoted to be a committers, and I believe that having betters tools can improve the process too. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Vacuum Cost Documentation?
Bruce Momjian wrote: Jan Wieck wrote: On 8/6/2004 9:04 PM, Bruce Momjian wrote: Updated. Thanks. I thought we want to have the feature activated ... I reversed your change and brought guc.c in sync instead. Uh, if the guy is doing a vacuum at night, does he want the delay? Seems someone should have to enable the delay by default, or does your setup recoginize when it is being run on a lightly loaded system? TODO: make vacuum_cost_naptime aware of system load :-) Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_dump: could not parse ACL list
Hi all,
I have a fresh installation of 8.0devel but I'm not able to
perform any backup using pg_dump:
$ pg_dump -p 5433 test
pg_dump: could not parse ACL list ([0:1]={postgres=UC/postgres,=UC/postgres}) for object
public (SCHEMA)
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] No such file or directory during PITR
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I did a recovery strictly following the doc instructions, the recovery succeded but I'm wondering if the following line in the logs is normal or not. cp: cannot stat `/home/pitr/0001.history': No such file or directory Yes, see the point in the docs that the recovery_command *will* be asked for nonexistent files. Thanks, I got it. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR - recovery to a particular transaction
G u i d o B a r o s i o wrote: 8.0 || 7.5?? 8.0 Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] CVS comment
Alvaro Herrera wrote: On Tue, Aug 03, 2004 at 06:42:03PM +0200, Gaetano Mendola wrote: I'm reading some comment on CVS and I seen this comment for tab-complete.c revision 1.109: Fix subtransaction behavior for large objects, temp namespace, files, password/group files. Also allow read-only subtransactions of a read-write parent, but not vice versa. These are the reasonably noncontroversial parts of Alvaro's recent mop-up patch, plus further work on large objects to minimize use of the TopTransactionResourceOwner. but the modification on that file have noting to see with this. Is it normal ? Yeah. I included your tab-complete patch in the patch I sent to pgsql-patches, which later Tom reworked and applied. His CVS comment didn't mention the tab completion change. This isn't surprising at all, as minor changes go uncommented sometimes when they are surrounded by bigger changes (like the large object work). Understood. Why not comment each file separately too much work with CVS? I do not have experience with CVS ( at work I user Clearcase ) and for my personal purpose I use subversion ( any plans to migrate the CVS repository to subversion or even bitkeeper ? ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] cache lookup failed for type 0
Hi all, doing some tests: regression=# create table test (a integer); CREATE TABLE regression=# alter table test add column b test; ALTER TABLE regression=# select * from test; a | b ---+--- (0 rows) regression=# insert into test values ( 1, (2,null)); --(1) INSERT 4260928 1 regression=# \d test Table public.test Column | Type | Modifiers +-+--- a | integer | b | test| regression=# alter table test drop column a; ALTER TABLE regression=# insert into test values ( (3,null)); ERROR: cache lookup failed for type 0 regression=# select * from test; b () (1 row) regression=# alter table test add column c integer; ALTER TABLE kalman=# select * from test; b | c -+--- (,) | (1 row) regression=# insert into test values ( (null,3), 4); --(2) ERROR: cannot cast type record to test DETAIL: Input has wrong number of columns. regression=# \d test Table public.test Column | Type | Modifiers +-+--- b | test| c | integer | The two insert in (1) and (2) are the same ( change only the column order ), but the (1) works and the (2) not. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open items
Bruce Momjian wrote: Jeff Davis wrote: On Mon, 2004-08-02 at 12:35, Bruce Momjian wrote: Does anyone have any more? From reading the lists it seems like most of PITR is in. However, I can't find any docs for it so I don't know how I'd test it. I downloaded the latest snapshot and don't immediately see anything about PITR. Yep, PITR docs is an open item. And is really needed because we can not play/experiment/test it without instructions... Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] reindex force
After two series 7.3 and 7.4 that ignore the force argument, why don't remove it in the 8.0 ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] CVS comment
Hi all, I'm reading some comment on CVS and I seen this comment for tab-complete.c revision 1.109: Fix subtransaction behavior for large objects, temp namespace, files, password/group files. Also allow read-only subtransactions of a read-write parent, but not vice versa. These are the reasonably noncontroversial parts of Alvaro's recent mop-up patch, plus further work on large objects to minimize use of the TopTransactionResourceOwner. but the modification on that file have noting to see with this. Is it normal ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] cannot remove lock from HTAB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm not able to reproduce it anymore but I seen this after a deadlock detection involving a subtransaction: ERROR: deadlock detected DETAIL: Process 31330 waits for ShareLock on transaction 570; blocked by process 31328. Process 31328 waits for ShareLock on transaction 572; blocked by process 31330. regression=# abort; WARNING: cannot remove lock from HTAB ROLLBACK I don't have any HTAB around!! Basically this is what I did: begin; begin; update record 1; savepoint a; ~ update record 2; update record 2; ~ update record 1; ~ dead lock detected I hope that looking at the code is possible understand what the problem is ( if exists ). Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBD8iS7UpzwH2SGd4RAgjxAJ9Gv2vkTjsdVaMSL2+b3yAcJlR5EgCgzwOD Owmr5zCfrhFE9eee0764p80= =jstJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Version Numbering -- The great debate
Peter Eisentraut wrote: Alvaro Herrera wrote: What was the rule for increasing the first number after just before 7.0? That was just to avoid having to release a 6.6.6, which Jan had clearly been working towards. :-) Seriously, major version jumps correspond to epoch-like changes, like when the code moved out of Berkeley, or when we switched from bug fixing to adding features. Maybe the next epoch would be after a hostile takeover of firebird. But right now I see no epoch change, just a potential for confusing users. Consistency and humbleness can be a virtue. Have a win32 native implementation is not a epoch change about you ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] missing link on ftp
Hi all, I'm seeing that a link is missing inside the src directory, the last version is 7.4.1 !! There is also something wrong with the doc directory. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Version Numbering -- The great debate
Christopher Browne wrote: After takin a swig o' Arrakan spice grog, Gaetano Mendola [EMAIL PROTECTED] belched out: Peter Eisentraut wrote: Alvaro Herrera wrote: What was the rule for increasing the first number after just before 7.0? That was just to avoid having to release a 6.6.6, which Jan had clearly been working towards. :-) Seriously, major version jumps correspond to epoch-like changes, like when the code moved out of Berkeley, or when we switched from bug fixing to adding features. Maybe the next epoch would be after a hostile takeover of firebird. But right now I see no epoch change, just a potential for confusing users. Consistency and humbleness can be a virtue. Have a win32 native implementation is not a epoch change about you ? I saw mention in the thread that the shift to 7.0 took place when people realized that 6.5 should have been 7.0. I think that the set of new features here will fairly likely warrant the 8.0 moniker; the 'consistent' way to go would be to call this version 7.5, and then 8.0 would soon follow, and be the release where some degree of improved maturity has been achieved for: a) Win32 support b) Nested transactions (thereby leading to the ability to have exception handling support in stored procedures) c) PITR. It would be surprising for these to all be _completely_ ready for all purposes come 7.5.0. The reasonable thing might be to say Forget 7.5.1; call it 8.0! Instead I think is good release a 8.0 in order to underline that this could be more buggy then a very stable 7.x series. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] missing link on ftp
Marc G. Fournier wrote: On Mon, 2 Aug 2004, Gaetano Mendola wrote: Hi all, I'm seeing that a link is missing inside the src directory, the last version is 7.4.1 !! There is also something wrong with the doc directory. Please provide an exact path for what you are seeing ... everything I see points to 7.4.3, at least at the top level ... in /pub/src the 7.4.2 - ../source/v7.4.2 7.4.3 - ../source/v7.4.3 are missing. And this is the directort /pub/doc content: drwxrwxr-x 6 70 70 512 Apr 25 2003 . drwxrwxr-x 15 70 70 512 Jun 15 04:29 .. drwxrwxr-x 2 70 70 1024 Nov 2 2001 7.1 drwxrwxr-x 2 70 70 512 Feb 21 2002 7.2 drwxrwxr-x 2 70 70 512 Apr 25 2003 7.3 lrwxrwxrwx 1 0 70 3 Apr 5 13:58 current - 7.3 drwxrwxr-x 2 70 70 512 Nov 2 2001 sql Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] extra info on autovaccum log
Hi all, till today I was running in production one only db so the autovacuum log was fine. Since today I have two db but looking at the following line: Performing: VACUUM ANALYZE public.current_connection I don't have idea about the database involved, I suggest: Performing: VACUUM ANALYZE dbame.public.current_connection or Performing: VACUUM ANALYZE public.current_connection@dbname I know that I will know the database on the very next line, but sometimes is too late :-( Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] extra info on autovaccum log
Matthew T. O'Connor wrote: Gaetano Mendola wrote: I don't have idea about the database involved, I suggest: Performing: VACUUM ANALYZE dbame.public.current_connection or Performing: VACUUM ANALYZE public.current_connection@dbname I know that I will know the database on the very next line, but sometimes is too late :-( I have thought about this before. I didn't do it since as you say, you can figure it out from looking at later log lines, but also that the log line entries are already very long and will only get longer with the extra info. BTW, I believe this will be moot in 7.5 since autovacuum will be integrated into the backend and uses elog calls, so you can format the log entries using GUC variables. Well but a default line will be in the postgresql.conf, you'll insert the dbname in the default one ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] No mail?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Marc G. Fournier wrote: | | all fixed ... had a kernel panic this morning, and fsck took a while to | run ... Also news.fr.postgresql.org it seems down since yesterday. Regards Gaeatano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBB1hM7UpzwH2SGd4RAv+/AKDgHnPTI5VD5NZTiJ8FFzJcZToanQCZAWfn niCEVWsy3KTJPz/j/YqoWvw= =d+6Y -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.5 backend crash
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola [EMAIL PROTECTED] writes: | |kalman=# create table test ( a integer, b integer ); |CREATE TABLE |kalman=# create table test1 ( c integer , d test ); |CREATE TABLE | | |kalman=# alter table test drop column b; |ALTER TABLE--- Here I think the server shall complain about | | | Yeah, the design intention is that the ALTER should refuse to do it. | Looks like I missed a case. Will fix, thanks for the report. Hi, in the mean time that you are working on it I believe that this is ugly too: kalman=# create table test ( a integer ); CREATE TABLE kalman=# alter table test add columb b test; ALTER TABLE kalman=# insert into test values (1 ,(2,(3,(4,(5,(6, null)); INSERT 33639 1 kalman=# select * from test; ~ a | b - ---+ ~ 1 | (2,(3,(4,(5,(6,) (1 row) The uglyness is on that series of '' why not the following result ? kalman=# select * from test; ~ a | b - ---+ ~ 1 | (2,(3,(4,(5,(6,) (1 row) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBMRp7UpzwH2SGd4RAuV+AJsFBLv0pD8U9UaXQKwxnFvqSqWacACgzipD 0ZdJ2csaQ5kBEI3ADnMX4zk= =iKgR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Sketch of extending error handling for subtransactions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola [EMAIL PROTECTED] writes: | |Tom Lane wrote: || I was just looking around the net to see exactly what Oracle's PL/SQL || syntax is. It doesn't seem too unreasonable syntax-wise: || [ snip pl/sql syntax ] | | |Is this sintax SQL standard driven ? | | | No, AFAIK it's just Oracle's syntax. | | |If not I'd prefere this one: | [ some other syntax ] | | | Can you point to any SQL standard or existing database that uses your | suggestion? Oracle is certainly the de facto standard in this area, | and plpgsql in particular is an unabashed effort to follow their PL/SQL | implementation... I didn't know we where following the Oracle syntax indeed. | | er ... I'm not clear why type names would have anything to do with | exceptions. What's your vision here exactly? | | regards, tom lane Because I believe that exception handler need to have some informations in order to handle the exception and only the exception_name is not enough, so who generate the exception can throw a type that the handler can use ( I have in mind the C++/Java exception handler ): CATCH INTEGER THEN ~ Work with the integer $1 in order to manage what is going on CATCH VARCHAR THEN ~ Insert in the logs table the message $1 ... but may be I'm completely missing the target that you are going to achieve. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBMsf7UpzwH2SGd4RAqM1AKCSg4jOT7J52nZRU98Np/JAIcAz4wCbB/O3 J94yQ7NFB/JE3uUCR/OgkUs= =oWOX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.5 backend crash
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Greg Stark [EMAIL PROTECTED] writes: | |Gaetano Mendola [EMAIL PROTECTED] writes: | |kalman=# insert into test values (1 ,(2,(3,(4,(5,(6, null)); |INSERT 33639 1 |kalman=# select * from test; |~ a | b |---+ |~ 1 | (2,(3,(4,(5,(6,) |(1 row) | | |Omg. | | |This is pretty cool and frankly amazing that it works. | | | It shouldn't work --- the ALTER TABLE code should reject it. Looks like | there are a few holes to be plugged here ... The SQL standard forbid it ? I was believing that was a pretty cool feature! :-) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBYB47UpzwH2SGd4RAoyfAJ9B5AIHC8A8nyWLOt+Xw6GXJa3kFACgxkzu kBxnvugWhzjNvc+UboAwAjE= =xEBD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.5 release notes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 2004-07-25 at 05:25, Bruce Momjian wrote: |I have completed the 7.5 release notes. You can view them in HTML on |the developer web page. I have marked a few items with question marks |that need to be addressed. I am looking for improvements, even minor |ones. Either send in a patch or committers can modify the file |directly. Did you forget to insert the autovacuum integrated ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBA+cZ7UpzwH2SGd4RAgQwAKD11vpcxmsjkwdJABDMEpe27NS0HQCg1DVq qnZ1ZENnFUVD0nl358vpxj4= =WE9n -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] storage engine , mysql syntax CREATE TABLE t (i INT)
Andreas Pflug wrote: Pierre Emmanuel Gros wrote: In mysql, we can wrote a create table like CREATE TABLE t (i INT) ENGINE = INNODB||BDB|; where the storage engine is the innodb one. MySQL needs this because they have a weird understanding of RDBMS. This could be true, but the answer doesn't make sense, at least it's in a perfect mysql people style. Isn't ? We don't need transactions, we don't needs store procedure and so on... There's absolutely no sense in trying to transfer this stuff into PostgreSQL. Use it as designed, and you'll never miss this MySQL feature. Another mysql people style answer. We have only one engine: the full transactional one. If the OP need to have for example the MEMORY one the he can easily create a RAM disk and with the tablespaces support he can create tables or index or whatever objects in memory. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Sketch of extending error handling for subtransactions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Bruce Momjian [EMAIL PROTECTED] writes: | |So it allows functions to use subtransactions and recover from errors. |I thought that was more than we could do for 7.5 and in fact the release |notes now saw that will be done in a future release. | | | I think there's only a day or two's work between here and there, and it | would be foolish not to have the feature if we can get it. | | As I see it, we need: | | 1. The elog.c factoring described in this thread. | | 2. An extension to the SPI API to allow execution of commands within |a subtransaction, with catching of errors. | | 3. A bit of work on plpgsql to support some kind of EXCEPTION syntax. | | I might decide to forget about SPI and trap errors directly in plpgsql, | but in any case it doesn't seem out of reach. | | I was just looking around the net to see exactly what Oracle's PL/SQL | syntax is. It doesn't seem too unreasonable syntax-wise: | | BEGIN | ... controlled statements ... | EXCEPTION | WHEN exception_name THEN | ... error handling statements ... | WHEN exception_name THEN | ... error handling statements ... | ... | WHEN OTHERS THEN | ... error handling statements ... | END; Is this sintax SQL standard driven ? If not I'd prefere this one: ~TRY ~... controlled statements ... ~CATCH INTEGER THEN ~ ... error handling statements ... ~CATCH VARCHAR THEN ~... error handling statements ... ~ ... ~CATCH OTHERS THEN ... error handling statements ... ~END; and of course who trhow the exception: ~THROW 3::INTEGER; in this way who throw the exception can also transfer informations on what is going on. Am I may be not understanding what are you trying to do ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBCsu7UpzwH2SGd4RAo1aAKDUX+afgx+RjBdCtG9sdGp9eT6j1QCfasdq hcm4Vt2RLsoN5cSvIfbgGiw= =iJgV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.5 release notes
In the release not I can read: #Allow arbitrary row expressions (Tom) This allows columns to contain arbitrary composite types like rows from other tables. [SNIPPED] Wasn't this alread true on 7.4 ? See: kalman=# select version(); version - PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) (1 row) kalman=# create table test ( a varchar, b varchar, c varchar ); CREATE TABLE kalman=# create table test1 ( d integer, e test ); CREATE TABLE kalman=# select * from test1; d | e ---+--- (0 rows) kalman=# select e from test1; e --- (0 rows) kalman=# select (e).a from test1; a --- (0 rows) Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] 7.5 backend crash
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm playing with complex row type: kalman=# create table test ( a integer, b integer ); CREATE TABLE kalman=# create table test1 ( c integer , d test ); CREATE TABLE kalman=# insert into test1 values ( 1, (2,3) ); INSERT 17277 1 kalman=# select * from test1; ~ c | d - ---+--- ~ 1 | (2,3) (1 row) kalman=# alter table test drop column b; ALTER TABLE --- Here I think the server shall complain about ~ ( 7.4 doesn't complain neither ) the following select on table test1 will crash the back end kalman=# select * from test1; server closed the connection unexpectedly ~This probably means the server terminated abnormally ~before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBFQH7UpzwH2SGd4RAvIXAJ0bcfGdP5sfPFRYdfKOJ0YchRYXPgCfZti7 wKfzG4GwdFft0a1LDXK9fmo= =wjvZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Fixing PKs and Uniques in tablespaces
Christopher Kings-Lynne wrote: I never really considered oracle's implementation of tablespaces when I worked on tablespaces. The database default tablespace seems similar to Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace like we do. My point was that Oracle has added a tablespace clause to the constraint clause, so we can too! Also, since I checked and it seems that our syntax for putting tables an d indexes in tablespaces at creation time is identical to oracle's, perhaps we should copy them on constraints as well. Since we're getting close to beta, can we have consensus on what I'm to do about this? We are already in a features freeze period, or not ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Wrong index choosen?
I hall I have a query in this form: empdb=# explain analyze select * from v_past_connections where id_user = 26195 and login_time '2004-07-21'; QUERY PLAN - Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..14.10 rows=1 width=28) (actual time=66.890..198.998 rows=5 loops=1) Index Cond: (login_time '2004-07-21 00:00:00+02'::timestamp with time zone) Filter: (id_user = 26195) Total runtime: 199.083 ms (4 rows) as you see the index on the time stamp column is used The table have indexes on both columns: empdb=# explain analyze select * from v_past_connections where login_time '2004-07-21'; QUERY PLAN -- Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..12.90 rows=481 width=28) (actual time=7.338..661.300 rows=22477 loops=1) Index Cond: (login_time '2004-07-21 00:00:00+02'::timestamp with time zone) Total runtime: 676.472 ms (3 rows) empdb=# explain analyze select * from v_past_connections where id_user = 26195; QUERY PLAN --- Index Scan using idx_user_user_logs on user_logs (cost=0.00..252.47 rows=320 width=28) (actual time=4.420..100.122 rows=221 loops=1) Index Cond: (id_user = 26195) Total runtime: 100.348 ms (3 rows) The rows filtered out with both condictions are two order of magnitude differents, also the extimated rows are close to real numbers: empdb=# select count(*) from v_past_connections where id_user = 26195; count --- 221 (1 row) empdb=# select count(*) from v_past_connections where login_time '2004-07-21'; count --- 22441 (1 row) why then the planner choose to do an index scan using the filter that retrieve a bigger ammount of rows ? A bug ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] check point segments leakage ?
Bruce Momjian wrote: Gaetano Mendola wrote: Bruce Momjian wrote: Scott Marlowe wrote: I use a checkpoint_segments = 16 but in my pg_xlog I have 35 files. Why 35 files ? You have 35 because the max files in pg_xlog is 2*checkpoint_segments +1 or something like that. This is documented in the SGML. Ok, that explain why. And they will remain there also if not needed ? Yes, it keeps them around so it doesn't need to recreate them. Another weird behaviour is that during the day the storage space usage increase gruadualy. Since today as the graph show the space usage is constant, it's like if some space was pre-allocated and now is used, see same yesterday period between 18:00 and 24:00. Toughts ? My guess is that you need a certain amount of free space in the tables to operate properly. Well, today I stop the pg_autovacuum and I did a vacuum full and I reindexed all big tables and other 500 MB were reclamed. Could be the pg_autovacuum running yesterday the responsible for these 500MB not reclamed during a vacuum full and reindex already performed yesterday ? I'm wandering if will be possible in the 7.5 start and stop the the autovacuum integrated in the backend. I don't know if there is space for improvements but add columns to a table with milion rows is very painfull, for sure could be usefull to do the following tree operation in one shot: 1) Add column 2) Update the column 3) Set not null Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] check point segments leakage ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew T. O'Connor wrote: | Gaetano Mendola wrote: | | Well, today I stop the pg_autovacuum and I did a vacuum full and I | reindexed | all big tables and other 500 MB were reclamed. Could be the pg_autovacuum | running yesterday the responsible for these 500MB not reclamed during | a vacuum full and reindex already performed yesterday ? | | | Probably not. Most of the time pg_autovacuum is just sleeping. If you | happened to fun a VACUUM FULL while pg_autovacuum was running a vacuum, | there might have been a conflict on the tabke pg_autovacuum was working | with at the time. | | Also, are you sure that the space wasn't reclaimed yesterday after the | VACUUM FULL? It could be that your tables have grown 500M since then. | Remember, the minimum table size (the size after a VACUUM FULL) is not | necessarilly the optimial size. Postgresql will almost always need to | reallocate the space that was reclaimed by VACUUM FULL. I'm pretty sure, see the attached graph. Each morning at 7 a script stop the autovacuum, vacuum full the database and reindex the eavy updated tables and restart of course the autovacuum. Note also that for all the day I didn't have the usual disk usage increment. | I'm wandering if will be possible in the 7.5 start and stop the the | autovacuum integrated in the backend. | | | Yes (at least the patch waiting to be applied to CVS HEAD does) in order | to stop autovacuum you will have to edit the autovac option in | postgresql.conf and HUP the postmaster. This is a good news. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA/o2Z7UpzwH2SGd4RAi38AKCO7XqClR/+X5b8szVJwbREC50HrQCg5M8n R5ODgRU05IGnnS1YaK4afIk= =ftFY -END PGP SIGNATURE- inline: space_usage.png ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] check point segments leakage ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew T. O'Connor wrote: | Gaetano Mendola wrote: | | I'm pretty sure, see the attached graph. Each morning at 7 a script stop | the autovacuum, vacuum full the database and reindex the eavy updated | tables | and restart of course the autovacuum. Note also that for all the day I | didn't | have the usual disk usage increment. | | | I don't know why the 1st VACUUM FULL wasn't able to reclaim the same | amount of space as the 2nd one, but I would guess that it wasn't able to | get a lock on some table. It could have been autovac if it was doing a | vacuum at that moment, but it could have been something else too. | | From the attached graph, it looks like your stead state database size | is approx 3.0G. After the 2nd VACUUM FULL, you dropped to 2.5G, but as | you can see it's creeping up back up again. | | If you let it continue to run without running VACUUM FULL, but with | autovacuum enabled, and it climbs to 3.0G and stops growing, then I | think you are fine and you don't need to run VACUUM FULL at all. If it | continues to grop witout bound, then you need to up your FSM and/or make | autovac more aggressive. | | Bottom line, you shouldn't need VACUUM FULL, if you do, I think there | are people on this list that would like to hear about it. I will try to disable ( I hope the management is not reading this list ) the vacuum full performed each morning, I'll leave only the reindex for a couple of table and I'll see what happen I will post another graph Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA/p247UpzwH2SGd4RAokEAJ9+xhF9g8ZbzE3ne6qCFOuV6z3LmACg9yQR hL7LaOX8EucswifK5okQZ9g= =jKG9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] localhost redux
Gavin M. Roy wrote: I have no problem resolving localhost anywhere else on the box, do you have any suggestions on finding out if it's a misconfiguration? Gavin Are you the users on IRC yesterday night ? He had the same problem, however he/she had the GUC variable stats_start_collector equal to on in the configuration file but was shown as off with a SHOW stats_start_collector inside a psql section. That GUC variable is resetted to off is the collector doesn't start ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] check point segments leakage ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, today I add 4 new columns to a table with 4E+06 rows, I also update to an initial value these new columns. The new columns are 3 INTEGER one of type DOUBLE. The table have also 5 indexes. Immediately after the operation my partition data had an usage increment of 1.2GB. I did a reindex and a vacuum full on that table and 600MB were freed. Now I have an increment of only 600 MB. I use a checkpoint_segments = 16 but in my pg_xlog I have 35 files. Why 35 files ? Where are lost my 600MB ? Also the load increased from 1 to 5 !! Any ideas ? I'm attaching boot graphs ( HD space usage and load ). Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA/Ydh7UpzwH2SGd4RAuhKAKCTftBGjBLSfR+OTy5vHlYpL46TXQCfc65/ VfepMM87dQKvg3rswhGUNL8= =HWHy -END PGP SIGNATURE- inline: load.pnginline: space_usage.png ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] check point segments leakage ?
Joshua D. Drake wrote: Hello, Perhaps you have an open transaction that isn't closing and thus the pg_xlog continues to grow? Sincerely, Joshua D. Drake I was thinking about it but unfortunately there is no transaction open. On my development database, were I simulate the same operation that I did in production I have the same situation: 34 files and same configuration, right now there are 5 connection and no one of them have a transaction opened: template1=# select * from pg_locks ; relation | database | transaction | pid | mode | granted --+--+-+---+-+- 16759 |1 | | 15910 | AccessShareLock | t | | 7714652 | 15910 | ExclusiveLock | t Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] no instruction after install
Hi all, why after compiling and install postgresql CVS tip there is no information on how to run it ? Also the file INSTALL is not there anymore. Do I miss something in these days ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] NT and aborted transaction
Hi all, still experimenting NT: kalman=# begin; BEGIN kalman=# select * from a; ERROR: relation a does not exist kalman=# begin; BEGIN kalman=# select * from test; ERROR: current transaction is aborted, commands ignored until end of transaction block I think the second begin shall fail instead of open an already invalid transaction. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] NT + deadlock intended behaviour ?
Hi all, I'm doing some experiments with NT, I din't expect this behaviuor: create table test ( a integer ); insert into test values (3); insert into test values (4); insert into test values (5); insert into test values (6); SESSION 1;SESSION 2; begin;begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; begin; update test set a = 400 where a = 4; BLOCKED update test set a = 30 where a = 3; DEAD LOCK DETECTED commit; UNBLOCKED-- !?!?! here I'm able to do another commit why SESSION 1 was unblocked ? If I repeat again but I do an abort: SESSION 1;SESSION 2; begin;begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; begin; update test set a = 400 where a = 4; BLOCKED update test set a = 30 where a = 3; DEAD LOCK DETECTED abort; STILL BLOCKED Why that commit unblock the SESSION 1? Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] unused variable
Hi all, I'm compiling the last postgres CVS version and I get: vacuum.c: In function `repair_frag': vacuum.c:1528: warning: unused variable `myXID' Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] NT tab complete patch
Hi all,
I'm trying the savepoint-5.patch
Using the psql the TAB autocomplete doesn't work for savepoint and for release
commands,
also the ROLLBACK doesn't know yet the ROLLBACK TO sintax.
Attached the patch that solve these issues.
BTW, there is a way to know which savepoint are active ?
Regards
Gaetano Mendola
--- pgsql_patched/src/bin/psql/tab-complete.c 2004-07-19 17:21:50.0 +0200
+++ pgsql/src/bin/psql/tab-complete.c 2004-05-26 15:56:55.0 +0200
@@ -463,8 +463,8 @@
ABORT, ALTER, ANALYZE, BEGIN, CHECKPOINT, CLOSE,
CLUSTER, COMMENT,
COMMIT, COPY, CREATE, DEALLOCATE, DECLARE, DELETE, DROP,
EXECUTE,
EXPLAIN, FETCH, GRANT, INSERT, LISTEN, LOAD, LOCK,
MOVE, NOTIFY,
- PREPARE, REINDEX, RELEASE, RESET, REVOKE, ROLLBACK,
SAVEPOINT,
-SELECT, SET, SHOW, START, TRUNCATE, UNLISTEN, UPDATE,
VACUUM, NULL
+ PREPARE, REINDEX, RESET, REVOKE, ROLLBACK, SELECT, SET,
SHOW, START,
+ TRUNCATE, UNLISTEN, UPDATE, VACUUM, NULL
};
static const char * const pgsql_variables[] = {
@@ -726,6 +726,7 @@
else if (pg_strcasecmp(prev_wd, BEGIN) == 0 ||
pg_strcasecmp(prev_wd, END) == 0 ||
pg_strcasecmp(prev_wd, COMMIT) == 0 ||
+pg_strcasecmp(prev_wd, ROLLBACK) == 0 ||
pg_strcasecmp(prev_wd, ABORT) == 0)
{
static const char * const list_TRANS[] =
@@ -733,14 +734,6 @@
COMPLETE_WITH_LIST(list_TRANS);
}
-/* ROLLBACK*/
- else if ( pg_strcasecmp(prev_wd, ROLLBACK) == 0 )
- {
- static const char * const list_TRANS[] =
- {WORK, TRANSACTION, TO, NULL};
-
- COMPLETE_WITH_LIST(list_TRANS);
- }
/* CLUSTER */
/* If the previous word is CLUSTER, produce list of indexes. */
else if (pg_strcasecmp(prev_wd, CLUSTER) == 0)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] NT + deadlock intended behaviour ?
Alvaro Herrera wrote: On Sun, Jul 18, 2004 at 01:06:39AM +0200, Gaetano Mendola wrote: I'm doing some experiments with NT, I din't expect this behaviuor: First of all, let me point that the behavior on deadlock has been agreed to change. Instead of only aborting the innermost transaction, it will abort the whole transaction tree. The reason is simple. Consider this case: create table foo (a int); insert into test values (1); insert into test values (2); begin; update foo set a=20 where a=1; begin; update foo set a=21 where a=2; begin; update foo set a=22 where a=2; LOCKED begin; update foo set a=23 where a=1; DEADLOCK DETECTED If I abort only the innermost transaction on session 2, the application writer can have a retry loop on it, so it will issue the begin again and the same update. Since session 1 is still locked, session 2 will see a deadlock again. The user could cope with detecting a deadlock condition and do something else, but frankly I don't think we can leave this as is. I understand your point but I don't like the solution of invalidate the whole transaction tree ( I don't know the good one ). See also my comment at the end of this reply. SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ commit; UNBLOCKED-- !?!?! ~ here I'm able to do another commit why SESSION 1 was unblocked? Because when you COMMIT a subtransaction that was in aborted state, the parent is aborted too. So when you COMMIT you are not really committing, you are aborting. That gives session 1 green light to continue, because session 2 has released all locks. So why the second commit on SESSION 2 works without complain about the fact that there is no transaction active to commit ? I think the first commit have to fail because the transaction is aborted ( I know this was discussed before ). If I repeat again but I do an abort: SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ abort; STILL BLOCKED This is what you expected, wasn't it? When you ABORTed the subtransaction, the parent did not abort, so it held it locks. So session 1 does not have the lock it needs. This is what I was expecting; here we are in the same situation of your example, what happen if the application open another transaction and try to update the same row ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] NT + deadlock intended behaviour ?
Alvaro Herrera wrote: Gaetano, please apply the latest savepoints patch (savepoint-5.patch) and let me know how it goes ... where is it ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] NT + deadlock intended behaviour ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm doing some experiments with NT, I din't expect this behaviuor: create table test ( a integer ); insert into test values (3); insert into test values (4); insert into test values (5); insert into test values (6); SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ commit; UNBLOCKED-- !?!?! ~ here I'm able to do another commit why SESSION 1 was unblocked ? If I repeat again but I do an abort: SESSION 1;SESSION 2; begin; begin; update test set a = 300 where a = 3; update test set a = 40 where a = 4; ~ begin; update test set a = 400 where a = 4; BLOCKED ~ update test set a = 30 where a = 3; ~ DEAD LOCK DETECTED ~ abort; STILL BLOCKED Why that commit unblock the SESSION 1? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bD+7UpzwH2SGd4RAq0VAJ9rZQ3aJmsJM6WSlLqIERJzDDS9iQCeL5rT rF7PkCaJ59PWNQw4CK6uvug= =Rb3s -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] NT and aborted transaction
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, still experimenting NT: kalman=# begin; BEGIN kalman=# select * from a; ERROR: relation a does not exist kalman=# begin; BEGIN kalman=# select * from test; ERROR: current transaction is aborted, commands ignored until end of transaction block I think the second begin shall fail instead of open an already invalid transaction. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bEz7UpzwH2SGd4RAujVAJ4wBx64eSKwokcBUl/l3sJJV9LelwCcDf/Z K/kv375ERUxwvY8tGUmhmYA= =K8OT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] unused variable
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm compiling the last postgres CVS version and I get: vacuum.c: In function `repair_frag': vacuum.c:1528: warning: unused variable `myXID' Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bCj7UpzwH2SGd4RAtyKAJsH6V22SijBN3VD/HJ/sWoLO8Ta7wCcD1wt U2VsEehBuK64q4/kjsHA4g4= =TsL8 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Release planning
Christopher Browne wrote: A long time ago, in a galaxy far, far away, Gaetano Mendola [EMAIL PROTECTED] wrote: I was thinking of something much simpler where Jan would create an ARC patch against 7.4.X and have it either in /contrib for 7.4.X or on our ftp servers, or on a web site. I could create a mechanism so SELECT version() would display Jan's add-on. :-( I was asking to add the vacuum delayed patch to 7.4 months ago and the response was: why introduce instability to a stable release ? I hope the global consensus is a no way to procede also for ARC. If, as you suggest, ARC is too immature to go in, then I presume that would also imply that global consensus should also be that: a) PITR is much too immature to put in; b) Win32 is way too immature to put in; c) NT is way too immature, as well; d) Tablespaces are way too immature to be included. Which eliminates all of the big, interesting reasons to upgrade to 7.5. Or is ARC the only thing you regard as a misfeature? Interesting that it was put into 7.5 _early_ in the process, so that everyone that has lately touched the betas would be getting bitten pretty heavily if it was laden with bugs... No no, please don't mistake me. I wrote that shall be a no way to back patch ARC in the 7.4 version, starting from consideration that the delayed vacuum was considered too dangerous... My humble opinion is that have all that new funcionts in one shot with the same ammount of beta period for other release is too dangerous. From my side the only think that I can do with a beta is just run our regression tests ( almost 2000 tests ) for our installation, but for sure I can not use it extensively. We could ( may be is a crazy idea )release the 7.5 with ARC + BW + new list implemetation 7.6 with 7.5 + PITR 7.7 with 7.6 + NT 7.8 with 7.7 + Win32 7.9 with 7.8 + Table Space with 2 months between a release and the other with a one month of beta for each release; this in order to reach the version 8.0 rock stable; and possibly without need to initdb between these releases. Is it too insane ? :-) I'm sorry to see Postgresql releases driven by advertisment instead by good sense ( as it was till today ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Release planning (was: Re: Status report)
Bruce Momjian wrote: I was thinking of something much simpler where Jan would create an ARC patch against 7.4.X and have it either in /contrib for 7.4.X or on our ftp servers, or on a web site. I could create a mechanism so SELECT version() would display Jan's add-on. :-( I was asking to add the vacuum delayed patch to 7.4 months ago and the response was: why introduce instability to a stable release ? I hope the global consensus is a no way to procede also for ARC. It's true the version 7.5 ( or 8.0 ) will be really a great release but IMHO introduce in one shot: 1) PITR 2) Nested Transaction 3) WIN32 porting 4) ARC 5) Table Space 6) I'm sure I'm forgetting something was really too much. I hope that all will be fine. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SAN, clustering, MPI, Backplane Re: Postgresql on SAN
Tom Lane wrote: Andrew Piskorski [EMAIL PROTECTED] writes: Another thing I've been wondering about, but haven't been able to find any discussion of: Just how closely tied is PostgreSQL to its use of shared memory? Pretty damn closely. You would not be happy with the performance of anything that tried to insert a network communication layer into access to what we think of as shared memory. For a datapoint, check the list archives for discussions a few months ago about performance with multiple Xeons. We were seeing significant performance degradation simply because the communications architecture for multiple Xeon chips on one motherboard is badly designed :-( The particular issue we were able to document was cache-line swapping for spinlock variables, but AFAICS the issue would not go away even if we had a magic zero-overhead locking mechanism: the Xeons would still suck, because of contention for access to the shared variables that the spinlocks are protecting. OpenMosix is in the category of does not work, and would be unusably slow if it did work ... AFAIK any similar design would have the same problem. However shall be nice if the postmaster is not selfish as is it now (two postmastera are not able to work on the same shared memory segment), projects like cashmere ( www.cs.rochester.edu/research/cashmere/ ) or this www.tu-chemnitz.de/informatik/HomePages/RA/projects/VIA_SCI/via_sci_hardware.html are able to run a single database mananged by a postmaster for each node in a distributed architecture. I seen these hardware working at CeBIT some years ago and it's possible to setup any kind of configuration: linear, triangular, cube, ipercube. Basically each node share part of the local RAM in order to create a big shared memory segment and the shared memory is managed without kernel intervention. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] [CHECKER] 4 memory leaks in Postgresql 7.4.2
Alvaro Herrera wrote: On Mon, Jul 05, 2004 at 05:13:23PM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: On Sun, May 02, 2004 at 07:50:46PM -0400, Tom Lane wrote: It's entirely likely that ecpg's derivative of the backend's datetime modules contains lots and lots of memory leaks, since AFAIK the palloc infrastructure is not there in the ecpg environment :-(. I wonder why is this? Is there some limitation to using palloc outside the backend itself? I ask because I have considered using it outside Postgres several times (a consideration that has never materialized yet), and I wonder if it needs something special to work. The semantics of palloc is that most stuff is freed on statement completion. In most cases, interfaces need different semantics so we haven't seen much need for making something like palloc available to clients. I can see ecpg using it in a few cases, and libpq too, but probably not enough to make it worthwhile. Yes, I understand that part -- what I was talking about was not using the code in the Pg interfaces, but in another software project which also consists of a daemon that has several well defined durations of objects. In that (as of yet unwritten) code, palloc would fit very well. But does palloc depend on some other part of the Postgres code? If you don't mind you can write your application in C++ and use a boost smartpointer: http://www.boost.org/libs/smart_ptr/smart_ptr.htm Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] warning missing
Gaetano Mendola wrote: Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I think a warning is missing if I create a table without OIDS that inherits from a table with oids: don't you think a warning shall to be raised here ? Nope ... this is not different from the behavior for merging duplicate column definitions. You get an OID column if the child table definition *or* any parent table requests OIDs. Mmm, you are not convincing me, don't we break the principle of minor surprise? I mean if you duplicate a column name that column is present on the inherited table, but if I say: I don't want the column OID and I found it, this then surprise me. Peter, what do you think about it ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] warning missing
Thomas Hallgren wrote: Greg, You don't like Java/C#. I do. What appear here is that you hate C++. I'm a C++ developer since long time now, and I can not use JAVA and or C# just for a couple of reason: 1) Java was supposed to be platform compatible: in thereality is not really true. 2) I can not use the RAII Idiom, or at least without be a joggler 3) I miss the const modifier for methods, and I really can not be sure of what happen to my objects when are used around. Do you want now speak about the missing template feature? Don't say template are the same of Generics. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] warning missing
I think a warning is missing if I create a table without OIDS that inherits from a table with oids: kalman=# create table test ( a integer ); CREATE TABLE kalman=# create table test_2 ( b integer ) inherits (test) without oids; CREATE TABLE kalman=# select oid from test_2; oid - (0 rows) don't you think a warning shall to be raised here ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] warning missing
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I think a warning is missing if I create a table without OIDS that inherits from a table with oids: don't you think a warning shall to be raised here ? Nope ... this is not different from the behavior for merging duplicate column definitions. You get an OID column if the child table definition *or* any parent table requests OIDs. Mmm, you are not convincing me, don't we break the principle of minor surprise? I mean if you duplicate a column name that column is present on the inherited table, but if I say: I don't want the column OID and I found it, this then surprise me. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Minor DROP TABLESPACE issue
Tom Lane wrote: Although DROP TABLESPACE can detect tables existing in the target tablespace, it doesn't have any way to detect schemas that reference that tablespace as their default tablespace. Thus you can get implementation-level failures like this one: $ mkdir /tmp/junk regression=# create tablespace junk location '/tmp/junk'; CREATE TABLESPACE regression=# create schema junk tablespace junk; CREATE SCHEMA regression=# drop tablespace junk; DROP TABLESPACE Why this doesn't fail? The junk schema depend on tablespace junk, is there no dependencies between these two objects. G. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] File leak?
Bruce Momjian wrote: Tom Lane wrote: Now that I think about it, I believe Bruce recently removed this on my advice; I was thinking that the problem shouldn't occur anymore now that True. we WAL-log file creation and deletion. But actually the present form of the WAL entries doesn't ensure that a file created by a transaction that crashes before committing will go away, because file deletion actions are only logged (and replayed) at transaction commit/abort. So it probably should go back in. Or else we could add more WAL logging Wording updated to: * Remove unreferenced table files created by a transactions that were in-progress when the server crashed I don't think is a good idea put the words: when the server crashed in a TODO list, may be is better write: when the server is killed abruptly. My 2 cents. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Improving postgresql.conf
Scott Marlowe wrote: On Fri, 2004-06-11 at 11:02, Bruce Momjian wrote: Gaetano Mendola wrote: [ PGP not available, raw data follows ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: | Gaetano Mendola wrote: | |Bruce Momjian wrote: | | I understand your points below. However, the group has weighed in the | direction of clearly showing non-default values and not duplicating | documentation. We can change that, but you will need more folks | agreeing with your direction. | |I don't remember the behaviour but tell me what happen if |I comment out a value changing the value. Kill UP the postmater. |Recommenting that value and now re killing the postmaster. | |I believe that postmaster will not run with the default value. |Who will look the configuration file will not understand the right |reality. | | | If you comment a variable in postgresql.conf, it will use the | default value. That's not true at least with the version 7.4.2. Try yourself, I did the experiment changing the cpu_tuple_cost and commenting out the cpu_tuple_cost, after sending the SIGHUP to postmaster the value remain: 0.005 that is not the default value at all. Oh, sorry, you are right. Not sure if this is a bug or not. This point has come up before, and I think it's intended behavior. Stopping and restarting the database will, of course, make it load the defaults. Yes and this doesn't help to understand how the postmaster is running unless you use SHOW ALL. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Improving postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: | Gaetano Mendola wrote: | |Bruce Momjian wrote: | | I understand your points below. However, the group has weighed in the | direction of clearly showing non-default values and not duplicating | documentation. We can change that, but you will need more folks | agreeing with your direction. | |I don't remember the behaviour but tell me what happen if |I comment out a value changing the value. Kill UP the postmater. |Recommenting that value and now re killing the postmaster. | |I believe that postmaster will not run with the default value. |Who will look the configuration file will not understand the right |reality. | | | If you comment a variable in postgresql.conf, it will use the | default value. That's not true at least with the version 7.4.2. Try yourself, I did the experiment changing the cpu_tuple_cost and commenting out the cpu_tuple_cost, after sending the SIGHUP to postmaster the value remain: 0.005 that is not the default value at all. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAyWbI7UpzwH2SGd4RAre5AJ4sakTxqvcjbq8Cz6Qoj2bnDO5/7gCfTWdp nyWvDNTeQNEfwYJWHHL+0W0= =JFgw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving postgresql.conf
Bruce Momjian wrote: I understand your points below. However, the group has weighed in the direction of clearly showing non-default values and not duplicating documentation. We can change that, but you will need more folks agreeing with your direction. I don't remember the behaviour but tell me what happen if I comment out a value changing the value. Kill UP the postmater. Recommenting that value and now re killing the postmaster. I believe that postmaster will not run with the default value. Who will look the configuration file will not understand the right reality. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] CREATE DATABASE on the heap with PostgreSQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 jihuang wrote: | May Users forcely assign a table / database / cluster storage in RAM | purely ? NO. | or a in-directly-way , like making a RAM-Disk-Device and assign this | device as a postgreSQL cluster? YES. | I think this feature will push a lot High-Performance usage , | any suggestion ? I don't think you'll obtain this performance increase. You can write your own script that before postgres start: 1) Create the RAM disk 2) Copy the table in memory 3) Create the link between the old location to the new one and after stop postgres: 1) copy the table from RAM to DISK Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxDC07UpzwH2SGd4RAm3KAJ9HFgvTSqXSGCh3Xx2n6+Mfqb7AcQCgzWht CeFGnUTQrD9AWOTvwdkVr0A= =evpH -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Postres dilemma
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Why are you running a vacuum every 45 seconds? Increase your fsm_pages and run it every hour. If I understood his description correctly, he's turning over 10% of a 500-row table every minute. So waiting an hour would mean 3000 dead rows in a 500-live-row table, which seems excessive. I'd agree with running a vacuum on this specific table every five minutes or so. Given that he is doing more than enough vacuums, I think that the problem is probably not table bloat, but index bloat (ie, from a constantly shifting range of live index keys, which pre-7.4 btrees didn't handle well at all). This is just speculation though, without proof as yet. Another information to know is if there are connection in the unfamous state: Idle in transaction. Is usefull if the OP show us the vacuum verbose output. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE DATABASE on the heap with PostgreSQL?
Albretch wrote: After RTFM and googling for this piece of info, I think PostgreSQL has no such a feature. Why not? . Isn't RAM cheap enough nowadays? RAM is indeed so cheap that you could design diskless combinations of OS + firewall + web servers entirely running off RAM. Anything needing persistence you will send to the backend DB then . Granted, coding a small Data Structure with the exact functionality you need will do exactly this keeping the table's data on the heap. But why doing this if this is what DBMS have been designed for in the first place? And also, each custom coded DB functionality will have to be maintaned. Is there any way or at least elegant hack to do this? I don't see a technically convincing explanation to what could be a design decision, could you explain to me the rationale behind it, if any? If you access a table more frequently then other and you have enough RAM your OS will mantain that table on RAM, don't you think ? BTW if you trust on your UPS I'm sure you are able to create a RAM disk and place that table in RAM. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PageGetMaxOffsetNumber on uninitialized pages
Tom Lane wrote: I was just looking at this macro: /* * PageGetMaxOffsetNumber *Returns the maximum offset number used by the given page. *Since offset numbers are 1-based, this is also the number *of items on the page. * *NOTE: to ensure sane behavior if the page is not initialized *(pd_lower == 0), cast the unsigned values to int before dividing. *That way we get -1 or so, not a huge positive number... */ #define PageGetMaxOffsetNumber(page) \ (((int) (((PageHeader) (page))-pd_lower - SizeOfPageHeaderData)) \ / ((int) sizeof(ItemIdData))) The macro does the right thing on its own terms when applied to a zeroed page, but in some places it's used like this: OffsetNumber n; OffsetNumber maxoff; maxoff = PageGetMaxOffsetNumber(p); for (n = FirstOffsetNumber; n = maxoff; n++) and OffsetNumber is uint16 not int. So a loop like this would go nuts instead of treating the zeroed page as if it were empty. This is not good (see the comments for PageHeaderIsValid in bufpage.c if you disremember why). We could fix this by changing the declarations of the maxoff variables to int, but I think it's probably cleaner to recode PageGetMaxOffsetNumber like so: #define PageGetMaxOffsetNumber(page) \ (((PageHeader) (page))-pd_lower = SizeOfPageHeaderData ? 0 : \ PageHeader) (page))-pd_lower - SizeOfPageHeaderData) \ / sizeof(ItemIdData))) Well I think that is safe change: a = b ? 0 : ( a-b ) /c in max( 0, (a-b)/c ) so I think (not tested) you can rewrite that macro in: #define PageGetMaxOffsetNumber(page) \ (max(0, PageHeader) (page))-pd_lower - SizeOfPageHeaderData) \ / sizeof(ItemIdData Regards Gaeatano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PageGetMaxOffsetNumber on uninitialized pages
Gaetano Mendola wrote:
Tom Lane wrote:
We could fix this by changing the declarations of the maxoff variables
to int, but I think it's probably cleaner to recode
PageGetMaxOffsetNumber like so:
#define PageGetMaxOffsetNumber(page) \
(((PageHeader) (page))-pd_lower = SizeOfPageHeaderData ? 0 : \
PageHeader) (page))-pd_lower - SizeOfPageHeaderData) \
/ sizeof(ItemIdData)))
Well I think that is safe change:
a = b ? 0 : ( a-b ) /c
in
max( 0, (a-b)/c )
so I think (not tested) you can rewrite that macro in:
#define PageGetMaxOffsetNumber(page) \
(max(0, PageHeader) (page))-pd_lower - SizeOfPageHeaderData) \
/ sizeof(ItemIdData
Hi all,
no reply yet! I did this post in a provocative way.
Let me explain.
I know that most probably the max function is written in this way:
int max(int a, int b) { return a b ? b : a; }
so this means obtain almost the Tom's proposal.
I seen that usually postgres rpm distributed code, I think a big percentage
of postgres installation is used by an rpm, is compiled without taking
care of the architecture. Am I wrong ?
make now some benchmark using these two implementation:
(a) int max(int a, int b) { return a b ? b : a; }
or this unusual version:
(b) int max(int a, int b) { int i = -(a b); return (a i)|(b ~i); }
make an order of 10E6 maxing compiling your program without specify
the -march parameter.
Do the same specifying if you can -march=pentium3 or -march=pentium4
what I see on my pentiumIII is 100% of improvement, I didn't believe this
improvement just avoid ( I think ) dead branch, specifying the architecture.
So, am I hand waving/red herring ? May be yes, but my conclusion (wrong as
always in this list :-) ) is: if we don't specify the architecture as we do
= it's better use the nifty ( IMHO ) max implementation = is better write
my suggested macro ( with the opposite max implementation of course ).
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] false infinite recursion detected
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: kalman=# select * from v_v_test; ERROR: infinite recursion detected in rules for relation v_test I think also my other message: passing a whole record is a bug that shall to be fixed. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] -Wall and Wmissing-prototype
Hi all, I compiled postgres7.5devel and I see that during compilation are used togheter: -Wall -Wmissing-proptotype -Wmissing-declaration there is any reason to specify after -Wall others warning ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] passing a whole record variable into a SQL command is not implemented
Hi all, I'm running our regression test on top of postgresql 7.5 devel. I found this function not working anymore ( is a distillated ): create table test ( quota integer ); CREATE OR REPLACE FUNCTION sp_test ( ) RETURNS TEXT AS' DECLARE quota RECORD; my_ret TEXT := ; BEGIN FOR quota IN SELECT quota FROM test LOOP my_ret := my_ret || quota.quota || '',''; END LOOP; RETURN my_ret; END; ' LANGUAGE 'plpgsql' WITH ( iscachable ); when I execute it I obtain: ERROR: passing a whole record variable into a SQL command is not implemented the problem is that the variable quota have the same column name for the table test, for us it's a minimum issue due the fact that is easily fixed changing the variable name, but I'm wondering if behind there is something wrong. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] false infinite recursion detected
Hi all, I distilled another our regression test failing: create table test ( id integer, quota integer ); create or replace view v_test AS select id, quota from test; create or replace view v_v_test AS select t1.id, t1.quota from v_test t1 join v_test t2 using ( id ) ; kalman=# select * from v_v_test; ERROR: infinite recursion detected in rules for relation v_test Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT * FROM table LIMIT 1; is really slow
David Blasby wrote: I have a table with about 16,000,000 rows in it. When I do a: SELECT * FROM table LIMIT 1; it takes about 10 minutes (thats about how long it takes to do a full sequential scan). I had originally thought that there might be a large number of wasted/retired tuples in the table so I vacuum analysed the database. It had no effect. I had a vacuum full going on the table for 17 hours before I killed it. Are you sure that the vacuum full was running or sitting there to wait an idle transaction ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Optimizer bug??
Ismail Kizir wrote: Gaetano, I've changed my settings as : #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: #effective_cache_size = 1000# typically 8KB each random_page_cost = 2# units are one sequential page fetch cost cpu_tuple_cost = 0.009 # (same) cpu_index_tuple_cost = 0.0009 # (same) cpu_operator_cost = 0.0025 # (same) # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each But it still doesn't optimize for that range. Finally, i've set seq_scan off and, it works now. But i think, there must be a way to handle those settings automatically for cpu, ram and hdd settings(is it a sweet dream??) Did you SIGHUP the postmaster after ? You can change these settings also from command line, what you have to do is decrease the cost of the index scan till is less of the sequential scan cost. Good luck. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Optimizer bug??
Josh Berkus wrote: Gaetano, Hackers, what about to decrease the default values for this quantities ? Oh, I don't think Ismail has a bug, I think he probably just needs to tune his database. I just only suggesting to decrease that values that are oversized for a modern hardware. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Optimizer bug??
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Berkus wrote: | Gaetano, | | |I just only suggesting to decrease that values that are oversized for a | | modern | |hardware. | | | Hey, have you had success with those settings that you suggested? I've tried | tinkering with the relative CPU cost settings, and had mixed results. | That's why I have no particular recommmendation for them. | Usually yes, decreasing that values I'm able to decrease the index scan cost, so when I enable again the sequential scan the index one is choosed. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAs5Je7UpzwH2SGd4RAg/1AKCUYdTGIm5c7kG/ZXvmb49RWybs2ACgg3Wk zOHkWnCvbyPgeDCU3pn6UfQ= =Td0B -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Optimizer bug??
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I just only suggesting to decrease that values that are oversized for a modern hardware. I've seen no evidence saying that random_page_cost needs to be decreased for modern hardware. Disk seek speed versus bandwidth hasn't changed that much. People sometimes find it profitable to decrease that setting to compensate for other optimizer issues, but that doesn't mean we ought to change the default. As for the other settings you mentioned, I'd agree that the defaults are pretty arbitrary, but what evidence have you got to suggest better ones? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org I had queries not using a index scan ( was the best method ) that started to use the index scan decreasing that values. What I also notice is that under certain values I'm not able to decrease anymore the cost of a query. I'm using now: random_page_cost = 2.0 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.0025 vs these default costs: #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) look at this two queries (I just pick up one): DEFAULT VALUES test=# explain analyze select * from v_psr_guide_web; QUERY PLAN -- Subquery Scan v_psr_guide_web (cost=750.58..750.85 rows=21 width=236) (actual time=196.420..197.210 rows=178 loops=1) - Sort (cost=750.58..750.64 rows=21 width=236) (actual time=196.411..196.532 rows=178 loops=1) Sort Key: vg.estimated_start - Hash Join (cost=717.57..750.12 rows=21 width=236) (actual time=190.489..195.817 rows=178 loops=1) Hash Cond: (outer.id_publisher = inner.id_publisher) - Hash Left Join (cost=716.17..747.87 rows=128 width=208) (actual time=190.288..194.757 rows=178 loops=1) Hash Cond: (outer.id_drm_service = inner.id_drm_service) - Hash Left Join (cost=715.12..746.15 rows=128 width=188) (actual time=189.978..193.734 rows=178 loops=1) Hash Cond: (outer.id_cas_service = inner.id_cas_service) - Subquery Scan vg (cost=85.34..87.57 rows=127 width=168) (actual time=125.807..128.751 rows=178 loops=1) Filter: (view_target_group 2) - Unique (cost=85.34..85.98 rows=127 width=324) (actual time=125.743..127.723 rows=192 loops=1) - Sort (cost=85.34..85.66 rows=127 width=324) (actual time=125.738..126.573 rows=1298 loops=1) Sort Key: vp.id_package, s.estimated_start, sl.end_date - Hash Join (cost=71.92..80.91 rows=127 width=324) (actual time=103.605..118.505 rows=1298 loops=1) Hash Cond: (outer.id_package = inner.id_package) - Subquery Scan vp (cost=37.86..42.66 rows=384 width=304) (actual time=97.514..100.926 rows=384 loops=1) - Sort (cost=37.86..38.82 rows=384 width=219) (actual time=97.488..97.744 rows=384 loops=1) Sort Key: p.id_publisher, p.name - Hash Left Join (cost=1.96..21.37 rows=384 width=219) (actual time=1.003..95.690 rows=384 loops=1) Hash Cond: (outer.id_package = inner.id_package) - Seq Scan on packages p (cost=0.00..13.84 rows=384 width=203) (actual time=0.005..0.780 rows=384 loops=1) - Hash (cost=1.77..1.77 rows=77 width=20) (actual time=0.214..0.214 rows=0 loops=1) - Seq Scan on package_security ps (cost=0.00..1.77 rows=77 width=20) (actual time=0.011..0.126 rows=77 loops=1) - Hash (cost=33.81..33.81 rows=102 width=24) (actual time=5.756..5.756 rows=0 loops=1) - Hash Join (cost=17.45..33.81 rows=102 width=24) (actual time=1.625..4.216 rows=1298
Re: [HACKERS] Optimizer bug??
Ismail Kizir wrote: Hi everybody, 1 EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE ((mydate BETWEEN '2004-04-24' AND '2004-05-24' ) ) 2 EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE ((mydate = '2004-04-24') ) (I ran VACUUM ANALYZE before running those) mydate is an indexed date column. The optimizer optimizes the second query but, it doesn't optimize the first one and decides to make a sequential scan. Is this a bug? Or may someone explain me the reason? give us the result of these queries: SELECT COUNT(*) FROM articletbl; SELECT COUNT(*) AS c FROM articletbl WHERE mydate BETWEEN '2004-04-24' AND '2004-05-24'; SELECT COUNT(*) AS c FROM articletbl WHERE mydate = '2004-04-24'; Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Big problem
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Hmmm - I agree it's difficult, but somehow I think it's something we should do. Just imagine if some major user of postgres did it - they'd be screaming blue murder... Shrug. Superusers can *always* shoot themselves in the foot in Postgres. Try delete from pg_proc, for instance. This sounds right up there with the notion of preventing a Unix superuser from doing rm -rf /. Why not simply add a flag undeleteable applicable only to super user? In this way is enough in the initdb fase create the postgres user as undeleateable. I think this is resonable. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
