Re: [GENERAL] pgAdmin 4 - auto disconnect
Thank you very much, I will try this approach Paolo 2016-12-19 15:30 GMT+01:00 Melvin Davidson : > > On Mon, Dec 19, 2016 at 7:28 AM, Paolo Saudin > wrote: > >> Hi, >> >> I deployed pgAdmin4 on a server and I have a script that every day >> restore a database from a dump file. The problem I am facing at, is that if >> somebody forget to diconnect from the database, the restore command fails >> with an error "database Test is being accessed by other users". >> >> Is there a way to tell pgAdmin to auto disconnect from all databases? >> >> Thanks >> Paolo Saudin >> > > PgAdmin4 actually has nothing to do with it, as it is nothing more than a > tool to monitor PostgreSQL. > However, you can kill all user processes (except your own) by submitting > the following query. > > SELECT pg_terminate_backend(pid) > FROM pg_stat_activity > WHERE pg_backend_pid() <> pid; > > CAVEAT EMPTOR: You must be a superuser for this to work properly. > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. >
[GENERAL] pgAdmin 4 - auto disconnect
Hi, I deployed pgAdmin4 on a server and I have a script that every day restore a database from a dump file. The problem I am facing at, is that if somebody forget to diconnect from the database, the restore command fails with an error "database Test is being accessed by other users". Is there a way to tell pgAdmin to auto disconnect from all databases? Thanks Paolo Saudin
Re: [GENERAL] postgresql doesn't start
Adrian, thanks a lot about your questions 1. postgresql was installed via apt-get install postgresql -y command 2. in /var/run/postgresql/ there is only one file called 9.3-main.pid 3. postgres is not running, I did not find nothing with ps command 4. I remove the postmaster.pid file and I tried to run postgresql manually. this is the result: # /etc/init.d/postgresql start * Starting PostgreSQL 9.3 database server The PostgreSQL server failed to start. Please check the log output. ...fail! I verified in /var/log/postgresql/ and I've not found nothing On 3 October 2015 at 15:19, Adrian Klaver wrote: > On 10/03/2015 05:50 AM, Paolo De Michele wrote: > >> hi there, >> >> thanks a lot >> so, I'm using docker on ubuntu 14.04 >> about permissions: >> >> # ls -la /var/lib/postgresql/9.3/main >> total 72 >> drwx-- 28 postgres postgres 4096 Oct 3 12:41 . >> drwxr-xr-x 4 postgres postgres 4096 Oct 1 11:59 .. >> -rwx-- 1 postgres postgres4 Mar 21 2015 PG_VERSION >> drwx-- 12 postgres postgres 4096 Oct 1 11:59 base >> drwx-- 2 postgres postgres 4096 Oct 1 11:59 global >> drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_clog >> drwx-- 6 postgres postgres 4096 Oct 1 11:59 pg_multixact >> drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_notify >> drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_serial >> drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_snapshots >> drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_stat >> drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_stat_tmp >> drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_subtrans >> drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_tblspc >> drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_twophase >> drwx-- 4 postgres postgres 4096 Oct 1 12:18 pg_xlog >> -rwx-- 1 postgres postgres 133 Oct 1 20:00 postmaster.opts >> -rw--- 1 postgres postgres 50 Oct 3 12:41 postmaster.pid >> >> in /var/log/postgresql/ I've a file with 0kb >> it is weird >> is there a way to recover all dbs without starting the daemon? >> > > > What OS and version are you running? Assuming a Debian/Ubuntu flavor for > now. > > How was Postgres installed? > > So there is a pid file present in the data directory, how about in the run > directory, /var/run/postgresql? > > Is Postgres running? so: > > ps ax|grep post > > or > > pg_ctl status -D /var/lib/postgresql/9.3/main > > If not what happens if you remove the pid and start Postgres manually, not > through supervisor? > > > because I've another one docker with the same configuration and works >> fine (same os, same versione of postgresql) >> >> On 3 October 2015 at 07:13, Nicolas Paris > <mailto:nipari...@gmail.com>> wrote: >> >> Are you using docker on centos ? I had problem with >> centos/docker/postgresql because container size was (maybe still is) >> limited to 20GB on that specific OS. Maybe not related, but good to >> know >> >> 2015-10-03 0:03 GMT+02:00 John R Pierce > <mailto:pie...@hogranch.com>>: >> >> On 10/2/2015 2:02 PM, Paolo De Michele wrote: >> >> exec su postgres -c "/usr/lib/postgresql/9.3/bin/postgres -D >> /var/lib/postgresql/9.3/main -c >> config_file=/etc/postgresql/9.3/main/postgresql.conf" >> >> until yesterday there were no problem >> right now I see this in the /var/log/supervisor's directory: >> >> 2015-10-01 21:40:18 UTC HINT: The file seems accidentally >> left over, but it could not be removed. Please remove the >> file by hand and try again. >> 2015-10-01 21:40:20 UTC FATAL: could not remove old lock >> file "postmaster.pid": Permission denied >> >> >> >> try... >> ls -la /var/lib/postgresql/9.3/main >> >> The directory . should be owned by the postgres user, and it >> should have 700, 750, or 770 permissions. all the files in it >> should also be owned by postgres. >> >> also look and see if postgres logged anything in its own system >> log files (/var/log/postgresql/9.3 or whatever). >> >> >> >> -- >> john r pierce, recycling bits in santa cruz >> >> >> >> -- >> Sent via pgsql-general mailing list >> (pgsql-general@postgresql.org > pgsql-general@postgresql.org>) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] postgresql doesn't start
hi there, thanks a lot so, I'm using docker on ubuntu 14.04 about permissions: # ls -la /var/lib/postgresql/9.3/main total 72 drwx-- 28 postgres postgres 4096 Oct 3 12:41 . drwxr-xr-x 4 postgres postgres 4096 Oct 1 11:59 .. -rwx-- 1 postgres postgres4 Mar 21 2015 PG_VERSION drwx-- 12 postgres postgres 4096 Oct 1 11:59 base drwx-- 2 postgres postgres 4096 Oct 1 11:59 global drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_clog drwx-- 6 postgres postgres 4096 Oct 1 11:59 pg_multixact drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_notify drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_serial drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_snapshots drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_stat drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_stat_tmp drwx-- 2 postgres postgres 4096 Oct 1 11:59 pg_subtrans drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_tblspc drwx-- 2 postgres postgres 4096 Mar 21 2015 pg_twophase drwx-- 4 postgres postgres 4096 Oct 1 12:18 pg_xlog -rwx-- 1 postgres postgres 133 Oct 1 20:00 postmaster.opts -rw--- 1 postgres postgres 50 Oct 3 12:41 postmaster.pid in /var/log/postgresql/ I've a file with 0kb it is weird is there a way to recover all dbs without starting the daemon? because I've another one docker with the same configuration and works fine (same os, same versione of postgresql) On 3 October 2015 at 07:13, Nicolas Paris wrote: > Are you using docker on centos ? I had problem with > centos/docker/postgresql because container size was (maybe still is) > limited to 20GB on that specific OS. Maybe not related, but good to know > > 2015-10-03 0:03 GMT+02:00 John R Pierce : > >> On 10/2/2015 2:02 PM, Paolo De Michele wrote: >> >>> exec su postgres -c "/usr/lib/postgresql/9.3/bin/postgres -D >>> /var/lib/postgresql/9.3/main -c >>> config_file=/etc/postgresql/9.3/main/postgresql.conf" >>> >>> until yesterday there were no problem >>> right now I see this in the /var/log/supervisor's directory: >>> >>> 2015-10-01 21:40:18 UTC HINT: The file seems accidentally left over, >>> but it could not be removed. Please remove the file by hand and try again. >>> 2015-10-01 21:40:20 UTC FATAL: could not remove old lock file >>> "postmaster.pid": Permission denied >>> >> >> >> try... >> ls -la /var/lib/postgresql/9.3/main >> >> The directory . should be owned by the postgres user, and it should have >> 700, 750, or 770 permissions. all the files in it should also be owned by >> postgres. >> >> also look and see if postgres logged anything in its own system log files >> (/var/log/postgresql/9.3 or whatever). >> >> >> >> -- >> john r pierce, recycling bits in santa cruz >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
[GENERAL] postgresql doesn't start
hi there, I've a big problem with my postgresql installation I've postgresql 9.3 installed on docker; I start it via supervisord I've never had issues with postgresql and I don't touched nothing (no update, no changes) this is my configuration there's a file called postgresql.conf in /etc/supervisor/conf.d/ cat command: [program:postgres] command=/opt/postgresql.sh autostart=true autorestart=true stopsignal=QUIT lookt at /opt/postgresql.sh #!/bin/sh # This script is run by Supervisor to start PostgreSQL 9.3 in foreground mode if [ -d /var/run/postgresql ]; then chmod 2775 /var/run/postgresql else install -d -m 2775 -o postgres -g postgres /var/run/postgresql fi exec su postgres -c "/usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf" until yesterday there were no problem right now I see this in the /var/log/supervisor's directory: 2015-10-01 21:40:18 UTC HINT: The file seems accidentally left over, but it could not be removed. Please remove the file by hand and try again. 2015-10-01 21:40:20 UTC FATAL: could not remove old lock file "postmaster.pid": Permission denied if I remove this file with sudo permissions when I re-run the process I've the same error and I don't understand why. I tried the same configuration (postgresql installation and configuration) in another new docker installation and works fine. what's the problem? someone help me? I've very important databases and I dont' know how to recover it please let me know, thanks in advance
Re: [GENERAL] could not load plperl library
Try to check the perl version against Postgres version at http://forums.enterprisedb.com/posts/list/3295.page paolo On Wed, Apr 3, 2013 at 7:14 PM, Robert Fitzpatrick wrote: > I have a Windows XP laptop I've loaded postgres on for dev purposes. When > I try to create the plperl language on a db, I get an error 'cannot load > library' referencing the location where plperl.dll does exist. It has the > lastest version of ActiveState Perl, but I remember (it's been a while) > that I need an older version. I found a 5.8 installer, uninstalled the 5.16 > and then installed the older 5.8, but still getting the error. I restarted > the postgres service, do I need to reinstall? I used the Enterprise DB > installer. > > Hope this is the right list for this, let me know if not, thanks > -- > Robert > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
[GENERAL] orafce
Hi all, I am quite new in postgres, having experience with Oracle. I was trying to migrate some very simple applications from Oracle and found out that some functions like TRUNC() are only available via an addon called "orafce". The problem is that it seems only available for Postgres 9.1, while we have obviously started with the latest release. Does anybody know how to compile orafce for 9.2 or if there is any other solution to add some basic oracle functions (dual table, TRUNC, ecc...)?? Thanks a lot, Paolo
[GENERAL] SELECT to_timestamp crash PostgreSQL 9.1beta1
Trying to convert unix time to date time format, I encountered a database crash. Environment : WINDOWS 7 Professional - Service Pack1 -- -- PostgreSQL 8.4.7, compiled by Visual C++ build 1400, 32-bit -- SELECT to_timestamp(1306760400); 2011-05-30 15:00:00+02 -- -- PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit -- SELECT to_timestamp(1306760400); ** Errore ** -- SELECT version(); ** Errore ** no connection to the server -- Trying to re-click on the connection in the pgAdminIII server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Here are the log files : FILE : postgresql-2011-06-01_183350.log 2011-06-01 18:33:51 CEST LOG: database system was shut down at 2011-06-01 06:24:09 CEST 2011-06-01 18:33:51 CEST FATAL: the database system is starting up 2011-06-01 18:33:51 CEST LOG: database system is ready to accept connections 2011-06-01 18:33:51 CEST LOG: autovacuum launcher started 2011-06-01 21:01:01 CEST LOG: server process (PID 3552) was terminated by exception 0xC005 2011-06-01 21:01:01 CEST HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. 2011-06-01 21:01:01 CEST LOG: terminating any other active server processes 2011-06-01 21:01:01 CEST WARNING: terminating connection because of crash of another server process 2011-06-01 21:01:01 CEST DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2011-06-01 21:01:01 CEST HINT: In a moment you should be able to reconnect to the database and repeat your command. 2011-06-01 21:01:01 CEST WARNING: terminating connection because of crash of another server process 2011-06-01 21:01:01 CEST DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2011-06-01 21:01:01 CEST HINT: In a moment you should be able to reconnect to the database and repeat your command. 2011-06-01 21:01:01 CEST LOG: all server processes terminated; reinitializing 2011-06-01 21:01:11 CEST FATAL: pre-existing shared memory block is still in use 2011-06-01 21:01:11 CEST HINT: Check if there are any old server processes still running, and terminate them. FILE : postgresql-2011-06-01_210902.log 2011-06-01 21:09:02 CEST FATAL: the database system is starting up 2011-06-01 21:09:03 CEST LOG: database system was interrupted; last known up at 2011-06-01 18:33:51 CEST 2011-06-01 21:09:03 CEST LOG: database system was not properly shut down; automatic recovery in progress 2011-06-01 21:09:03 CEST LOG: consistent recovery state reached at 0/16E2DA0 2011-06-01 21:09:03 CEST LOG: record with zero length at 0/16E2DA0 2011-06-01 21:09:03 CEST LOG: redo is not required 2011-06-01 21:09:04 CEST FATAL: the database system is starting up 2011-06-01 21:09:04 CEST LOG: database system is ready to accept connections 2011-06-01 21:09:04 CEST LOG: autovacuum launcher started Thanks, paolo saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] plperl.dll on windows with postgresql 9.0
I had the same issue last week, I installed the Active Perl 5.10 and all worked ok Paolo Saudin Da: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Per conto di Sachin Srivastava Inviato: domenica 6 febbraio 2011 18:30 A: Robert Fitzpatrick Cc: PostgreSQL Oggetto: Re: [GENERAL] plperl.dll on windows with postgresql 9.0 I can find the plperl.dll in the lib folder of my installation (Windows 7 32 bit). How did you installed the postgresql-9.0.3? On Feb 6, 2011, at 10:38 PM, Robert Fitzpatrick wrote: I am upgrading a Windows install for a client of mine from 8.2.x to 9.0.3 and understand the pginstaller does not provide plperl for this version. ActivePerl 5.8 was already installed and after uninstalling 8.2 and installing 9.0.3, there is no plperl.dll in the lib folder. I thought this was due to the older version, so I uninstall Postgres and ActivePerl, restarted, downloaded and installed latest ActivePerl 5.12, restarted and re-installed Postgres to still not find the dll. How do I get the lib to install? I did a google and find perhaps 5.12 does not work (at least during beta)? http://postgresql.1045698.n5.nabble.com/BUG-5601-cannot-create-language-plpe rl-td2264970.html Can someone help with how to get plperl module installed or confirm the version required? I am installing on Windows 2003 server. Unfortunately I never installed Postgres on Windows prior to the pginstaller. Is there a plperl package that I need to install similar to Unix? Thanks. -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Sachin Srivastava EnterpriseDB <http://www.enterprisedb.com> , the Enterprise PostgreSQL <http://www.enterprisedb.com> company.
[GENERAL] Autovacuum running although set to off, and wraparound limit has not been reached
POSTGRESQL version 8.3.5
Centos5 x64
#--
# AUTOVACUUM PARAMETERS
#--
autovacuum = off# Enable autovacuum subprocess?
'on'
# requires track_counts to also be
on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions
and
# their durations, > 0 logs only
# actions running at least that
time.
#autovacuum_max_workers = 3 # max number of autovacuum
subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
autovacuum_freeze_max_age = 25000 # maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
Autovacuum is set to off, but we are still seeing these in pg_stat_activity:
autovacuum: VACUUM pg_catalog.pg_statistic
autovacuum: VACUUM {schemaX.tableY}
autovacuum: VACUUM {schemaA.TableB}
These do not have the "(to prevent TransactionID Wraparound)" comment in
them, so I am surprised to see these running with autovacuum set to off.
These tables have not reached their wraparound limits yet.
Out of curiosity, I modified the config file settings and increased the
autovacuum_vacuum_threshold to a relatively high value for our environment
(50M)
and reloaded the config settings via pg_reload_conf(). After this, the
vacuum activities stopped.
My questions would be:
1) Aside from preventing wraparounds, in what other circumstances does
autovacuum run?
2) Did changing the autovacuum config settings cause the autovacuums to
stop, if so, why, when autovacuum is set to 'off' the whole time?
Best Regards,
Paolo Saul
Re: [GENERAL] Visualize GiST Index
I firstly tried to "solve" the problem deleting the second parameter from all the calls to the stringToQualifiedNameList function, I wouldn't expect it, but it worked out, of course it was not the most elegant way. Oleg Bartunov wrote: > > Get gevel from cvs, address is on http://www.sai.msu.su/~megera/wiki/Gevel > Thanks, the version form cvs compiles fine, there are some differences in the when you make the installcheck but it works. I noticed that the expected output has been generated several months ago, so I supposed the differences can come from using different versions of postgresql. Oleg Bartunov wrote: > > btw, have you seen http://www.sai.msu.su/~megera/wiki/Rtree_Index ? > This also helps. Paolo Fogliaroni -- View this message in context: http://postgresql.1045698.n5.nabble.com/Visualize-GiST-Index-tp2849197p2857004.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visualize GiST Index
Hi all, we are making some experiments with postgresql and postgis. We need to visualize R-trees and are trying to use GiST and gevel. During the installation phase of gevel we had the following output: sed 's,MODULE_PATHNAME,$libdir/gevel,g' gevel.sql.in >gevel.sql gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I. -I../../src/include -c -o gevel.o gevel.c gevel.c: In function ‘gist_dumptree’: gevel.c:99: warning: format ‘%d’ expects type ‘int’, but argument 10 has type ‘Size’ gevel.c: In function ‘gist_tree’: gevel.c:134: error: too many arguments to function ‘stringToQualifiedNameList’ gevel.c: In function ‘gist_stat’: gevel.c:225: error: too many arguments to function ‘stringToQualifiedNameList’ gevel.c: In function ‘setup_firstcall’: gevel.c:325: error: too many arguments to function ‘stringToQualifiedNameList’ make: *** [gevel.o] Error 1 We are running Postgresql 8.4.4, can you please help us? Paolo & Waqas -- View this message in context: http://postgresql.1045698.n5.nabble.com/Visualize-GiST-Index-tp2849197p2855017.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visualize GiST Index
Hi all, we are making some experiments with postgresql and postgis. We need to visualize R-trees and are trying to use GiST and gevel. During the installation phase of gevel we had the following output: sed 's,MODULE_PATHNAME,$libdir/gevel,g' gevel.sql.in >gevel.sql gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I. -I../../src/include -c -o gevel.o gevel.c gevel.c: In function ‘gist_dumptree’: gevel.c:99: warning: format ‘%d’ expects type ‘int’, but argument 10 has type ‘Size’ gevel.c: In function ‘gist_tree’: gevel.c:134: error: too many arguments to function ‘stringToQualifiedNameList’ gevel.c: In function ‘gist_stat’: gevel.c:225: error: too many arguments to function ‘stringToQualifiedNameList’ gevel.c: In function ‘setup_firstcall’: gevel.c:325: error: too many arguments to function ‘stringToQualifiedNameList’ make: *** [gevel.o] Error 1 We are running Postgresql 8.4.4, can you please help us? Paolo & Waqas -- View this message in context: http://postgresql.1045698.n5.nabble.com/Visualize-GiST-Index-tp2849197p2855255.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 8.4 Window functions
Hi all, I am trying to calculate an 8 hour moving average using the new Window functions without success. Here is what I am trying to do : -- create test table CREATE TABLE temperatures ( fulldate timestamp NOT NULL PRIMARY KEY, value numeric ); -- inserts INSERT INTO temperatures select '2009-07-01 00:00:00'::timestamp + interval '1 hour' * s.a as fulldate, round(cast(random() as numeric), 1) as value from generate_series(0,23) as s(a) ; -- selects select * from temperatures order by fulldate; -- window function SELECT fulldate, value, avg(value) OVER () FROM temperatures ORDER BY fulldate; SELECT fulldate, value, avg(value) OVER (ORDER BY fulldate) FROM temperatures ORDER BY fulldate; SELECT fulldate, value, round(avg(value) OVER (ORDER BY fulldate RANGE UNBOUNDED PRECEDING), 2) as value FROM temperatures ORDER BY fulldate; -- not supported SELECT fulldate, value, round(avg(value) OVER (ORDER BY fulldate RANGE -8 PRECEDING), 2) as value FROM temperatures ORDER BY fulldate; Is there any way to PARTITION on a subset of rows (in this case 8) ? -- expected result -- date timevalue moving-average 2009-07-01 00:00:00 0,3 2009-07-01 01:00:00 0,1 2009-07-01 02:00:00 0,5 2009-07-01 03:00:00 0,1 2009-07-01 04:00:00 0,2 2009-07-01 05:00:00 0,7 2009-07-01 06:00:00 0,9 2009-07-01 07:00:00 0,7 0,44 2009-07-01 08:00:00 0 0,4 2009-07-01 09:00:00 0,9 0,5 2009-07-01 10:00:00 0,8 0,54 2009-07-01 11:00:00 0,4 0,58 2009-07-01 12:00:00 0,6 0,63 2009-07-01 13:00:00 0,4 0,59 2009-07-01 14:00:00 0,7 0,56 2009-07-01 15:00:00 0,2 0,5 2009-07-01 16:00:00 0,2 0,53 2009-07-01 17:00:00 0,5 0,48 2009-07-01 18:00:00 0,7 0,46 2009-07-01 19:00:00 0 0,41 2009-07-01 20:00:00 0,4 0,39 2009-07-01 21:00:00 0,9 0,45 2009-07-01 22:00:00 0,4 0,41 2009-07-01 23:00:00 0,7 0,48 0,51 0,52 0,48 0,6 0,67 0,55 0,7 Thanks in advance Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is this a bug or a feature? Column visibility in subquery from outer query
Thank you for clearing that up. Regards, Paolo Saul > This behavior is required by the SQL standard. It's called an outer > reference. > >regards, tom lane >
[GENERAL] Is this a bug or a feature? Column visibility in subquery from outer query
postgres=# create table public.ps_test_x (x1 oid, x2 oid, x3 oid); CREATE TABLE postgres=# create table public.ps_test_y (y1 oid, y2 oid, y3 oid); CREATE TABLE postgres=# explain select * from public.ps_test_x where x1 in (select x1 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x2 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x3 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x4 from public.ps_test_y); ERROR: column "x4" does not exist LINE 1: ...elect * from public.ps_test_x where x1 in (select x4 from pu... ^ postgres=# explain select * from public.ps_test_x where x1 in (select y1 from public.ps_test_y); QUERY PLAN --- Hash Join (cost=36.62..88.66 rows=1770 width=12) Hash Cond: (ps_test_x.x1 = ps_test_y.y1) -> Seq Scan on ps_test_x (cost=0.00..27.70 rows=1770 width=12) -> Hash (cost=34.12..34.12 rows=200 width=4) -> HashAggregate (cost=32.12..34.12 rows=200 width=4) -> Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=4) (6 rows) I just want to point out that the sub-query is using a column from the outer query (eg. x1) without an alias from the table in the outer query. This can lead to a confusion when, for example: delete from table1 where foreign_id in (select foreign_id from table2) -- ! table2 does not have the foreign_id column ! This would do a table scan on table1 and delete all its rows. Why isn't it like: delete from table1 where foreign_id in (select table1.foreign_id from table2) where you must specify the outer query's table reference inside the subquery. I suspect the original intent was to use the outer query columns in some processing inside the subquery, which is valid. I'm just wondering why an explicit reference isn't required to distinguish the column. For convenience, or part of the SQL spec? Cheers! --Paolo Saul
R: [GENERAL] Rounding problems
>>"Paolo Saudin" writes: >> I have a problem with a query wich simple aggregate values. In the sample >> below I have two values, 1.3 and 1.4. Rounding their average with one >> decimals, should give 1.4. > >You seem way overoptimistic about float4 values being exact. They are >not. The actual computation being done here is more like > >regression=# select (1.3::real + 1.4::real) / 2 ; > ?column? >-- > 1.3490463257 > (1 row) > >If you want an exact sum with no roundoff error you should be storing >all your values as numeric (and taking the consequent speed and space >hit :-(). > > regards, tom lane > I converted all the fields in numeric type instead of real and now both queries return the same result ! Now I need to test about performances ... Thank you very much !! Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] Rounding problems
>Paolo Saudin wrote: >Hi, > >I have a problem with a query wich simple aggregate values. In the sample below I have two values, 1.3 and 1.4. Rounding their average with one decimals, should give 1.4. >The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS value - give the expected result, while the second one with - tables_seb.tbl_arvier_chamencon.id_1 AS >value - give 1.3. > >Which could be the reason ?? > > >My first thought is whats with all the castings??? > >Castings are mostly likely the cause of your problems, What is tbl_arvier_chamencon.id_1 data type??? > >I'm guessing its something other than numeric. All other floating point data types will have problems caused by Binary Floating-Point Arithmetic > >Numeric data type uses different functions to do its math for the stated purpose of being exact yet being allot slower. > >In one query casting is done prior to avg() yet in the other casting is done after avg(). This will allow Postgres to use different functions to calculate average giving an unexpected >result. Here is the table layout CREATE TABLE tables_seb.tbl_arvier_chamencon ( fulldate timestamp without time zone NOT NULL DEFAULT '2000-01-01 00:00:00'::timestamp without time zone, id_1 real, id_1_cod smallint, id_2 real, id_2_cod smallint, id_3 real, id_3_cod smallint, id_4 real, id_4_cod smallint, CONSTRAINT tbl_arvier_chamencon_pkey PRIMARY KEY (fulldate) ) WITH ( OIDS=FALSE); Thanks, Paolo Saudin
[GENERAL] Rounding problems
Hi,
I have a problem with a query wich simple aggregate values. In the sample
below I have two values, 1.3 and 1.4. Rounding their average with one
decimals, should give 1.4.
The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS
numeric) AS value - give the expected result, while the second one with -
tables_seb.tbl_arvier_chamencon.id_1 AS value - give 1.3.
Which could be the reason ??
-- data
fulldate timestamp;tables_seb.tbl_arvier_chamencon.id_1- reals
2009-03-29 00:00:00; 1.3
2009-03-29 00:30:00; 1.4
--Good query
SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric),
1 ) AS value
FROM
(
SELECT _master_30.fulldate AS data,
cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS
value
--tables_seb.tbl_arvier_chamencon.id_1 AS value
FROM _master_30
LEFT JOIN tables_seb.tbl_arvier_chamencon ON
_master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate
WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND
'2009-03-29 00:59:59'
ORDER BY data
) foo
GROUP BY 1 ORDER BY 1;
-- value = 1.4 OK
--wrong query
SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric),
1 ) AS value
FROM
(
SELECT _master_30.fulldate AS data,
--cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS
value
tables_seb.tbl_arvier_chamencon.id_1 AS value
FROM _master_30
LEFT JOIN tables_seb.tbl_arvier_chamencon ON
_master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate
WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND
'2009-03-29 00:59:59'
ORDER BY data
) foo
GROUP BY 1 ORDER BY 1
-- value = 1.3 NOT OK
-- test
select round(cast( (1.3 + 1.4)::real / 2 as numeric), 1);
-- value = 1.4 OK
Using PostgreSQL 8.3.7 on Windows Server 2008
Thank in advance,
Paolo Saudin
R: R: R: [GENERAL] How to check if 2 series of data are equal
-Messaggio originale- Da: Adrian Klaver [mailto:akla...@comcast.net] Inviato: giovedì 12 febbraio 2009 23.22 A: Paolo Saudin Cc: pgsql-general@postgresql.org Oggetto: Re: R: R: [GENERAL] How to check if 2 series of data are equal On Thursday 12 February 2009 11:37:37 am Paolo Saudin wrote: > > > > Can be the same data ( and it is ) because of errors in the remote > > stations configurations. > > The Stations and parameters IDs were mixed up resulting in same data > > in different tables ... > > > > > >I am afraid I more confused now. From the table schema the value is a real > > number only and has no units. As I understand the units >designation lies > > in the id. If the ids are mixed up I can't see how it is possible to > > differentiate between a value of 25 that maybe >degrees C or % relative > > humidity for instance. You are going to have to step me through this. > > Yes, the parameter is defined by the id and stored in another table with > the name, units and other properties. I need to find out a sequence of > meanvalues (without taking care of ids) which exists in another table > > Here is some sample data, I need to found out if some sequence of data in > table1 is equal to data in table2, table3 ... tableN. > > Table1 > fulldate, id, meanvalue > 2009-01-01 00:00:00, 1, 12.3 -- temperature > 2009-01-01 01:00:00, 1, 12.5 > 2009-01-01 02:00:00, 1, 12.6 > 2009-01-01 03:00:00, 1, 12.7 > 2009-01-01 04:00:00, 1, 12.8 > 2009-01-01 05:00:00, 1, 12.2 > > Table1 > fulldate, id, meanvalue > 2009-01-01 00:00:00, 2, 80.3 -- humidity > 2009-01-01 01:00:00, 2, 81.6 > 2009-01-01 02:00:00, 2, 82.1 > 2009-01-01 03:00:00, 2, 79.8 > 2009-01-01 04:00:00, 2, 77.2 > 2009-01-01 05:00:00, 2, 77.1 > -- > > Table2 > fulldate, id, meanvalue > 2009-01-01 00:00:00, 1, 12.3 -- temperature > 2009-01-01 01:00:00, 1, 11.8 > 2009-01-01 02:00:00, 1, 82.1 ! WRONG DATA - SAME AS Table2 id->2 > 2009-01-01 03:00:00, 1, 79.8 ! WRONG DATA - SAME AS Table2 id->2 > 2009-01-01 04:00:00, 1, 77.2 ! WRONG DATA - SAME AS Table2 id->2 > 2009-01-01 05:00:00, 1, 13.1 I am going to assume you mean Table1 above. > > Table2 > fulldate, id, meanvalue > 2009-01-01 00:00:00, 2, 78.9 -- humidity > 2009-01-01 01:00:00, 2, 76.4 > 2009-01-01 02:00:00, 2, 74.7 > 2009-01-01 03:00:00, 2, 73.1 > 2009-01-01 04:00:00, 2, 71.6 > 2009-01-01 05:00:00, 1, 70.8 > > Hope this might help, > Paolo Saudin >I modified Sams query- > > >SELECT fulldate,sensor > FROM (SELECT fulldate,sensor,count(sensor) > FROM (SELECT 1 AS station, fulldate, meanvalue AS sensor FROM table1 >UNION >SELECT 2, fulldate, meanvalue FROM table2 ORDER BY >fulldate,sensor) AS x >GROUP BY fulldate,sensor) AS y >WHERE y.count>1; > > >and got- > > fulldate | sensor >-+ > 2009-01-01 00:00:00 | 12.3 > 2009-01-01 02:00:00 | 82.1 > 2009-01-01 03:00:00 | 79.8 > 2009-01-01 04:00:00 | 77.2 Thank you very much to you all, this one works perfectly !! >Though I think you might want to deal with the remote sensor problem first. I >would be hesitant to trust any of the data. Just a thought. The problem has been fixed and does not happens any more. Unfortunately there are 14 years (1992-2006) in which data could be corrupted for short periods. Now I must found them out ... Thanks once more Paolo Saudin >-- >Adrian Klaver >akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: R: [GENERAL] How to check if 2 series of data are equal
>-Messaggio originale- >Da: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] Per conto di Adrian Klaver >Inviato: giovedì 12 febbraio 2009 18.57 >A: Paolo Saudin >Cc: pgsql-general@postgresql.org >Oggetto: Re: R: [GENERAL] How to check if 2 series of data are equal >- "Paolo Saudin" wrote: > >-Messaggio originale- > >Da: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] Per conto di Adrian > Klaver > >Inviato: giovedì 12 febbraio 2009 17.28 > >A: pgsql-general@postgresql.org > >Cc: Paolo Saudin > >Oggetto: Re: [GENERAL] How to check if 2 series of data are equal > > >On Thursday 12 February 2009 12:06:41 am Paolo Saudin wrote: > >> Hi, > >> > >> I have 14 tables filled with meteorological data, one record per > parameter > >> per hour. The id field holds the parameter type (1=temperature, > 2=humidity > >> ...) My problem is that for short periods (maybe one week, one > month) there > >> are two stations with the same data, I mean the temperature of > table1 is > >> equal to the humidity of table3. I need to discover those cases. > > >Before I can start to answer this I need some clarification. How can > temperature > >and humidity be the same data? > > Can be the same data ( and it is ) because of errors in the remote > stations configurations. > The Stations and parameters IDs were mixed up resulting in same data > in different tables ... > >I am afraid I more confused now. From the table schema the value is a real >number only and has no units. As I understand the units >designation lies in >the id. If the ids are mixed up I can't see how it is possible to >differentiate between a value of 25 that maybe >degrees C or % relative >humidity for instance. You are going to have to step me through this. Yes, the parameter is defined by the id and stored in another table with the name, units and other properties. I need to find out a sequence of meanvalues (without taking care of ids) which exists in another table Here is some sample data, I need to found out if some sequence of data in table1 is equal to data in table2, table3 ... tableN. Table1 fulldate, id, meanvalue 2009-01-01 00:00:00, 1, 12.3-- temperature 2009-01-01 01:00:00, 1, 12.5 2009-01-01 02:00:00, 1, 12.6 2009-01-01 03:00:00, 1, 12.7 2009-01-01 04:00:00, 1, 12.8 2009-01-01 05:00:00, 1, 12.2 Table1 fulldate, id, meanvalue 2009-01-01 00:00:00, 2, 80.3-- humidity 2009-01-01 01:00:00, 2, 81.6 2009-01-01 02:00:00, 2, 82.1 2009-01-01 03:00:00, 2, 79.8 2009-01-01 04:00:00, 2, 77.2 2009-01-01 05:00:00, 2, 77.1 -- Table2 fulldate, id, meanvalue 2009-01-01 00:00:00, 1, 12.3-- temperature 2009-01-01 01:00:00, 1, 11.8 2009-01-01 02:00:00, 1, 82.1 ! WRONG DATA - SAME AS Table2 id->2 2009-01-01 03:00:00, 1, 79.8 ! WRONG DATA - SAME AS Table2 id->2 2009-01-01 04:00:00, 1, 77.2 ! WRONG DATA - SAME AS Table2 id->2 2009-01-01 05:00:00, 1, 13.1 Table2 fulldate, id, meanvalue 2009-01-01 00:00:00, 2, 78.9-- humidity 2009-01-01 01:00:00, 2, 76.4 2009-01-01 02:00:00, 2, 74.7 2009-01-01 03:00:00, 2, 73.1 2009-01-01 04:00:00, 2, 71.6 2009-01-01 05:00:00, 1, 70.8 Hope this might help, Paolo Saudin >Adrian Klaver >akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] How to check if 2 series of data are equal
>-Messaggio originale-
>Da: pgsql-general-ow...@postgresql.org
>[mailto:pgsql-general-ow...@postgresql.org] Per conto di Adrian Klaver
>Inviato: giovedì 12 febbraio 2009 17.28
>A: pgsql-general@postgresql.org
>Cc: Paolo Saudin
>Oggetto: Re: [GENERAL] How to check if 2 series of data are equal
>On Thursday 12 February 2009 12:06:41 am Paolo Saudin wrote:
>> Hi,
>>
>> I have 14 tables filled with meteorological data, one record per parameter
>> per hour. The id field holds the parameter type (1=temperature, 2=humidity
>> ...) My problem is that for short periods (maybe one week, one month) there
>> are two stations with the same data, I mean the temperature of table1 is
>> equal to the humidity of table3. I need to discover those cases.
>Before I can start to answer this I need some clarification. How can
>temperature
>and humidity be the same data?
Can be the same data ( and it is ) because of errors in the remote stations
configurations.
The Stations and parameters IDs were mixed up resulting in same data in
different tables ...
>>
>> I could pick one record in the first station and then compare it with the
>> ones in the other tables for all the parameters at that particular date. If
>> two records are equals (it probably happens) I must then check the next one
>> in the timeserie. If the second record is equal too, then probably the two
>> series may be equals and I must raise an alert from my application. Is
>> there a better and faster way to perform such a check ?
>>
>> -- tables
>> CREATE TABLE table1
>> (
>> fulldate timestamp,
>> id smallint NOT NULL,
>> meanvalue real
>> ) WITH (OIDS=FALSE);
>> --.
>> --.
>> CREATE TABLE table14
>> (
>> fulldate timestamp,
>> id smallint NOT NULL,
>> meanvalue real
>> ) WITH (OIDS=FALSE);
>> --
>> -- inserts
>> insert into table1(select
>> ('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
>> 1::smallint, round(cast(random() as numeric), 1)::real
>> from generate_series(0,1000) as s(a)
>> );
>> --
>> insert into table2(select
>> ('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
>> 1::smallint, round(cast(random() as numeric), 1)::real
>> from generate_series(0,1000) as s(a)
>> );
>> --
>> -- same data as table 1 -- MUST BE FOUND BY THE CKECK ROUTINE
>> insert into table3(select fulldate, id, meanvalue from table1);
>>
>>
>> Thank in advance,
>> Paolo Saudin
>--
>Adrian Klaver
>akla...@comcast.net
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to check if 2 series of data are equal
Hi,
I have 14 tables filled with meteorological data, one record per parameter per
hour. The id field holds the parameter type (1=temperature, 2=humidity ...) My
problem is that for short periods (maybe one week, one month) there are two
stations with the same data, I mean the temperature of table1 is equal to the
humidity of table3. I need to discover those cases.
I could pick one record in the first station and then compare it with the ones
in the other tables for all the parameters at that particular date. If two
records are equals (it probably happens) I must then check the next one in the
timeserie. If the second record is equal too, then probably the two series may
be equals and I must raise an alert from my application. Is there a better and
faster way to perform such a check ?
-- tables
CREATE TABLE table1
(
fulldate timestamp,
id smallint NOT NULL,
meanvalue real
) WITH (OIDS=FALSE);
--.
--.
CREATE TABLE table14
(
fulldate timestamp,
id smallint NOT NULL,
meanvalue real
) WITH (OIDS=FALSE);
--
-- inserts
insert into table1(select
('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
1::smallint, round(cast(random() as numeric), 1)::real
from generate_series(0,1000) as s(a)
);
--
insert into table2(select
('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
1::smallint, round(cast(random() as numeric), 1)::real
from generate_series(0,1000) as s(a)
);
--
-- same data as table 1 -- MUST BE FOUND BY THE CKECK ROUTINE
insert into table3(select fulldate, id, meanvalue from table1);
Thank in advance,
Paolo Saudin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
R: R: [GENERAL] complex custom aggregate function
>-Messaggio originale- >Da: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] Per conto di Scara Maccai >Inviato: lunedì 2 febbraio 2009 10.36 >A: Paolo Saudin; pgsql-general@postgresql.org >Cc: pgsql-general >Oggetto: Re: R: [GENERAL] complex custom aggregate function > Paolo Saudin wrote: > For that purpose, a sliding mean calculation I use the following > > CREATE TABLE tbl_ayas > ( > fulldate timestamp without time zone NOT NULL, > id_1 real, -- temperature > id_2 real, -- pressure > .. > CONSTRAINT tbl_ayas_pkey PRIMARY KEY (fulldate) > ) WITH (OIDS=FALSE); > > [...] > Select perl_sliding_mean(0,0,0,0,'f','t'); > SELECT perl_sliding_mean(0," id_1 ", 8, 6, 'f', 'f') AS numeric), 1) AS > "ayas_temperature", > perl_sliding_mean(1," id_2 ", 8, 6, 'f', 'f') AS numeric), 1) AS > "ayas_pressure" >I don't understand: how can you be sure that data is passed to the function >ordered by "fulldate"? >Thank you. I use a master table with a "fulldate" field and filled with sequential dates to fill gaps when meteo data is missing. CREATE TABLE master ( fulldate timestamp without time zone NOT NULL, CONSTRAINT master_pkey PRIMARY KEY (fulldate) ) WITH (OIDS=FALSE); So the query will be: SELECT fulldate, id_3 AS "ayas_temperature" , round(cast(perl_sliding_mean(0,id_3, 8, 6, 'f', 'f') AS numeric), 3) AS "ayas_temperature_sliding" FROM _master LEFT JOIN tables_ar.tbl_ayas USING(fulldate) WHERE fulldate > '2009-01-01' ORDER BY fulldate limit 16; 01/01/2009 1.00 -7 01/01/2009 2.00 -7,1 01/01/2009 3.00 -5,3 01/01/2009 4.00 -5,2 01/01/2009 5.00 -4,8 01/01/2009 6.00 -4 01/01/2009 7.00 -4,3 01/01/2009 8.00 -5,2-5,363 ( mean from 01/01/2009 1.00 - 01/01/2009 8.00 ) 01/01/2009 9.00 -5,4-5,163 ... 01/01/2009 10.00-3 -4,65 ... 01/01/2009 11.00-0,4-4,038 ... 01/01/2009 12.000,4 -3,338 ... 01/01/2009 13.00-0,2-2,763 ... 01/01/2009 14.00-1,8-2,488 ... 01/01/2009 15.00-2,2-2,225 ... 01/01/2009 16.00-2,6-1,9( mean from 01/01/2009 9.00 - 01/01/2009 16.00 ) And all the sliding means are correct ( from the 8th value ahead) Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] complex custom aggregate function
>-Messaggio originale-
>Da: pgsql-general-ow...@postgresql.org
>[mailto:pgsql-general-ow...@postgresql.org] Per conto di Scara Maccai
>Inviato: venerdì 30 gennaio 2009 9.36
>A: pgsql-general@postgresql.org
>Oggetto: [GENERAL] complex custom aggregate function
>
>Hi all,
>
>I have a table like:
>
>value int,
>quarter timestamp
>
>I need an aggregate function that gives back the maximum "value" using
>this algorithm:
>
>AVG of the first hour (first 4 quarters) (AVG0)
>same as above, but 1 quarter later (AVG1)
>
>same as above, but n quarters later (AVGn)
>
>result: the quarter where AVGn was MAX.
>
>Example:
>
>quartervalue AVGn
>
>2008-01-01 00:00 10
>2008-01-01 00:15 15
>2008-01-01 00:30 5
>2008-01-01 00:45 20 -> 12.5 ((10+15+5+20)/4)
>2008-01-01 01:15 2 -> 21 ((15+5+20+2)/4)
>2008-01-01 01:30 30 -> 14.25 ((5+20+2+30)/4))
>
>the result should be ('2008-01-01 00:15', 21)
>
>
>
>It would be very easy if the input to the custom aggregate function was
>ordered (because I would keep 4 internal counters), but I guess there's
>no way of "forcing" the ordering of the input to the function, right?
>
>So I have to cache all the (quarter,value) couples and give back a
>result at the end, right?
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
For that purpose, a sliding mean calculation I use the following
--
-- Sample table definition
--
CREATE TABLE tbl_ayas
(
fulldate timestamp without time zone NOT NULL,
id_1 real, -- temperature
id_2 real, -- pressure
..
CONSTRAINT tbl_ayas_pkey PRIMARY KEY (fulldate)
) WITH (OIDS=FALSE);
--
-- Function
--
CREATE OR REPLACE FUNCTION perl_sliding_mean(integer, real, integer, integer,
bpchar, bpchar)
RETURNS real AS
$BODY$
#BEGIN { strict->import(); }
# get values
my ($myid, $myval, $mycount, $myvalid, $myslidesum, $myreset) = @_;
# reset the arry if requested
if ( $myreset eq 't' ) {
@stored_sl_val=();
@stored_arr=();
return 0;
}
# restore the array of array
@temp_sl_val = $stored_arr[$myid];
@stored_sl_val = @{$temp_sl_val[0]};
# check if the value is null
if ( ! defined $myval ) {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => push null value [undef]" );
# sum does not change
push(@stored_sl_val, undef);
} else {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => push value $myval" );
# assign the new value
push(@stored_sl_val, $myval);
}
# log log log log log log
elog(NOTICE, "perl_sliding_mean => scalar array " . scalar @stored_sl_val );
if ( ( scalar @stored_sl_val ) > $mycount ) {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => pop element" );
# Remove one element from the beginning of the array.
shift(@stored_sl_val);
}
# getting mean
# log log log log log log
elog(NOTICE, "perl_sliding_mean => getting mean" );
my $good_values;
my $result;
foreach (@stored_sl_val) {
# log log log log log log
elog(NOTICE, "arr : " . $_ );
if ( defined $_ ) {
$result += $_;
$good_values ++;
}
}
# log log log log log log
elog(NOTICE, "perl_sliding_mean => sum : $result, good values : $good_values"
);
my $mean;
if ( $good_values >= $myvalid ) {
# reset the arry if requested
if ( $myslidesum eq 't' ) {
$mean = $result; # sum
} else {
$mean = $result / $good_values; # average
}
} else {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => good_values < myvalid" );
$mean = -; # skip later and return null
}
# save back the array of array
elog(NOTICE, "perl_sliding_mean => scalar stored_sl_val " . scalar
@stored_sl_val );
$stored_arr[$myid] = [ @stored_sl_val ];
# return calculated sliding mean or null
if ( $mean == - ) { return; }
return $mean;
$BODY$
LANGUAGE 'plperlu' VOLATILE;
COMMENT ON FUNCTION perl_sliding_mean(integer, real, integer, integer, bpchar,
bpchar) IS 'Calculate sliding means/sums';
--
-- query
--
Select perl_sliding_mean(0,0,0,0,'f','t');
SELECT perl_sliding_mean(0," id_1 ", 8, 6, 'f', 'f') AS numeric), 1) AS
"ayas_temperature",
perl_sliding_mean(1," id_2 ", 8, 6, 'f', 'f') AS numeric), 1) AS
"ayas_pressure"
.
Regards,
Paolo Saudin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] How to get the real postgreql error from visual basic
>>-Messaggio originale- >>Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto di dfx >>Inviato: domenica 27 luglio 2008 19.37 >>A: pgsql-general@postgresql.org >>Oggetto: [GENERAL] How to get the real postgreql error from visual basic >> >>Dear Sirs, >> >>when I execute a function that returns an error, visual basic shows always >>the same error code ( -214767259) but I would like to know the real postgres >>code of the error. >> >>The visual basic code that I use is the following: >> >>Dim Cmd As new ADODB.Command >>Cmd.CommandText = "delete from where id=;" >>Cmd.ActiveConnection = mvarConnection >>Cmd.Execute >> >>Whichever error appens the visual basic Err object returns the same number. >> >> >>What I have to do? >> >>Any suggestion will be appreciated. >> >>Domenico Hi, I use GetODBCerrors function (which I found somewhere in internet) to return the errors I get back from PostgreSQL. Hope this help dim m_Dbh As ADODB.Connection dim m_LastError as String ... Run query ... If m_Dbh.Errors.Count > 0 Then m_LastError = GetODBCerrors Function GetODBCerrors() As String On Error GoTo GetODBCerrors_ErrHandler GetODBCerrors = "" Dim objError As ADODB.Error Dim strError As String If m_Dbh.Errors.Count > 0 Then For Each objError In m_Dbh.Errors strError = strError & "Error #" & objError.Number & " " & objError.Description & vbCrLf & "NativeError: " _ & objError.NativeError & vbCrLf & "SQLState: " & objError.SQLState & vbCrLf & "Reported by: " & _ objError.Source & vbCrLf & "Help file: " & objError.HelpFile & vbCrLf & "Help Context ID: " & _ objError.HelpContext Next GetODBCerrors = strError End If Exit Function GetODBCerrors_ErrHandler: GetODBCerrors = Err.Number & " " & Err.Source & " " & Err.Description End Function Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] Debugging Pl/PgSQL functions with the debug contrib module
>-Messaggio originale- >Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto di Peter Geoghegan >Inviato: mercoledì 9 aprile 2008 12.50 >A: Richard Huxton >Cc: pgsql-general@postgresql.org >Oggetto: Re: [GENERAL] Debugging Pl/PgSQL functions with the debug contrib module > >Richard, > >I recall that in earlier versions of the PostgreSQL windows binary >distribution, I had finer grained control of what modules were >installed. The only optional module is now PL/Java, which I don't use, >and things like headers and libraries. I cannot specify installing >Pl/PgSQL, Pl/Perl etc, or this fabled debugging module for that >matter. > >This isn't the first time I encountered weirdness with the installer. >I recall that when I went to build libpq about 8 months ago, the >installer said it installed headers and libraries, which it simply did >not. > >Having gone through the 8.3.1 MSI installer meticulously, it is >abundantly clear that this module is unavailable. I uninstalled the >last version, 8.3.0, and installed 8.3.1 today. I recall that in the >8.3.0 installer, I could specify that I wanted the module, and I did, >but it didn't work, just as the 8.3.1 installer didn't work as >described in my original e-mail.That being the case, how should I >proceed? > >Thanks, >Peter Geoghegan > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general I just installed Postgresql 8.3.1 on a fresh machine and in the step 'Enable contrib modules', you can select plDebugger option to enable the debugger. Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
1) What type of names do you prefer? --- b) new one with pg_ prefix - pg_createdb, pg_creteuser ... 2) How often do you use these tools? --- b) one per week 3) What name of initdb do you prefer? -- -- b) pg_initdb 4) How do you perform VACUUM? - b) VACUUM - SQL command c) autovacuum Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Linux distro
-Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto di Reid Thompson Inviato: mercoledì 1 agosto 2007 15.15 A: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Linux distro On Wed, 2007-08-01 at 13:29 +0200, [EMAIL PROTECTED] wrote: > Hello, > > I bought a Dell server and I am going to use it for installing > PostgrSQL 8.2.4. I always used Windows so far and I would like now to > install a Linux distribution on the new server. Any suggestion on > which distribution ? Fedora, Ubuntu server, Suse or others? > > Thanks in advance, > Paolo Saudin > > > > ---(end of > broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match >Is this going to be a production server. or a learning server, or a i'm >learning all things linux server/desktop? This is going to be a test server holding meteorological data (100 tables with 1-2 millions rows each) that will serve as a kind of replica/backup for others databases (pull data from an FTP server via perl scripts). No matter for data loss since all the other databases are backed-up on a daily basis. >If it's a dedicated production server, look at UBUNTU 6.10 server. >If you're planning to connect a monitor and run X-windows ( i.e. I >bought a server, but i'm going to use it as a learning platform for >LINUX in general also), i'd suggest either UBUNTU 6.10 or 7.04 desktop >( or, start with the 6.10 server, and use apt/synaptic/etc to add >whatever additional packages you want ) >---(end of >broadcast)--- >TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Linux distro
Hello, I bought a Dell server and I am going to use it for installing PostgrSQL 8.2.4. I always used Windows so far and I would like now to install a Linux distribution on the new server. Any suggestion on which distribution ? Fedora, Ubuntu server, Suse or others? Thanks in advance, Paolo Saudin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How do I create a database if I can't connect to it?
Success! Here's what happened: 1. I should -not- use sudo to "make install", since my user already had the required permissions. Using sudo messed up and caused those permission errors. 2. As I mentioned before, I did install pg through the package system before. The problem is: when I removed the package, all of pg's bin files (createdb, initdb, etc) remained at /usr/bin , conflicting with the new ones at /usr/local/pgsql/bin. After removing the dupe files... [EMAIL PROTECTED]:~$ postgres -D /home/paolo/db -p 5435 -i LOG: database system was shut down at 2007-07-27 14:47:19 BRT LOG: checkpoint record is at 0/42C424 LOG: redo record is at 0/42C424; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/593; next OID: 10820 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready Terminal 2: [EMAIL PROTECTED]:~$ createdb nowItWorks -p 5435 CREATE DATABASE :D Once again thanks for the help and remember: Packaging systems may bite :o Cheers, Paolo On 7/27/07, Paolo Victor <[EMAIL PROTECTED]> wrote: > > > > On 7/27/07, Paolo Victor <[EMAIL PROTECTED]> wrote: > > > > Ok, I found the problem: Permissions. > > > > Although I -do- have write/read/exec permission /usr/local/pgsql , some > > files couldn't be copied after the install, probably rendering PG a bit > > unstable =) > > > Erm, I guess I mean "the config proccess failed" =) > > I tried installing PG in a folder in my home directory, and it worked like > > a charm. > > > > Here's the output of the "sudo make install" command, when I tried > > installing it at /usr/local/pgsql : > > > > /* > > Actually, for the sake of the internet's tubes, I'll just post the > > error (and last) lines. > > > > And for our non-portuguese-speaking friends: > > Entrando no diretório = Entering Directory > > Saindo do diretório = Leaving directory > > */ > > > > make[4]: Entrando no diretório `/home/paolo/Desktop/postgresql- 8.2.4 > > /src/interfaces/ecpg/include' > > cd ../../../.. && ./config.status > > src/interfaces/ecpg/include/ecpg_config.h > > ./config.status: line 91: conf20566.sh: Permission denied > > ./config.status: line 92: conf20566.sh : Permission denied > > chmod: cannot access `conf20566.sh': No such file or directory > > ./config.status: line 206: conf20566.file: Permission denied > > sed: couldn't close stdout: Permission denied > > cat: write error: Permission denied > > mkdir: cannot create directory `./confstat20566-20898': Permission > > denied > > : cannot create a temporary directory in . > > make[4]: ** [../../../../src/interfaces/ecpg/include/ecpg_config.h] Erro > > 1 > > make[4]: Saindo do diretório `/home/paolo/Desktop/postgresql- 8.2.4 > > /src/interfaces/ecpg/include' > > make[3]: ** [install] Erro 2 > > make[3]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4 > > /src/interfaces/ecpg' > > make[2]: ** [install] Erro 2 > > make[2]: Saindo do diretório `/home/paolo/Desktop/postgresql- 8.2.4 > > /src/interfaces' > > make[1]: ** [install] Erro 2 > > make[1]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4/src' > > make: ** [install] Erro 2 > > > > On 7/27/07, Paolo Victor <[EMAIL PROTECTED]> wrote: > > > > > > First: Thanks for all the advice! > > > > > > David: > > > > > > I tried installing the system's packages, but as I plan using the > > > python procedures feature, I've got to compile it with the "--with-python" > > > option. I'm not sure if the default package already includes the feature, > > > but since the createlang command failed and I found out on some forum > > > (sorry, I lost the link) that I had to compile PG with this option set, I > > > tried compiling PG myself. > > > > > > Oh, and I've already compiled PG before (but without the python > > > option) and had no problems. I'm trying to compile the 8.2.4 version, > > > I'll try with 8.2.3. > > > > > > Scott, Merlin: > > > > > > When I try to connect to one of the default databases, here's what I > > > get: > > > > > > [EMAIL PROTECTED]:~$ psql -d template1 -p 5435 > > > NOTICE: table "pg_class" was reindexed > > > NOTICE: table "sql_sizing" was reindexed > > > NOTICE: table "sql_sizing_profiles" was reindexed > > > NOTICE: table "sql_features" was reindexed &
Re: [GENERAL] How do I create a database if I can't connect to it?
Ok, I found the problem: Permissions. Although I -do- have write/read/exec permission /usr/local/pgsql , some files couldn't be copied after the install, probably rendering PG a bit unstable =) I tried installing PG in a folder in my home directory, and it worked like a charm. Here's the output of the "sudo make install" command, when I tried installing it at /usr/local/pgsql : /* Actually, for the sake of the internet's tubes, I'll just post the error (and last) lines. And for our non-portuguese-speaking friends: Entrando no diretório = Entering Directory Saindo do diretório = Leaving directory */ make[4]: Entrando no diretório `/home/paolo/Desktop/postgresql-8.2.4 /src/interfaces/ecpg/include' cd ../../../.. && ./config.status src/interfaces/ecpg/include/ecpg_config.h ./config.status: line 91: conf20566.sh: Permission denied ./config.status: line 92: conf20566.sh: Permission denied chmod: cannot access `conf20566.sh': No such file or directory ./config.status: line 206: conf20566.file: Permission denied sed: couldn't close stdout: Permission denied cat: write error: Permission denied mkdir: cannot create directory `./confstat20566-20898': Permission denied : cannot create a temporary directory in . make[4]: ** [../../../../src/interfaces/ecpg/include/ecpg_config.h] Erro 1 make[4]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4 /src/interfaces/ecpg/include' make[3]: ** [install] Erro 2 make[3]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4 /src/interfaces/ecpg' make[2]: ** [install] Erro 2 make[2]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4 /src/interfaces' make[1]: ** [install] Erro 2 make[1]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4/src' make: ** [install] Erro 2 On 7/27/07, Paolo Victor <[EMAIL PROTECTED]> wrote: > > First: Thanks for all the advice! > > David: > > I tried installing the system's packages, but as I plan using the python > procedures feature, I've got to compile it with the "--with-python" option. > I'm not sure if the default package already includes the feature, but since > the createlang command failed and I found out on some forum (sorry, I lost > the link) that I had to compile PG with this option set, I tried compiling > PG myself. > > Oh, and I've already compiled PG before (but without the python option) > and had no problems. I'm trying to compile the 8.2.4 version, I'll try > with 8.2.3. > > Scott, Merlin: > > When I try to connect to one of the default databases, here's what I get: > > [EMAIL PROTECTED]:~$ psql -d template1 -p 5435 > NOTICE: table "pg_class" was reindexed > NOTICE: table "sql_sizing" was reindexed > NOTICE: table "sql_sizing_profiles" was reindexed > NOTICE: table "sql_features" was reindexed > NOTICE: table "sql_implementation_info" was reindexed > NOTICE: table "sql_languages" was reindexed > NOTICE: table "sql_packages" was reindexed > NOTICE: table "sql_parts" was reindexed > NOTICE: table "pg_statistic" was reindexed > NOTICE: table "pg_type" was reindexed > NOTICE: table "pg_attribute" was reindexed > NOTICE: table "pg_proc" was reindexed > NOTICE: table "pg_autovacuum" was reindexed > NOTICE: table "pg_attrdef" was reindexed > NOTICE: table "pg_constraint" was reindexed > NOTICE: table "pg_inherits" was reindexed > NOTICE: table "pg_index" was reindexed > NOTICE: table "pg_operator" was reindexed > NOTICE: table "pg_opclass" was reindexed > NOTICE: table "pg_am" was reindexed > NOTICE: table "pg_amop" was reindexed > NOTICE: table "pg_amproc" was reindexed > NOTICE: table "pg_language" was reindexed > NOTICE: table "pg_largeobject" was reindexed > NOTICE: table "pg_aggregate" was reindexed > NOTICE: table "pg_rewrite" was reindexed > NOTICE: table "pg_trigger" was reindexed > NOTICE: table "pg_description" was reindexed > NOTICE: table "pg_cast" was reindexed > NOTICE: table "pg_namespace" was reindexed > NOTICE: table "pg_conversion" was reindexed > NOTICE: table "pg_depend" was reindexed > REINDEX > > I get the same output when I execute the createuser command. > > On 7/27/07, Merlin Moncure <[EMAIL PROTECTED] > wrote: > > > > On 7/27/07, David Fetter <[EMAIL PROTECTED] > wrote: > > > On Fri, Jul 27, 2007 at 11:28:58AM -0300, Paolo Victor wrote: > > > > Hello, > > > > > > &
Re: [GENERAL] How do I create a database if I can't connect to it?
On 7/27/07, Paolo Victor <[EMAIL PROTECTED]> wrote: > > Ok, I found the problem: Permissions. > > Although I -do- have write/read/exec permission /usr/local/pgsql , some > files couldn't be copied after the install, probably rendering PG a bit > unstable =) Erm, I guess I mean "the config proccess failed" =) I tried installing PG in a folder in my home directory, and it worked like a > charm. > > Here's the output of the "sudo make install" command, when I tried > installing it at /usr/local/pgsql : > > /* > Actually, for the sake of the internet's tubes, I'll just post the error > (and last) lines. > > And for our non-portuguese-speaking friends: > Entrando no diretório = Entering Directory > Saindo do diretório = Leaving directory > */ > > make[4]: Entrando no diretório `/home/paolo/Desktop/postgresql- 8.2.4 > /src/interfaces/ecpg/include' > cd ../../../.. && ./config.status > src/interfaces/ecpg/include/ecpg_config.h > ./config.status: line 91: conf20566.sh: Permission denied > ./config.status: line 92: conf20566.sh : Permission denied > chmod: cannot access `conf20566.sh': No such file or directory > ./config.status: line 206: conf20566.file: Permission denied > sed: couldn't close stdout: Permission denied > cat: write error: Permission denied > mkdir: cannot create directory `./confstat20566-20898': Permission denied > : cannot create a temporary directory in . > make[4]: ** [../../../../src/interfaces/ecpg/include/ecpg_config.h] Erro 1 > make[4]: Saindo do diretório `/home/paolo/Desktop/postgresql- 8.2.4 > /src/interfaces/ecpg/include' > make[3]: ** [install] Erro 2 > make[3]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4 > /src/interfaces/ecpg' > make[2]: ** [install] Erro 2 > make[2]: Saindo do diretório `/home/paolo/Desktop/postgresql- 8.2.4 > /src/interfaces' > make[1]: ** [install] Erro 2 > make[1]: Saindo do diretório `/home/paolo/Desktop/postgresql-8.2.4/src' > make: ** [install] Erro 2 > > On 7/27/07, Paolo Victor <[EMAIL PROTECTED]> wrote: > > > > First: Thanks for all the advice! > > > > David: > > > > I tried installing the system's packages, but as I plan using the python > > procedures feature, I've got to compile it with the "--with-python" option. > > I'm not sure if the default package already includes the feature, but since > > the createlang command failed and I found out on some forum (sorry, I lost > > the link) that I had to compile PG with this option set, I tried compiling > > PG myself. > > > > Oh, and I've already compiled PG before (but without the python option) > > and had no problems. I'm trying to compile the 8.2.4 version, I'll try > > with 8.2.3. > > > > Scott, Merlin: > > > > When I try to connect to one of the default databases, here's what I > > get: > > > > [EMAIL PROTECTED]:~$ psql -d template1 -p 5435 > > NOTICE: table "pg_class" was reindexed > > NOTICE: table "sql_sizing" was reindexed > > NOTICE: table "sql_sizing_profiles" was reindexed > > NOTICE: table "sql_features" was reindexed > > NOTICE: table "sql_implementation_info" was reindexed > > NOTICE: table "sql_languages" was reindexed > > NOTICE: table "sql_packages" was reindexed > > NOTICE: table "sql_parts" was reindexed > > NOTICE: table "pg_statistic" was reindexed > > NOTICE: table "pg_type" was reindexed > > NOTICE: table "pg_attribute" was reindexed > > NOTICE: table "pg_proc" was reindexed > > NOTICE: table "pg_autovacuum" was reindexed > > NOTICE: table "pg_attrdef" was reindexed > > NOTICE: table "pg_constraint" was reindexed > > NOTICE: table "pg_inherits" was reindexed > > NOTICE: table "pg_index" was reindexed > > NOTICE: table "pg_operator" was reindexed > > NOTICE: table "pg_opclass" was reindexed > > NOTICE: table "pg_am" was reindexed > > NOTICE: table "pg_amop" was reindexed > > NOTICE: table "pg_amproc" was reindexed > > NOTICE: table "pg_language" was reindexed > > NOTICE: table "pg_largeobject" was reindexed > > NOTICE: table "pg_aggregate" was reindexed > > NOTICE: table "pg_rewrite" was reindexed > > NOTICE: table "pg_trigger" was reindexed > > NOTICE: table "pg_description&quo
Re: [GENERAL] How do I create a database if I can't connect to it?
First: Thanks for all the advice! David: I tried installing the system's packages, but as I plan using the python procedures feature, I've got to compile it with the "--with-python" option. I'm not sure if the default package already includes the feature, but since the createlang command failed and I found out on some forum (sorry, I lost the link) that I had to compile PG with this option set, I tried compiling PG myself. Oh, and I've already compiled PG before (but without the python option) and had no problems. I'm trying to compile the 8.2.4 version, I'll try with 8.2.3. Scott, Merlin: When I try to connect to one of the default databases, here's what I get: [EMAIL PROTECTED]:~$ psql -d template1 -p 5435 NOTICE: table "pg_class" was reindexed NOTICE: table "sql_sizing" was reindexed NOTICE: table "sql_sizing_profiles" was reindexed NOTICE: table "sql_features" was reindexed NOTICE: table "sql_implementation_info" was reindexed NOTICE: table "sql_languages" was reindexed NOTICE: table "sql_packages" was reindexed NOTICE: table "sql_parts" was reindexed NOTICE: table "pg_statistic" was reindexed NOTICE: table "pg_type" was reindexed NOTICE: table "pg_attribute" was reindexed NOTICE: table "pg_proc" was reindexed NOTICE: table "pg_autovacuum" was reindexed NOTICE: table "pg_attrdef" was reindexed NOTICE: table "pg_constraint" was reindexed NOTICE: table "pg_inherits" was reindexed NOTICE: table "pg_index" was reindexed NOTICE: table "pg_operator" was reindexed NOTICE: table "pg_opclass" was reindexed NOTICE: table "pg_am" was reindexed NOTICE: table "pg_amop" was reindexed NOTICE: table "pg_amproc" was reindexed NOTICE: table "pg_language" was reindexed NOTICE: table "pg_largeobject" was reindexed NOTICE: table "pg_aggregate" was reindexed NOTICE: table "pg_rewrite" was reindexed NOTICE: table "pg_trigger" was reindexed NOTICE: table "pg_description" was reindexed NOTICE: table "pg_cast" was reindexed NOTICE: table "pg_namespace" was reindexed NOTICE: table "pg_conversion" was reindexed NOTICE: table "pg_depend" was reindexed REINDEX I get the same output when I execute the createuser command. On 7/27/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > On 7/27/07, David Fetter <[EMAIL PROTECTED]> wrote: > > On Fri, Jul 27, 2007 at 11:28:58AM -0300, Paolo Victor wrote: > > > Hello, > > > > > > For a short description, I'll just show the sequence of commands I'm > trying > > > to execute: > > > > > > [EMAIL PROTECTED]> initdb -D /usr/local/pgsql/data/ > > > > This looks like a mistake. Unless you plan to develop the PostgreSQL > > code itself, you should not be installing from source. Instead, you > > should be using one from the packaging system your operating system > > uses. > > I don't necessarily agree with this advice. I encourage everyone who > is considering serious development with PostgreSQL to become familiar > with the database architecture...there are advantages to compiling > from source yourself if you know what you are doing, and simply > learning how to do it is a good exercise. > > I am also very suspicious of the assertion that knowledge of how to > manually invoke initdb is not necessary. While the binary vs source > argument certainly debatable, I would certainly advise every > PostgreSQL dba to memorize the initdb man page for various reasons. > However, there are few reasons to run postgres directly, we normally > rely on pg_ctl for that (but it's still useful to know it can be > done). > > anyways, to the OP, you need to connect to one of the default > databases (postgres, or template1) and create one from there...or > invoke the createdb command. > > merlin >
[GENERAL] How do I create a database if I can't connect to it?
Hello, For a short description, I'll just show the sequence of commands I'm trying to execute: [EMAIL PROTECTED]> initdb -D /usr/local/pgsql/data/ [EMAIL PROTECTED]> postgres -D /usr/local/pgsql/data -i -p 5435 [EMAIL PROTECTED]> createdb foo -p 5435 createdb: could not connect to database foo: FATAL: database "foo" does not exist And "postgres -D /usr/local/pgsql/data -i -p 5435"'s output: /* LOG: database system was shut down at 2007-07-27 11:25:27 BRT LOG: checkpoint record is at 0/42C4B4 LOG: redo record is at 0/42C4B4; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/593; next OID: 10820 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready FATAL: database "foo" does not exist */ Now, am I doing something terribly wrong/noobish, or Postgres is freaking out because I want to create a database that does not exist? Thanks in advance, ~Paolo
Re: [GENERAL] Corruption of files in PostgreSQL
On 6/5/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: Greg Smith wrote: > On Tue, 5 Jun 2007, Paolo Bizzarri wrote: > >> On 6/4/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: >>> http://lwn.net/Articles/215868/ >>> documents a bug in the 2.6 linux kernel that can result in corrupted >>> files if there are a lot of processes accessing it at once. >> >> in fact, we were using a 2.6.12 kernel. Can this be a problem? > > That particular problem appears to be specific to newer kernels so I > wouldn't think it's related to your issue. That is not entirely correct. The problem was present all the way back to the 2.5 kernels, before the 2.6 kernels were released. However, there was an update to the 2.6.18/19 kernels that made this problem much more likely to bite. There were reports of data loss for many people running on older 2.6 kernels that mysteriously went away after updating to post 2.6.19 kernels (or in the case of redhat, the updated 2.6.9-44 or so kernels, which backported the fix.) I understand this. At the same time, the system was under quite heavy load, so it is possible that some peculiar, rather subtle bug was biting us. There were many files manipulated all in the same way, but only some (really little of them) were truncated. I would like to remove all possible known cases of bugs. BTW, as ou Postgresql was recompiled from sources, do you suggest to recompile the whole after upgrading the kernel? So, it IS possible that it's the kernel, but not likely. I'm still betting on a bad RAID controller or something like that. But updating the kernel probably wouldn't be a bad idea. The deployed configuration is quite large (two servers using a shared SCSI-to-IDE large disk array), and it would be quite difficult to replicate a different configuration. At the same time, problems were visible only under heavy load, so using a simpler system would not really help. Ciao Paolo Bizzarri Icube S.r.l. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Corruption of files in PostgreSQL
Hi Scott, in fact, we were using a 2.6.12 kernel. Can this be a problem? Best regards. Paolo Bizzarri On 6/4/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: Paolo Bizzarri wrote: > On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> "Paolo Bizzarri" <[EMAIL PROTECTED]> writes: >> > On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> >> Please provide a reproducible test case ... >> >> > as explained above, the problem seems quite random. So I need to >> > understand what we have to check. >> >> In this context "reproducible" means that the failure happens >> eventually. I don't care if the test program only fails once in >> thousands of tries --- I just want a complete self-contained example >> that produces a failure. > > As said above, our application is rather complex and involves several > different pieces of software, including Zope, OpenOffice both as > server and client, and PostgreSQL. We are absolutely NOT sure that the > problem is inside PostgreSQL. > > What we are trying to understand is, first and foremost, if there are > known cases under which PostgreSQL can truncate a file. I would suspect either your hardware (RAID controller, hard drive, cache etc) or your OS (kernel bug, file system bug, etc) For instance: http://lwn.net/Articles/215868/ documents a bug in the 2.6 linux kernel that can result in corrupted files if there are a lot of processes accessing it at once. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Corruption of files in PostgreSQL
On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Paolo Bizzarri" <[EMAIL PROTECTED]> writes: > On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> Please provide a reproducible test case ... > as explained above, the problem seems quite random. So I need to > understand what we have to check. In this context "reproducible" means that the failure happens eventually. I don't care if the test program only fails once in thousands of tries --- I just want a complete self-contained example that produces a failure. As said above, our application is rather complex and involves several different pieces of software, including Zope, OpenOffice both as server and client, and PostgreSQL. We are absolutely NOT sure that the problem is inside PostgreSQL. What we are trying to understand is, first and foremost, if there are known cases under which PostgreSQL can truncate a file. I don't have the time to try to reverse-engineer a test case from your rather vague description, whereas I suppose you can make one by stripping down code you've already got. I was not asking for a reverse engineering of a test case. I will try to provide an example, but the problem is, without knowing what to see, that I could omit fundamental details. The sub-text here is that I don't really believe that lo_import and lo_export in themselves are broken. There must be some extra factor --- something else you are doing, or something in your environment --- contributing to the bug. I certainly agree with you. I was asking what to see and what to check. Thus, the odds of someone else building a usable test case from scratch aren't that good, and being able to reproduce the failure outside your environment is an essential step. I agree with you. I was not hoping for this. At the same time, I was asking an help for what to see, so that I can reproduce a test case. As an alternate, I can suggest to download and install PAFlow, but I understand it is a rather large application Best regards. Paolo Bizzarri Icube S.r.l. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Corruption of files in PostgreSQL
Hi Tom, as explained above, the problem seems quite random. So I need to understand what we have to check. Best regards. Paolo Bizzarri Icube S.r.l. On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Paolo Bizzarri" <[EMAIL PROTECTED]> writes: > Any hint? Please provide a reproducible test case ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Corruption of files in PostgreSQL
Hi everyone, a little update. We have upgraded our system to 7.4.17. The problem of truncated files seems now better, but it is still present. We have not found a clearly understandable pattern on why this happens. Just to provide some further information: - we create a file and store on the DB; - we give the file to the user, and he can modify at its wish the file; - we store back the modified file on the DB; - the last two points can happen several times. Any hint? Best regards. Paolo Bizzarri Icube S.r.l. On 5/30/07, Purusothaman A <[EMAIL PROTECTED]> wrote: Paolo Bizzarri, I am also using postgresql in my application and also facing file object corruption problem. I already discussed several times with Richard Huxton, and ended without any clue. Here I am briefing my problem, see if u find any clue about it. I am storing/retrieving my file in postgresql using lo_export() and lo_import() api. after few weeks (as application is being used - number of file objects in database also grows) my file object gets corrupted. And I have no clue about which causes this problem. I confirmed the file corruption by the following query, sfrs2=> select loid, pageno, length(data) from pg_largeobject where loid = 101177 and pageno = 630; loid | pageno | length ++ 101177 |630 |181 (1 row) But actually the result of the above query before corruption(ie, immediately after file object added to table) fasp_test=> select loid, pageno, length(data) from pg_largeobject where loid = 106310 and pageno = 630; loid | pageno | length ++ 106310 |630 |205 (1 row) I uploaded same file in both(sfrs2, fasp_test) databases. The first one result is after the corruption. and the later is before corruption. You also confirm you problem like this. And I strongly believe that, there is some bug in PostgreSQL. Kindly don't forget to alert me once u find solution/cause. Regards, Purusothaman A On 5/30/07, Paolo Bizzarri <[EMAIL PROTECTED]> wrote: > > On 5/30/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Paolo Bizzarri wrote: > > > We use postgres as a backend, and we are experimenting some corruption > > > problems on openoffice files. > > > > 1. How are you storing these files? > > Files are stored as large objects. They are written with an lo_write > and its contents is passed as a Binary object. > > > 2. What is the nature of the corruption? > > Apparently, files get truncated. > > > > As our application is rather complex (it includes Zope as an > > > application server, OpenOffice as a document server and as a client) > > > we need some info on how to check that we are interacting correctly > > > with Postgres. > > > > Shouldn't matter. > > I hope so... > > > > We are currently using: > > > > > > - PostgreSQL 7.4.8; > > > > Well, you need to upgrade this - version 7.4.17 is the latest in the 7.4 > > series. You are missing 9 separate batches of bug and security fixes. > > Ok. We will upgrade and see if this can help solve the problem. > > > > > > - pyscopg 1.1.11 ; > > > - Zope 2.7.x; > > > - Openoffice 2.2. > > > > None of this should matter really, unless there's some subtle bug in > > psycopg causing corruption of data in-transit. > > > > Let's get some details on the two questions above and see if there's a > > pattern to your problems. > > Ok. Thank you. > > Paolo Bizzarri > Icube S.r.l. > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- http://PurusothamanA.wordpress.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question about corruption about openoffice file
On 5/30/07, Matthew T. O'Connor <[EMAIL PROTECTED]> wrote: Paolo Bizzarri wrote: > my name is Paolo Bizzarri and I am a developer of PAFlow, an document > tracking and management system for public administrations. > > We use postgres as a backend, and we are experimenting some corruption > problems on openoffice files. > > As our application is rather complex (it includes Zope as an > application server, OpenOffice as a document server and as a client) > we need some info on how to check that we are interacting correctly > with Postgres. > > Do you have any hints on how what is useful to check/see? I think we need more information. I assume you are storing the OOo files in PostgreSQL? Yes. OOo files are stored as large objects inside PostgreSQL. Are the documents themselves getting corrupted? They seems to get truncated. All of them only some of them? Only some of them. There is no clear pattern on why this is happening. How are you storing them etc? etc.. Can you explain me what you mean? > We are currently using: > > - PostgreSQL 7.4.8; > - pyscopg 1.1.11; > - Zope 2.7.x; > - Openoffice 2.2. 7.4.8 is very old at this point, any reason not to be using 8.2? Or at least something newer? This is a production system, where we have a large number of users of a critical application. We have experienced bugs in pratically every piece of software we have used. We are using a policy of "upgrade only if needed, and only after you are sure that nothing is going to break". We are definitively going to upgrade to 7.4.17. For the moment, we are not considering more recent versions. Thank you Ciao Paolo Bizzarri Icube S.r.l. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Question about corruption about openoffice file
Hi everyone, my name is Paolo Bizzarri and I am a developer of PAFlow, an document tracking and management system for public administrations. We use postgres as a backend, and we are experimenting some corruption problems on openoffice files. As our application is rather complex (it includes Zope as an application server, OpenOffice as a document server and as a client) we need some info on how to check that we are interacting correctly with Postgres. Do you have any hints on how what is useful to check/see? We are currently using: - PostgreSQL 7.4.8; - pyscopg 1.1.11; - Zope 2.7.x; - Openoffice 2.2. Best regards. Paolo Bizzarri Icube S.r.l. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Corruption of files in PostgreSQL
On 5/30/07, Richard Huxton <[EMAIL PROTECTED]> wrote: Paolo Bizzarri wrote: > We use postgres as a backend, and we are experimenting some corruption > problems on openoffice files. 1. How are you storing these files? Files are stored as large objects. They are written with an lo_write and its contents is passed as a Binary object. 2. What is the nature of the corruption? Apparently, files get truncated. > As our application is rather complex (it includes Zope as an > application server, OpenOffice as a document server and as a client) > we need some info on how to check that we are interacting correctly > with Postgres. Shouldn't matter. I hope so... > We are currently using: > > - PostgreSQL 7.4.8; Well, you need to upgrade this - version 7.4.17 is the latest in the 7.4 series. You are missing 9 separate batches of bug and security fixes. Ok. We will upgrade and see if this can help solve the problem. > - pyscopg 1.1.11; > - Zope 2.7.x; > - Openoffice 2.2. None of this should matter really, unless there's some subtle bug in psycopg causing corruption of data in-transit. Let's get some details on the two questions above and see if there's a pattern to your problems. Ok. Thank you. Paolo Bizzarri Icube S.r.l. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Corruption of files in PostgreSQL
Hi everyone, my name is Paolo Bizzarri and I am a developer of PAFlow, an document tracking and management system for public administrations. We use postgres as a backend, and we are experimenting some corruption problems on openoffice files. As our application is rather complex (it includes Zope as an application server, OpenOffice as a document server and as a client) we need some info on how to check that we are interacting correctly with Postgres. Do you have any hints on how what is useful to check/see? We are currently using: - PostgreSQL 7.4.8; - pyscopg 1.1.11; - Zope 2.7.x; - Openoffice 2.2. Best regards. Paolo Bizzarri ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Get the exeption error description
Hi, Is there a way to know the error description / message in a BEGIN END block in a plpgsql functioin as in the 'Appendix A. PostgreSQL Error Codes' ? I would like to pass it back to the function caller DECLARE the_error varchar; BEGIN .. ... ... .. ... ... EXCEPTION WHEN OTHERS THEN the_error := ERROR DESCRIPTION; ? END; Thanks, Paolo
R: [GENERAL] Postgres 8.3-dev
Thanks for the suggestions, the directory is removed automatically by the installer, and I looked in the registry for any PostgreSQL 8.3 related key with no success, there are lot of keys from previous versions but no one about 8.3-dev. on the VMware XP Home clean machine there is nothing but the OS installed. Ill keep trying !! Thanks anyway, Paolo Da: Prashant Ranjalkar [mailto:[EMAIL PROTECTED] Inviato: lunedì 7 maggio 2007 12.06 A: Paolo Saudin Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Postgres 8.3-dev Hello, Remove Postgres directory from the your drive and also entries from Registry. Reboot and try regards Prashant Ranjalkar On 5/2/07, Paolo Saudin <[EMAIL PROTECTED]> wrote: I am trying out postgresql-8.3-dev1 on Windows XP SP2 and during the installation, I get the following error during the database cluster initialization : Here the log from initdb.log file : The files belonging to this database system will be owned by user "SYSTEM". This user must also own the server process. The database cluster will be initialized with locale C. creating directory C:/Programmi/PostgreSQL/8.3-dev1/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in C:/Programmi/PostgreSQL/8.3-dev1/data/base/1 ... ok initializing pg_authid ... ok setting password ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... child process exited with exit code 1 initdb: removing data directory "C:/Programmi/PostgreSQL/8.3-dev1/data" Paolo Saudin
R: R: [GENERAL] Postgres 8.3-dev
I tried to set that policy to all available settings [activate, not activate, not set] but the result does not change, the installer stops with the same error. Here below elevated privileges set for Windows installer I then tried the same things on a true Windows XP Professional SP2 (not virtual machine) trying to set the above settings and the results were the same. I added the postgres user to Administrators group, but doesnt work either. On that machine there is a 8.2.4 version running Paolo -Messaggio originale- Da: Dave Page [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 maggio 2007 23.04 A: Magnus Hagander Cc: Paolo Saudin; pgsql-general@postgresql.org Oggetto: Re: R: [GENERAL] Postgres 8.3-dev Magnus Hagander wrote: > Yeah. But look at the part about SYSTEM being the owner, I wonder if that's > related. Hmm, that is odd. iirc, there is a Windows policy option that tells the installer to always run with elevated privileges. Do you know if that effectively runs installers as SYSTEM, or does it elevate the privileges of the current user somehow? Paolo; could that option be set manually, or though group policy on your system? Regards, Dave. <>
R: R: [GENERAL] Postgres 8.3-dev
> There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using > EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... Sorry, the version is 8.2.4, the latest available on the PostgreSQL web site and I am using only PostgreSQL. > Is this both for the service account and the superuser account? Does this > accoutn already exist, or is the installer creating it? I installed both versions with the following settings : service account: postgres with password postmaster superuser account : postgres with password postgres When I install the 8.3-dev version on a clean machine, the installer creates the account When I install the 8.3-dev version after installing the 8.2.4, the installer uses the already created account (postgres with password postmaster ) HERE IS THE LOG FROM THE WINDOWS SYSTEM EVENT LOG Tipo evento:Errore Origine evento: PostgreSQL Categoria evento: Nessuno ID evento: 0 Data: 04/05/2007 Ora:10.57.14 Utente: N/D Computer: XPHOME Descrizione: Impossibile trovare la descrizione dell'ID evento ( 0 ) nell'origine ( PostgreSQL ). Il computer locale potrebbe non disporre delle necessarie informazioni nel Registro di sistema o dei file DLL necessari per visualizzare messaggi da un computer remoto. Utilizzare /AUXSOURCE= flag per recuperare la descrizione. Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico. Le seguenti informazioni sono parte dell'evento: FATAL: could not load library "C:/Programmi/PostgreSQL/8.3-dev1/lib/ascii_and_mic.dll": unknown error 14001 STATEMENT: CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT; . Paolo -Messaggio originale- Da: Magnus Hagander [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 maggio 2007 9.39 A: Paolo Saudin Cc: pgsql-general@postgresql.org Oggetto: Re: R: [GENERAL] Postgres 8.3-dev On Fri, May 04, 2007 at 09:00:32AM +0200, Paolo Saudin wrote: > I am trying to install the 8.3-dev version on a Vmware virtual machine with > WinXP SP2. I am able to install the 8.2.4.1 version with no problem using > the very same settings for both servers as follow: There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... > SETTINGS : > Account name postgres with password postmaster Is this both for the service account and the superuser account? Does this accoutn already exist, or is the installer creating it? > I then reset the virtual machine and installed the 8.2 with no problem. At > that point I tried to install the 8.3-dev with the account created by the > 8.2 installation and I end up the same error. Any ideas on this Dave? //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
R: [GENERAL] Postgres 8.3-dev
I am trying to install the 8.3-dev version on a Vmware virtual machine with WinXP SP2. I am able to install the 8.2.4.1 version with no problem using the very same settings for both servers as follow: SETTINGS : Account name postgres with password postmaster Accept connections on all addresses, not just localhost Install Adminpack During 8.3-dev installation I got through the usual error, and there are no previous warning during the installation process. MSG : Failed to run initdb: 1! Please see the logfile in 'C:\...\...\initdb.log'. Note! You must read . LOG FILE : The same as the other email I then reset the virtual machine and installed the 8.2 with no problem. At that point I tried to install the 8.3-dev with the account created by the 8.2 installation and I end up the same error. Paolo -Messaggio originale- Da: Magnus Hagander [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 maggio 2007 7.58 A: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Postgres 8.3-dev > I am trying out postgresql-8.3-dev1 on Windows XP SP2 and during the installation, I get the following error during the database cluster initialization : This looks pretty interesting. Are you specifying an existing account for the service or are you letting the installer create one? Any warnings or other messages earlier in the install process? Are you able to install 8.2 on the same machine with the same options and account? /Magnus > The files belonging to this database system will be owned by user "SYSTEM". ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postgres 8.3-dev
I am trying out postgresql-8.3-dev1 on Windows XP SP2 and during the installation, I get the following error during the database cluster initialization : Here the log from initdb.log file : The files belonging to this database system will be owned by user "SYSTEM". This user must also own the server process. The database cluster will be initialized with locale C. creating directory C:/Programmi/PostgreSQL/8.3-dev1/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in C:/Programmi/PostgreSQL/8.3-dev1/data/base/1 ... ok initializing pg_authid ... ok setting password ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... child process exited with exit code 1 initdb: removing data directory "C:/Programmi/PostgreSQL/8.3-dev1/data" Paolo Saudin <>
[GENERAL] ALTER column TYPE varying question
I need to increase the length of a string field using version 8.1 I was thinking to use ALTER TABLE since now altering a column type should be supported by pg. The column is currently varying(60) and I want to have it varying(120) After executing ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(120) I can see the column definition correctly changes and I can insert rows with longer data in mycolumn. But when I try to update data which were in the table before the ALTER TABLE I get "ERROR: value too long for type character varying(60)" It's like the old rows didn't update correctly keeping the old maximum length. I can see there's an optional USING clause for the ALTER TYPE, but is not really clear to me what should i add in this case, since basically I'd need no convertion... Thanks Paolo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Cumulative aggregate
Hi all, I want to write an aggregate to sum the values for rain precipitations. I found a working example with integer values, but I cannot find a way to to the same with float ones. Here is what I did : -- table testcreate table mytest (fld1 int4, fld2 float4);insert into mytest values (1, 1.23);insert into mytest values (6, 5.38);insert into mytest values (2, 0.12); -- function for integer (OK)create function myint4_sum(int4,int4) returns int4as 'select int4_sum($1,$2)::int4;'language SQL;-- aggregatecreate aggregate myagg1_sum (basetype = int4, sfunc = myint4_sum, stype = int4);-- resultselect myagg1_sum(fld1) from mytest; --> give 9 -- function for float (NOT WORKING)create function myfloat4_sum(float4,float4) returns float4as 'select float4pl($1,$2)::float4;'language SQL;-- aggregatecreate aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype = float4);-- resultselect myagg2_sum(fld2) from mytest; --> NOTHING ??? INSTEAD OF 6.73 What am I doing wrong ?? Thank you very much, Paolo Saudin Italy
Re: [GENERAL] unattened dump
Martijn van Oosterhout scrisse in data 04/13/06 10:47: On Thu, Apr 13, 2006 at 10:35:55AM +0200, Paolo Sala wrote: Hi all I am a newbe in postgres and I'm trying to obtain an unattended dump to pgsql. I mean, I've tryed to use pg_dump using the -U flag but I havn't found a way to pass a password in a unattended way. So I have to guess the only solution is to configure pg_hba.conf to use ident as authentication method? Well, there's the .pgpass file. However, I usually find the best method is to connect using unix domain sockets using ident (which isn't really ident, it uses the OS to verify the user) from the database user (postgres). Set this up in a cronjob and you're all set. Thank you very much Martjin I was in doubt using your procedure because I have phppgadmin installed on the same server and configured to use unix domain sockets. But now I have disabled the use of postgres user in phppgadmin so I hope postgres is secure... Thank you very much Piviul ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] unattened dump
Hi all I am a newbe in postgres and I'm trying to obtain an unattended dump to pgsql. I mean, I've tryed to use pg_dump using the -U flag but I havn't found a way to pass a password in a unattended way. So I have to guess the only solution is to configure pg_hba.conf to use ident as authentication method? Thank you very much Piviul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] help
karthick muthu scrisse in data 04/06/06 07:54: hello hai, I am new to linux,now i have a job to maintain database using postgre in debian,so i want to know the completebasic details about how to use this. thanking you There are very useful manual online: http://www.postgresql.org/docs/ Have a nice day Piviul ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inheritance
Richard Broersma Jr scrisse in data 03/28/06 15:18: No it isn't. But I remember reading on one of the lists that it was on the to-do list for 8.2. However, I do not know how high it is on the list of things to do. So I imagine that there is the potential that it might not be added. I'll wait 8.2 or 8.3. Thank you very much. Piviul ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Inheritance
Jebus scrisse in data 03/27/06 19:03: I could be wrong but primary keys and other constraints are not inherited. Thank you very much Jebus; in effect I've found in the mailing list archives a 2003 thread "INHERITS and Foreign keys" that claim the same problem. Someone (Stephan Szabo) answered saying that "At some point in the future, that's likely to change" (http://archives.postgresql.org/pgsql-sql/2003-12/msg00101.php). Now I'm using postgres 7.4.7 and I've found the same problem; do you know if this problem is solved in postgres 8.1? Thank you very much Piviul ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Inheritance
Hi, I'm new on postgres and I've just installed postgres 7.4.7 on a
debian sarge. I'm interested on using inheritance. I've tried a simple code:
CREATE TABLE t_main (
id serial primary key
);
CREATE TABLE t_derived1 (
field1 varchar(128)default NULL
) INHERITS (t_main);
Now I have to create another table having a field having a reference to
t_derived1. If I use the code
CREATE TABLE t_table1 (
id serial primary key,
id_derived1 int references t_derived1
);
I got an error: t_derived1 have no primary key... and in effect is
t_main that have the primary key... So I modified the code in
CREATE TABLE t_table1 (
id serial primary key,
id_derived1 int references t_main
);
and now all seems to work so I inserted a record on t_derived1
INSERT INTO t_derived1
(field1) VALUES ('field1 content of derived1 table');
and a record in t_table1 that have a reference to the record I've just
inserted:
INSERT INTO t_table1
(id_derived1) VALUES (1);
but I've got the error 'ERROR: insert or update on table "t_table1"
violates foreign key constraint "$1"
DETAIL: Key (id_derived1)=(1) is not present in table "t_main".'
So I ask you: there is a way to reference a record to an hinherited table?
Thank you very much
Piviul
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] help
Hi. I would want do a php script to access my postgres database from remote host. I would like to know how I can do. Particularly, I would to know the default directory where my database is located so that to be able to call it with my php script. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Hash join operator question
I'd like to understand if it is possible to find a
solution to the problem that we have on ours DB in production.
I make an example simplified in order to explain
itself better:
We have 2 table :
TABLE vendor ( group TEXT, client
TEXT, vdr_venue_code CHAR(8), vdr_location_code
CHAR(8))
TABLE venue ( title TEXT, date
timestamp, ... code CHAR(8), location
CHAR(8))
For being able to couple some tuples of vendor with all the tuple of
venue that are inserted from other systems ,we have used a default character
"*",so that whichever is venue.code it comes coupled to the vendor tuple
thet have vdr_venue_code = "*".
For this reason base query base will be:
SELECT group FRON vendor,venue WHERE vdr_venue_code in (venue.code,"*") and
vdr_location_code in (venue.location,"*") and venue.data < and
;
This type of query does not allow planner to use HASH JOIN
slowing down the query for great amounts of data.Therefore we have tried to
create an operator and a function that they supported the hash and they resolved
this case:
strcmp_left_default(PG_FUNCTION_ARGS){
text *str = PG_GETARG_TEXT_P(0);
text *cmp = PG_GETARG_TEXT_P(1); char
*my_str = NULL; char *my_cmp = NULL;
bool
result;
textInChar(&my_str,str);
textInChar(&my_cmp,cmp);
result = (strcmp(my_str, my_cmp) == 0 ||
strcmp(my_str,
"*") == 0);
if (my_str != NULL) pfree(my_str);
if (my_cmp != NULL) pfree(my_cmp);
PG_FREE_IF_COPY(str, 0); PG_FREE_IF_COPY(cmp, 1);
PG_RETURN_BOOL(result);}
CREATE OPERATOR ==* ( PROCEDURE =
strcmp_left_default, LEFTARG = text,
RIGHTARG = text, COMMUTATOR =
OPERATOR(*==), HASHES, RESTRICT =
eqsel, JOIN = eqjoinsel, SORT1
= <, SORT2 = <, LTCMP =
<, GTCMP = >);
CREATE OPERATOR *== ( PROCEDURE =
strcmp_right_default, LEFTARG =
text, RIGHTARG = text, COMMUTATOR =
OPERATOR(==*), HASHES, RESTRICT =
eqsel, JOIN = eqjoinsel, SORT1 =
<, SORT2 = <, LTCMP =
<, GTCMP = >);
CREATE OPERATOR CLASS text_default_ops FOR TYPE
text USING btree AS OPERATOR 3 ==*(text,text)
, FUNCTION 1 bttextcmp(text,text);
CREATE OPERATOR CLASS text_default_ops FOR TYPE
text USING hash AS OPERATOR 1 ==*(text,text)
, FUNCTION 1 hashtext(text);
For being able to have query of the type :
SELECT group FROM vendor,venue WHERE vendor.vdr_venue_code ==* venue.code
and vendor.vdr_location_code ==* venue.location and ...;
Effectively it comes used the hash join clause,but this cannot be the
solution,because my operator come used after the creation of the bucketsof
the hash, so bucket that do not have correspondence on the key
(vdr_venue_code,code) does not come considers, even if has default value
"*".
The situation does not change also using one our various function,
different from hashtext, for the creation of the hashtable,in fact I cannot
force the comparison between a value of venue.code with a different bucket value
where the default value "*" resides.
My feeling is that having to only confront the value key venue_code
with a variable value and a constant value "*",it can be possible to create
an operator that it manages this type of query using a hashjoin clause.Is it
possible??
[GENERAL] Hash join operator question
I'd like to understand if it is possible to find a solution to the problem
that we have on ours DB in production.
I make an example simplified in order to explain itself better:
We have 2 table :
TABLE vendor (
group TEXT,
client TEXT,
vdr_venue_code CHAR(8),
vdr_location_code CHAR(8)
)
TABLE venue (
title TEXT,
date timestamp,
...
code CHAR(8),
location CHAR(8)
)
For being able to couple some tuples of vendor with all the tuple of venue
that are inserted from other systems ,we have used a default character "*",
so that whichever is venue.code it comes coupled to the vendor tuple thet have
vdr_venue_code = "*".
For this reason base query base will be:
SELECT group FRON vendor,venue WHERE vdr_venue_code in (venue.code,"*") and
vdr_location_code in (venue.location,"*") and venue.data < and ;
This type of query does not allow planner to use HASH JOIN slowing down the
query for great amounts of data.
Therefore we have tried to create an operator and a function that they
supported the hash and they resolved this case:
strcmp_left_default(PG_FUNCTION_ARGS)
{
text *str = PG_GETARG_TEXT_P(0);
text *cmp = PG_GETARG_TEXT_P(1);
char *my_str = NULL;
char *my_cmp = NULL;
boolresult;
textInChar(&my_str,str);
textInChar(&my_cmp,cmp);
result = (strcmp(my_str, my_cmp) == 0 ||
strcmp(my_str, "*") == 0);
if (my_str != NULL)
pfree(my_str);
if (my_cmp != NULL)
pfree(my_cmp);
PG_FREE_IF_COPY(str, 0);
PG_FREE_IF_COPY(cmp, 1);
PG_RETURN_BOOL(result);
}
CREATE OPERATOR ==* (
PROCEDURE = strcmp_left_default,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = OPERATOR(*==),
HASHES,
RESTRICT = eqsel,
JOIN = eqjoinsel,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >
);
CREATE OPERATOR *== (
PROCEDURE = strcmp_right_default,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = OPERATOR(==*),
HASHES,
RESTRICT = eqsel,
JOIN = eqjoinsel,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >
);
CREATE OPERATOR CLASS text_default_ops
FOR TYPE text USING btree AS
OPERATOR 3 ==*(text,text) ,
FUNCTION 1 bttextcmp(text,text);
CREATE OPERATOR CLASS text_default_ops
FOR TYPE text USING hash AS
OPERATOR 1 ==*(text,text) ,
FUNCTION 1 hashtext(text);
For being able to have query of the type :
SELECT group FROM vendor,venue WHERE vendor.vdr_venue_code ==* venue.code and
vendor.vdr_location_code ==* venue.location and ...;
Effectively it comes used the hash join clause,but this cannot be the
solution,because my operator come used after the creation of the buckets
of the hash, so bucket that do not have correspondence on the key
(vdr_venue_code,code) does not come considers, even if has default value "*".
The situation does not change also using one our various function, different
from hashtext, for the creation of the hashtable,
in fact I cannot force the comparison between a value of venue.code with a
different bucket value where the default value "*" resides.
My feeling is that having to only confront the value key venue_code with a
variable value and a constant value "*",
it can be possible to create an operator that it manages this type of query
using a hashjoin clause.
Is it possible??
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] slow query if add order by
I have 2 query that differ only for order by clause.
The time of execution of the two query is a lot of different.
1) explain analyze
select
tkstore.gruppo,tkstore.cassa,enabledcodes.sala,spettacoli.code
from
tkstore,enabledcodes,spettacoli
where
tkstore.id = enabledcodes.tkstore_id and
tkstore.gruppo in ('amit') and
enabledcodes.sala = spettacoli.teatro and
spettacoli.system = 0 ;
The explain is :
Hash Join (cost=173.06..3810.20 rows=115782 width=42) (actual
time=16.248..1265.331 rows=380736 loops=1)
Hash Cond: ("outer".teatro = "inner".sala)
-> Seq Scan on spettacoli (cost=0.00..1342.35 rows=23935 width=24)
(actual time=0.012..35.999 rows=26846 loops=1)
Filter: (system = 0)
-> Hash (cost=168.28..168.28 rows=1913 width=31) (actual
time=15.995..15.995 rows=0 loops=1)
-> Hash Join (cost=4.47..168.28 rows=1913 width=31) (actual
time=1.021..12.693 rows=5076 loops=1)
Hash Cond: ("outer".tkstore_id = "inner".id)
-> Seq Scan on enabledcodes (cost=0.00..113.45 rows=6245
width=16) (actual time=0.007..3.439 rows=6245 loops=1)
-> Hash (cost=4.39..4.39 rows=34 width=23) (actual
time=0.213..0.213 rows=0 loops=1)
-> Seq Scan on tkstore (cost=0.00..4.39 rows=34
width=23) (actual time=0.024..0.187 rows=33 loops=1)
Filter: ((gruppo)::text = 'amit'::text)
Total runtime: 1330.843 ms
2) explain analyze
select
tkstore.gruppo,tkstore.cassa,enabledcodes.sala,spettacoli.code
from
tkstore,enabledcodes,spettacoli
where
tkstore.id = enabledcodes.tkstore_id and
tkstore.gruppo in ('amit') and
enabledcodes.sala = spettacoli.teatro and
spettacoli.system = 0
order by 2;
The explain is :
Sort (cost=13548.08..13837.53 rows=115782 width=42) (actual
time=10631.389..10774.964 rows=380736 loops=1)
Sort Key: tkstore.cassa
-> Hash Join (cost=173.06..3810.20 rows=115782 width=42) (actual
time=16.227..1392.206 rows=380736 loops=1)
Hash Cond: ("outer".teatro = "inner".sala)
-> Seq Scan on spettacoli (cost=0.00..1342.35 rows=23935 width=24)
(actual time=0.011..47.329 rows=26846 loops=1)
Filter: (system = 0)
-> Hash (cost=168.28..168.28 rows=1913 width=31) (actual
time=16.018..16.018 rows=0 loops=1)
-> Hash Join (cost=4.47..168.28 rows=1913 width=31) (actual
time=1.023..12.680 rows=5076 loops=1)
Hash Cond: ("outer".tkstore_id = "inner".id)
-> Seq Scan on enabledcodes (cost=0.00..113.45
rows=6245 width=16) (actual time=0.008..3.469 rows=6245 loops=1)
-> Hash (cost=4.39..4.39 rows=34 width=23) (actual
time=0.214..0.214 rows=0 loops=1)
-> Seq Scan on tkstore (cost=0.00..4.39 rows=34
width=23) (actual time=0.023..0.181 rows=33 loops=1)
Filter: ((gruppo)::text = 'amit'::text)
Total runtime: 10858.720 ms
The db is ANALYZED;
Which is the reason of this difference??
What can I do ??
Thank
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] postgres FROM clause problem
Alle 18:53, giovedì 11 marzo 2004, hai scritto:
> Paolo Tavalazzi <[EMAIL PROTECTED]> writes:
> > [ query plans after updating to 7.4.2 ]
>
> Okay, they're certainly a lot closer than before, so I think I was right
> that you were getting bitten somehow by the pg_statistic alignment
> problem. It seems like there may still be a bug lurking though. The
> rows-out estimates are 7 versus 9 for the middle nested-loop join.
> That might just be roundoff error, or there may be something else going
> on. Also the estimates for the top join are 7 versus 1 rows, which
> seems too large a ratio to be explainable as roundoff error.
>
> Could I pester you to send me a pg_dump dump of this database off-list?
> I'd like to trace through the planner with a debugger and see exactly
> where the results are diverging.
>
> regards, tom lane
>
> ---(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
I have reconstructed the database from zero and i have tried the usual two query
in various conditions :
NO ANALYZE : similar execution time but different plan
1)
FROM
seat,spettacoli,tran,teatri
QUERY PLAN
---
HashAggregate (cost=51.00..51.00 rows=1 width=37) (actual time=4163.191..4164.883
rows=1317 loops=1)
-> Nested Loop (cost=0.00..50.99 rows=1 width=37) (actual time=0.253..3868.225
rows=67218 loops=1)
-> Nested Loop (cost=0.00..46.15 rows=1 width=50) (actual
time=0.215..2903.309 rows=68167 loops=1)
-> Nested Loop (cost=0.00..41.31 rows=1 width=38) (actual
time=0.124..508.361 rows=68174 loops=1)
-> Index Scan using time_idx on tran (cost=0.00..17.11 rows=5
width=32) (actual time=0.068..42.747 rows=24923 loops=1)
Index Cond: (("time" >= ('2004-03-01 00:00:00'::timestamp
without time zone)::timestamp with time zone) AND ("time" <= ('2004-03-08
23:59:59'::timestamp without time zone)::timestamp with time zone))
-> Index Scan using id_system_idx on seat (cost=0.00..4.83
rows=1 width=14) (actual time=0.007..0.010 rows=3 loops=24923)
Index Cond: (("outer".id = seat.bt_id) AND ("outer".system
= seat.system))
-> Index Scan using spe_sys_tea_perf on spettacoli (cost=0.00..4.83
rows=1 width=31) (actual time=0.031..0.032 rows=1 loops=68174)
Index Cond: ((spettacoli.system = "outer".system) AND
("outer".thea_code = spettacoli.teatro) AND (("outer".perf_code)::text =
(spettacoli.code)::text))
-> Index Scan using teatri_pkey on teatri (cost=0.00..4.82 rows=1 width=13)
(actual time=0.011..0.012 rows=1 loops=68167)
Index Cond: (teatri.code = "outer".thea_code)
Total runtime: 4165.522 ms
2) FROM
seat,teatri,tran,spettacoli
QUERY PLAN
---
HashAggregate (cost=51.00..51.00 rows=1 width=37) (actual time=4161.159..4162.811
rows=1317 loops=1)
-> Nested Loop (cost=0.00..50.99 rows=1 width=37) (actual time=0.251..3865.205
rows=67218 loops=1)
-> Nested Loop (cost=0.00..46.14 rows=1 width=51) (actual
time=0.186..1512.814 rows=67225 loops=1)
-> Nested Loop (cost=0.00..41.31 rows=1 width=38) (actual
time=0.121..507.694 rows=68174 loops=1)
-> Index Scan using time_idx on tran (cost=0.00..17.11 rows=5
width=32) (actual time=0.066..40.742 rows=24923 loops=1)
Index Cond: (("time" >= ('2004-03-01 00:00:00'::timestamp
without time zone)::timestamp with time zone) AND ("time" <= ('2004-03-08
23:59:59'::timestamp without time zone)::timestamp with time zone))
-> Index Scan using id_system_idx on seat (cost=0.00..4.83
rows=1 width=14) (actual time=0.008..0.011 rows=3 loops=24923)
Index Cond: (("outer"
Re: [GENERAL] postgres FROM clause problem (GROUP BY subquestion)
Alle 20:14, giovedì 11 marzo 2004, hai scritto: > On Thu, Mar 11, 2004 at 09:43:57 +0100, > > Paolo Tavalazzi <[EMAIL PROTECTED]> wrote: > > Alle 19:12, mercoledì 10 marzo 2004, hai scritto: > > > On Wed, Mar 10, 2004 at 18:33:41 +0100, > > > > > > Paolo Tavalazzi <[EMAIL PROTECTED]> wrote: > > > > I don't know what to make, help me please! > > > > > > > > Another question, in 7.4 GROUP BY clause not guarantee the ordering > > > > of result. Can I desable the new group by algorithm to maintain the > > > > coherence whit the programs that I have in production with pg_7.3 > > > > > > GROUP BY never guarenteed an order. That this happened in 7.3 was an > > > implementation detail. If you want a specific order you need to use an > > > ORDER BY clause. > > > > Seeing that GROUP BY in 7.3 required sorting the input data to bring > > group members together, this guaranteed to me also the order of the > > attributes in GROUP BY clause. > > This might be a language difference. You weren't guarenteed (or promised) > that the output would be sorted. It just happened that way because of how > the database implemented the group by. By reading the source code for > that version it would be possible to determine that the output would > always be sorted. However, that could change at any upgrade or if you > switched databases. You have reason. My deduction comes only from the reading of the code. I thought to have read it also in the postgres documentation,but it is not true. I have seen that the algorithm of group by in 7.4 is various then 7.3,and it seems more efficent. Therefore, i have changed my program to manage the order by clause. Thank you ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgres FROM clause problem
Alle 19:40, mercoledì 10 marzo 2004, hai scritto:
> Paolo Tavalazzi <[EMAIL PROTECTED]> writes:
> > I have applied the procedure for fixing pg_statistic as you had said,
> > but the result is the same!
>
> Hm. It could be a planner bug. Can you reproduce the misbehavior if
> you dump and load the tables into a fresh database? If so, could you
> send me the dump so I can look at the problem with a debugger?
>
> (Also, you might try updating to 7.4.2 first and see if that changes
> anything. We did fix quite a number of bugs already in 7.4.2...)
>
> > Another question, in 7.4 GROUP BY clause not guarantee the ordering of
> > result. Can I desable the new group by algorithm to maintain the
> > coherence whit the programs that I have in production with pg_7.3
>
> As Bruno said, your programs are broken because they are assuming
> something not guaranteed by the SQL spec. But until you get around to
> adding the ORDER BY clauses they should have, see enable_hashagg.
>
> regards, tom lane
I update to 7.4.2 (dump - initdb -restore) and I have had this result :
QUERY 1
explain analyze SELECT
teatri.code,
spettacoli.code,
sum(seat.price),
sum(seat.prev)
FROM
seat,
spettacoli,
tran ,
teatri
WHERE
tran.time >= timestamp '2004-02-20 00:00:00' AND
tran.time <= timestamp '2004-03-08 23:59:59' AND
tran.perf_code = spettacoli.code AND
tran.thea_code = spettacoli.teatro AND
tran.id = seat.bt_id AND
tran.system = seat.system AND
spettacoli.system= tran.system AND
spettacoli.teatro= teatri.code
GROUP BY
1,2
EXPLAIN output :
HashAggregate (cost=8057.62..8057.63 rows=1 width=32) (actual time=2711.656..2713.337
rows=1317 loops=1)
-> Nested Loop (cost=1538.40..8057.61 rows=1 width=32) (actual
time=648.011..2425.314 rows=67218 loops=1)
-> Nested Loop (cost=1538.40..8015.80 rows=9 width=45) (actual
time=647.968..1494.797 rows=68167 loops=1)
-> Merge Join (cost=1538.40..7920.98 rows=16 width=45) (actual
time=647.909..1087.032 rows=24919 loops=1)
Merge Cond: (("outer".system = "inner".system) AND
("outer".teatro = "inner".thea_code) AND (("outer".code)::text = "inner"."?column5?"))
-> Index Scan using spe_sys_tea_perf on spettacoli
(cost=0.00..5836.07 rows=56079 width=26) (actual time=0.049..64.118 rows=55565 loops=1)
-> Sort (cost=1538.40..1569.84 rows=12576 width=30) (actual
time=647.374..653.048 rows=24923 loops=1)
Sort Key: tran.system, tran.thea_code,
(tran.perf_code)::text
-> Index Scan using time_idx on tran (cost=0.00..682.08
rows=12576 width=30) (actual time=0.082..73.057 rows=24923 loops=1)
Index Cond: (("time" >= ('2004-03-01
00:00:00'::timestamp without time zone)::timestamp with time zone) AND ("time" <=
('2004-03-08 23:59:59'::timestamp without time zone)::timestamp with time zone))
-> Index Scan using id_system_idx on seat (cost=0.00..5.91 rows=1
width=14) (actual time=0.007..0.010 rows=3 loops=24919)
Index Cond: (("outer".id = seat.bt_id) AND ("outer".system =
seat.system))
-> Index Scan using teatri_pkey on teatri (cost=0.00..4.63 rows=1 width=13)
(actual time=0.010..0.011 rows=1 loops=68167)
Index Cond: (teatri.code = "outer".thea_code)
Total runtime: 2717.573 ms
QUERY 2
explain analyze SELECT
teatri.code,
spettacoli.code,
sum(seat.price),
sum(seat.prev)
FROM
seat,
teatri,
tran ,
spettacoli
WHERE
tran.time >= timestamp '2004-03-01 00:00:00' AND
tran.time <= timestamp '2004-03-08 23:59:59' AND
tran.perf_code = spettacoli.code AND
tran.thea_code = spettacoli.teatro AND
tran.id = seat.bt_id AND
tran.system = seat.system AND
spettacoli.system= tran.system AND
spettacoli.teatro= teatri.code
GROUP BY
1,2
EXPLAIN output :
HashAggregate (cost=8057.62..8057.63 rows=1 width=32) (actual time=2711.656..2713.337
rows=1317 loops=1)
-> Nested Loop (cost=1538.40..8057.61 rows=1 width=32) (actual
time=648.011..2425.314 rows=67218 loops=1)
-> Nested Loop (cost=1538.40..8015.80 rows=9 width=45) (actual
time=647.968..1494.797 rows=68167 loops=1)
-> Merge Join (cost=1538.40..7920.98 rows=16 width=45) (actual
time=647.909..1087.032 rows=24919 loops=1)
Re: [GENERAL] postgres FROM clause problem
Alle 18:03, giovedì 11 marzo 2004, hai scritto:
> Paolo Tavalazzi <[EMAIL PROTECTED]> writes:
> > But the query plans are still various!!
>
> I think you made a copy-and-paste mistake, because the explain results
> you posted are exactly the same ...
>
> regards, tom lane
Excuse I have mistaken!!
QUERY 1
explain analyze SELECT
teatri.code,
spettacoli.code,
sum(seat.price),
sum(seat.prev)
FROM
seat,
spettacoli,
tran ,
teatri
WHERE
tran.time >= timestamp '2004-02-20 00:00:00' AND
tran.time <= timestamp '2004-03-08 23:59:59' AND
tran.perf_code = spettacoli.code AND
tran.thea_code = spettacoli.teatro AND
tran.id = seat.bt_id AND
tran.system = seat.system AND
spettacoli.system= tran.system AND
spettacoli.teatro= teatri.code
GROUP BY
1,2
EXPLAIN :
HashAggregate (cost=8042.89..8042.92 rows=7 width=32) (actual
time=2069.895..2071.505 rows=1317 loops=1)
-> Hash Join (cost=8015.82..8042.82 rows=7 width=32) (actual
time=1538.771..1779.257 rows=67218 loops=1)
Hash Cond: ("outer".code = "inner".teatro)
-> Seq Scan on teatri (cost=0.00..22.62 rows=862 width=13) (actual
time=0.008..0.572 rows=862 loops=1)
-> Hash (cost=8015.80..8015.80 rows=7 width=45) (actual
time=1538.652..1538.652 rows=0 loops=1)
-> Nested Loop (cost=1538.40..8015.80 rows=7 width=45) (actual
time=652.105..1486.577 rows=68167 loops=1)
-> Merge Join (cost=1538.40..7920.98 rows=16 width=45) (actual
time=652.045..1095.559 rows=24919 loops=1)
Merge Cond: (("outer".system = "inner".system) AND
("outer".teatro = "inner".thea_code) AND (("outer".code)::text = "inner"."?column5?"))
-> Index Scan using spe_sys_tea_perf on spettacoli
(cost=0.00..5836.07 rows=56079 width=26) (actual time=0.049..63.556 rows=55565 loops=1)
-> Sort (cost=1538.40..1569.84 rows=12576 width=30)
(actual time=651.509..656.391 rows=24923 loops=1)
Sort Key: tran.system, tran.thea_code,
(tran.perf_code)::text
-> Index Scan using time_idx on tran
(cost=0.00..682.08 rows=12576 width=30) (actual time=0.083..69.887 rows=24923 loops=1)
Index Cond: (("time" >= ('2004-03-01
00:00:00'::timestamp without time zone)::timestamp with time zone) AND ("time" <=
('2004-03-08 23:59:59'::timestamp without time zone)::timestamp with time zone))
-> Index Scan using id_system_idx on seat (cost=0.00..5.91
rows=1 width=14) (actual time=0.006..0.010 rows=3 loops=24919)
Index Cond: (("outer".id = seat.bt_id) AND ("outer".system
= seat.system))
Total runtime: 2076.726 ms
QUERY 2 :
explain anlyze SELECT
teatri.code,
spettacoli.code,
sum(seat.price),
sum(seat.prev)
FROM
seat,
teatri,
tran ,
spettacoli
WHERE
tran.time >= timestamp '2004-03-01 00:00:00' AND
tran.time <= timestamp '2004-03-08 23:59:59' AND
tran.perf_code = spettacoli.code AND
tran.thea_code = spettacoli.teatro AND
tran.id = seat.bt_id AND
tran.system = seat.system AND
spettacoli.system= tran.system AND
spettacoli.teatro= teatri.code
GROUP BY
1,2
EXPLAIN output :
HashAggregate (cost=8057.62..8057.63 rows=1 width=32) (actual
time=2728.066..2729.738 rows=1317 loops=1)
-> Nested Loop (cost=1538.40..8057.61 rows=1 width=32) (actual
time=665.122..2438.275 rows=67218 loops=1)
-> Nested Loop (cost=1538.40..8015.80 rows=9 width=45) (actual
time=665.078..1509.890 rows=68167 loops=1)
-> Merge Join (cost=1538.40..7920.98 rows=16 width=45) (actual
time=665.018..1101.716 rows=24919 loops=1)
Merge Cond: (("outer".system = "inner".system) AND
("outer".teatro = "inner".thea_code) AND (("outer".code)::text = "inner"."?column5?"))
-> Index Scan using spe_sys_tea_perf on spettacoli
(cost=0.00..5836.07 rows=56079 width=26) (actual time=0.046..63.772 rows=55565 loops=1)
-> Sort (cost=1538.40..1569.84 rows=12576 width=30) (actual
time=664.481..669.947 rows=24923 loops=1)
Sort Key: tran.system, tran.thea_code,
(tran.perf_code)::text
-> Index Scan using time_idx on tran (cost
Re: [GENERAL] postgres FROM clause problem
Alle 16:54, mercoledì 10 marzo 2004, hai scritto: > Paolo Tavalazzi <[EMAIL PROTECTED]> writes: > > I have two query that they are different only for order of the tables > > in FROM lclause , but give back different query plan : > > Hm, seems like the planner is making wacko estimates in the second case. > You didn't say what data types are involved in this query --- are any of > the join columns int8 or float8 or timestamp? If so you might be > getting bitten by the 7.4 pg_statistic alignment bug. Please follow the > repair procedures suggested in the 7.4.2 release notes: > http://developer.postgresql.org/docs/postgres/release.html#RELEASE-7-4-2 > and see if that improves matters. > > regards, tom lane I have applied the procedure for fixing pg_statistic as you had said, but the result is the same! Only tran.time in the query is a timestamp , no int8 or float8. The OS is FEDORA 0.96 x86_64 and the flag --enable-integer-datetimes is false,it could be a problem?? I don't know what to make, help me please! Another question, in 7.4 GROUP BY clause not guarantee the ordering of result. Can I desable the new group by algorithm to maintain the coherence whit the programs that I have in production with pg_7.3 Thank you !!! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] VARCHAR and TEXT
Hi everybody, I have a little question about string types in PostgreSQL: it seems I don't understood well how string data are stored in the database, from a recent post by Tom Lane I feel encouraged to think that VARCHAR and TEXT are treated the same way by PG, i.e. they occupy ony the space needed by their actual length, bt I'm not so sure of it. I ask this because I'm trying using PG as database server in a project developed in delphi under ms-win and it seems to me that varchar and text fields are not treated the same way by odbc interface and/or bde (borland database engine, the database interface layer used by delphi programs). When I connect to a table the fields of type VARCHAR are seen as strings while the fields of type TEXT are seen as blobs, and this is a little upsetting, because some useful functions are not implemented for blob fields. I have several tables with fields that could contain quite lengthy strings, but usually don't. So I don't want to define theese fields as VARCHAR(400) if this means a disk occupation of 400 bye or so for each row (I have some 50 rows in a table and the average length of the field in question is about 30, but the max length is near 400). Can someone clarify this point? Thanks in advance ___PS ---(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
