Re: [GENERAL] Generating unique session ids
On Wed, 26 Jul 2006, Tom Lane wrote: Antimon [EMAIL PROTECTED] writes: As the id field is primary key, it should generate a unique violation if duplicate ids created, might be seen rarely but wanted to solve it anyway. Why don't you just use a serial generator? If I may interrupt: Session id's for web cannot be predictable because this will create a security hole in application. md5(random()) is also a bad choise - very much predictable. Mr Antimon would definately better use another way of generating session ID's - for example PHP sessions and session_id(). He can also use system entropy source like /dev/urandom on POSIX systems. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(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] Database corruption with Postgre 7.4.2 on FreeBSD 6.1?
It shouldnt run into these problems from time to time, that kind of a scenario only happened to me once so dont know exactly how often this can happen. But a recommendation from my end will be to upgrade to the newer PostgreSQL version as you are using an old release. Also try running some disk check utlities to see if your hardware is in a good condition. Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 7/27/06, aurora [EMAIL PROTECTED] wrote: From your experience do you expect the database would run into this from time to time that requires DBA's interventions? Is so it would become aproblem for our customers because our product is a standalone system. Wedon't intend to expose the Postgre database underneath.wy Try doing a REINDEX and see if you can recover all data blocks as it appears to me you have some data blocks messed up. If possible try taking the backup for your database as well. Thanks,---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Generating unique session ids
Tomasz Ostrowski wrote:
On Wed, 26 Jul 2006, Tom Lane wrote:
Antimon [EMAIL PROTECTED] writes:
As the id field is primary key, it should generate a unique violation
if duplicate ids created, might be seen rarely but wanted to solve it
anyway.
Why don't you just use a serial generator?
If I may interrupt:
Session id's for web cannot be predictable because this will create a
security hole in application. md5(random()) is also a bad choise -
very much predictable.
Mr Antimon would definately better use another way of generating
session ID's - for example PHP sessions and session_id(). He can also
use system entropy source like /dev/urandom on POSIX systems.
Regards
Tometzky
Using a sequence does not mean it will be predictable.
In the past I have used something similar to this:
SELECT md5('secret_salt' || nextval('my_seq')::text)
Regards,
LL
---(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] Mapping/DB Migration tool
On Wed, Jul 26, 2006 at 08:48:14AM -0700, Reece Hart wrote: In case your interested in these pgtools views, I've uploaded them to http://harts.net/reece/pgtools/ . I am looking into it. Any chance you could do a text dump with --no-owner --no-acl ? Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] PostgreSQL and Windows 2003 DFS Replication
Hi list ! I am currently deploying two servers (Windows 2003 R2) that will be used as file servers as well as PostgreSQL servers. One of the server will be the main server, the other one a backup server (no load-balancing, only an easy-recoverage solution). The goal is to be able to start working quickly after one of the server fails (after the main server fails actually, since the backup server is not used). I already configured a high-availability solution for the file server part by using the built-in DFS Replication service. I first thought I would use Slony-I to replicate changes to the main database on the backup server, but I then realized that I might use DFS Replication for that. The point is that I am not sure that it will work. Documentation about DFS Replication is not very talkative (IMHO), I have to little knowledge of PostgreSQL's file handling to know if it will work or not. I have compiled some informations about DFS Replication from Microsofts web site. Could you PostgreSQL gurus tell me whether using this replication mechanism is a good idea or not ? The main advantage for me is that I will not need to configure 2 replication systems (one for the files, on for the DBs). I would only need to maintain one of them ! Here is the documentation I got, thanks a lot for reading me to this point ! : DFS Replication, the successor to the File Replication service (FRS) introduced in Windows 2000 Server operating systems, is a new, state-based, multimaster replication engine that supports replication scheduling and bandwidth throttling. DFS Replication uses a new compression algorithm known as remote differential compression (RDC). RDC is a diff-over-the wire client-server protocol that can be used to efficiently update files over a limited-bandwidth network. RDC detects insertions, removals, and re-arrangements of data in files, enabling DFS Replication to replicate only the changed file blocks when files are updated. DFS Replication uses many sophisticated processes to keep data synchronized on multiple servers. Before you begin using DFS Replication, it is helpful to understand the following concepts. * DFS Replication is a multimaster replication engine. Any change that occurs on one member is replicated to all other members of the replication group. * DFS Replication detects changes on the volume by monitoring the update sequence number (USN) journal, and DFS Replication replicates changes only after the file is closed. * DFS Replication uses a staging folder to stage a file before sending or receiving it. For more information about staging folders, see Staging folders and Conflict and Deleted folders. * DFS Replication uses a version vector exchange protocol to determine which files need to be synchronized. The protocol sends less than 1 kilobyte (KB) per file across the network to synchronize the metadata associated with changed files on the sending and receiving members. * When a file is changed, only the changed blocks are replicated, not the entire file. The RDC protocol determines the changed file blocks. Using default settings, RDC works for any type of file larger than 64 KB, transferring only a fraction of the file over the network. * DFS Replication uses a conflict resolution heuristic of last writer wins for files that are in conflict (that is, a file that is updated at multiple servers simultaneously) and earliest creator wins for name conflicts. Files and folders that lose the conflict resolution are moved to a folder known as the Conflict and Deleted folder. You can also configure the service to move deleted files to the Conflict and Deleted folder for retrieval should the file or folder be deleted. For more information, see Staging folders and Conflict and Deleted folders. * DFS Replication is self-healing and can automatically recover from USN journal wraps, USN journal loss, or loss of the DFS Replication database. * DFS Replication uses a Windows Management Instrumentation (WMI) provider that provides interfaces to obtain configuration and monitoring information from the DFS Replication service. Many thanks for your advices on this ! Regards -- Arnaud ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Shared buffers
Hi list, just wanted to ask what is a good/reasonable value for the shared_bufferes variable. Right now i set it to 64000 on a windows 2003 server with 1GB ram and 3.2 GHz which runs as file server (for only a small number of users) and db server. Thanks -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Database Oid from SPI
Hi, how can I get the database name or OID of the current backend in a SPI function (in plain C)? I tried including storage/proc.h and accessing MyProc-databaseId, but that leads to a segfault :-( (and seems like the wrong way to do it.) The SPI documentation didn't help. Thank you Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Database Oid from SPI
Whoops, sorry, there was another reason for the segfault. Using MyProc-databaseId works. Is it the right way to do it, though? Markus Schiltknecht wrote: Hi, how can I get the database name or OID of the current backend in a SPI function (in plain C)? I tried including storage/proc.h and accessing MyProc-databaseId, but that leads to a segfault :-( (and seems like the wrong way to do it.) The SPI documentation didn't help. Thank you Markus ---(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] Database Oid from SPI
Markus Schiltknecht wrote: Whoops, sorry, there was another reason for the segfault. Using MyProc-databaseId works. Is it the right way to do it, though? I'd use MyDatabaseId ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] JDBC
Hello I'm new with DB, I'm reading the PostgreSQL doc 8.1. I almost use the PostgreSQL from the 'psql' terminal and also I prove the Toolkit Tora, and Open Office using ODBC driver. I have problem using the JDBC driver, I always receive the error: A driver is not registered for the URL jdbc:postgresql://localhost/accs By the way, also try instead the word accs the name of the DataBase and also with and without the localhost, but always the driver looks not registered. I also write org.postgresql.Driver in the JDBC driver class place. So you know what can be the problem?? Second question, the Open Office using the driver ODBC can work only with tables created with IODS column, the same restriccion is for the JDBC driver ?? Last question, I want to design for my mother, 60 years old, a PostgreSQL DB. The DB that she need will have around of 1 rows each table and approximate 3 o 4 tables. There are any simple client application that don't need SQL acknowledgments that you recommend for her.Or if I want something simple I must write it, maybe using 'libpq' together with some Toolkit, for example 'FLTK'. Thank you very much, Gustavo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database Oid from SPI
Markus Schiltknecht [EMAIL PROTECTED] writes: Whoops, sorry, there was another reason for the segfault. Using MyProc-databaseId works. Is it the right way to do it, though? Actually I'd recommend you use the global MyDatabaseId from miscadmin.h. It'll be the same value, but it's always best to avoid unnecessary accesses to shared memory. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] JDBC
On Wednesday 26 July 2006 11:54 am, gustavo halperin wrote: Hello I'm new with DB, I'm reading the PostgreSQL doc 8.1. I almost use the PostgreSQL from the 'psql' terminal and also I prove the Toolkit Tora, and Open Office using ODBC driver. I have problem using the JDBC driver, I always receive the error: A driver is not registered for the URL jdbc:postgresql://localhost/accs By the way, also try instead the word accs the name of the DataBase and also with and without the localhost, but always the driver looks not registered. I also write org.postgresql.Driver in the JDBC driver class place. So you know what can be the problem?? To register the driver go to menu item Tools--Options--Java. This will pop up a window. Click on the Class Path button and use Add Archive to browse for the jar file. Second question, the Open Office using the driver ODBC can work only with tables created with IODS column, the same restriccion is for the JDBC driver ?? Last question, I want to design for my mother, 60 years old, a PostgreSQL DB. The DB that she need will have around of 1 rows each table and approximate 3 o 4 tables. There are any simple client application that don't need SQL acknowledgments that you recommend for her.Or if I want something simple I must write it, maybe using 'libpq' together with some Toolkit, for example 'FLTK'. Thank you very much, Gustavo ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Generating unique session ids
On Thu, 27 Jul 2006, Lexington Luthor wrote:
Session id's for web cannot be predictable because this will create a
security hole in application.
Using a sequence does not mean it will be predictable.
In the past I have used something similar to this:
SELECT md5('secret_salt' || nextval('my_seq')::text)
* When somebody knows md5('secret_salt' || '5') he will be able to
easily compute
md5('secret_salt' || '50')
md5('secret_salt' || '51')
md5('secret_salt' || '52')
...
md5('secret_salt' || '59')
md5('secret_salt' || '500')
md5('secret_salt' || '501')
...
md5('secret_salt' || '[any number starting from 5]').
Without knowledge of 'secret_salt'. So your proposal is totally
insecure.
* PostgreSQL integers (as returned by nextval()) are 4 bytes. This
means only 32 bit strength - much too low for today computers.
* Any database user is most of the time able to read function
bodies, so anybody who is able co connect to your database will be
able to get your 'secret_salt' and then predict session id's.
* If you think that nobody will connect to a database but
web-application frontend there's a high probability of SQL-injection
hole in frontend, which is sufficient.
So, basically, a very bad idea.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Generating unique session ids
SELECT md5('secret_salt' || nextval('my_seq')::text)
* When somebody knows md5('secret_salt' || '5') he will be able to
easily compute
md5('secret_salt' || '50')
md5('secret_salt' || '51')
md5('secret_salt' || '52')
...
md5('secret_salt' || '59')
md5('secret_salt' || '500')
md5('secret_salt' || '501')
...
md5('secret_salt' || '[any number starting from 5]').
Without knowledge of 'secret_salt'. So your proposal is totally
insecure.
Challenge :)
chris= select md5('**' || '5');
md5
--
7b076f591070f6912e320b95782250ae
(1 row)
I won't tell what '**' was.
Can you send me what md5('**' || '50') will give?
Bye,
Chris.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Generating unique session ids
Tomasz Ostrowski [EMAIL PROTECTED] writes:
* When somebody knows md5('secret_salt' || '5') he will be able to
easily compute
md5('secret_salt' || '50')
md5('secret_salt' || '51')
Sure, but can't you fix that by putting the secret part at the end?
* PostgreSQL integers (as returned by nextval()) are 4 bytes. This
means only 32 bit strength - much too low for today computers.
Um, nextval returns int8.
* Any database user is most of the time able to read function
bodies, so anybody who is able co connect to your database will be
able to get your 'secret_salt' and then predict session id's.
Yeah, it's not clear where to hide the secret.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Generating unique session ids
Tom Lane wrote: * Any database user is most of the time able to read function bodies, so anybody who is able co connect to your database will be able to get your 'secret_salt' and then predict session id's. Yeah, it's not clear where to hide the secret. In a memfrob'ed (or something better probably) area in a C function? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database Oid from SPI
Hi, thank you both. I first tried that, but the segfault really irritated me. It's now working fine with miscadmin.h. Sorry for the noise. Regards Markus Tom Lane wrote: Actually I'd recommend you use the global MyDatabaseId from miscadmin.h. It'll be the same value, but it's always best to avoid unnecessary accesses to shared memory. regards, tom lane Alvaro Herrera wrote: I'd use MyDatabaseId ... ---(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] Generating unique session ids
I'm not an expert as you, but what about a small table where just one user can read and create the function with this same user and definer security? Excuse if I say something stupid Alvaro Herrera wrote: Tom Lane wrote: * Any database user is most of the time able to read function bodies, so anybody who is able co connect to your database will be able to get your 'secret_salt' and then predict session id's. Yeah, it's not clear where to hide the secret. In a memfrob'ed (or something better probably) area in a C function? ---(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] Generating unique session ids
On Thu, 27 Jul 2006, Tom Lane wrote:
Tomasz Ostrowski [EMAIL PROTECTED] writes:
* When somebody knows md5('secret_salt' || '5') he will be able to
easily compute
md5('secret_salt' || '50')
md5('secret_salt' || '51')
Sure, but can't you fix that by putting the secret part at the end?
I'm not so sure anymore. I think I was wrong... Forget it.
* PostgreSQL integers (as returned by nextval()) are 4 bytes. This
means only 32 bit strength - much too low for today computers.
Um, nextval returns int8.
OK. 64 bit should be enough.
* Any database user is most of the time able to read function
bodies, so anybody who is able co connect to your database will be
able to get your 'secret_salt' and then predict session id's.
Yeah, it's not clear where to hide the secret.
As somebody said it would be possible with restricted table and
security definer function.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] UTF-8, upper() and Chinese characters yielding blank result
While I could see various multibyte issues in the archives and in the TODO list, I couldn't spot this exact issue: I am working with a database that uses UNICODE encoding. I have a varchar column (col_x) that includes a mix of Chinese and regular ASCII characters. On PostgreSQL 7.4.13 (on RHEL4) select col_x, upper(col_x) from my_table performs the desired upper() conversion - i.e. the ASCII characters are converted to upper case and the Chinese characters are left as is. The problem appears on PostgreSQL 8.0.7 (on WinXP) where the upper() result is apparently blank (this is via pgAdmin III). Worde still, via JDBC I am getting: java.sql.SQLException: Invalid character data was found. This is most likely caused by stored data containing characters that are invalid for the character set the database was created in. The most common example of this is storing 8bit data in a SQL_ASCII database. Is this a bug or a change of behaviour between versions? Is there some way I can get the 7.4.13 behaviour in 8.0.7? TIA, Scott ---(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] Permissions to connect to postgres database
Hi,I have a database in postgres and i've given trusted permissions to all making required changesin pg_hba.conf.Now, I'm trying to connect to the database using ECPG. Unfortunately afterEXEC SQL CONNECT dbxyz gives me error -402 that means i don't have permissions to the database.I'm running this ECPG code under root permissions.Don't know what needs to be done to make it work.Is there some kind of a script file that needs to be run so that everything is well in place.Regards,~Jas
[GENERAL] Update entire column with new date values
Hi all,
I have a simple table which contains information about our safety files:
CREATE TABLE docs
(
filename varchar(256) NOT NULL,
filepath varchar(256) NOT NULL,
version varchar(8),
date timestamp NOT NULL,
docid int8 NOT NULL DEFAULT nextval('docs_docid_seq'::regclass),
category varchar(16), NOT NULL
CONSTRAINT pkey_docs PRIMARY KEY (docid, filename, date),
CONSTRAINT ukey_docid UNIQUE (docid)
)
I need to update the date for each file (the date filename
columns). However, the updated values for date are not the same for each
file (due to when the files were last modified). I can import the new
date values from a text file containing the date and filename.
What sort of UPDATE command do I use in this case? Essentially, I'm
trying to do something like:
UPDATE docs SET date = '{$input_date}'
FROM ('C:/Temp/docupdate.txt')
WHERE filename = '{$input_filename}';
Any help is appreciated. Thanks!
Kevin Weiss
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Generating unique session ids
Alvaro Herrera wrote: Tom Lane wrote: * Any database user is most of the time able to read function bodies, so anybody who is able co connect to your database will be able to get your 'secret_salt' and then predict session id's. Yeah, it's not clear where to hide the secret. In a memfrob'ed (or something better probably) area in a C function? You could also do it in a untrusted plperl or plpython function. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] Permissions to connect to postgres database
Jasbinder Bali wrote: Hi, I have a database in postgres and i've given trusted permissions to all making required changes in pg_hba.conf. Now, I'm trying to connect to the database using ECPG. Unfortunately after EXEC SQL CONNECT dbxyz gives me error -402 that means i don't have permissions to the database. If you turn on connection logging on the postgresql server you'll see what's happening. Don't forget system users and postgresql users aren't connected. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL and Windows 2003 DFS Replication
On 7/27/06, Arnaud Lesauvage [EMAIL PROTECTED] wrote: Hi list ! I am currently deploying two servers (Windows 2003 R2) that will be used as file servers as well as PostgreSQL servers. One of the server will be the main server, the other one a backup server (no load-balancing, only an easy-recoverage solution). The goal is to be able to start working quickly after one of the server fails (after the main server fails actually, since the backup server is not used). I already configured a high-availability solution for the file server part by using the built-in DFS Replication service. I am very suspicious about DFS for this. File based replication usually doesn't work for sql servers because of the complex interdependencies in the files. It sounds like a fancy rsync and is very unlikely to be able to guarantee consistent backup unless all writes are synchronous. for a cold/warm standby postgresql backup, I'd suggest using pitr. It's easy to set up and administer. for hot read only backup, bite the bullet and use slony. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Permissions to connect to postgres database
How do i turn on the connection logging and then how do you actually check it?On 7/27/06, Richard Huxton dev@archonet.com wrote:Jasbinder Bali wrote: Hi, I have a database in postgres and i've given trusted permissions to all making required changes in pg_hba.conf. Now, I'm trying to connect to the database using ECPG. Unfortunately after EXEC SQL CONNECT dbxyz gives me error -402 that means i don't have permissions to the database. If you turn on connection logging on the postgresql server you'll seewhat's happening. Don't forget system users and postgresql users aren'tconnected.-- Richard Huxton Archonet Ltd
Re: [GENERAL] Update entire column with new date values
Weiss, Kevin wrote:
I need to update the date for each file (the date filename
columns). However, the updated values for date are not the same for each
file (due to when the files were last modified). I can import the new
date values from a text file containing the date and filename.
What sort of UPDATE command do I use in this case? Essentially, I'm
trying to do something like:
UPDATE docs SET date = '{$input_date}'
FROM ('C:/Temp/docupdate.txt')
WHERE filename = '{$input_filename}';
You'll want to gather your updates into a table and then use the
non-standard FROM clause to do pretty much what you're trying to here.
http://www.postgresql.org/docs/8.1/static/sql-update.html
UPDATE docs SET date = ???
FROM newvals
WHERE docs.filename=newvals.filename
Failing that look at a subselect in your SET clause.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Permissions to connect to postgres database
Jasbinder Bali wrote: How do i turn on the connection logging and then how do you actually check it? Log settings are in your postgresql.conf file and are detailed in this part of the manual: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html Where your logfiles are stored will depend on your settings, but typically on a unix-style system they will be in /var/log/. I recommend you stop what you're doing for an hour or two and quickly read through section III (Server Administration) of the manuals. That will save you a lot of time later. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Update entire column with new date values
am 27.07.2006, um 10:17:48 -0500 mailte Weiss, Kevin folgendes:
I need to update the date for each file (the date filename
columns). However, the updated values for date are not the same for each
file (due to when the files were last modified). I can import the new
date values from a text file containing the date and filename.
What sort of UPDATE command do I use in this case? Essentially, I'm
trying to do something like:
UPDATE docs SET date = '{$input_date}'
FROM ('C:/Temp/docupdate.txt')
WHERE filename = '{$input_filename}';
Any help is appreciated. Thanks!
Create a new table with filename and date - column and fill this table
with your text file. Then you have 2 tables and can run normal SQL to
update your table.
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(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] PostgreSQL and Windows 2003 DFS Replication
for a cold/warm standby postgresql backup, I'd suggest using pitr. I found that PITR using WAL shipping is not protecting against all failure scenarios... it sure will help if the primary machine's hardware fails, but in one case it was useless for us: the primary had a linux kernel with buggy XFS code (that's what I think it was, cause we never found out for sure) and we did use XFS for the data partition, and at one point it started to get corruptions at the data page level. The corruption was promptly transferred to the standby, and therefore it was also unusable... we had to recover from a backup, with the related downtime. Not good for business... It's easy to set up and administer. for hot read only backup, bite the bullet and use slony. I think slony would have helped us recovering from the above mentioned situation earlier and easier, as it transfers logical data and not pages directly. It has though a bigger overhead than WAL shipping in terms of administration and performance penalty. Cheers, Csaba. ---(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] Mapping/DB Migration tool
On Thu, 2006-07-27 at 13:02 +0200, Karsten Hilbert wrote: Any chance you could do a text dump with --no-owner --no-acl ? The pgdump is already --no-owner, but I forgot --no-acl. I just uploaded a new tarball using both flags. BTW, you can generate this yourself with what you have using pg_restore. eg$ pg_restore --no-acl --no-owner pgtools.pgdump pgtools.sql -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
[GENERAL] Can't start PostgreSQL
Hi List I want to develop an application in PostgreSQL but when starting the service I get the following message: An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.1.4/README.rpm-dist for more information. I am running Fedora Core 5 but initially had built the system with Fedora Core 3, with PostgreSQL installed and automatically updated it over the past 2 years. I may have created a test database 2 years ago which may be the source of the problem. Is this the source of the problem? I could find nothing in README.rpm-dist that helps. I would appreciate any advice on how to deal with this problem. Thank you Ian Johnson
[GENERAL] PostgreSQL theoretical maximums.
How many tables and rows can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? What is an effective way to predict database size when designing tables? regards, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL theoretical maximums.
How many tables can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? What is an effective way to predict database size when designing tables? regards, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can't start PostgreSQL
Ian Johnson wrote: Hi List I want to develop an application in PostgreSQL but when starting the service I get the following message: An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.1.4/README.rpm-dist for more information. I am running Fedora Core 5 but initially had built the system with Fedora Core 3, with PostgreSQL installed and automatically updated it over the past 2 years. I may have created a test database 2 years ago which may be the source of the problem. Is this the source of the problem? I could find nothing in README.rpm-dist that helps. Yep - you have an old installation still there. The RPM probably refused to destroy the old installation without approval from you. You'll need to initdb the directory (see the online manuals for how to run initdb, or check the RPM /etc/init.d/ scripts) -- Richard Huxton Archonet Ltd ---(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] PostgreSQL theoretical maximums.
Karen Hill wrote: How many tables can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? Maximum number of tables etc. is a FAQ: http://www.postgresql.org/docs/faqs.FAQ.html#item4.4 It's been running on unix-like systems for much longer than Windows. Apart from that, the best system is probably determined by your experience. What is an effective way to predict database size when designing tables? Rule-of-thumb - assume 3-5 times the size of the raw data to allow for overhead, indexes etc. Other than that, you can find details of on-disk formats for rows towards the end of the manuals. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Backslash as ordinary char vs. not; set via a connection/session variable
Tom and folks, Will it be possible to set this more standard backslash handling behavior -- and possibly similar conformance modes... in a way similar to how mysql allows? They allow one to issue commands on the connection like: SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI' The advantage to this is that ISPs, etc can, by default, run the database with the old/incorrect mode (which is more compatible with the correspondingly legacy/broken apps.. and for newer apps to issue that command to make the DB act more standards compliant.. I personally have no need for the old backslash behavior (currently using databases/queries that don't use it), but sometimes one may have to coexist in a shared server environment. (for those who may not know,) the actual overhead of issuing that command (on the client side at least) before each exec is low, since connection pools for example usually already need to issue a 'ping' command (e.g SELECT 1) to test the connection before executing on it. So if the pool/api allows the user to define connection-setup DML, that conveniently takes the place of the ping anyway (if the API supports). And, given that the last user of a pooled connection may have tweaked the settings, it's prob. best to reset them before each exec. I seem to have lost the URL that describes the upcoming string conformance mode.. can't find the 4.1.2.1 section that John Gunther mentioned.. does anyone have that handy? Thank you, ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL theoretical maximums.
Karen Hill wrote: How many tables and rows can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? What is an effective way to predict database size when designing tables? http://www.postgresql.org/docs/faqs.FAQ.html -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Using an alternate PGDATA on RHEL4 with SELinux enabled
I just tried installing Postgres 8.1.4 (RPMs from postgresql.org web site) on a clean RHEL4 Update 2 machine that had SELinux enabled. When I created a /etc/sysconfig/pgsql/postgresql config file with PGDATA=/data/pgdata I was unable to get the start script (/etc/init.d/postgresql) to populate the data directory nor launch postgres (after I manually did an initdb on the directory) The file permissions were drwx-- 11 postgres postgres 4096 Jul 27 12:51 pgdata But the pgstartup.log was still reporting that initdb coudln't create write to /data/pgdata (Permission denied) When I manually copied and pasted the line that was in /etc/init.d/postgresql to the command line, it ran just fine (executing as root) runuser -l postgres -c /usr/bin/initdb --pgdata='/data/pgdata' --auth='ident sameuser' /var/lib/pgsql/pgstartup.log 21 /dev/null But there's some voodoo going on when this is executed inside of the start script ... I started monkeying around with cutting the start script down to the point where I finally got this error to appear (I believe it was removing the /dev/null redirect): Your default context is user_u:system_r:unconfined_t. Do you want to choose a different one? [n] If I just hit enter, the script would continue and successfully create the data directory and launch postgres To be perfectly clear: If I don't set a custom PGDATA in /etc/sysconfig/pgsql/postgresql, everything works fine ... the data directory is created in /var/lib/pgsql/data as expected ... it's only with the custom PGDATA .. Long story short, I have disabled SELinux on this box because this isn't the first time SELinux stuff has burned hours of my day and this is an internal box so I don't feel it's worth the battle right now ... but figured that maybe someone else out there might benefit from reading this ... -Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UTF-8, upper() and Chinese characters yielding blank result
Scott Eade wrote: The problem appears on PostgreSQL 8.0.7 (on WinXP) PostgreSQL 8.0 on Windows does not support UTF-8. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't start PostgreSQL
It seems you were running a pre-8.x postgresql version before, its data files are not compatible with the new version you have now. You'll need to find out the version that used to be installed by looking at the PG_VERSION file in your postgres data directory. Once you do that you will need to install the old version (you can download it from postgresql.org), start it against the database directory and dump the databases. You can use pg_dumpall for this. Once you have dumped the dbs, you can move the old data directory aside, and run initdb for the new postgresql (8.1.4) and start it. Then import the databases you previously dumped. hth, -Casey On Jul 27, 2006, at 9:22 AM, Ian Johnson wrote: Hi List I want to develop an application in PostgreSQL but when starting the service I get the following message: An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.1.4/README.rpm-dist for more information. I am running Fedora Core 5 but initially had built the system with Fedora Core 3, with PostgreSQL installed and automatically updated it over the past 2 years. I may have created a test database 2 years ago which may be the source of the problem. Is this the source of the problem? I could find nothing in README.rpm-dist that helps. I would appreciate any advice on how to deal with this problem. Thank you Ian Johnson ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] server administration problem: Database startup and permissions
Hi,I created a database cluster using the following commands as per what is given in the postgres server administration manualroot# mkdir /usr/local/pgsql/jasroot# chown jsbali /usr/local/pgsql/jasroot# su jsbali jsbali# initdb -D /usr/local/pgsql/jasAfter that i tried to start up the postmaster process using the follwing commandjsbali# pg_ctl start -D /usr/local/pgsql/jasIt does start the postmaster process. Then i create a new database here using the following commandjsbali# createdb DBxyzhere in the same user account i try to open pgadmin3 and it doens'tI open pgadmin3 from the root account and try to connect to my local host and it says An error has occured:Error connecting to the server: FATAL: role postgres does not existDon't know whats going on wrong.I'm totally confused. ~Jas
Re: [GENERAL] Generating unique session ids
On Thu, Jul 27, 2006 at 15:15:32 +0200, Tomasz Ostrowski [EMAIL PROTECTED] wrote: * PostgreSQL integers (as returned by nextval()) are 4 bytes. This means only 32 bit strength - much too low for today computers. They are actually 8 bytes. Since session ids aren't valuable for very long you could actually make a usable system out of this if you rekeyed frequently. If the issue is how to cheaply prevent collisions that might occur from using random session ids, one might consider concatenating a random string with a sequence. As long as the sequence won't wrap around before a session id will expire, this will prevent collisions. ---(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] Backslash as ordinary char vs. not; set via a connection/session variable
Ken Johanson wrote: Tom and folks, Will it be possible to set this more standard backslash handling behavior -- and possibly similar conformance modes... in a way similar to how mysql allows? They allow one to issue commands on the connection like: SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI' Is this something you cannot do with ALTER ROLE SET foo=bar ? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backslash as ordinary char vs. not; set via a connection/session
Ken Johanson wrote: Tom and folks, Will it be possible to set this more standard backslash handling behavior -- and possibly similar conformance modes... in a way similar to how mysql allows? They allow one to issue commands on the connection like: SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI' The advantage to this is that ISPs, etc can, by default, run the database with the old/incorrect mode (which is more compatible with the correspondingly legacy/broken apps.. and for newer apps to issue that command to make the DB act more standards compliant.. postgresql can do that in an even more powerful way - but people tend to not notice much of it in your case that would be: ALTER ROLE foo SET standard_conforming_strings='off' or even: ALTER DATABASE bar SET standard_conforming_strings='off' you can do that for nearly all GUCs (like logging,client_encoding,search_path,) Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Backslash as ordinary char vs. not; set via a connection/session
Stefan Kaltenbrunner wrote: postgresql can do that in an even more powerful way - but people tend to not notice much of it in your case that would be: ALTER ROLE foo SET standard_conforming_strings='off' or even: ALTER DATABASE bar SET standard_conforming_strings='off' you can do that for nearly all GUCs (like logging,client_encoding,search_path,) Stefan Stefan and Alvaro, Thank you!!! Yes, that is the feature I'd like... and yes, setting it on a per role or per database level is something I personally would prefer over the connection level. But, is there also a way to set it on the connection? Just because, one can imagine scenarios where two APIs share the same role database, but one API forces backslashes 'on' during its statement-prepare just playing devil's advocate :-) So is this 'standard_conforming_strings' variable already set-able in a recent build, at the role or db level? Or will that need to wait for 8.2? Thanks again!! ken ---(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] Can't start PostgreSQL
On Thu, Jul 27, 2006 at 10:22:33 -0600, Ian Johnson [EMAIL PROTECTED] wrote: I want to develop an application in PostgreSQL but when starting the service I get the following message: An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.1.4/README.rpm-dist for more information. I am running Fedora Core 5 but initially had built the system with Fedora Core 3, with PostgreSQL installed and automatically updated it over the past 2 years. I may have created a test database 2 years ago which may be the source of the problem. Is this the source of the problem? I could find nothing in README.rpm-dist that helps. That could certainly be the problem. I would appreciate any advice on how to deal with this problem. Probably you want to delete the old database cluster and do a dbinit to create a new one. The old one will probably be in /var/lib/pgsql/data . ---(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] Shared buffers
Go to http://www.powerpostgresql.com/PerfList/ and see the shared_buffers settings section there.Thanks,-- Shoaib MirEnterpriseDB ( www.enterprisedb.com)On 7/27/06, Christian Rengstl [EMAIL PROTECTED] wrote:Hi list,just wanted to ask what is a good/reasonable value for the shared_bufferes variable. Right now i set it to 64000 on a windows 2003 server with 1GB ram and 3.2 GHz which runs as file server (for only a small number of users) and db server.Thanks--Christian Rengstl M.A.Klinik und Poliklinik für Innere Medizin IIKardiologie - ForschungUniversitätsklinikum Regensburg B3 1.388Franz-Josef-Strauss-Allee 1193053 RegensburgTel.: +49-941-944-7230---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL theoretical maximums.
Hi, Have a look at: http://www.postgresql.org/about/ /David Karen Hill wrote: How many tables can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? What is an effective way to predict database size when designing tables? regards, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Backslash as ordinary char vs. not; set via a connection/session
Ken Johanson wrote:
Stefan Kaltenbrunner wrote:
postgresql can do that in an even more powerful way - but people tend to
not notice much of it in your case that would be:
ALTER ROLE foo SET standard_conforming_strings='off'
or even:
ALTER DATABASE bar SET standard_conforming_strings='off'
you can do that for nearly all GUCs (like
logging,client_encoding,search_path,)
Stefan
Stefan and Alvaro,
Thank you!!! Yes, that is the feature I'd like... and yes, setting it on
a per role or per database level is something I personally would prefer
over the connection level. But, is there also a way to set it on the
connection? Just because, one can imagine scenarios where two APIs share
the same role database, but one API forces backslashes 'on' during its
statement-prepare just playing devil's advocate :-)
foo=# create table backslash(baz text);
CREATE TABLE
foo=# set standard_conforming_strings to on;
SET
foo=# insert into backslash values ('\\');
INSERT 0 1
foo=# set standard_conforming_strings to off;
SET
foo=# insert into backslash values ('\\');
WARNING: nonstandard use of \\ in a string literal
LINE 1: insert into backslash values ('\\');
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
INSERT 0 1
foo=# select * from backslash;
baz
-
\\
\
(2 rows)
like that ? :-)
So is this 'standard_conforming_strings' variable already set-able in a
recent build, at the role or db level? Or will that need to wait for 8.2?
it's already in -HEAD and will therefor be in 8.2 when that gets released.
Stefan
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Using an alternate PGDATA on RHEL4 with SELinux enabled
David Esposito [EMAIL PROTECTED] writes: I just tried installing Postgres 8.1.4 (RPMs from postgresql.org web site) on a clean RHEL4 Update 2 machine that had SELinux enabled. When I created a /etc/sysconfig/pgsql/postgresql config file with PGDATA=/data/pgdata I was unable to get the start script (/etc/init.d/postgresql) to populate the data directory nor launch postgres (after I manually did an initdb on the directory) The default selinux policy prevents postgres from writing anywhere except under /var/lib/pgsql. If you want a nondefault PGDATA location then you have to tweak the policy. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Backslash as ordinary char vs. not; set via a connection/session
Stefan Kaltenbrunner wrote:
foo=# create table backslash(baz text);
CREATE TABLE
foo=# set standard_conforming_strings to on;
SET
foo=# insert into backslash values ('\\');
INSERT 0 1
foo=# set standard_conforming_strings to off;
SET
foo=# insert into backslash values ('\\');
WARNING: nonstandard use of \\ in a string literal
LINE 1: insert into backslash values ('\\');
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
INSERT 0 1
foo=# select * from backslash;
baz
-
\\
\
(2 rows)
like that ? :-)
Yes - that is the eye candy I'm looking for. Anxiously looking forward
to 8.2 :-)
ken
---(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] Permissions to connect to postgres database
You can turn on connection logging by setting the following in postgresql.conf file:log_connections = onlog_disconnections = onand this will log all the connections and disconnections to your database server in the db server log files. Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 7/27/06, Richard Huxton dev@archonet.com wrote:Jasbinder Bali wrote: How do i turn on the connection logging and then how do you actually check it?Log settings are in your postgresql.conf file and are detailed in thispart of the manual:http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html Where your logfiles are stored will depend on your settings, buttypically on a unix-style system they will be in /var/log/.I recommend you stop what you're doing for an hour or two and quicklyread through section III (Server Administration) of the manuals. That will save you a lot of time later.-- Richard Huxton Archonet Ltd---(end of broadcast)---TIP 6: explain analyze is your friend
Re: [GENERAL] server administration problem: Database startup and permissions
This is because in the pgadmin3 server settings for PostgreSQL database you did set the user as 'postgres' (default behavior) and never changed it. Now as you did run the 'initdb' process with user 'jsbali' so that is the user which exists in the database not the 'postgres' user. So the solution for that will add the user 'jsbal' in your pgadmin3 server settings instead of 'postgres' and then try connecting from pgadmin3. Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 7/28/06, Jasbinder Bali [EMAIL PROTECTED] wrote:Hi,I created a database cluster using the following commands as per what is given in the postgres server administration manual root# mkdir /usr/local/pgsql/jasroot# chown jsbali /usr/local/pgsql/jasroot# su jsbali jsbali# initdb -D /usr/local/pgsql/jasAfter that i tried to start up the postmaster process using the follwing commandjsbali# pg_ctl start -D /usr/local/pgsql/jasIt does start the postmaster process. Then i create a new database here using the following commandjsbali# createdb DBxyzhere in the same user account i try to open pgadmin3 and it doens'tI open pgadmin3 from the root account and try to connect to my local host and it says An error has occured:Error connecting to the server: FATAL: role postgres does not existDon't know whats going on wrong.I'm totally confused.~Jas
Re: [GENERAL] UTF-8, upper() and Chinese characters yielding blank result
On Thu, Jul 27, 2006 at 07:22:17PM +0200, Peter Eisentraut wrote: Scott Eade wrote: The problem appears on PostgreSQL 8.0.7 (on WinXP) PostgreSQL 8.0 on Windows does not support UTF-8. In addition, PostgreSQL is totally reliant on the OS for upper/lower/collation support, so there is no way you can expect to get similar result across different OSes, or even different versions of the same OS. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] automatic and randomally population
Hello I want try my tables with some thousands of rows. There are any manner to make automatic and randomly population ?? Thank you, Gustavo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] copy losing information
Thanks guys, The output of select version() is PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 Tom, how and why would INSERTs be dropped on the client side? I'll be away next week, but when I get back I'll conduct iterations to find out how often this happens and if there is a pattern. Reece, is there an ftp site where I can drop the file? It's way too large for email. The copy to the table from the file has been tried in two ways: C:\Program Files\PostgreSQL\8.1\bin\psql -c \copy mytable (series, ticker, date, value) from C:\temp\myfile.out -h server -U user database And also through a ruby script that uses the ruby-native library to connect to Postgres, and sends INSERTs in batches of 2000. Both ways work unreliably, sometimes getting every line, sometimes dropping a few lines. I check that doing a select count(*), and comparing against the number of lines in the file. The table gets truncated before the import, and has no sort of indexing. In case it can help, here is the definition. CREATE TABLE mytable ( series varchar(15), ticker varchar(20), date date, value numeric, variable varchar(20), msa varchar(3), year int4, month int2, freq varchar(2), geog varchar(6) ) Thanks Jaime -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 5:05 PM To: Alvaro Herrera Cc: Silvela, Jaime (Exchange); pgsql-general@postgresql.org Subject: Re: [GENERAL] copy losing information Alvaro Herrera [EMAIL PROTECTED] writes: Silvela, Jaime (Exchange) wrote: No lines contain quotes. And the same file will sometimes be fully imported, and sometimes lose data. I'm thinking that under heavy loads, the database is discarding INSERTS. I don't think that's very likely. Especially not since he says he's using COPY --- any sort of error would be all-or-nothing. Personally I'm wondering about individual rows getting dropped on the client side. regards, tom lane *** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *** ---(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] automatic and randomally population
You can use loops (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html). Are you trying to do some performance analysis for the database server? -- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 7/28/06, gustavo halperin [EMAIL PROTECTED] wrote:Hello I want try my tables with some thousands of rows. There are anymanner to make automatic and randomly population ??Thank you,Gustavo---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] copy losing information
Silvela, Jaime \(Exchange\) [EMAIL PROTECTED] writes: Tom, how and why would INSERTs be dropped on the client side? [ shrug... ] I don't know your code; I was thinking about garden variety bugs in your ruby script. However, if you can make it happen just through psql \copy then that theory seems to lose its luster :-( Something else that might be worth looking at: have you got any user-written BEFORE INSERT triggers on that table? A trigger that sometimes returned NULL would explain the symptoms. regards, tom lane ---(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] Can't start PostgreSQL
Thank you all Deleting the old data base cleared up the problem. Ian On Thu, 2006-07-27 at 15:59 -0500, Bruno Wolff III wrote: On Thu, Jul 27, 2006 at 10:22:33 -0600, Ian Johnson [EMAIL PROTECTED] wrote: I want to develop an application in PostgreSQL but when starting the service I get the following message: An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.1.4/README.rpm-dist for more information. I am running Fedora Core 5 but initially had built the system with Fedora Core 3, with PostgreSQL installed and automatically updated it over the past 2 years. I may have created a test database 2 years ago which may be the source of the problem. Is this the source of the problem? I could find nothing in README.rpm-dist that helps. That could certainly be the problem. I would appreciate any advice on how to deal with this problem. Probably you want to delete the old database cluster and do a dbinit to create a new one. The old one will probably be in /var/lib/pgsql/data . ---(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] newbie here
Hi all, I'm using postgresql version 8.1 on FREEBSD 6.xsome info - bash-2.05b# pkg_info | grep postpostgresql-client-8.1.4 PostgreSQL database (client)postgresql-docs-8.1.4 The PostgreSQL documentation setpostgresql-relay-1.3.1 Multiplex multiple PostgreSQL databases to one relaypostgresql-server-8.1.4 The most advanced open-source database available anywherebash-2.05b#i've already configured the file postgresql.conf (at /usr/local/pgsql/data path) to send te log to /var/log/pgsql/ directory and it's working but How can I get more info on the log fileI need to get the erros generated by php sessions or requests generetad by my web serverregards,--- postgresql.conf ---without #commented linesmax_connections = 40shared_buffers = 1000 # min 16 or max_connections*2, 8KB eachlog_destination = 'syslog'#log_destination = 'stderr' # Valid values are combinations oflog_destination = 'stderr' # Valid values are combinations of # stderr, syslog and eventlog, # depending on platform.redirect_stderr = on # Enable capturing of stderr into loglog_directory = '/var/log/pgsql/' # Directory where log files are writtenlog_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.log_rotation_age = 1440 # Automatic rotation of logfiles willlog_rotation_size = 10240 # Automatic rotation of logfiles willclient_min_messages = log # Values, in order of decreasing detail:log_min_messages = notice # Values, in order of decreasing detail:log_error_verbosity = default # terse, default, or verbose messagessilent_mode = onlog_connections = onlog_disconnections = onlog_line_prefix = '%u %d %h %r %i' # Special values:log_statement = 'all' # none, mod, ddl, alllog_hostname = on- Thiago Beier __Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/
Re: [GENERAL] automatic and randomally population
On 7/28/06, Shoaib Mir [EMAIL PROTECTED] wrote: You can use loops (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html ). Thanks Are you trying to do some performance analysis for the database server? Yes I will, but first I want see how the Open Office behave with larger DB. I need to build a DB for an old man, and I want give to him the easier client toolkit application. Do you have any recommendation ?? Thank you again, Gustavo-- Shoaib MirEnterpriseDB ( www.enterprisedb.com)On 7/28/06, gustavo halperin [EMAIL PROTECTED] wrote: Hello I want try my tables with some thousands of rows. There are anymanner to make automatic and randomly population ??Thank you,Gustavo---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
[GENERAL] Consulta de importar o restaurar base
Muy buenas, pues estoy dando mis primeros pininos en postgres, y pues me veo en la necesidad de restaurar un backup de una base que es de SQL SERVER, pero la verdad he buscado informacion sobre pg_restore y segun la informacion que tengo lo hago asi pg_restore -f nombre.bak pero no me funciona. Les agradecere me ayuden, ademas no se si alguien me puede orientar con el modulo de cube de postgres. Ya que en mi trabajo estan queriendo migrar asia SQL SERVER, pero quiero proponerles que migremos mejor a POSTGRES, pero necesito levantar esos datos, ademas necesito crear un par de cubos de informacion, quiero hacerlo para que ellos vean funcionar la base, y asi pues tenga mas peso mi propuesta de migrar a POSTGRES. Muchas gracias
Re: [GENERAL] newbie here
Thiago Germano Beier wrote: Hi all, I'm using postgresql version 8.1 on FREEBSD 6.x some info - bash-2.05b# pkg_info | grep post postgresql-client-8.1.4 PostgreSQL database (client) postgresql-docs-8.1.4 The PostgreSQL documentation set postgresql-relay-1.3.1 Multiplex multiple PostgreSQL databases to one relay postgresql-server-8.1.4 The most advanced open-source database available anywhere bash-2.05b# i've already configured the file postgresql.conf (at /usr/local/pgsql/data path) to send te log to /var/log/pgsql/ directory and it's working but How can I get more info on the log file I need to get the erros generated by php sessions or requests generetad by my web server That has nothing to do with postgres, that depends on your php set up (see log_errors error_log in the php.ini file) and your webserver setup (depends what webserver it is - apache,lighttpd etc). Or am I misunderstanding the question (most likely!). -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
