[GENERAL] Fedora 11 where is plpython.so
Where can i find plpython.so in the Fedora 11 i586 rpm packages? The postgresql-python does not have it: $ rpm -q --filesbypkg postgresql-python | grep plpython $ http://download.fedora.redhat.com/pub/fedora/linux/releases/11/Fedora/i386/os/Packages/postgresql-python-8.3.7-1.fc11.i586.rpm There is no postgresql-pl package. Regards, Clodoaldo -- 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] Fedora 11 where is plpython.so
2009/6/25 Clodoaldo Pinto Neto clodoaldo.pinto.n...@gmail.com: Where can i find plpython.so in the Fedora 11 i586 rpm packages? The postgresql-python does not have it: $ rpm -q --filesbypkg postgresql-python | grep plpython $ http://download.fedora.redhat.com/pub/fedora/linux/releases/11/Fedora/i386/os/Packages/postgresql-python-8.3.7-1.fc11.i586.rpm There is no postgresql-pl package. Just found it. It is in the Everything repository. Sorry for the noise Regards, Clodoaldo -- 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] empty folder for downlaoding PostgreSQL 8.1.5 for FC 4
2006/11/14, surabhi.ahuja [EMAIL PROTECTED]: hi I am trying to download PostgreSQL 8.1.5 however this link does not contain anything: http://www.postgresql.org/ftp/binary/v8.1.5/linux/rpms/fedora/fedora-core-4-x86_64/ moreover the link http://www.postgresql.org/ftp/binary/v8.1.5/linux/rpms/fedora/fedora-core-4/ is just containing the drivers. Can you please tell what the problem is? Probably there was no one with FC4 systems to build the rpms. You can download the source rpms and build the rpms from it: Download from: http://www.postgresql.org/ftp/binary/v8.1.5/linux/srpms/fedora/fedora-core-4/ And then build for x86: rpmbuild --rebuild --target i686-redhat-Linux http://wwwmaster.postgresql.org/download/mirrors-ftp?file=binary%2Fv8.1.5%2Flinux%2Fsrpms%2Ffedora%2Ffedora-core-4%2Fpostgresql-8.1.5-6PGDG.src.rpm and for x86_64: rpmbuild --rebuild http://wwwmaster.postgresql.org/download/mirrors-ftp?file=binary%2Fv8.1.5%2Flinux%2Fsrpms%2Ffedora%2Ffedora-core-4%2Fpostgresql-8.1.5-6PGDG.src.rpm If you do it place the rpms where Devrim can copy them to the ftp server. Regards, Clodoaldo Pinto Neto ---(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] plpython.so
I had 8.1.4 i386 installed from the FC5 repository. To upgrade to 8.1.5 I downloaded the srpm from pgsql mirrors and rebuilt it for i686. Then installed the 8 resulting rpms with yum update from a local repo. When I ran the application I had this error: psql:/fahstats/scripts/sql/fahstats.sql:197: ERROR: plpython: function update_donor_yearly failed DETAIL: exceptions.SystemExit: psql:/fahstats/scripts/sql/fahstats.sql:140: ERROR: could not access file $libdir/plpython: No such file or directory I noticed there was no /usr/lib/plpython.so. Then with more attention i also noticed there was no postgresql-python in the 8 rpms built from the srpm in the pgsql mirror. Then I downloaded the postgresql-python rpm from the mirror to the local repo and tried yum update but it failed with no messages. I did yum remove postgresql-python and yum install postgresql-python which worked and installed the new version. But I'm still with no /usr/lib/plpython.so. What am i missing? Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plpython.so
2006/10/27, Tom Lane [EMAIL PROTECTED]: Clodoaldo Pinto Neto [EMAIL PROTECTED] writes: But I'm still with no /usr/lib/plpython.so. What am i missing? That's not where we keep it. Try /usr/lib/pgsql/plpython.so (or wherever pg_config --pkglibdir points to). My mistake, I was looking at /usr/lib/pgsql # pg_config --pkglibdir /usr/lib/pgsql # ll /usr/lib/pgsql/pl* -rwxr-xr-x 1 root root 166887 Oct 26 22:12 /usr/lib/pgsql/plperl.so -rwxr-xr-x 1 root root 340887 Oct 26 22:12 /usr/lib/pgsql/plpgsql.so -rwxr-xr-x 1 root root 76833 Oct 26 22:12 /usr/lib/pgsql/pltcl.so Also, the .so is probably in the postgresql-pl RPM not postgresql-python, I have postgresql-pl installed: # yum list installed postgresql\* Loading installonlyn plugin Installed Packages postgresql.i686 8.1.5-3PGDGinstalled postgresql-contrib.i686 8.1.5-3PGDGinstalled postgresql-debuginfo.i3868.1.4-1.FC5.1 installed postgresql-devel.i6868.1.5-3PGDGinstalled postgresql-docs.i686 8.1.5-3PGDGinstalled postgresql-jdbc.i686 8.1.407-4PGDG installed postgresql-libs.i686 8.1.5-3PGDGinstalled postgresql-odbc.i686 08.02.0100-2PGDG installed postgresql-odbc-debuginfo.i386 08.01.0200-1.2 installed postgresql-pl.i686 8.1.5-3PGDGinstalled postgresql-python.i686 3.8.1-3PGDGinstalled postgresql-server.i686 8.1.5-3PGDGinstalled postgresql-tcl.i386 8.1.4-1.FC5.1 installed postgresql-test.i686 8.1.5-3PGDGinstalled Clodoaldo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plpython.so
2006/10/27, Devrim GUNDUZ [EMAIL PROTECTED]: Hi, On Fri, 2006-10-27 at 11:46 -0400, Tom Lane wrote: Also, the .so is probably in the postgresql-pl RPM not postgresql-python, unless Devrim has moved things around when I wasn't looking. Yeah, it is my mistake :-( I had removed plpython.so from installed files Is it a problem if i use plpython.so from 8.1.4? Clodoaldo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] plpython.so
2006/10/27, Devrim GUNDUZ [EMAIL PROTECTED]: Hi, Anyway... I have uploaded new sets. SRPMs and FC5 RPMs will be in main FTP site in an hour. They are labeled as 8.1.5-4PGDG . Thanks! Clodoaldo ---(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] plpython
2006/10/27, km [EMAIL PROTECTED]: i am stuck at createlang for plpythonu! with postgres user error reads: $createlang plpythonu template1; createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpython.so: /usr/local/pgsql/lib/plpython.so: undefined symbol: Py_InitModule4_64 i am on a x86_64 linux box. couldnt comprehend the error. plpython.so is in /usr/local/pgsql/lib whats wrong? Read the thread named plpython.so Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Moving data to another disk
This is the original message from october 2 that didn't appear in the list. I don't know if someone received it. X-Gmail-Received: c62ada517ba59dc1cb70c46da86fdd974cd80c63 Received: by 10.70.35.9 with HTTP; Mon, 2 Oct 2006 10:59:43 -0700 (PDT) Message-ID: [EMAIL PROTECTED] Date: Mon, 2 Oct 2006 14:59:43 -0300 From: Clodoaldo Pinto Neto [EMAIL PROTECTED] To: pgsql-general postgresql.org pgsql-general@postgresql.org Subject: Moving data to another disk MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Delivered-To: [EMAIL PROTECTED] I want to move the data directory to another disk and mantain the config files in the same place. I will change the data_directory parameter in postgresql.conf. I could just copy everything under /var/lib/pgsql/data to the other disk but I also want to clean the diretory now used thus I need to know for sure what files/directories are data. I have read the 50.1 section of the postgres manual. I suppose all directories under /var/lib/pgsql/data are data directories and i guess the postmaster.opts, postmaster.pid and PG_VERSION files should stay in the config directory and obviously the *.conf files are config files. Am i right? Regards, Clodoaldo ---(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] Moving data to another disk
I want to move the data directory to another disk and mantain the config files in the same place. I will change the data_directory parameter in postgresql.conf. I could just copy everything under /var/lib/pgsql/data to the other disk but I also want to clean the diretory now used thus I need to know for sure what files/directories are data. I have read the 50.1 section of the postgres manual. I suppose all directories under /var/lib/pgsql/data are data directories and i guess the postmaster.opts, postmaster.pid and PG_VERSION files should stay in the config directory and obviously the *.conf files are config files. Am i right? Regards, Clodoaldo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres
Em 29/08/06, [EMAIL PROTECTED][EMAIL PROTECTED] escreveu: Prezado Suporte, Gostaria de saber se existe upgradedo BD Postgres 7.2.1 para BD Postgres 8.1? onde consigo obter? Bruno, Esta lista é apenas em inglês. Para suporte em português procure: http://wiki.postgresql.org.br/ http://pgfoundry.org/mailman/listinfo/brasil-usuarios Saudações, Clodoaldo Pinto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Alter Sequencie
19 Jul 2006 10:59:10 -0700, Léo Matos [EMAIL PROTECTED]: Olá senhores, boa tarde. Estou com o seguinte problema...cadastrei em lote alguns dados nas tabelas do meu bd, só que as seqüências não foram atualizadas, todas elas se encontram com valor atual igual a 1, gerando erros quando eu vou cadastrar alguma informação. Primeiro gostaria de saber se existe algo fácil de reindexar a seqüência, ou se é necessário refazer a seqüência de cada tabela com o valor atual da minha pk. Eu tentei criar uma função que varresse todas as minhas tabelas e utilizasse o alter sequence nome_da_minha_sequencia restart with valor_atual, mas para isso eu preciso saber o valor atual das minhas chaves primárias, aí eu parei... Alguém pode me ajudar... Leonardo, Nesta lista dificilmente você vai encontrar ajuda pois o idioma aqui é o inglês. Procure a lista em português. Saudações, Clodoaldo Pinto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] doesn't recognize !=- (not equal to a negative value)
2006/7/11, Oisin Glynn [EMAIL PROTECTED]: Paul Tilles wrote: Version postgres 7.4.7: Following sql UPDATE tablename SET value = 0.0 where value!=-9.4; results in the error message ERROR: operator does not exist: smallint !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Seems that postgres has a problem parsing a not equal negative value. Anybody know if this is fixed in later versions? Paul Tilles ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Just tested on 8.1.1 I was getting the same error but if i put a space between the = and the - it works! I have already counted 6 very similar answers. Is there some problem with the list software that prevents people from noticing it has already been answered? Regards, Clodoaldo Pinto ---(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] php can't connect to postgresql server
2006/7/3, Clodoaldo Pinto [EMAIL PROTECTED]:
2006/7/3, Clodoaldo Pinto [EMAIL PROTECTED]:
php can't connect to postgresql server
php error log message:
PHP Warning: pg_connect() [a
href='function.pg-connect'function.pg-connect/a]: Unable to connect
to PostgreSQL server: could not connect to server: Permission
denied\n\tIs the server running on host quot;127.0.0.1quot; and
accepting\n\tTCP/IP connections on port 5432?
php connection string:
$conn_string = hostaddr=127.0.0.1 port=5432 dbname=dbname
user=username password=password;
Also tried host=localhost
pg_hba:
host dbname username 127.0.0.1/32 md5
I can connect as that user with psql:
$ psql -h localhost -U username dbname
Password for user username:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
This same setup works in another server running 8.0. Differences:
old server | new server
FC3 | FC5
php 4.3.11 | php 5.1.4
httpd 2.0 | httpd 2.2
What else should I check? I am out of ideas.
Solved. It was a SELinux problem. From /var/log/messages:
kernel: audit(1151945653.900:39): avc: denied { name_connect } for
pid=17167 comm=httpd dest=5432 scontext=user_u:system_r:httpd_t:s0
tcontext=system_u:object_r:postgresql_port_t:s0 tclass=tcp_socket
Again followed this:
http://fedora.redhat.com/docs/selinux-faq-fc5/#id2961385
I searched for this problem and I wonder how could nobody reported it
before. Am I the only one connecting locally to pgsql from php with
SELinux enabled in a FC5 box or what?
A much simpler solution:
# setsebool -P httpd_can_network_connect_db 1
Regards, Clodoaldo Pinto
---(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] php can't connect to postgresql server
php can't connect to postgresql server php error log message: PHP Warning: pg_connect() [a href='function.pg-connect'function.pg-connect/a]: Unable to connect to PostgreSQL server: could not connect to server: Permission denied\n\tIs the server running on host quot;127.0.0.1quot; and accepting\n\tTCP/IP connections on port 5432? php connection string: $conn_string = hostaddr=127.0.0.1 port=5432 dbname=dbname user=username password=password; Also tried host=localhost pg_hba: host dbname username 127.0.0.1/32 md5 I can connect as that user with psql: $ psql -h localhost -U username dbname Password for user username: Welcome to psql 8.1.4, the PostgreSQL interactive terminal. This same setup works in another server running 8.0. Differences: old server | new server FC3 | FC5 php 4.3.11 | php 5.1.4 httpd 2.0 | httpd 2.2 What else should I check? I am out of ideas. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] php can't connect to postgresql server
2006/7/3, A. Kretschmer [EMAIL PROTECTED]: am 03.07.2006, um 10:25:29 -0300 mailte Clodoaldo Pinto folgendes: $conn_string = hostaddr=127.0.0.1 port=5432 dbname=dbname ^ I can connect as that user with psql: $ psql -h localhost -U username dbname ^ Try 'localhost' instead '127.0.0.1' in your $conn_string. As I already said I have tried localhost also. My guess: pg don't listen on tcp/ip, only on the local socket. (check listen_addresses in your postgresql.conf) listen_addresses = '127.0.0.1,xx.xx.xxx.xx' If I can connect with the -h localhost psql's option then I guess it is listening to tcp/ip, isn't it? Clodoaldo ---(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] php can't connect to postgresql server
2006/7/3, Robert Treat [EMAIL PROTECTED]: On Monday 03 July 2006 09:25, Clodoaldo Pinto wrote: php can't connect to postgresql server php error log message: PHP Warning: pg_connect() [a href='function.pg-connect'function.pg-connect/a]: Unable to connect to PostgreSQL server: could not connect to server: Permission denied\n\tIs the server running on host quot;127.0.0.1quot; and accepting\n\tTCP/IP connections on port 5432? php connection string: $conn_string = hostaddr=127.0.0.1 port=5432 dbname=dbname user=username password=password; Also tried host=localhost It needs to be host, not hostaddr. Changed to host=localhost as I had already done before with the same results. From the php manual: The currently recognized parameter keywords are: host, hostaddr,... What else should I check? I am out of ideas. Make sure that your postgresql.conf listen_addresses is properly set, What do you mean by properly set? What I had already post is not properly set?: listen_addresses = '127.0.0.1,xx.xx.xxx.xx' make sure you dont have a firewall setting that is blocking connections, the default firewall settings on FC boxes are notorious for blocking pg. Opened the 5432 port in the firewall and still the same problem. Clodoaldo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] php can't connect to postgresql server
2006/7/3, Clodoaldo Pinto [EMAIL PROTECTED]: php can't connect to postgresql server php error log message: PHP Warning: pg_connect() [a href='function.pg-connect'function.pg-connect/a]: Unable to connect to PostgreSQL server: could not connect to server: Permission denied\n\tIs the server running on host quot;127.0.0.1quot; and accepting\n\tTCP/IP connections on port 5432? php connection string: $conn_string = hostaddr=127.0.0.1 port=5432 dbname=dbname user=username password=password; Also tried host=localhost pg_hba: host dbname username 127.0.0.1/32 md5 I can connect as that user with psql: $ psql -h localhost -U username dbname Password for user username: Welcome to psql 8.1.4, the PostgreSQL interactive terminal. This same setup works in another server running 8.0. Differences: old server | new server FC3 | FC5 php 4.3.11 | php 5.1.4 httpd 2.0 | httpd 2.2 What else should I check? I am out of ideas. The same script running from another server can connect to the new server using this pg_hba entry: host dbname usename xx.xx.xxx.xx/32 md5 Clodoaldo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postgresql does not start on reboot
Postgresql does not start on reboot but starts normally with pg_ctl
start. Running FC5 with selinux enabled.
# chkconfig --list postgresql
postgresql 0:off 1:off 2:on3:on4:on5:on6:off
/etc/selinux/config:
SELINUX=enforcing
SELINUXTYPE=targeted
SETLOCALDEFS=0
In /var/log/messages there is this message:
kernel: audit(1151719618.110:4): avc: denied { search } for
pid=1849 comm=postmaster name=/ dev=sdb1 ino=2
scontext=system_u:system_r:postgresql_t:s0
tcontext=system_u:object_r:file_t:s0 tclass=dir
There is a simlink in /var/lib/pgsql/data pointing to /disk2/pg_xlog
which is in sbd1 and is owned by postgres.
If is this a selinux problem, how can I configure it to let postgres
use the pg_xlog dir in /disk2 on startup?
Regards, Clodoaldo Pinto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Postgresql does not start on reboot
2006/7/1, Tom Lane [EMAIL PROTECTED]: Clodoaldo Pinto [EMAIL PROTECTED] writes: There is a simlink in /var/lib/pgsql/data pointing to /disk2/pg_xlog which is in sbd1 and is owned by postgres. You need to modify the selinux policy to let the postmaster access /disk2/pg_xlog ... by default, it's constrained to only be able to touch stuff under /var/lib/pgsql. I followed this: http://fedora.redhat.com/docs/selinux-faq-fc5/#id2961385 I will reboot in a few days and then I will know if it is fixed. Clodoaldo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to build with bigger WAL segment file?
Since I have a database with a frequent and huge update batch, the WAL segment file number is about 130. I suspect these files management during the update is hurting performance. Is it likely? I'm using Fedora Core 3 and checkpoint_segments is set to 64. To overcome this I'm considering to make the WAL segment files bigger. The segment file default size is 16 MB. How to build from the source rpm to have bigger files, say 128 MB? Regards, Clodoaldo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to build with bigger WAL segment file?
2006/6/19, Clodoaldo Pinto [EMAIL PROTECTED]: Since I have a database with a frequent and huge update batch, the WAL segment file number is about 130. I suspect these files management during the update is hurting performance. Is it likely? I'm using Fedora Core 3 and checkpoint_segments is set to 64. To overcome this I'm considering to make the WAL segment files bigger. The segment file default size is 16 MB. How to build from the source rpm to have bigger files, say 128 MB? I found it in src/include/pg_config_manual.h: /* * XLOG_SEG_SIZE is the size of a single WAL file. This must be a power of 2 * and larger than BLCKSZ (preferably, a great deal larger than BLCKSZ). * * Changing XLOG_SEG_SIZE requires an initdb. */ #define XLOG_SEG_SIZE (16*1024*1024) ---(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] Calling the same function more than once with the same arguments
2006/4/19, Ben [EMAIL PROTECTED]: Look into the immutable flag on function creation: http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html That is what I needed to know, Thanks. Regards, Clodoaldo Pinto ---(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] Calling the same function more than once with the same arguments
2006/4/19, Ben [EMAIL PROTECTED]: Look into the immutable flag on function creation: I have read that section and I'm still not sure about it. This is the caller() function: create or replace function caller(int4) returns some_type as $body$ select array(select distinct a from called($1)) as a , array(select distinct b from called($1)) as b ; $body$ language 'sql' stable strict; Since called() will read from a table that can be modified it is declared as stable and not as immutable. This is what is in section 32.6: A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments FOR ALL ROWS WITHIN A SINGLE STATEMENT. Is called() called from within a single statement in caller()? Will the optimizer optimize the multiple calls (there will be about 30) to a single call? Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Calling the same function more than once with the same arguments
I'm building a function caller() in which a certain function called() will be called many times in from clauses with the same arguments and I'm wondering if is there a performance penalty for that or if the sql engine is smart enough to call called() only once. I tried to substitute called() in the from clauses for a temporary table created first in caller() but it refuses to build caller() with the message ERROR: relation temp_table_name does not exist. It does not exist in build time but it will exist in run time. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] From ASCII to UTF-8
As part of a migration from 8.0 to 8.1 i want to convert the data from ASCII to UTF-8. I dumped the database with pg_dump (8.0) and tried to convert it with iconv, but it shows an error: $ iconv -t ASCII -t UTF-8 fahstats_data.dump -o fahstats_data_utf-8.dump iconv: illegal input sequence at position 71407864 That position contains the decimal value 233: $ od -A d -j 71407864 -N 1 -t u1 fahstats_data.dump 71407864 233 71407865 I could use pg_dump -E in 8.1 but it is in another machine with ADSL connection and the dump size is 1.8GB. It would take more than 12 hours. How to install pg_dump 8.1 only? I tried to copy the executable and the libs but it did not work. Regards, Clodoaldo Pinto ---(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] update functions locking tables
|0 | 0 | 0 | 0 | 0 | 0 | 1813896 | 1813868 || 7040 | AccessShareLock | t 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 31383 | AccessShareLock | f 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 12351 | AccessShareLock | f 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 26871 | AccessShareLock | f 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 26844 | AccessShareLock | f 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 24021 | AccessShareLock | f 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 31212 | AccessShareLock | f 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 7040 | AccessShareLock | t 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 7040 | RowExclusiveLock| t 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 7040 | ShareLock | t 2314110 | public | times_producao|0 |0 | 0 | 0 | 0 | 0 | 0 | 2314110 | 1813868 || 7040 | AccessExclusiveLock | t 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 26872 | AccessShareLock | f 1813887 | public | datas |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813887 | 1813868 || 7040 | AccessShareLock | t 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 8875 | AccessShareLock | f 1813907 | public | usuarios |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813907 | 1813868 || 7040 | AccessShareLock | t 1813911 | public | usuarios_indice |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813911 | 1813868 || 7040 | AccessShareLock | t (23 rows) I had just enabled stats_command_string and in about 15 hours i will be able to post pg_stat_activity. Regards, Clodoaldo Pinto ---(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] update functions locking tables
2005/8/29, Tom Lane [EMAIL PROTECTED]: What is the function doing to the table, exactly? DDL changes generally take exclusive locks ... This is the transaction: begin; select update_last_date(); truncate times_producao; select kstime(), insert_times_producao(), kstime(); select kstime(), update_ranking_times(), kstime(); truncate usuarios_producao; select kstime(), insert_usuarios_producao(), kstime(); analyze usuarios_producao; select kstime(), update_ranking_usuarios(), kstime(); select kstime(), update_ranking_usuarios_time(), kstime(); select kstime(), update_team_active_members(), kstime(); commit; This is one of the functions: CREATE OR REPLACE FUNCTION update_ranking_usuarios() RETURNS void AS $BODY$declare linha record; rank integer; begin rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 desc, pontos_7 desc, pontos_24 desc loop rank := rank + 1; update usuarios_producao set rank_0 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 + (pontos_7 / 7) desc, pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_24 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 + pontos_7 desc, pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_7 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 + (pontos_7 * 30 / 7) desc, pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_30 = rank where usuario = linha.usuario ; end loop; return; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT; There is no DDL inside the functions. Regards, Clodoaldo Pinto ---(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] update functions locking tables
2005/8/30, Michael Fuhr [EMAIL PROTECTED]: TRUNCATE acquires an AccessExclusiveLock, which conflicts with all other lock types. Locks are held until the transaction completes, so once this lock is acquired no other transactions will be able to access the table until this transaction commits or rolls back. DELETE is slower than TRUNCATE but it won't block readers in other transactions. I think it is of great help. I will change it and let you know what happened. Regards, Clodoaldo Pinto ---(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] update functions locking tables
30 Aug 2005 09:10:51 -0400, Greg Stark [EMAIL PROTECTED]: I think truncate takes a table lock. Just change it to delete from times_producao. Thanks, i will try it. Also, if consider doing a vacuum full or cluster after the batch job to clear up the free space (not in a large transaction). That will still take a table lock but it may be a small enough downtime to be worth the speed increase the rest of the day. I'm already doing a vacuum (not full) once a day. A vacuum full or a cluster is totally out of reach since each take about one hour. The biggest table is 170 million rows long. Regards, Clodoaldo Pinto ---(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] update functions locking tables
2005/8/30, Alvaro Herrera [EMAIL PROTECTED]: On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote: A vacuum full or a cluster is totally out of reach since each take about one hour. Even if you cluster/vacuum only the just-loaded table? No, that would be much faster. The biggest just updated is about 600 thousand rows. I will consider it. The biggest table is 170 million rows long. I hope this is not the one you are loading daily ... I load daily 8 times 700+ thousand rows. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] update functions locking tables
30 Aug 2005 10:35:31 -0400, Greg Stark [EMAIL PROTECTED]: Well a regular vacuum will mark the free space for reuse. If you insert or update any records the new ones will go into those spots. Make sure you set the fsm_* parameters high enough to cover all the updates and inserts for the entire day (or repeat the vacuum periodically even if there are no deletes or updates going on to create more free space). I will check those fsm_* parameters. You should realize that what's going on here is that the old records are still in your table, marked as deleted. So any sequential scan will take twice as long as otherwise. I think even index scans could take twice as long too depending on the distribution of values. I'm not saying that's untenable. If all your queries are fast enough then you're set and it's just a cost of having no downtime. -- greg ---(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] update functions locking tables
2005/8/30, Jim C. Nasby [EMAIL PROTECTED]: FWIW, that where clause might be more efficient as WHERE pontos_0 pontos_7. Some databases would be able to use indexes to answer that (not sure if PostgreSQL could), plus it removes an operator. It also seems to be cleaner code to me. :) -- Done, thanks. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] update functions locking tables
I have almost completed one web site migration from mysql to pgsql. It is already running totally pgsql. But there is one last conversion problem. Most of the queries use tables populated every 3 hours. In mysql, for the site to not be unavailable during updating i was creating temporary tables, populating them, dropping the main tables and then renaming the temp tables to the main tables. The updating is not trivial (for me) and needs some coding effort to be done. Since pgsql has MVCC I wanted to eliminate the table rotation step and use a transaction to update the tables. But what is happening is that the plpgsql update functions are locking the tables and this is what the web clients are getting (from ps ax): 26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting 23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting 31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting 21601 ? S 0:00 postgres: user database 127.0.0.1(46770) SELECT waiting I've been trying for 3 days to figure out what is happening to no avail. What am i missing about transactions and MVCC? What could make a plpgsql update function lock a table? The indexes are default btree. Otherwise the functions are behaving exactly as expected. Regards, Clodoaldo Pinto ---(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] Problem upgrading from 8.0.1 to 8.0.3
2005/8/28, Alvaro Herrera [EMAIL PROTECTED]: On Thu, Aug 25, 2005 at 10:17:05AM -0300, Clodoaldo Pinto wrote: It would have been better if it had picked postgresql-compat (or whatever the name is for the package that has libpq3 in the 8.0.3 release). Probably if you install that package by hand or convince yum to do it for you, the problem would be solved. Most likely you need to uninstall the 7.4 package at the same time. I didn't know about compat. What I did was to extract libpq3 from 7.4.8 and copied it to its place. Then forced the install of php-pgsql. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problem upgrading from 8.0.1 to 8.0.3
I was running 8.0.1 and PHP in FC3 with no problems. Then i upgraded to 8.0.3 using yum and yum installed postgresql-libs.i386 0:7.4.8-1.FC3.1. (Is it Ok?) Now i am receving this message from PHP: PHP Fatal error: Call to undefined function: pg_pconnect() php-pgsql was already installed. I removed it and tried to reinstall and got this dependencies errors: # yum install php-pgsql* Setting up Install Process Setting up Repos base 100% |=| 1.1 kB00:00 updates-released 100% |=| 951 B00:00 Reading repository metadata in from local files base : ## 2622/2622 updates-re: ## 1041/1041 Parsing package install arguments Resolving Dependencies -- Populating transaction set with selected packages. Please wait. --- Package php-pgsql.i386 0:4.3.11-2.6 set to be updated -- Running transaction check -- Processing Dependency: libpq.so.3 for package: php-pgsql -- Restarting Dependency Resolution with new changes. -- Populating transaction set with selected packages. Please wait. --- Package postgresql-libs.i386 0:7.4.8-1.FC3.1 set to be updated -- Running transaction check -- Processing Dependency: libpq.so.4 for package: postgresql-python -- Processing Dependency: libpq.so.4 for package: postgresql-contrib -- Processing Dependency: postgresql-libs = 8.0.3 for package: postgresql-devel-- Processing Dependency: libpq.so.4 for package: postgresql-server -- Processing Dependency: libpq.so.4 for package: postgresql -- Finished Dependency Resolution Error: Missing Dependency: libpq.so.4 is needed by package postgresql-python Error: Missing Dependency: libpq.so.4 is needed by package postgresql-contrib Error: Missing Dependency: postgresql-libs = 8.0.3 is needed by package postgresql-devel Error: Missing Dependency: libpq.so.4 is needed by package postgresql-server Error: Missing Dependency: libpq.so.4 is needed by package postgresql Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] extract (dow/week from date)
2005/8/21, Jim C. Nasby [EMAIL PROTECTED]: On Sun, Aug 21, 2005 at 08:00:45AM -0300, Clodoaldo Pinto wrote: Of course it would be even better if we could pass parameters to the functions changing its behavior such as sunday/monday as the first day or 0-1 as the first day. FWIW, it seems most things that support changing first day of the week to be something other than default do it through a global setting. ISTM that would be much less error prone than having to make sure you used it in all your function calls (not to mention a lot less typing...) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 Yes, I think you are correct. Regards, Clodoaldo Pinto ---(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] extract (dow/week from date)
There were two hints by readers about this yearweek issue at the 7.4.8 manual:
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html
The first by Daniel Grace graced AT monroe.wednet.edu 21 May 2004 0:39:19
CREATE OR REPLACE FUNCTION yearweek(TIMESTAMP WITH TIME ZONE)
RETURNS INT LANGUAGE plpgsql IMMUTABLE STRICT
AS '
DECLARE
t TIMESTAMP;
BEGIN
t := $1::date - EXTRACT(dow FROM $1::date)::int;
RETURN EXTRACT(year FROM t)*100 + EXTRACT(week FROM t);
END;';
And the second by Wolfgang Diestelkamp wolfgang AT dndata.de
09 Mar 2005 15:44:05
CREATE OR REPLACE FUNCTION yearweek(TIMESTAMP)
RETURNS INT LANGUAGE plpgsql IMMUTABLE STRICT
AS '
DECLARE
t TIMESTAMP;
d INTEGER;
BEGIN
d := EXTRACT(dow FROM $1::date)::int;
t := $1::date -
CASE
WHEN d = 0 THEN 6
ELSE d - 1
END;
RETURN EXTRACT(year FROM t)*100 + EXTRACT(week FROM t);
END;';
While this second attempt improved on the iso day of the week it was
broken about the year.
Here is the comparison of my yearweek () function and the previous
two. The isodow () uses the construct suggested by Tom Lane.
create or replace function isodow (timestamp with time zone)
returns int language plpgsql immutable strict
as '
declare
begin
return (extract (dow from $1) +6)::int % 7;
end;';
create or replace function yearweek (timestamp with time zone)
returns int language plpgsql immutable strict
as '
declare
date timestamp with time zone = $1;
fyear integer;
begin
fyear := extract (year from date - ((isodow (date) -3)::text || \'
day\')::interval);
return fyear * 100 + extract (week from date);
end;';
drop table dates;
create table dates (date timestamp);
insert into dates values ('1990-01-01');
insert into dates values ('1990-12-31');
insert into dates values ('1991-01-01');
insert into dates values ('1991-12-31');
insert into dates values ('1992-01-01');
insert into dates values ('1992-12-31');
insert into dates values ('1993-01-01');
insert into dates values ('1993-12-31');
insert into dates values ('1994-01-01');
insert into dates values ('1994-12-31');
insert into dates values ('1995-01-01');
insert into dates values ('1995-12-31');
insert into dates values ('1996-01-01');
insert into dates values ('1996-12-31');
insert into dates values ('1997-01-01');
insert into dates values ('1997-12-31');
insert into dates values ('1998-01-01');
insert into dates values ('1998-12-31');
insert into dates values ('1999-01-01');
insert into dates values ('1999-12-31');
insert into dates values ('2000-01-01');
insert into dates values ('2000-12-31');
insert into dates values ('2001-01-01');
insert into dates values ('2001-12-31');
insert into dates values ('2002-01-01');
insert into dates values ('2002-12-31');
insert into dates values ('2003-01-01');
insert into dates values ('2003-12-31');
insert into dates values ('2004-01-01');
insert into dates values ('2004-12-31');
insert into dates values ('2005-01-01');
insert into dates values ('2005-01-02');
insert into dates values ('2005-01-03');
insert into dates values ('2005-01-04');
insert into dates values ('2005-01-05');
insert into dates values ('2005-01-06');
insert into dates values ('2005-01-07');
insert into dates values ('2005-01-08');
insert into dates values ('2005-01-09');
select date,
to_char (date, 'Dy') as cday,
isodow (date) as isod,
yearweek (date) as yw_Clo,
yearweek_Daniel (date) as yw_Dan,
yearweek_Wolfgang (date) as yw_Wol
from dates
where yearweek (date) yearweek_Wolfgang (date)
order by date;
date | cday | isod | yw_clo | yw_dan | yw_wol
-+--+--+++
1990-12-31 00:00:00 | Mon |0 | 199101 | 199052 | 199001
1991-01-01 00:00:00 | Tue |1 | 199101 | 199052 | 199001
1991-12-31 00:00:00 | Tue |1 | 199201 | 199152 | 199101
1992-01-01 00:00:00 | Wed |2 | 199201 | 199152 | 199101
1996-12-31 00:00:00 | Tue |1 | 199701 | 199652 | 199601
1997-01-01 00:00:00 | Wed |2 | 199701 | 199652 | 199601
1997-12-31 00:00:00 | Wed |2 | 199801 | 199752 | 199701
1998-01-01 00:00:00 | Thu |3 | 199801 | 199752 | 199701
2001-12-31 00:00:00 | Mon |0 | 200201 | 200152 | 200101
2002-01-01 00:00:00 | Tue |1 | 200201 | 200152 | 200101
2002-12-31 00:00:00 | Tue |1 | 200301 | 200252 | 200201
2003-01-01 00:00:00 | Wed |2 | 200301 | 200252 | 200201
2003-12-31 00:00:00 | Wed |2 | 200401 | 200352 | 200301
2004-01-01 00:00:00 | Thu |3 | 200401 | 200352 | 200301
(14 rows)
Regards, Clodoaldo Pinto
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] extract (dow/week from date)
2005/8/21, Stephan Szabo [EMAIL PROTECTED]:
On Sat, 20 Aug 2005, Tom Lane wrote:
Clodoaldo Pinto [EMAIL PROTECTED] writes:
I'm ordering by date just to show that sunday, the 0th day of the
week, is the last day of a given week, which is not what I need.
extract(week) follows the ISO definition of week, which is pretty
strange anyway, but in particular it says that weeks start on Monday.
extract(dow) follows a different convention. There's not a lot we
can do about this --- we're certainly not going to change extract(week),
and I can't see changing extract(dow) either.
Instead of change the existing ones, couldn't we add a new extract format
for iso day of week that returns 1-7 for monday-sunday that would be
consistent with the week definition?
It would work for me. The problem is not if is sunday or monday the
first day of the week, but to make all days of the week from extract
(dow) (or a new extract (isodow)) fit into the same week from extract
(week). It does not happen now:
drop table dates;
create table dates (date timestamp);
insert into dates values ('2004-12-31');
insert into dates values ('2005-01-01');
insert into dates values ('2005-01-02');
insert into dates values ('2005-01-03');
insert into dates values ('2005-01-04');
insert into dates values ('2005-01-05');
insert into dates values ('2005-01-06');
insert into dates values ('2005-01-07');
insert into dates values ('2005-01-08');
insert into dates values ('2005-01-09');
select date,
to_char (date, 'Dy') as cday,
extract (week from date) as eweek,
extract (dow from date) as edow,
to_char (date, 'WW')::int as cweek,
to_char (date, 'D')::int as cdow
from dates
order by date;
date | cday | eweek | edow | cweek | cdow
-+--+---+--+---+--
2004-12-31 00:00:00 | Fri |53 |5 |53 |6
2005-01-01 00:00:00 | Sat |53 |6 | 1 |7
2005-01-02 00:00:00 | Sun |53 |0 | 1 |1
2005-01-03 00:00:00 | Mon | 1 |1 | 1 |2
2005-01-04 00:00:00 | Tue | 1 |2 | 1 |3
2005-01-05 00:00:00 | Wed | 1 |3 | 1 |4
2005-01-06 00:00:00 | Thu | 1 |4 | 1 |5
2005-01-07 00:00:00 | Fri | 1 |5 | 1 |6
2005-01-08 00:00:00 | Sat | 1 |6 | 2 |7
2005-01-09 00:00:00 | Sun | 1 |0 | 2 |1
(10 rows)
There is the same mismatch in to_char ('WW') related to to_char ('D')
Of course it would be even better if we could pass parameters to the
functions changing its behavior such as sunday/monday as the first day
or 0-1 as the first day.
Regards, Clodoaldo Pinto
---(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] extract (dow/week from date)
2005/8/21, Tom Lane [EMAIL PROTECTED]:
Stephan Szabo [EMAIL PROTECTED] writes:
I think something like:
(CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
It's really not that hard:
(extract(dow from date) + 6) % 7
You can rotate to any week-start day you like by substituting different
things for 6.
regards, tom lane
Not obvious as extract (isodow) but good enough for me. Thanks.
But then i also need to order by year-week the same way mysql's
yearweek (date, 3) so i did:
drop table dates;
create table dates (date timestamp);
insert into dates values ('1990-01-01');
insert into dates values ('1990-12-31');
insert into dates values ('1991-01-01');
insert into dates values ('1991-12-31');
insert into dates values ('1992-01-01');
insert into dates values ('1992-12-31');
insert into dates values ('1993-01-01');
insert into dates values ('1993-12-31');
insert into dates values ('1994-01-01');
insert into dates values ('1994-12-31');
insert into dates values ('1995-01-01');
insert into dates values ('1995-12-31');
insert into dates values ('1996-01-01');
insert into dates values ('1996-12-31');
insert into dates values ('1997-01-01');
insert into dates values ('1997-12-31');
insert into dates values ('1998-01-01');
insert into dates values ('1998-12-31');
insert into dates values ('1999-01-01');
insert into dates values ('1999-12-31');
insert into dates values ('2000-01-01');
insert into dates values ('2000-12-31');
insert into dates values ('2001-01-01');
insert into dates values ('2001-12-31');
insert into dates values ('2002-01-01');
insert into dates values ('2002-12-31');
insert into dates values ('2003-01-01');
insert into dates values ('2003-12-31');
insert into dates values ('2004-01-01');
insert into dates values ('2004-12-31');
insert into dates values ('2005-01-01');
insert into dates values ('2005-01-02');
insert into dates values ('2005-01-03');
insert into dates values ('2005-01-04');
insert into dates values ('2005-01-05');
insert into dates values ('2005-01-06');
insert into dates values ('2005-01-07');
insert into dates values ('2005-01-08');
insert into dates values ('2005-01-09');
select date,
to_char (date, 'Dy') as cday,
extract (year from date - cast (((extract (dow from date) +6)::int %
7 -3)::text || ' day' as interval)) as yearweek,
extract (week from date) as eweek,
(extract (dow from date) +6)::int % 7 as edow
from dates
order by date;
date | cday | yearweek | eweek | edow
-+--+--+---+--
1990-01-01 00:00:00 | Mon | 1990 | 1 |0
1990-12-31 00:00:00 | Mon | 1991 | 1 |0
1991-01-01 00:00:00 | Tue | 1991 | 1 |1
1991-12-31 00:00:00 | Tue | 1992 | 1 |1
1992-01-01 00:00:00 | Wed | 1992 | 1 |2
1992-12-31 00:00:00 | Thu | 1992 |53 |3
1993-01-01 00:00:00 | Fri | 1992 |53 |4
1993-12-31 00:00:00 | Fri | 1993 |52 |4
1994-01-01 00:00:00 | Sat | 1993 |52 |5
1994-12-31 00:00:00 | Sat | 1994 |52 |5
1995-01-01 00:00:00 | Sun | 1994 |52 |6
1995-12-31 00:00:00 | Sun | 1995 |52 |6
1996-01-01 00:00:00 | Mon | 1996 | 1 |0
1996-12-31 00:00:00 | Tue | 1997 | 1 |1
1997-01-01 00:00:00 | Wed | 1997 | 1 |2
1997-12-31 00:00:00 | Wed | 1998 | 1 |2
1998-01-01 00:00:00 | Thu | 1998 | 1 |3
1998-12-31 00:00:00 | Thu | 1998 |53 |3
1999-01-01 00:00:00 | Fri | 1998 |53 |4
1999-12-31 00:00:00 | Fri | 1999 |52 |4
2000-01-01 00:00:00 | Sat | 1999 |52 |5
2000-12-31 00:00:00 | Sun | 2000 |52 |6
2001-01-01 00:00:00 | Mon | 2001 | 1 |0
2001-12-31 00:00:00 | Mon | 2002 | 1 |0
2002-01-01 00:00:00 | Tue | 2002 | 1 |1
2002-12-31 00:00:00 | Tue | 2003 | 1 |1
2003-01-01 00:00:00 | Wed | 2003 | 1 |2
2003-12-31 00:00:00 | Wed | 2004 | 1 |2
2004-01-01 00:00:00 | Thu | 2004 | 1 |3
2004-12-31 00:00:00 | Fri | 2004 |53 |4
2005-01-01 00:00:00 | Sat | 2004 |53 |5
2005-01-02 00:00:00 | Sun | 2004 |53 |6
2005-01-03 00:00:00 | Mon | 2005 | 1 |0
2005-01-04 00:00:00 | Tue | 2005 | 1 |1
2005-01-05 00:00:00 | Wed | 2005 | 1 |2
2005-01-06 00:00:00 | Thu | 2005 | 1 |3
2005-01-07 00:00:00 | Fri | 2005 | 1 |4
2005-01-08 00:00:00 | Sat | 2005 | 1 |5
2005-01-09 00:00:00 | Sun | 2005 | 1 |6
(39 rows)
I am not sure it is bullet proof.
If no one comes up with something simpler, it looks like extract
(yearweek) would be welcome.
Regards, Clodoaldo Pinto
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
[GENERAL] extract (dow/week from date)
The extract (dow from date) function returns 0 for Sunday (nice).
My problem is that Sunday is the last day of the week according to
extract (week from date). Is it the expected behavior?
teste=# create table dates (date timestamp);
CREATE TABLE
teste=# insert into dates values ('2005-08-08');
INSERT 0 1
teste=# insert into dates values ('2005-08-09');
INSERT 0 1
teste=# insert into dates values ('2005-08-10');
INSERT 0 1
teste=# insert into dates values ('2005-08-11');
INSERT 0 1
teste=# insert into dates values ('2005-08-12');
INSERT 0 1
teste=# insert into dates values ('2005-08-13');
INSERT 0 1
teste=# insert into dates values ('2005-08-14');
INSERT 0 1
teste=# select date, extract (week from date) as week, extract (dow
from date) as dow
teste-# from dates
teste-# order by date;
date | week | dow
-+--+-
2005-08-08 00:00:00 | 32 | 1
2005-08-09 00:00:00 | 32 | 2
2005-08-10 00:00:00 | 32 | 3
2005-08-11 00:00:00 | 32 | 4
2005-08-12 00:00:00 | 32 | 5
2005-08-13 00:00:00 | 32 | 6
2005-08-14 00:00:00 | 32 | 0
(7 rows)
In mysql the date functions work as I need it:
order by yearweek(day, 2) desc, dayofweek(day);
Regards,
Clodoaldo Pinto
create table dates (date timestamp);
insert into dates values ('2005-08-08');
insert into dates values ('2005-08-09');
insert into dates values ('2005-08-10');
insert into dates values ('2005-08-11');
insert into dates values ('2005-08-12');
insert into dates values ('2005-08-13');
insert into dates values ('2005-08-14');
select date, extract (week from date) as week, extract (dow from date) as dow
from dates
order by date;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] extract (dow/week from date)
2005/8/20, Bruce Momjian pgman@candle.pha.pa.us:
Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
I'm ordering by date just to show that sunday, the 0th day of the
week, is the last day of a given week, which is not what I need.
I'm migrating a site from mysql to postgres and the logic of the table
at the page works for Sunday as the first day of the week. I will make
the pg query produce the same behavior as the mysql query but the pg
function behavior seems confusing.
Note how are the dates sorted at the table:
http://fahstats.com/tp.php?t=13802
Regards, Clodoaldo Pinto
2005/8/20, Bruce Momjian pgman@candle.pha.pa.us:
Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
---
Clodoaldo Pinto wrote:
The extract (dow from date) function returns 0 for Sunday (nice).
My problem is that Sunday is the last day of the week according to
extract (week from date). Is it the expected behavior?
teste=# create table dates (date timestamp);
CREATE TABLE
teste=# insert into dates values ('2005-08-08');
INSERT 0 1
teste=# insert into dates values ('2005-08-09');
INSERT 0 1
teste=# insert into dates values ('2005-08-10');
INSERT 0 1
teste=# insert into dates values ('2005-08-11');
INSERT 0 1
teste=# insert into dates values ('2005-08-12');
INSERT 0 1
teste=# insert into dates values ('2005-08-13');
INSERT 0 1
teste=# insert into dates values ('2005-08-14');
INSERT 0 1
teste=# select date, extract (week from date) as week, extract (dow
from date) as dow
teste-# from dates
teste-# order by date;
date | week | dow
-+--+-
2005-08-08 00:00:00 | 32 | 1
2005-08-09 00:00:00 | 32 | 2
2005-08-10 00:00:00 | 32 | 3
2005-08-11 00:00:00 | 32 | 4
2005-08-12 00:00:00 | 32 | 5
2005-08-13 00:00:00 | 32 | 6
2005-08-14 00:00:00 | 32 | 0
(7 rows)
In mysql the date functions work as I need it:
order by yearweek(day, 2) desc, dayofweek(day);
Regards,
Clodoaldo Pinto
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] kernels performance difference
I have a kernel version problem: 2.6.10 and 2.6.11 are much slower than 2.6.9 in Fedora Core 3 within a certain function. One machine runs 8.03, 512 MB, XP2100. Times: 2.6.11 and 2.6.10 - 83 minutes. 2.6.9 - 32 minutes In another FC3 machine, 8.0.1, 1 GB, P4 2.4, the times are 35 and 5 minutes respectively. I think memory size and a faster HD are probably the cause for the difference between machines. But I don't care about the machines differences but I care about the kernel differences. Due to the huge performance difference I'm stuck with 2.6.9. Since I'm not knowledgeable enough to find the source of the kernel problem I'm asking if someone could try it. I prepared the files to download from http://txt.fahstats.com : restorecontext.sh - creates a test database and loads a table and a function testit.sh - runs the function usuarios_producao.txt.bz2 - table dump 3.9 MB create_function_update_ranking_usuarios.sql - function postgresql.conf To test run restorecontext.sh then testit.sh By the way, if someone knows a better way to update a ranking table, please let me know. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Out of memory error
After much work i was able to do it: The psql script was changed to: \o '/KakaoStats/bak/groupdup1.txt' select data, usuario, sum(pontos), sum(wus) from usuarios where data = 2056 group by data, usuario ; \o \o '/KakaoStats/bak/groupdup2.txt' select data, usuario, sum(pontos), sum(wus) from usuarios where data 2056 and data = 2400 group by data, usuario ; \o ...snip... \o '/KakaoStats/bak/groupdup8.txt' select data, usuario, sum(pontos), sum(wus) from usuarios where data 2600 group by data, usuario ; \o Then I had 8 files which i concateneted and then i tried in pgadmin3: truncate table usuarios2; vacuum usuarios2; copy usuarios2 ( data, usuario, pontos, wus ) from '/KakaoStats/bak/groupdup.txt' ; Again out of memory error. Tried to break it the same way i did with the select: truncate table usuarios2; vacuum usuarios2; copy usuarios2 ( data, usuario, pontos, wus ) from '/KakaoStats/bak/groupdup1.txt' ; ...snip... copy usuarios2 ( data, usuario, pontos, wus ) from '/KakaoStats/bak/groupdup8.txt' ; And again out of memory error. ??? Then I added begin and commit to each of the copy commands and it worked !!! truncate table usuarios2; vacuum usuarios2; begin; copy usuarios2 ( data, usuario, pontos, wus ) from '/KakaoStats/bak/groupdup1.txt' ; commit; ...snip... begin; copy usuarios2 ( data, usuario, pontos, wus ) from '/KakaoStats/bak/groupdup8.txt' ; commit; This was with pgadmin3. Does pgadmin3 turns the whole script in one only transaction? My hardware: Asus a7v8x-mx motherboard, 80GB Maxtor HD, XP2600/333, 2x512MB memory at the most conservative settings. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Out of memory error
On Wed, 09 Feb 2005 13:18:44 +0100, Tino Wildenhain [EMAIL PROTECTED] wrote: Will this go into the same database? Yes, this *went* into the same database. If so, you should probably use: CREATE TABLE targettable AS select data, usuario, sum(pontos) as sum_pontos, sum(wus) as sum_wus from usuarios group by data, usuario; Didn't try the create route. I have already performed the grouping. As the table is big, each step takes very long and i don't have more time to investigate further. Regards, Clodoaldo Pinto HTH Tino ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of memory error
I did: # /sbin/sysctl -w vm.overcommit_memory=2 following http://www.postgresql.org/docs/7.4/static/kernel-resources.html#AEN17068 And got the same error: ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: PL/pgSQL function group_dup line 9 at SQL statement The difference now is that the process was killed before overcommiting. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Out of memory error
On Tue, 8 Feb 2005 09:06:38 -0200, Clodoaldo Pinto [EMAIL PROTECTED] wrote: I did: # /sbin/sysctl -w vm.overcommit_memory=2 following http://www.postgresql.org/docs/7.4/static/kernel-resources.html#AEN17068 And got the same error: ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: PL/pgSQL function group_dup line 9 at SQL statement The difference now is that the process was killed before overcommiting. Regards, Clodoaldo Pinto This is the log file content: TopMemoryContext: 32768 total in 3 blocks; 3720 free (1 chunks); 29048 used TopTransactionContext: 8192 total in 1 blocks; 2432 free (0 chunks); 5760 used SPI Exec: 8192 total in 1 blocks; 8064 free (0 chunks); 128 used ExecutorState: 8192 total in 1 blocks; 5352 free (1 chunks); 2840 used ExecutorState: 35643416 total in 14 blocks; 3999744 free (16 chunks); 31643672 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: 8192 total in 1 blocks; 8128 free (1 chunks); 64 used ExprContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Proc: 8192 total in 1 blocks; 7736 free (0 chunks); 456 used SPI TupTable: 8192 total in 1 blocks; 7024 free (0 chunks); 1168 used SPI TupTable: 8192 total in 1 blocks; 3832 free (0 chunks); 4360 used DeferredTriggerXact: 1796202496 total in 224 blocks; 3752 free (10 chunks); 1796198744 used SPI Plan: 7168 total in 3 blocks; 32 free (0 chunks); 7136 used SPI Plan: 3072 total in 2 blocks; 1280 free (0 chunks); 1792 used SPI Plan: 1024 total in 1 blocks; 672 free (0 chunks); 352 used MessageContext: 8192 total in 1 blocks; 6696 free (1 chunks); 1496 used PortalMemory: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used PortalHeapMemory: 3072 total in 2 blocks; 1272 free (0 chunks); 1800 used ExecutorState: 8192 total in 1 blocks; 6440 free (1 chunks); 1752 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used PortalHeapMemory: 1024 total in 1 blocks; 912 free (0 chunks); 112 used ExecutorState: 8192 total in 1 blocks; 7064 free (1 chunks); 1128 used ExprContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used CacheMemoryContext: 516096 total in 6 blocks; 130408 free (19 chunks); 385688 used ndx_usuarios_data: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_description_o_c_o_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_depend_depender_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_depend_reference_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_database_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_database_datname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_conversion_default_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used pg_group_sysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_namespace_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_proc_proname_args_nsp_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used pg_opclass_am_name_nsp_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used pg_operator_oid_index: 1024 total in 1 blocks; 640
[GENERAL] Out of memory error
I had an Out of Memory error while running this query in psql over a 170 million rows table: select data, usuario, sum(pontos), sum(wus) from usuarios group by data, usuario FC2 PG 7.4.6 1GB mem Linux s1 2.6.9-1.11_FC2 #1 Sun Jan 2 15:49:30 EST 2005 i686 athlon i386 GNU/Linux logfile: LOG: checkpoints are occurring too frequently (2 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. ... LOG: recycled transaction log file 01D7009A LOG: removing transaction log file 01D70097 ... ERROR: canceling query due to user request LOG: could not send data to client: Broken pipe LOG: unexpected EOF on client connection postgresql.conf (everything else default): shared_buffers = 3000 sort_mem = 131072 vacuum_mem = 131072 max_fsm_pages = 30 fsync = false wal_buffers = 256 checkpoint_segments = 32 extra_float_digits = 2 lc_messages = 'C' lc_monetary = 'pt_BR.UTF-8' lc_numeric = 'pt_BR.UTF-8' lc_time = 'pt_BR.UTF-8' var/log/messages: Feb 7 15:29:32 s1 su(pam_unix)[24675]: session closed for user root Feb 7 16:30:22 s1 kernel: oom-killer: gfp_mask=0x1d2 Feb 7 16:30:22 s1 kernel: Mem-info: Feb 7 16:30:22 s1 kernel: DMA per-cpu: Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 2, high 6, batch 1 Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 2, batch 1 Feb 7 16:30:22 s1 kernel: Normal per-cpu: Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 32, high 96, batch 16 Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 32, batch 16 Feb 7 16:30:22 s1 kernel: HighMem per-cpu: Feb 7 16:30:22 s1 kernel: cpu 0 hot: low 12, high 36, batch 6 Feb 7 16:30:22 s1 kernel: cpu 0 cold: low 0, high 12, batch 6 Feb 7 16:30:22 s1 kernel: Feb 7 16:30:22 s1 kernel: Free pages:1280kB (120kB HighMem) Feb 7 16:30:23 s1 kernel: Active:132041 inactive:116634 dirty:0 writeback:0 unstable:0 free:320 slab:2226 mapped:246816 pagetables:1456 Feb 7 16:30:23 s1 kernel: DMA free:16kB min:16kB low:32kB high:48kB active:6616kB inactive:6068kB present:16384kB Feb 7 16:30:23 s1 kernel: protections[]: 0 0 0 Feb 7 16:30:23 s1 kernel: Normal free:1144kB min:936kB low:1872kB high:2808kB active:445348kB inactive:426140kB present:901120kB Feb 7 16:30:23 s1 kernel: protections[]: 0 0 0 Feb 7 16:30:24 s1 kernel: HighMem free:120kB min:128kB low:256kB high:384kB active:76200kB inactive:34328kB present:114624kB Feb 7 16:30:24 s1 kernel: protections[]: 0 0 0 Feb 7 16:30:24 s1 kernel: DMA: 0*4kB 0*8kB 1*16kB 0*32kB 0*64kB 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 16kB Feb 7 16:30:24 s1 kernel: Normal: 32*4kB 9*8kB 3*16kB 2*32kB 1*64kB 0*128kB 1*256kB 1*512kB 0*1024kB 0*2048kB 0*4096kB = 1144kB Feb 7 16:30:24 s1 kernel: HighMem: 0*4kB 1*8kB 1*16kB 1*32kB 1*64kB 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 120kB Feb 7 16:30:25 s1 kernel: Swap cache: add 422343, delete 421792, find 12780/16340, race 0+0 Feb 7 16:30:25 s1 kernel: Free swap:0kB Feb 7 16:30:25 s1 kernel: 258032 pages of RAM Feb 7 16:30:25 s1 kernel: 28656 pages of HIGHMEM Feb 7 16:30:25 s1 kernel: 3138 reserved pages Feb 7 16:30:26 s1 kernel: 14914 pages shared Feb 7 16:30:26 s1 kernel: 551 pages swap cached Feb 7 16:30:26 s1 kernel: Out of Memory: Killed process 30679 (psql). Any advice on how to avoid it? Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Out of memory error
On Mon, 07 Feb 2005 09:32:47 -0800, Joshua D. Drake [EMAIL PROTECTED] wrote: Any advice on how to avoid it? Use a cursor. Same thing using a cursor: declare rdata record; begin truncate table usuarios2; for rdata in select distinct on (data) data from usuarios loop insert into usuarios2 ( data, usuario, pontos, wus ) select data, usuario, sum(pontos), sum(wus) from usuarios where data = rdata.data group by data, usuario ; end loop; return; end; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Out of memory error
On Mon, 07 Feb 2005 13:51:46 -0800, Joshua D. Drake [EMAIL PROTECTED] wrote: Well your first email didn't explain that you were doing the below :) In the first email I was not doing the insert. I was executing a psql script: $ psql -e -f groupdup.psql ks2 This was the groupdup.psql script: \pset format unaligned \pset fieldsep '\t' \pset tuples_only \o '/KakaoStats/bak/groupdup.txt' select data, usuario, sum(pontos), sum(wus) from usuarios group by data, usuario ; \o \pset tuples_only \pset format aligned I was planning to insert the file with copy from. Is this the entire function? Yes, it is the entire function. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to delete duplicate rows?
[EMAIL PROTECTED] wrote: Can you not add a serial or sequence column to the table for the purposes of the de-dupe? Then create an index on that column in one operation at the end and use that in the way that you would use Oracle's rowid from the examples? Yes. It could work. I have a two hours window to do it. Creating the index alone takes about one hour... Clodoaldo Pinto wrote: Method 3 also relies in the row id. If no one can help I will do this: Insert the distinct rows in a temporary table. Drop the index. Insert into the original from the temporary. How did i wrote it? The correct is: Insert the distinct rows in a temporary table. Truncate the original. Drop the index. Insert into the original from the temporary. Recreate the index. Clodoaldo ---(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
[GENERAL] How to delete duplicate rows?
This one must be obvious for most here. I have a 170 million rows table from which I want to eliminate duplicate would be keys and leave only uniques. I found a query in http://www.jlcomp.demon.co.uk/faq/duplicates.html for the oracle database but can't figure out how to refer to the row id in postgresql: delete from test where rowid not in (select min(rowid) from test group by a,b); How to refer to the row id? Any better way to do it? Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 7.4.6 FC2 MUCH slower from 2.6.9-1.11 to 2.6.10-1.8
Just tried 2.6.10-1.9_FC2 with the same bad results. Am I the only one experiencing this? What would be special? The hardware? It has nothing special, a popular Asus motherboard, 1GB memory, a modern Maxtor HD and nothing else. Clodoaldo --- Clodoaldo Pinto [EMAIL PROTECTED] escreveu: 31 minutes in 2.6.10-1.8: select kstime(), update_ranking_usuarios(), kstime(); kstime| update_ranking_usuarios | kstime -+-+- 2005-01-13 20:27:56 | | 2005-01-13 20:58:46 (1 row) 5 minutes in 2.6.9-1.11: select kstime(), update_ranking_usuarios(), kstime(); kstime| update_ranking_usuarios | kstime -+-+- 2005-01-13 23:24:30 | | 2005-01-13 23:29:26 (1 row) The function: CREATE OR REPLACE FUNCTION update_ranking_usuarios() RETURNS void AS 'declare linha record; rank integer; begin rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_0 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 + (( pontos_0 - pontos_7) / 7) desc loop rank := rank + 1; update usuarios_producao set rank_24 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 + pontos_0 - pontos_7 desc loop rank := rank + 1; update usuarios_producao set rank_7 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 + (( pontos_0 - pontos_7) * 30 / 7) desc loop rank := rank + 1; update usuarios_producao set rank_30 = rank where usuario = linha.usuario ; end loop; return; end;' LANGUAGE 'plpgsql' STABLE; Part of postgresql.conf: shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each sort_mem = 49152# min 64, size in KB vacuum_mem = 32768 # min 1024, size in KB vacuum_mem = 32768 max_fsm_pages = 30 # min max_fsm_relations*16, 6 bytes each fsync = false wal_buffers = 256 checkpoint_segments = 32# in logfile segments, min 1, 16MB each The log file shows only a long list of: LOG: recycled transaction log file 010A00D0 LOG: recycled transaction log file 010A00CF LOG: recycled transaction log file 010A00C9 ... Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 8: explain analyze is your friend ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] 7.4.6 FC2 MUCH slower from 2.6.9-1.11 to 2.6.10-1.8
31 minutes in 2.6.10-1.8: select kstime(), update_ranking_usuarios(), kstime(); kstime| update_ranking_usuarios | kstime -+-+- 2005-01-13 20:27:56 | | 2005-01-13 20:58:46 (1 row) 5 minutes in 2.6.9-1.11: select kstime(), update_ranking_usuarios(), kstime(); kstime| update_ranking_usuarios | kstime -+-+- 2005-01-13 23:24:30 | | 2005-01-13 23:29:26 (1 row) The function: CREATE OR REPLACE FUNCTION update_ranking_usuarios() RETURNS void AS 'declare linha record; rank integer; begin rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_0 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 + (( pontos_0 - pontos_7) / 7) desc loop rank := rank + 1; update usuarios_producao set rank_24 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 + pontos_0 - pontos_7 desc loop rank := rank + 1; update usuarios_producao set rank_7 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao where not anonymous --and n_time != 446 order by pontos_0 + (( pontos_0 - pontos_7) * 30 / 7) desc loop rank := rank + 1; update usuarios_producao set rank_30 = rank where usuario = linha.usuario ; end loop; return; end;' LANGUAGE 'plpgsql' STABLE; Part of postgresql.conf: shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each sort_mem = 49152# min 64, size in KB vacuum_mem = 32768 # min 1024, size in KB vacuum_mem = 32768 max_fsm_pages = 30 # min max_fsm_relations*16, 6 bytes each fsync = false wal_buffers = 256 checkpoint_segments = 32# in logfile segments, min 1, 16MB each The log file shows only a long list of: LOG: recycled transaction log file 010A00D0 LOG: recycled transaction log file 010A00CF LOG: recycled transaction log file 010A00C9 ... Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Postgresql didn't start after power failure
There was a power failure and then the postgresql service didn't start on system restart: System restart after power failure: Jan 12 16:49:06 s1 syslogd 1.4.1: restart. Jan 12 16:49:18 s1 su(pam_unix)[2098]: session opened for user postgres by (uid=0) Jan 12 16:49:18 s1 su(pam_unix)[2098]: session closed for user postgres Jan 12 16:49:19 s1 postgresql: Iniciando serviço postgresql : failed When I manually rebooted the system postgres restarted: Jan 12 18:40:42 s1 su(pam_unix)[2083]: session opened for user postgres by (uid=0) Jan 12 18:40:43 s1 su(pam_unix)[2083]: session closed for user postgres Jan 12 18:40:44 s1 postgresql: Iniciando serviço postgresql : succeeded /var/log/pgsql is empty and is chmoded as executable (?). It is an Anaconda install in FC2. Now up to 7.4.6. The last activity before power failure was a vacuum full and after that nothing at all for more than one hour. Is there anyway to know why did it not start and prevent it to happen again? How to configure it to write a log at system boot? Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql website issues.
--- Mike Cox [EMAIL PROTECTED] escreveu: Am I the only one who has trouble reading the website? Some of the fonts are way too small, especially the Mirrors | Donate | Contact set. Here with Linux/FireFox and a 19 monitor at 1280x960 the fonts sizes are adequate. The other problem is that it is too light. My eyes strain to read the text as the background is white, and the text is also a light color. I think the light blue is not adequate. It is kind of shiny and hard to focus. And I prefer darker backgrounds. Also, on the news area, when one clicks on PostgresSQL performs well on 1TB database, the copyright notice along with the privacy policy are on top of the side menu, covering it. I used Mozilla 1.7 to view the site. Is is Ok here. Probably already fixed. As a former professional graphics designer, the new postgresql site is also not balanced in terms of color. I have the feeling (not the knowledge) of something wrong with the colors. I blame the light blue. Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PG8 final when
--- Együd Csaba [EMAIL PROTECTED] escreveu: Dear Developers, when do you plan to announce the final version of 8.0.0? As far as I can remember it was promised by 15. dec. Could you please point to where was it promised? Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pl/pgsql oddity
--- Tom Lane [EMAIL PROTECTED] escreveu: Neil Conway [EMAIL PROTECTED] writes: Tom also suggested just adding 'elseif' as an alternative for 'elsif'. That sounds like it would be worth doing. I think we should go ahead and do that for 8.0. I'm getting tired of reading reports that stem from this mistake (I think this is the third one in the past month ...). I can't see any real downside to accepting both spellings, can you? regards, tom lane I made the exact same mistake and it took me a good chunk of energy to figure it out. I didn't report it to the list. When one look at the manual it is very easy to read elseif in instead of elsif. Regards, Clodoaldo Pinto ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ ---(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] postgresql and javascript
Javascript is not only client side. IIS has two script languages installed as default: VBScript and JScript(MS version of Javascript). Indeed at my work I have already written hundreds of javascript server pages. We use MS SQL Server. I understand Robert Stewart's question, and the only advice I have is look at the ADO reference site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscmicrosoftadoprogrammersreference.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefodbcprovspec.asp If Postgresql can expose an ODBC interface then it will work. Regards, Clodoaldo Pinto --- Najib Abi Fadel [EMAIL PROTECTED] escreveu: Hi i don't see any practical use of javascript with Postgresql, since a java script is on the client side. Unless you want each client machine to have its own database ! Najib. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 5:27 PM Subject: [GENERAL] postgresql and javascript Does anyone know how to connect javascript to a postgresql database Please send example if anyone has done it thanks Robert Stewart Network Eng Commonwealth Office of Technology Finance and Administration Cabinet 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED] ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] ISO week and extract(dow from source)
From the 7.4.2 manual, Date/Time Functions and operators, 9.8.1. EXTRACT, date_part: week: (The ISO-8601 week starts on Monday.) dow: The day of the week (0 - 6; Sunday is 0) Why is Sunday dow 0, if the iso week starts on Monday and the iso week is used in extract(week from source)? Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Displaying more than six digits from a real number
--- Tom Lane [EMAIL PROTECTED] escreveu: Clodoaldo Pinto [EMAIL PROTECTED] writes: The 7.4.2 manual says the precision of a real number is 6 decimal digits. Is it possible to force the display of more than six decimal digits of a real number Perhaps the extra_float_digits runtime parameter is what you want? (You are of course aware that there aren't really more than six useful digits there... or if you're not, maybe you should stay away from float and use numeric.) That is exactly what I wanted. Yes, I'm aware. But I wonder how is the 7th digit right? At least with the few tests I did it was never wrong. I will have to change that column to float8 but for now it is good enough. Thanks --- Bruno Wolff III [EMAIL PROTECTED] escreveu: I think you can use the extra_float_digits GUC variable to do what you want. Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Displaying more than six digits from a real number
The 7.4.2 manual says the precision of a real number is 6 decimal digits. Is it possible to force the display of more than six decimal digits of a real number whithout using to_char() or casting? Using 7.4.2, FC2. create table test (f4 float4) without oids; insert into test values (4877852); select f4, to_char(f4, 'FM999'), cast(f4 as int) from test; f4 | to_char | f4 -+-+- 4.87785e+06 | 4877852 | 4877852 Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_restore: error returned by PQputline
chunks); 0 used DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: COPY usuarios, line 19646042: 428279 125110362 11 27 Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - navegue de graça com conexão de qualidade! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How copy a new line char to a file?
Hi all,
I should have explained it better:
Why don't I just insert each line in the table and let copy to do its
thing inserting a new line char (lf) at the end of each line?
Because I need the file lines to be in a certain order. Copy to will
copy the lines in its own order.
Until now I'm inserting html pages in a single line table without a lf
and copying then to files. Here is the result:
http://planeta.terra.com.br/informatica/kakaostats/
http://planeta.terra.com.br/informatica/kakaostats/t13802.html
Right click the page and click view source and there it is, a single
giant line.
The problem with mixing html and pl/pgsql is that it becomes a big
mess. It is very hard to evolve the code and understand it.
So I choose to not output html anymore but cvs text and build the page
with php. Advantages: much simpler pl/pgsql code and upload size 80%
smaller.
Lines finishing with a lf are much easier to parse in php (fgetcsv).
This and the need to have then ordered is why i need to insert lfs in
the single line table from which to copy to file.
I have solved it with a simple python script but it is one more step
and is not fail safe. Where there would be a lf I insert the string
newLine. Then in the python script I change it to an actual lf char.
But in the remote possibility that there is or there will be this
string in the database (600+ thousand distinct user names) then the
output file will be wrong.
Also it is one more piece of software to be mantained.
Regards,
Clodoaldo Pinto
--- Clodoaldo Pinto Neto [EMAIL PROTECTED] escreveu:
Hi all,
I'm trying to copy a table with a text field column containing a new
line char to a file:
ksDesenv=# create table page(line text) without oids;
CREATE TABLE
ksDesenv=# insert into page (line)
values('1stline'||chr(10)||'2ndline');
INSERT 0 1
ksDesenv=# select * from page;
line
-
1stline
2ndline
(1 registro)
ksDesenv=# copy page to '/var/www/html/kakaostats/page.txt';
COPY
But this is what the page.txt file looks like:
1stline\n2ndline
What can I do to make the copy to command insert an actual new line
char?
Regards,
Clodoaldo Pinto Neto
___
Yahoo! Acesso Grátis - navegue de graça com conexão de qualidade!
Acesse: http://br.acesso.yahoo.com/
---(end of
broadcast)---
TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
___
Yahoo! Acesso Grátis - navegue de graça com conexão de qualidade! Acesse:
http://br.acesso.yahoo.com/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] How copy a new line char to a file?
Hi all,
I'm trying to copy a table with a text field column containing a new
line char to a file:
ksDesenv=# create table page(line text) without oids;
CREATE TABLE
ksDesenv=# insert into page (line)
values('1stline'||chr(10)||'2ndline');
INSERT 0 1
ksDesenv=# select * from page;
line
-
1stline
2ndline
(1 registro)
ksDesenv=# copy page to '/var/www/html/kakaostats/page.txt';
COPY
But this is what the page.txt file looks like:
1stline\n2ndline
What can I do to make the copy to command insert an actual new line
char?
Regards,
Clodoaldo Pinto Neto
___
Yahoo! Acesso Grátis - navegue de graça com conexão de qualidade! Acesse:
http://br.acesso.yahoo.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] server closed the connection unexpectedly
There was no crash that I know of. # su postgres $ cd /var/lib/pgsql/data $ cat postmaster.pid 14877 /var/lib/pgsql/data 5432001393216 $ pg_ctl stop -D ./ waiting for postmaster to shut down..done $ rm postmaster.pid rm: imposível remover `postmaster.pid': Arquivo ou diretório não encontrado #(means file not found) $ postmaster -D /var/lib/pgsql/data logfile 21 [1] 19256 Now as user cpn: $ pg_dump -Fc KakaoStats KakaoStats.dump pg_dump: conector (socket) não está aberto #(means not open) pg_dump: Comando SQL para copiar o conteúdo da tabela usuarios falhou: PQendcopy() falhou. #(means failed) pg_dump: Mensagem de erro do servidor: conector (socket) não está aberto #(not open) pg_dump: O comando foi: COPY public.usuarios (usuario, data, n_time, wus, pontos) TO stdout; After reboot this was the log file: (forgot to get it before) # cat logfile LOG: database system was shut down at 2004-07-17 05:41:04 BRT LOG: checkpoint record is at 7/C63CFA44 LOG: redo record is at 7/C63CFA44; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 7704; next OID: 426301 LOG: database system is ready LOG: server process (PID 19261) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2004-07-17 05:45:22 BRT LOG: checkpoint record is at 7/C63CFA44 LOG: redo record is at 7/C63CFA44; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 7704; next OID: 426301 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 7/C63CFA84 LOG: redo is not required LOG: database system is ready LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down After reboot it worked OK. CPN --- mike g [EMAIL PROTECTED] escreveu: It seems that postgresql crashed recently, tried to restart automatically, and has not succesfully restarted. You will need to stop the postmaster daemon manually, probably delete the postmaster.pid file, and restart the postmaster daemon. Chances are that anyone trying to query the database is not able to HTH On Fri, 2004-07-16 at 21:05, Clodoaldo Pinto Neto wrote: Hi all, Errors running a psql batch that has worked for weeks without a problem: $ uname -a Linux s1 2.6.6-1.435.2.3 #1 Thu Jul 1 08:25:29 EDT 2004 i686 athlon i386 GNU/Linux $ rpm -q postgresql postgresql-7.4.2-1 $ psql KakaoStats Bem-vindo ao psql 7.4.2, o terminal iterativo do PostgreSQL. select insert_usuarios_producao(); psql:/KakaoStats/scripts/sql/kakaoStats.sql:92: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/KakaoStats/scripts/sql/kakaoStats.sql:92: connection to server was lost select deleta_antigos() server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. vacuum KakaoStats psql: FATAL: the database system is starting up These commands are some of the last of a script which runs normaly until then. Regards Clodoaldo Pinto ___ Yahoo! Mail agora com 100MB, anti-spam e antivÃrus grátis! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ___ Yahoo! Mail agora com 100MB, anti-spam e antivírus grátis! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] could not access file $libdir/plpython when restoring
The postgresql was installed by anaconda installer during the box setup. Tried to install to template1 as postgres with no success: bash-2.05b$ createlang plpythonu template1 createlang: instalação da linguagem falhou: ERROR: could not access file $libdir/plpython: No such file or directory Seems your FC2 box did not have the python language support compiled into the version of postgresql that was installed on that box or possibly not installed in the template1 database so it would be available to any other database created in your FC2. I do believe python is included in the rpm versions of postgresql that are put on the mirrors. Did you compile it from source? If so recompile and add the python option. On Mon, 2004-07-05 at 20:33, Clodoaldo Pinto Neto wrote: Hi all, I'm restoring a database from 7.3.4 FC1 to 7.4.2 FC2: [EMAIL PROTECTED] bak]$ pg_restore -d KakaoStats KakaoStats.dump pg_restore: [compressão de arquivo (banco de dados)] could not execute query: ERROR: could not access file $libdir/plpython: No such file or directory The dump was generated with the command: [EMAIL PROTECTED] bak]$ pg_dump -Fc KakaoStats KakaoStats.dump What value should $libdir contain? How to make it permantent? Tried createlang: [EMAIL PROTECTED] bak]$ createlang --echo plpythonu KakaoStats SELECT oid FROM pg_language WHERE lanname = 'plpythonu'; SELECT oid FROM pg_proc WHERE proname = 'plpython_call_handler' AND prorettype = (SELECT oid FROM pg_type WHERE typname = 'language_handler') AND pronargs = 0; CREATE FUNCTION plpython_call_handler () RETURNS language_handler AS '$libdir/plpython' LANGUAGE C; CREATE LANGUAGE plpythonu HANDLER plpython_call_handler; createlang: instalação da linguagem falhou: ERROR: could not access file $libdir/plpython: No such file or directory Regards, Clodoaldo Pinto ___ Yahoo! Mail agora com 100MB, anti-spam e antivÃrus grátis! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ___ Yahoo! Mail agora com 100MB, anti-spam e antivírus grátis! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Ordering YYYY MM DD in reverse chrono order
Did you try
ORDER BY
date_part('year', uu.add_date) desc, date_part('month', uu.add_date) desc,
date_part('day', uu.add_date) DESC;
Regards,
Clodoaldo
--- OtisUsenet [EMAIL PROTECTED] escreveu: Hello,
I am trying to select distinct dates and order them in the reverse
chronological order. Although the column type is TIMESTAMP, in this
case I want only , MM, and DD back.
I am using the following query, but it's not returning dates back in
the reverse chronological order:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date) DESC;
This is what the above query returns:
date_part | date_part | date_part
---+---+---
2004 | 2 | 6
2004 | 4 |20
(2 rows)
I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...
My query is obviously wrong, but I can't see the mistake. I was
wondering if anyone else can see it. Just changing DESC to ASC, did
not work.
Thank you!
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
__
Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] COPY TO order
--- Guy Fraser [EMAIL PROTECTED] If you have a 'serial' or 'bigserial' field like this : create table test_table ( test_id bigserial, data integer, comment text ); and you use : copy test_table (data,comment) from '/wherever/the/file/is' using delimiters ','; to insert data like this : 27,some kind of entry 32,another kind of entry 16,yet another entry ... Assuming this is the first set of data entered the table will get populated with : 1 | 27 | some kind of entry 2 | 32 | another kind of entry 3 | 16 | yet another entry ... I have used this in the past and it works well. The problem I have is with COPY TO and not COPY FROM as I need to write a file. Regards, Clodoaldo __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] COPY TO order
Hello, How to make sure COPY TO writes the table lines to the file in the same order they were inserted? I'm producing html pages in pl/pgsql and using COPY TO to write then to file. Occasionaly, about once in 7 or 9, the lines are copied to the file out of the order they were inserted in the table. The lines have one only column of the type text. The pages are here: www.kakao.pop.com.br Regards, Clodoaldo Pinto Neto __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
