date:20060105

Does anyone know where I can find the 'official' definition of what it
meant by ACID compliance?

We're having a discussion about it that we could do with resolving. In
particular, the key point is what it meant by the 'C' part. I maintain
that MySQL is not ACID compliant because it will (among other things)
swallow integers that don't fit into a column silently and just
truncate it, while our DBA (while agreeing that this is not good
behaviour) maintains that this is not what the C part means: he says
that's just about transaction states (succeed or fail etc).

Anyone have a link?

Thanks.

-- 

Russ

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread Alan Garrison


Russ Brown wrote:

Does anyone know where I can find the 'official' definition of what it
meant by ACID compliance?

We're having a discussion about it that we could do with resolving. In
particular, the key point is what it meant by the 'C' part. I maintain
that MySQL is not ACID compliant because it will (among other things)
swallow integers that don't fit into a column silently and just
truncate it, while our DBA (while agreeing that this is not good
behaviour) maintains that this is not what the C part means: he says
that's just about transaction states (succeed or fail etc).

Anyone have a link?

Thanks.




Pretty good overview, though not official:

http://en.wikipedia.org/wiki/ACID

The ACID concept is described in ISO/IEC 10026-1:1992 Section 4.



--
Alan Garrison
Cronosys, LLC http://www.cronosys.com
Phone: 216-221-4600 ext 308


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Problem with Starting server 7.4.10_1 on FreeBSD

2006-01-05 Thread |N_E_O|

On 1/5/06, Richard Huxton dev@archonet.com wrote:
|N_E_O| wrote: It did the initdb command(/usr/local/etc/rc.d/010.pgsql.sh initdb) and got an error: initdb: file /usr/local/share/postgresql/postgres.bki not found
 This means you have a corrupted installation or identified the wrong directory with the invocation option -L.1. What is in /usr/local/share/postgresql/ ?2. What does locate postgres.bki show?
-- Richard Huxton Archonet Ltd

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 2006-01-05 at 08:58, Russ Brown wrote:
 Does anyone know where I can find the 'official' definition of what it
 meant by ACID compliance?
 
 We're having a discussion about it that we could do with resolving. In
 particular, the key point is what it meant by the 'C' part. I maintain
 that MySQL is not ACID compliant because it will (among other things)
 swallow integers that don't fit into a column silently and just
 truncate it, while our DBA (while agreeing that this is not good
 behaviour) maintains that this is not what the C part means: he says
 that's just about transaction states (succeed or fail etc).

This seems a fairly good, short one:

http://databases.about.com/od/specificproducts/a/acid.htm

The C stands for consistency.  Consistency means that only valid data
can be written to the database.  MySQL fails this test precisely because
it does / can write inconsistent data to the database.  Note that even
the latest version, 5.0.xx, by default, inserts a truncated number on
overflow.  It does issue a warning, but that's little consolation now
that your data has been rendered inconsistent.  There is a switch you
can throw that tells it to only accept proper values, but get this,
users can turn it off.  So, there's no way to ensure that the database
enforces proper constraint on the values being inserted.

And why, after all this work, does MySQL still not have check
constraints.  They'd have to be easier to implement than some of the
other features they've implemented so far.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Monitoring PostgreSQL connections using cricket and

2006-01-05 Thread Steve Crawford


Resting Connections - connections that have no query information. I
suspect these are connections starting up or shutting down, but feel
free to clue me in.


OK, this appears to be version-dependent but it can mean that 
stats_query_string is false or that the user you are connecting as has 
no permission to see the query of the other user.



I thought about graphing the number of SELECTs/INSERTS/UPDATEs/DELETEs
currently running. If anyone is interested, I think it would be easy
to handle.


Each time you run your script you will only have a snapshot at that 
instant. It might provide minimally useful information to someone who is 
clear about what they are seeing but that snapshot could show a 
connection as idle even though it is handling hundreds of small queries 
per minute or a connection as running a query even though it sits idle 
in a connection pool nearly all the time.


It certainly won't tell you the server-load (you didn't claim it would, 
of course). A single huge or badly-written query can hose a server while 
piles of quick queries will hardly load it at all.


A snapshot showing a large number of running queries may even be fine if 
they are all backed up waiting for a few-second-long table-lock to be 
released.


For finding potential problems you should consider looking for idle in 
transaction queries - especially any that are aging as they can 
indicate that something has failed to commit or rollback a transaction. 
This can be especially bad on a pool-connection. Until the transaction 
is closed, locks can remain in place and start to cause all sorts of 
trouble.


Cheers,
Steve

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[GENERAL] Getting timestamp without milliseconds

2006-01-05 Thread Tadej Kanizar









Hi!



Ive got a timestamp field.. and it returns sth like 2006-01-04
21:33:17.156. How can I get the same result, just without the .156? Ive
looked at date/time formatting functions, etc, but couldnt find a
straight way to do this L

Thanks.



Regards,

Tadej

Re: [GENERAL] Getting timestamp without milliseconds

2006-01-05 Thread John Sidney-Woollett


Either date_trunc

eg, SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

Or format the timestamp as a string

select to_char(now(), '-mm-dd HH24:MI:SS');

Hope that helps.

John

Tadej Kanizar wrote:

Hi!

 


I've got a timestamp field.. and it returns sth like 2006-01-04
21:33:17.156. How can I get the same result, just without the .156? I've
looked at date/time formatting functions, etc, but couldn't find a straight
way to do this :-(

Thanks.

 


Regards,

Tadej




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Getting timestamp without milliseconds

2006-01-05 Thread Tom Lane

Tadej Kanizar [EMAIL PROTECTED] writes:
 I've got a timestamp field.. and it returns sth like 2006-01-04
 21:33:17.156. How can I get the same result, just without the .156?

Cast to timestamp(0).

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Getting timestamp without milliseconds

On Thu, 2006-01-05 at 10:04, Tadej Kanizar wrote:
 Hi!
 
  
 
 I’ve got a timestamp field.. and it returns sth like “2006-01-04
 21:33:17.156”. How can I get the same result, just without the .156?
 I’ve looked at date/time formatting functions, etc, but couldn’t find
 a straight way to do this L

Here ya go:

test= create table t1 (ts timestamp);
CREATE TABLE
test= insert into t1 values (now());
INSERT 2106750874 1
test= select ts from t1;
 ts

 2006-01-05 10:15:48.167951
(1 row)
 
test= select cast (ts as timestamp(0)) from t1;
 ts
-
 2006-01-05 10:15:48
(1 row)
 
test= select ts::timestamp(0) from t1;
 ts
-
 2006-01-05 10:15:48
(1 row)


---(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] 'Official' definition of ACID compliance?

2006-01-05 Thread Steve Crawford


Alan Garrison wrote:

Russ Brown wrote:

Does anyone know where I can find the 'official' definition of what it
meant by ACID compliance?

We're having a discussion about it that we could do with resolving. In
particular, the key point is what it meant by the 'C' part. I maintain
that MySQL is not ACID compliant because it will (among other things)
swallow integers that don't fit into a column silently and just
truncate it, while our DBA (while agreeing that this is not good
behaviour) maintains that this is not what the C part means: he says
that's just about transaction states (succeed or fail etc).


I personally read C to disallow MySQL's truncation behavior. Suppose 
you have a simple/stupid banking database with only one table listing 
the amount in the bank for each customer. The manager sums up the 
balances to find out how much is in the bank. Meanwhile Larry owes Jane 
some money so he has it transferred to her account. The balance the 
manager gets should be identical whether she runs the query before, 
during or after the transaction.


Now Jane is a good saver so when Larry transfers the money, MySQL 
truncates her account at the max amount allowed by the column-type. The 
manager's reports will be incorrect as will Jane's account balance. 
Unless Larry and Jane bank at Enron SL, this is bound to violate some 
rule or integrity constraint.


But if you and the DBA are in agreement that MySQL behaves badly, why 
waste time arguing over which letter in some acronym is pertinent?


Cheers,
Steve

---(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] Loading large amounts of data in a SQL command

2006-01-05 Thread Sean Davis




On 1/5/06 8:31 AM, frank church [EMAIL PROTECTED] wrote:

 
 
 I am loading lots of data via SQL into a database and wrapping it into
 transactions to speed it up.
 
 This fails a number of times and causes the whole transaction to fail. The
 queries results are logged so it is easy for me to find problem records.
 
 Is there a setting or feature that allows which allows the same performance as
 transactions, without causing the whole process to fail, like a delayed
 updates
 or write mechanism of some sort?

I typically load into a loader table (usually using copy rather than
inserts) that looks like the data rather than what you want the final data
to look like.  For example, if you have an integer field that happens to
contain a couple of non-numeric characters (127a, for example), then load
this column as a varchar.  Then, you can use all of the various regex
commands, coercion functions, etc that postgres has to offer to select from
the loader table into your clean production table.  This has the advantage
of being VERY fast, allows you to do a lot of data munging very easily, and
avoids having to continually clean the data before it successfully inserts
into the database where you can work with it.

In fact, I am often faced with non-normalized data in one large spreadsheet.
I could use perl or some other client to produce nice inserts into the
appropriate tables, but I find it easier to load the whole thing and then
just do selects to grab the data and put it into normalized form.

Hope that helps,
Sean



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 05 Jan 2006 08:22:01 -0800
Steve Crawford [EMAIL PROTECTED] wrote:

 Alan Garrison wrote:
  Russ Brown wrote:
  Does anyone know where I can find the 'official' definition of
  what it meant by ACID compliance?
 
  We're having a discussion about it that we could do with
  resolving. In particular, the key point is what it meant by the
  'C' part. I maintain that MySQL is not ACID compliant because it
  will (among other things) swallow integers that don't fit into a
  column silently and just truncate it, while our DBA (while
  agreeing that this is not good behaviour) maintains that this is
  not what the C part means: he says that's just about transaction
  states (succeed or fail etc).
 
 I personally read C to disallow MySQL's truncation behavior.
 Suppose you have a simple/stupid banking database with only one table
 listing the amount in the bank for each customer. The manager sums up
 the balances to find out how much is in the bank. Meanwhile Larry
 owes Jane some money so he has it transferred to her account. The
 balance the manager gets should be identical whether she runs the
 query before, during or after the transaction.
 
 Now Jane is a good saver so when Larry transfers the money, MySQL 
 truncates her account at the max amount allowed by the column-type.
 The manager's reports will be incorrect as will Jane's account
 balance. Unless Larry and Jane bank at Enron SL, this is bound to
 violate some rule or integrity constraint.
 

Yes, I agree entirely. Actually, reading the wikipedia definition it
looks like what he was talking about is actually covered by the 'A'
part of ACID.

 But if you and the DBA are in agreement that MySQL behaves badly, why 
 waste time arguing over which letter in some acronym is pertinent?
 

Oh, that's a long story. We're a MySQL house that I've been trying to
convert to PostgreSQL one way or the other for ages (with no success as
yet). Note that the argument isn't about which letter the type
truncation applies to, but whether it actually has anything to do
with ACID at all in the first place. The key for me is that the result of this 
argument has an
effect on the question: Is MySQL ACID compliant. If I'm right, it's
not (which has political strategic benefits to me).

Thanks.

-- 

Russ

---(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] 'Official' definition of ACID compliance?

2006-01-05 Thread Stephen Frost

* Russ Brown ([EMAIL PROTECTED]) wrote:
 Oh, that's a long story. We're a MySQL house that I've been trying to
 convert to PostgreSQL one way or the other for ages (with no success as
 yet). Note that the argument isn't about which letter the type
 truncation applies to, but whether it actually has anything to do
 with ACID at all in the first place. The key for me is that the result of 
 this argument has an
 effect on the question: Is MySQL ACID compliant. If I'm right, it's
 not (which has political strategic benefits to me).

An even better thing to point out is that a DBA recommending MySQL isn't
a DBA at all. :)

Enjoy,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] 'Official' definition of ACID compliance?

On 1/5/06, Stephen Frost [EMAIL PROTECTED] wrote:
 * Russ Brown ([EMAIL PROTECTED]) wrote:
  Oh, that's a long story. We're a MySQL house that I've been trying to
  convert to PostgreSQL one way or the other for ages (with no success as
  yet). Note that the argument isn't about which letter the type
  truncation applies to, but whether it actually has anything to do
  with ACID at all in the first place. The key for me is that the result of 
  this argument has an
  effect on the question: Is MySQL ACID compliant. If I'm right, it's
  not (which has political strategic benefits to me).

 An even better thing to point out is that a DBA recommending MySQL isn't
 a DBA at all. :)


or is one that _loves risk_   ;)


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread John Dean


At 16:38 05/01/2006, Stephen Frost wrote:

* Russ Brown ([EMAIL PROTECTED]) wrote:
 Oh, that's a long story. We're a MySQL house that I've been trying to
 convert to PostgreSQL one way or the other for ages (with no success as
 yet). Note that the argument isn't about which letter the type
 truncation applies to, but whether it actually has anything to do
 with ACID at all in the first place. The key for me is that the result 
of this argument has an

 effect on the question: Is MySQL ACID compliant. If I'm right, it's
 not (which has political strategic benefits to me).

An even better thing to point out is that a DBA recommending MySQL isn't
a DBA at all. :)

Enjoy,

Stephen


I used to work for MySQL (a job's a job after all) and I say in all honesty 
that MySQL is not ACID compliant. Furthermore, MySQL is so lacked in 
functionality that it should be used for anything but the simplest of 
solutions. A database engine that does not support referential integrity, 
triggers, stored procedures, user defined types, etc should not be taken 
seriously




---

Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Putting restrictions on pg_dump?

2006-01-05 Thread Benjamin Smith

Good ideas, all. but, what about keeping things like check constraints, 
foreign keys, etc? 

Hmmm... maybe, if I dumped the entire DB schema, with no data, and then looped 
thru the tables, creating a temp table (as you describe) with a funky name 
(such as TABLEaBcDeFgH_U) and then pg_dumping that, and then using a regex to 
rename the table in the output... (eg 

/TABLE\s+TABLEaBcDeFgH_U/TABLE customers/

Ugh. I was hoping there was a cleaner way...

-Ben 

On Wednesday 04 January 2006 23:35, you wrote:
 On Wed, 4 Jan 2006 21:00:25 -0800, Benjamin Smith [EMAIL PROTECTED] 
wrote:
  Is there a way to put a limit on pg_dump, so that it doesn't dump ALL 
data, 
  but that matching a particular query? 
  
  Something like: 
  
  pg_dump -da --attribute-inserts -t customers \
  --matching-query=select * from customers where id=11; 
  
  I'd like to selectively dump information from a query, but using the 
output 
  format from pg_dump so that it can be used to create a (partial) database. 
  
  Can this sort of thing be done? 
 
 Not directly with pg_dump.  
 
 You could create a table (create table customers_1 as select * from
 customers where id=11) and dump that but remember to change the
 tablename in the dump file or after loading it. You dont get any
 pk/fk/indexes on the table definition.
 
 You could also use copy to stdout/stdin.
 
 eg dump
 psql -d dbname -c create temp table dump as select * from customers
 where id=11; copy dump to stdout; dumpfile
 
 eg restore
 psql -d newdb -c copy customers from stdin dumpfile
 
 You might need to play around with supplying username/password.
 
 klint.
 
 +---+-+
 : Klint Gore: Non rhyming:
 : EMail   : [EMAIL PROTECTED]   :  slang - the:
 : Snail   : A.B.R.I.:  possibilities  :
 : Mail  University of New England   :  are useless   :
 :   Armidale NSW 2351 Australia : L.J.J.  :
 : Fax : +61 2 6772 5376 : :
 +---+-+
 

-- 
The best way to predict the future is to invent it.
- XEROX PARC slogan, circa 1978

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] java.sql.SQLException: ERROR: invalid byte sequence for encoding UNICODE: 0xe3936e

2006-01-05 Thread F. Fernandez


Hi!

I'm using PostgreSQL 8.0.4 with UNICODE encoding  and accessing it with
JDBC3 driver 8.0.313. Sometimes, we get this error:

java.sql.SQLException: ERROR: invalid byte sequence for encoding 
UNICODE: 0xe3936e
	at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
	at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
	at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
	at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
	at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
	at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)

at pt.moredata.dao.core.SQLReader._read(SQLReader.java:256)

I'm not sure what I can do. I thought of these alternatives:

   * May this be a network error? We're using a wireless network and
 sometimes it has problems. Would SSL help reducing comms errors,
 since it has more accurate error detection than TCP?
   * May this be caused by bad data coming from the java app? Is this
 possible, knowing that Java always works in Unicode internally?

Any thoughts?

Thanks in advance.

Fernando

--
Fernando Fernandez
http://www.moredata.pt/



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread Peter Eisentraut

Am Donnerstag, 5. Januar 2006 17:01 schrieb Scott Marlowe:
 The C stands for consistency.  Consistency means that only valid data
 can be written to the database.  MySQL fails this test precisely because
 it does / can write inconsistent data to the database.  Note that even
 the latest version, 5.0.xx, by default, inserts a truncated number on
 overflow.

That's not at all what the C is about.  The C criterion means that a 
transaction transfers the database from one consistent state to another.  To 
my knowledge, MySQL does that.  On its way there, it silently alters data 
that would violate this consistency criterion, but this does not affect the 
fulfillment of the ACID criteria.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] 'Official' definition of ACID compliance?

On 1/5/06, Peter Eisentraut [EMAIL PROTECTED] wrote:
 Am Donnerstag, 5. Januar 2006 17:01 schrieb Scott Marlowe:
  The C stands for consistency.  Consistency means that only valid data
  can be written to the database.  MySQL fails this test precisely because
  it does / can write inconsistent data to the database.  Note that even
  the latest version, 5.0.xx, by default, inserts a truncated number on
  overflow.

 That's not at all what the C is about.  The C criterion means that a
 transaction transfers the database from one consistent state to another.  To
 my knowledge, MySQL does that.  On its way there, it silently alters data
 that would violate this consistency criterion, but this does not affect the
 fulfillment of the ACID criteria.


so the problem is that MySQL _forces_ a consistent state but in the
process it violates the integrity of the data

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(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] Drop database/create database broken in 7.4?

2006-01-05 Thread John Wells

Guys,

I'm running PG 7.4 on Ubuntu Breezy for Ruby on Rails development. One of
the things I ofter have to do is rebuild the database...for which I have a
script that I've created to do it in one step.

I've noticed some oddities. The script connects to template1, drops the
target database, creates the target database, and the rebuilds the tables.
However, two specific tables and sequences are not dropped, although  the
drop command completes successfully.

For example, here's the error reproduced manually:

dailystatus= \d
  List of relations
 Schema |  Name   |   Type   | Owner
+-+--+
 public | garages | table| wellsj
 public | garages_id_seq  | sequence | wellsj
 public | trucks  | table| wellsj
 public | trucks_id_seq   | sequence | wellsj
 public | sites   | table| wellsj
 public | sites_id_seq| sequence | wellsj
 public | statuses| table| wellsj
 public | statuses_id_seq | sequence | wellsj
 public | users   | table| wellsj
 public | users_id_seq| sequence | wellsj
(10 rows)

dailystatus= \c template1
You are now connected to database template1.
template1= drop database dailystatus;
DROP DATABASE
template1= create database dailystatus;
CREATE DATABASE
template1= \c dailystatus
You are now connected to database dailystatus.
dailystatus= \d
 List of relations
 Schema | Name |   Type   | Owner
+--+--+
 public | sites| table| wellsj
 public | sites_id_seq | sequence | wellsj
 public | users| table| wellsj
 public | users_id_seq | sequence | wellsj
(4 rows)

dailystatus=

Anyone know what might be happening? I'm at a loss.

Thanks!
John



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Drop database/create database broken in 7.4?

On Thu, 2006-01-05 at 13:09, John Wells wrote:
 Guys,
 
 I'm running PG 7.4 on Ubuntu Breezy for Ruby on Rails development. One of
 the things I ofter have to do is rebuild the database...for which I have a
 script that I've created to do it in one step.
 
 I've noticed some oddities. The script connects to template1, drops the
 target database, creates the target database, and the rebuilds the tables.
 However, two specific tables and sequences are not dropped, although  the
 drop command completes successfully.
 
 For example, here's the error reproduced manually:
 
 dailystatus= \d
   List of relations
  Schema |  Name   |   Type   | Owner
 +-+--+
  public | garages | table| wellsj
  public | garages_id_seq  | sequence | wellsj
  public | trucks  | table| wellsj
  public | trucks_id_seq   | sequence | wellsj
  public | sites   | table| wellsj
  public | sites_id_seq| sequence | wellsj
  public | statuses| table| wellsj
  public | statuses_id_seq | sequence | wellsj
  public | users   | table| wellsj
  public | users_id_seq| sequence | wellsj
 (10 rows)
 
 dailystatus= \c template1
 You are now connected to database template1.
 template1= drop database dailystatus;
 DROP DATABASE
 template1= create database dailystatus;
 CREATE DATABASE
 template1= \c dailystatus
 You are now connected to database dailystatus.
 dailystatus= \d
  List of relations
  Schema | Name |   Type   | Owner
 +--+--+
  public | sites| table| wellsj
  public | sites_id_seq | sequence | wellsj
  public | users| table| wellsj
  public | users_id_seq | sequence | wellsj
 (4 rows)
 
 dailystatus=
 
 Anyone know what might be happening? I'm at a loss.


Yep, somewhere along the lines you accidentally created those tables in
template1

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Monitoring PostgreSQL connections using cricket and

2006-01-05 Thread Tony Wasson

On 1/5/06, Steve Crawford [EMAIL PROTECTED] wrote:
  Resting Connections - connections that have no query information. I
  suspect these are connections starting up or shutting down, but feel
  free to clue me in.

 OK, this appears to be version-dependent but it can mean that
 stats_query_string is false or that the user you are connecting as has
 no permission to see the query of the other user.

Ok, thanks. I forgot to mention that you do have to run this as
superuser to see the query information.

  I thought about graphing the number of SELECTs/INSERTS/UPDATEs/DELETEs
  currently running. If anyone is interested, I think it would be easy
  to handle.

 Each time you run your script you will only have a snapshot at that
 instant. It might provide minimally useful information to someone who is
 clear about what they are seeing but that snapshot could show a
 connection as idle even though it is handling hundreds of small queries
 per minute or a connection as running a query even though it sits idle
 in a connection pool nearly all the time.

Right, this is certainly far from comprehensive. I basically needed to
watch connection utilization so that was my primary focus. I have been
able to spot a few cases where pooling wasn't doing what we wanted or
expecting using this kind of information.

 It certainly won't tell you the server-load (you didn't claim it would,
 of course). A single huge or badly-written query can hose a server while
 piles of quick queries will hardly load it at all.

I'd suggest using this in conjuction with the system monitoring tools
available over SNMP and in cricket contrib. That can get you load,
memory, cpu, processes, packets and bandwidth. I think that get's you
a lot of what you need to keep a server happy.

 A snapshot showing a large number of running queries may even be fine if
 they are all backed up waiting for a few-second-long table-lock to be
 released.

 For finding potential problems you should consider looking for idle in
 transaction queries - especially any that are aging as they can
 indicate that something has failed to commit or rollback a transaction.
 This can be especially bad on a pool-connection. Until the transaction
 is closed, locks can remain in place and start to cause all sorts of
 trouble.

Yeah, locks can be interesting to troubleshoot. I ended up with a view
combining pg_stat_activity joined with pg_locks to see what queries
are causing what locks and how long they have been running.

On a longer term, it would be useful to NOT need to run pqa to see a
profile of what % of my queries are SELECT/INSERT/UPDATE/DELETE. Then
as things change, I'd drill down with the details pqa provides to see
what changed. PQA rocks, but it is an annoyance to need to flick on
logging and run that through pqa.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Drop database/create database broken in 7.4?

2006-01-05 Thread Jim Buttafuoco

John

check to see if the objects are in the template1 database which just gets 
copied 100% to the target db.

Jim



-- Original Message ---
From: John Wells [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Thu, 5 Jan 2006 14:09:02 -0500 (EST)
Subject: [GENERAL] Drop database/create database broken in 7.4?

 Guys,
 
 I'm running PG 7.4 on Ubuntu Breezy for Ruby on Rails development. One of
 the things I ofter have to do is rebuild the database...for which I have a
 script that I've created to do it in one step.
 
 I've noticed some oddities. The script connects to template1, drops the
 target database, creates the target database, and the rebuilds the tables.
 However, two specific tables and sequences are not dropped, although  the
 drop command completes successfully.
 
 For example, here's the error reproduced manually:
 
 dailystatus= \d
   List of relations
  Schema |  Name   |   Type   | Owner
 +-+--+
  public | garages | table| wellsj
  public | garages_id_seq  | sequence | wellsj
  public | trucks  | table| wellsj
  public | trucks_id_seq   | sequence | wellsj
  public | sites   | table| wellsj
  public | sites_id_seq| sequence | wellsj
  public | statuses| table| wellsj
  public | statuses_id_seq | sequence | wellsj
  public | users   | table| wellsj
  public | users_id_seq| sequence | wellsj
 (10 rows)
 
 dailystatus= \c template1
 You are now connected to database template1.
 template1= drop database dailystatus;
 DROP DATABASE
 template1= create database dailystatus;
 CREATE DATABASE
 template1= \c dailystatus
 You are now connected to database dailystatus.
 dailystatus= \d
  List of relations
  Schema | Name |   Type   | Owner
 +--+--+
  public | sites| table| wellsj
  public | sites_id_seq | sequence | wellsj
  public | users| table| wellsj
  public | users_id_seq | sequence | wellsj
 (4 rows)
 
 dailystatus=
 
 Anyone know what might be happening? I'm at a loss.
 
 Thanks!
 John
 
 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?
 
http://archives.postgresql.org
--- End of Original Message ---


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Drop database/create database broken in 7.4?

2006-01-05 Thread Joe Conway


John Wells wrote:


I've noticed some oddities. The script connects to template1, drops the
target database, creates the target database, and the rebuilds the tables.
However, two specific tables and sequences are not dropped, although  the
drop command completes successfully.



Anyone know what might be happening? I'm at a loss.



Sounds like those two tables were created in the template database at 
some point.


HTH,

Joe

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] NEW variable values in trigger functions

2006-01-05 Thread Ken Winter

1. What is the value of the NEW variable for a column that is not mentioned
in an UPDATE statement?  Is it NULL?  If not NULL, what?

For example, given this table:

   my_tbl (id integer, att1 varchar, att2 varchar)

and a row-wise ON UPDATE OR INSERT trigger function containing this
conditional:

   IF NEW.att2 IS NULL THEN
do stuff
   END IF;
   
and this UPDATE query:

UPDATE my_tbl SET att1 = 'foo' where id = 1;

will that conditional be satisfied?

2. Same questions re the value of a NEW variable that is not assigned a
value in an INSERT statement.  

For example, how would the previous conditional behave in response to:

INSERT INTO my_tbl (id) VALUES (1);

?

3. If an UPDATE query set a column to DEFAULT, what value does a trigger
function see for the column's NEW variable?  Is it the string 'DEFAULT', a
reserved word DEFAULT, an empty string, or what?

For example, what would you put in place of ?? in this UPDATE trigger
function:

   IF NEW.att2 ?? THEN
do stuff
   END IF;

to get it to do stuff in response to this UPDATE query:

UPDATE my_tbl SET att2 = DEFAULT where id = 1;

?

~ TIA
~ Ken



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Drop database/create database broken in 7.4?

2006-01-05 Thread John Wells

Scott Marlowe said:
 Yep, somewhere along the lines you accidentally created those tables in
 template1

sheepish grin yep, sorry guys. I need more sleep /sheepish grin

Thanks!
John


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] ACID compliance

2006-01-05 Thread John Meyer


Okay, what exactly is ACID compliance?

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Drop database/create database broken in 7.4?

On Thu, 2006-01-05 at 13:30, John Wells wrote:
 Scott Marlowe said:
  Yep, somewhere along the lines you accidentally created those tables in
  template1
 
 sheepish grin yep, sorry guys. I need more sleep /sheepish grin

Hey, how do you think we all recognized that mistake so quickly.  We've
all done it before. :)

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 2006-01-05 at 11:39, John Dean wrote:
 At 16:38 05/01/2006, Stephen Frost wrote:
 * Russ Brown ([EMAIL PROTECTED]) wrote:
   Oh, that's a long story. We're a MySQL house that I've been trying to
   convert to PostgreSQL one way or the other for ages (with no success as
   yet). Note that the argument isn't about which letter the type
   truncation applies to, but whether it actually has anything to do
   with ACID at all in the first place. The key for me is that the result 
  of this argument has an
   effect on the question: Is MySQL ACID compliant. If I'm right, it's
   not (which has political strategic benefits to me).
 
 An even better thing to point out is that a DBA recommending MySQL isn't
 a DBA at all. :)
 
  Enjoy,
 
  Stephen
 
 I used to work for MySQL (a job's a job after all) and I say in all honesty 
 that MySQL is not ACID compliant. Furthermore, MySQL is so lacked in 
 functionality that it should be used for anything but the simplest of 
 solutions. A database engine that does not support referential integrity, 
 triggers, stored procedures, user defined types, etc should not be taken 
 seriously

PHP 5.0 has most of those features now.  It's just the inability of the
DBA to force things like certain tables to be used that I hate about it.

That and even in V 5 it sill ignores row level foreign key definitions
(they have to be done at the end of the column list) silently.

I bet in another year or two MySQL will be breathing down the neck of
PostgreSQL V 6.5.3 in terms of features and proper operation.

---(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] 'Official' definition of ACID compliance?

On Thu, 2006-01-05 at 13:24, Scott Marlowe wrote:
 On Thu, 2006-01-05 at 11:39, John Dean wrote:
  At 16:38 05/01/2006, Stephen Frost wrote:
  * Russ Brown ([EMAIL PROTECTED]) wrote:
Oh, that's a long story. We're a MySQL house that I've been trying to
convert to PostgreSQL one way or the other for ages (with no success as
yet). Note that the argument isn't about which letter the type
truncation applies to, but whether it actually has anything to do
with ACID at all in the first place. The key for me is that the result 
   of this argument has an
effect on the question: Is MySQL ACID compliant. If I'm right, it's
not (which has political strategic benefits to me).
  
  An even better thing to point out is that a DBA recommending MySQL isn't
  a DBA at all. :)
  
   Enjoy,
  
   Stephen
  
  I used to work for MySQL (a job's a job after all) and I say in all honesty 
  that MySQL is not ACID compliant. Furthermore, MySQL is so lacked in 
  functionality that it should be used for anything but the simplest of 
  solutions. A database engine that does not support referential integrity, 
  triggers, stored procedures, user defined types, etc should not be taken 
  seriously
 
 PHP 5.0 has most of those features now.  It's just the inability of the
 DBA to force things like certain tables to be used that I hate about it.

That should be MySQL 5... ugh.  not enough coffee or sleep lately

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] ACID compliance

On 1/5/06, John Meyer [EMAIL PROTECTED] wrote:
 Okay, what exactly is ACID compliance?


http://en.wikipedia.org/wiki/ACID

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] ACID compliance

2006-01-05 Thread Jeffrey Webster

Atomicity, Consistency, Isolation and DurabilitySee:http://cegt201.bradley.edu/projects/proj2003/equiprd/acid.html
On 1/5/06, John Meyer [EMAIL PROTECTED] wrote:
Okay, what exactly is ACID compliance?---(end of broadcast)---TIP 6: explain analyze is your friend

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread Richard_D_Levine



[EMAIL PROTECTED] wrote on 01/05/2006 01:59:52 PM:
snip
 so the problem is that MySQL _forces_ a consistent state but in the
 process it violates the integrity of the data

That is a contradiction in terms.  Data integrity is a requirement of
database consistency.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Best Data type for Binary Data?

2006-01-05 Thread Ketema Harris

Hi, I would like to store binary data from a tcpdump (libpcap) file in
a table. What is the best type to use? i have read posts saying lo,
oid, and bytea. Which one would be best for this scenario?Thanks, ketema

Re: [GENERAL] 'Official' definition of ACID compliance?

On 1/5/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:


 [EMAIL PROTECTED] wrote on 01/05/2006 01:59:52 PM:
 snip
  so the problem is that MySQL _forces_ a consistent state but in the
  process it violates the integrity of the data
 
 That is a contradiction in terms.  Data integrity is a requirement of
 database consistency.



maybe, but it seems what happen in MySQL... because it forces a
consistent state (one the fullfill the rules and constraints of the
database) but when doing it it breaks or silently change your data...

so the data can be saved because it's legal data but not correct
data... then it is consistent to the machine but not for you...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(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] Suse Linux 10.0

2006-01-05 Thread Joseph M. Day

Has anyone been able to get the latest version of Postgres working on
Suse 10.0 ? I just switched form Fedora and realized that this version
is not specifically supported. 

If so, can I use the RPM's or do I need to recompile it ?

Thanks in advance!

Joe,



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Suse Linux 10.0

2006-01-05 Thread Moises Alberto Lindo Gutarra

i have compiled Postgresql 8.1.1 on OpenSuse 10.0
with out problems.

2006/1/5, Joseph M. Day [EMAIL PROTECTED]:
 Has anyone been able to get the latest version of Postgres working on
 Suse 10.0 ? I just switched form Fedora and realized that this version
 is not specifically supported.

 If so, can I use the RPM's or do I need to recompile it ?

 Thanks in advance!

 Joe,



 ---(end of broadcast)---
 TIP 5: don't forget to increase your free space map settings



--
Atentamente,
 .~.
( 0 0 )  Moisés Alberto Lindo Gutarra
/  V  \   Asesor - Desarrollador Java / Open Source
   //   \\  TUMI Solutions S.A.C.
/((   _))\ Cel: 511-97366260  Trab: 511-3481104
   oo0 0oo MSN: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Suse Linux 10.0

2006-01-05 Thread Peter Eisentraut

Am Donnerstag, 5. Januar 2006 21:15 schrieb Joseph M. Day:
 Has anyone been able to get the latest version of Postgres working on
 Suse 10.0 ? I just switched form Fedora and realized that this version
 is not specifically supported.

Get it here: ftp://ftp.suse.com/pub/people/max/postgresql

---(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] 'Official' definition of ACID compliance?

On Thu, 2006-01-05 at 14:11, Jaime Casanova wrote:
 On 1/5/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
  [EMAIL PROTECTED] wrote on 01/05/2006 01:59:52 PM:
  snip
   so the problem is that MySQL _forces_ a consistent state but in the
   process it violates the integrity of the data
  
  That is a contradiction in terms.  Data integrity is a requirement of
  database consistency.
 
 
 
 maybe, but it seems what happen in MySQL... because it forces a
 consistent state (one the fullfill the rules and constraints of the
 database) but when doing it it breaks or silently change your data...
 
 so the data can be saved because it's legal data but not correct
 data... then it is consistent to the machine but not for you...

But it's not consistent.  Imagine we do the one where we take one from
peter and give it to paul.  If paul's account is stored in an int, and
is at 2147483647, and we add one, it does not increment, and it does not
cause an error that will force a transaction to roll back.

Here's a self contained example:

create table test (id int, nom text, bal int) engine=innodb;
insert into test values (1,'paul',2147483647);
insert into test values (2,'peter',2134);
select * from test;
select * from test;
+--+---++
| id   | nom   | bal|
+--+---++
|1 | paul  | 2147483647 |
|2 | peter |   2134 |
+--+---++
begin;
update test set bal=bal-1 where nom='peter';
update test set bal=bal+1 where nom='paul';
commit;
select * from test;
select * from test;
+--+---++
| id   | nom   | bal|
+--+---++
|1 | paul  | 2147483647 |
|2 | peter |   2133 |
+--+---++

We robbed peter, and we didn't even pay paul.

Now, you can turn off this behaviour by default with a startup switch,
but the user can then turn it back on for their session.

Note that one gets a warning when the second update fires.  No error, no
exception.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 5 Jan 2006 15:11:49 -0500
Jaime Casanova [EMAIL PROTECTED] wrote:

 On 1/5/06, [EMAIL PROTECTED]
 [EMAIL PROTECTED] wrote:
 
 
  [EMAIL PROTECTED] wrote on 01/05/2006 01:59:52 PM:
  snip
   so the problem is that MySQL _forces_ a consistent state but in
   the process it violates the integrity of the data
  
  That is a contradiction in terms.  Data integrity is a requirement
  of database consistency.
 
 
 
 maybe, but it seems what happen in MySQL... because it forces a
 consistent state (one the fullfill the rules and constraints of the
 database) but when doing it it breaks or silently change your data...
 
 so the data can be saved because it's legal data but not correct
 data... then it is consistent to the machine but not for you...
 

See, this is why I was looking for some sort of 'official' definition
of the term, to remove the ambiguity introduced by individual
interpretation. :)

Anyone know who came up with the term in the first place?

-- 

Russ

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Suse Linux 10.0

2006-01-05 Thread Joe Audette

I installed from rpms after adding some sources to YaST based on this article
http://www.thejemreport.com/mambo/content/view/178/42/

Not sure its the absolute latest version of postgres.

Hope it helps,

Joe
 
 
joe_audette [at] yahoo dotcom 
http://www.joeaudette.com 
http://www.mojoportal.com

- Original Message 
From: Joseph M. Day [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Thursday, January 05, 2006 2:15:32 PM
Subject: [GENERAL] Suse Linux 10.0

Has anyone been able to get the latest version of Postgres working on
Suse 10.0 ? I just switched form Fedora and realized that this version
is not specifically supported. 

If so, can I use the RPM's or do I need to recompile it ?

Thanks in advance!

Joe,



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings





---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread Peter Eisentraut

Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
 But it's not consistent.  Imagine we do the one where we take one from
 peter and give it to paul.  If paul's account is stored in an int, and
 is at 2147483647, and we add one, it does not increment, and it does not
 cause an error that will force a transaction to roll back.

The effects of the commands on the database are not sensible with respect to 
the intent of the commands, but the state of the database is consistent both 
before and afterwards with respect to the integrity constraints defined 
within the database.  That's what this is all about.  ACID is about 
transaction processing, not about SQL data type semantics.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Can't connect to postgresql using 'psql -d database -U user -W'

2006-01-05 Thread Rick Ellis

In article [EMAIL PROTECTED],
ctobini [EMAIL PROTECTED] wrote:

tobini% psql -d template1 -U postgres -W
password: postgres
psql: FATAL:  IDENT authentication failed for user postgres

Do you have an idea about this problem ?

Use the su command to become user postgres and then run the
psql command.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread Chris Travers


Peter Eisentraut wrote:


Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
 


But it's not consistent.  Imagine we do the one where we take one from
peter and give it to paul.  If paul's account is stored in an int, and
is at 2147483647, and we add one, it does not increment, and it does not
cause an error that will force a transaction to roll back.
   



The effects of the commands on the database are not sensible with respect to 
the intent of the commands, but the state of the database is consistent both 
before and afterwards with respect to the integrity constraints defined 
within the database.  That's what this is all about.  ACID is about 
transaction processing, not about SQL data type semantics.
 

This is true, however, one can make a strong case that MySQL still has 
issues with ACID complaincy.  For example, how do you have an ACID 
compliant full text index in MySQL?  Basically there are features in 
MySQL that depend on MyISAM tables and don't provide ACID compliance 
where it might be needed.


In essence all of MySQL's data integrity issues aside, it has features 
that are not ACID compliant that are ACID compliant with appropriate 
add-ons in PostgreSQL.


Best Wishes,
Chris Travers

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 5 Jan 2006 22:25:21 +0100
Peter Eisentraut [EMAIL PROTECTED] wrote:

 Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
  But it's not consistent.  Imagine we do the one where we take one
  from peter and give it to paul.  If paul's account is stored in an
  int, and is at 2147483647, and we add one, it does not increment,
  and it does not cause an error that will force a transaction to
  roll back.
 
 The effects of the commands on the database are not sensible with
 respect to the intent of the commands, but the state of the database
 is consistent both before and afterwards with respect to the
 integrity constraints defined within the database.  That's what this
 is all about.  ACID is about transaction processing, not about SQL
 data type semantics.
 

That argument holds true when you consider two key points in a
transaction: before and after. But there is also a third: the
transaction itself. i.e. the actual changes that are being made to the
database. If you take the example given earlier about peter and paul,
yes the database it in a consistent state both before and after the
transaction. But it's *not* in a consistent state when compared with
the transaction itself. The transaction asked that a field value be
incremented, and after the transaction concluded this had not
happened, yet the transaction was committed. ACID
compliance requires that either all or none of the operations in the
transaction happen. In this case one of them does not.

That's how I view it anyway, but from what I can see you can only get
at the 'official' definition if you pay for it.

-- 

Russ

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, Jan 05, 2006 at 03:00:37PM -0600, Russ Brown wrote:
 See, this is why I was looking for some sort of 'official' definition
 of the term, to remove the ambiguity introduced by individual
 interpretation. :)
 
 Anyone know who came up with the term in the first place?

According to Date in _An Introduction to Database Systems_, 8th ed.,
the source of ACID is the 1983 paper Principles of Transaction-Oriented
Database Recovery by Theo Härder and Andreas Reuter.  Date has some
interesting things to say about ACID:

  So ACID is a nice acronym -- but do the concepts it represents
  really stand up to close examination?  In this section, we present
  some evidence to suggest that the answer to this question is, in
  general, _no_.  (485)

-- 
Michael Fuhr

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] 'Official' definition of ACID compliance?

2006-01-05 Thread Richard_D_Levine



[EMAIL PROTECTED] wrote on 01/05/2006 04:00:37 PM:

 On Thu, 5 Jan 2006 15:11:49 -0500
 Jaime Casanova [EMAIL PROTECTED] wrote:

  On 1/5/06, [EMAIL PROTECTED]
  [EMAIL PROTECTED] wrote:
  
  
   [EMAIL PROTECTED] wrote on 01/05/2006 01:59:52 PM:
   snip
so the problem is that MySQL _forces_ a consistent state but in
the process it violates the integrity of the data
   
   That is a contradiction in terms.  Data integrity is a requirement
   of database consistency.
  
  
 
  maybe, but it seems what happen in MySQL... because it forces a
  consistent state (one the fullfill the rules and constraints of the
  database) but when doing it it breaks or silently change your data...
 
  so the data can be saved because it's legal data but not correct
  data... then it is consistent to the machine but not for you...
 

 See, this is why I was looking for some sort of 'official' definition
 of the term, to remove the ambiguity introduced by individual
 interpretation. :)

 Anyone know who came up with the term in the first place?

Two Points: Russ is right, nobody has answered his question.  One link is a
Wikipedia entry, and the other a college student's fulfillment of an
assignment (excellent work, but not authoritative, unless there's a
bibliography that I missed containing an authoritative source.)

Second, if the integer that overflowed was a foreign key, would you agree
that consistency has been destroyed?  Answer yes.

Another point: not all database constraints are coded in the database, but
are upheld by the application using the database.  If that application's
SQL malfunctions without a rollback, consistency is shot.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Best Data type for Binary Data?

2006-01-05 Thread Aaron Koning

BYTEA is the easiest from my point of view. I like being able to treat
my binary data more like any other field (e.g. date, text, etc). Heres
some light reading on the BLOB/BYTEA debate:
 http://search.postgresql.org/www.search?cs=utf-8fm=onst=20dt=backq=blob+bytea

AaronOn 1/5/06, Ketema Harris [EMAIL PROTECTED] wrote:
Hi, I would like to store binary data from a tcpdump (libpcap) file in
a table. What is the best type to use? i have read posts saying lo,
oid, and bytea. Which one would be best for this scenario?Thanks, ketema

Re: [GENERAL] 'Official' definition of ACID compliance?

On 1/5/06, Russ Brown [EMAIL PROTECTED] wrote:
 On Thu, 5 Jan 2006 22:25:21 +0100
 Peter Eisentraut [EMAIL PROTECTED] wrote:

  Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe:
   But it's not consistent.  Imagine we do the one where we take one
   from peter and give it to paul.  If paul's account is stored in an
   int, and is at 2147483647, and we add one, it does not increment,
   and it does not cause an error that will force a transaction to
   roll back.
 
  The effects of the commands on the database are not sensible with
  respect to the intent of the commands, but the state of the database
  is consistent both before and afterwards with respect to the
  integrity constraints defined within the database.  That's what this
  is all about.  ACID is about transaction processing, not about SQL
  data type semantics.
 

 That argument holds true when you consider two key points in a
 transaction: before and after. But there is also a third: the
 transaction itself. i.e. the actual changes that are being made to the
 database. If you take the example given earlier about peter and paul,
 yes the database it in a consistent state both before and after the
 transaction. But it's *not* in a consistent state when compared with
 the transaction itself. The transaction asked that a field value be
 incremented, and after the transaction concluded this had not
 happened, yet the transaction was committed. ACID
 compliance requires that either all or none of the operations in the
 transaction happen. In this case one of them does not.


and that is the A of ACID, Atomicity

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Hardware recommendation for PostgreSQL on Windows?

2006-01-05 Thread Bjørn T Johansen

I an planning to make a small Windows application and need a nice database 
I am used to using PostgreSQL
under Linux and I am thinking about using this under Windows but how much 
resources does it use under Windows?
The server will be running on the workstation along with the Windows 
application, so it should be lite...
And I know that maybe I should be using something else, like SQLite or 
something but I have never used it and
I don't want to spend too much time learning how to use other databases... And 
I am also going to use Delphi,
so I am not sure how well any other db is supported?


Regards,

BTJ

-- 
---
Bjørn T Johansen

[EMAIL PROTECTED]
---
Someone wrote:
I understand that if you play a Windows CD backwards you hear strange Satanic 
messages
To which someone replied:
It's even worse than that; play it forwards and it installs Windows
---

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Adding columns to a view

On Wed, Dec 28, 2005 at 07:29:28PM +0100, Ingo van Lil wrote:
 Now, if I want to get a list of users that have a certain combination of
 valid status entries (e.g. all users that have paid their annual fee and
 are not banned for some reason), I have to use several subselects:
 
 SELECT person_id FROM person WHERE
 EXISTS (SELECT 1 FROM status WHERE status_id=1
 AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until)
 AND NOT
 EXISTS (SELECT 1 FROM status WHERE status_id=2
 AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until);
 
 This is what I'd like to simplify: My matrix view should contain one
 line for each user and one boolean column for each possible status flag.
 The field content should be 'true' if the selected user has a currently
 valid status entry assigned to it. The above statement could be written
 a great deal shorter as:
 
 SELECT person_id FROM person_status_matrix WHERE paid AND NOT banned;

Another alternative would be to create a check_status function that
did the lookup for you. If done correctly (as in using SQL as the
language and setting it to STABLE), the optimizer should inline the
fuction, giving you the same performance as the 1st query but without
all the typing (btw, isn't that first query missing person_id as part of
the WHERE clause in the EXISTS subqueries?)
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Hardware recommendation for PostgreSQL on Windows?

2006-01-05 Thread Qingqing Zhou


Bj?rn T Johansen [EMAIL PROTECTED] wrote
I an planning to make a small Windows application and need a nice 
database I am used to using PostgreSQL
 under Linux and I am thinking about using this under Windows but how much 
 resources does it use under Windows?
 The server will be running on the workstation along with the Windows 
 application, so it should be lite...
 And I know that maybe I should be using something else, like SQLite or 
 something but I have never used it and
 I don't want to spend too much time learning how to use other databases... 
 And I am also going to use Delphi,
 so I am not sure how well any other db is supported?



I am not sure how exactly you want to use PostgreSQL? In a client 
application or as a backend database server?
Need (1) multiple-connections? (2) Need transaction? (3) SQL?

Regards,
Qingqing




---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Moving Tablespaces

You can also use WAL log shipping; there's been recent discussion about
that on -admin.

BTW, I'm seeing duplicated emails from you...

On Tue, Jan 03, 2006 at 08:31:09PM -0500, Doug McNaught wrote:
 Allen Fair [EMAIL PROTECTED] writes:
 
  Our databases can get large and we want to find the best way to plan for
  when a database outgrows its current server.
 
  How about a replication scheme and cutover? If so, is there a preferred
  replication package to support this?
 
 Slony-I was designed for this scenario (among others) and is actively
 developed.  There are also other replication solutions, some of them
 proprietary.
 
 -Doug
 
 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] create summaries and update a second table?

On Wed, Jan 04, 2006 at 07:49:30PM +0100, Michelle Konzack wrote:
 Hello *,
 
 I have many tables (one per product) and the are two columns where I
 put my buyed and selled pieces.  Second I have a Main-Table with all
 products and a summary of the stock.
 
 What I want is, whenever I buy or sell something, the changements
 (buy/sell) in the product table must triger an update in the Main-
 Table.

See the end of
http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html.
Though one issue with that is there's a race condition in the
update/insert block. Ironically I just submitted a patch today to fix
that (see -patches archive), but in a nutshell you want to use the code
from example 34-1.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] NEW variable values in trigger functions

2006-01-05 Thread Tom Lane

Ken Winter [EMAIL PROTECTED] writes:
 1. What is the value of the NEW variable for a column that is not mentioned
 in an UPDATE statement?  Is it NULL?  If not NULL, what?

No, it's whatever value is going to be assigned to the new row (which in
this particular case would be the same as the OLD value).

 2. Same questions re the value of a NEW variable that is not assigned a
 value in an INSERT statement.  

Same answer: whatever value is due to go into the row (in this case,
whatever the default is for the column).

 3. If an UPDATE query set a column to DEFAULT, what value does a trigger
 function see for the column's NEW variable?

Same answer.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Putting restrictions on pg_dump?

2006-01-05 Thread Klint Gore

On Thu, 5 Jan 2006 09:59:45 -0800, Benjamin Smith [EMAIL PROTECTED] wrote:
 Good ideas, all. but, what about keeping things like check constraints, 
 foreign keys, etc? 

how about something like
  pg_dump -s -t customers dbname customers.def


 Hmmm... maybe, if I dumped the entire DB schema, with no data, and then 
 looped 
 thru the tables, creating a temp table (as you describe) with a funky name 
 (such as TABLEaBcDeFgH_U) and then pg_dumping that, and then using a regex to 
 rename the table in the output... (eg 
 
   /TABLE\s+TABLEaBcDeFgH_U/TABLE customers/
 
 Ugh. I was hoping there was a cleaner way...

Make a script with all the commands in it.  You should be able to
manually make a file that is similar to what pg_dump does.

  pg_dump -s -t customers dbname customers.def
  echo copy customers from stdin; customers.def
  psql -d dname -c create temp table dump as select * from customers
 where id=11; copy dump to stdout; customers.def
  echo \. customers.def

klint.

+---+-+
: Klint Gore: Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] copy from error

On Thu, Jan 05, 2006 at 02:34:14PM +, marcelo Cortez wrote:
  there any procedure for fix fts installation?

As Tom asked, what steps did you follow to install tsearch2?  The
standard installation script creates its tables with oids, at least
in current releases, so you appear to have installed tsearch2 some
other way.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] plpgsql question

On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:
 Is it possible to skip the loop and just return all records in a
 single query and shove all those rows into a table variable?

Not in PL/pgSQL -- you need to return each row with RETURN NEXT,
generally from within a loop.  Why do you want to avoid that?

For simple functions you could use SQL instead of PL/pgSQL:

http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31627

 Also, the WHERE part is also important cause I'm not sure i got
 that part right? Would this call for EXECUTE or will it be okay and
 be planned the first time by the query planner?

If each call to the function issues the same query, just with
different values, then you shouldn't need to use EXECUTE.  If the
query differs depending on the function parameters then you have
several possibilities:

* You could build the query string and use EXECUTE.  Be sure to
  read about quote_literal() and quote_ident().

* You could use an IF statement to execute the query you need.

* You could put the queries in separate functions.  You can use
  the same name for different functions if their call signatures
  are different, e.g., getrecord(integer) and getrecord(integer, text).

* You could rewrite the query, possibly using CASE or COALESCE
  to handle NULL values.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Adding another primary key to a populated table

2006-01-05 Thread Daniel Kunkel

Hi

I'm trying to add another primary key to a table populated with data and
a number of foreign key constraints.

The new column is populated with non-null data (a requirement as I
understand it to become a valid primary key)

I tried:

ALTER TABLE product_price
   ADD CONSTRAINT product_price_pkey6 PRIMARY KEY
(product_id,product_price_type_id,currency_uom_id,product_store_group_id,from_date,product_price_purpose_id);

without having any luck.

Any suggestions?

Would it work to dump the database, edit the sql, and re-import?

Thanks in advance.

-- 
Daniel

*-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-
Have a GREAT Day!

Daniel Kunkel   [EMAIL PROTECTED]
BioWaves, LLC   http://www.BioWaves.com
14150 NE 20th St. Suite F1
Bellevue, WA 98007
800-734-3588425-895-0050
http://www.WizCity.com  http://www.Illusion-Optical.com
*-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Adding another primary key to a populated table

On Thu, Jan 05, 2006 at 08:08:39PM -0800, Daniel Kunkel wrote:
 Hi
 
 I'm trying to add another primary key to a table populated with data and
 a number of foreign key constraints.
 
 The new column is populated with non-null data (a requirement as I
 understand it to become a valid primary key)
 
 I tried:
 
 ALTER TABLE product_price
ADD CONSTRAINT product_price_pkey6 PRIMARY KEY
 (product_id,product_price_type_id,currency_uom_id,product_store_group_id,from_date,product_price_purpose_id);
 
 without having any luck.

What's the exact error you got?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Data loading from a flat file...

Hi Pgsql,I've a table schema in pg say as:(A B C D E) where none of these fields allow null.Now, I've a flat file that has got tab-delimited data for B,C and E fields.And A,B come from sequences.
Could anyone please let me know how I can upload the complete dataset into the table?Thanks,AK-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

2006-01-05 Thread Bruno Wolff III

On Thu, Jan 05, 2006 at 22:51:55 -0600,
  Angshu Kar [EMAIL PROTECTED] wrote:
 Hi Pgsql,
 
 I've a table schema in pg say as:
 
 (A B C D E)
 
 where none of these fields allow null.
 
 Now, I've a flat file that has got tab-delimited data for B,C and E fields.
 And A,B come from sequences.
 
 Could anyone please let me know how I can upload the complete dataset into
 the table?

Use COPY or INSERT with explicitly named columns. (I think this is a
relatively new feature for COPY, but you can use it for INSERT in any version.)

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Data loading from a flat file...

Thanks Jim. the statement is running without any error but nothing is getting copied into the table!Also, my data file is showing some ^M chars likeB1^M C1^M E1B2^M C2^M E2...
Does anyone know what can it represent?Is it creating any trouble for the COPY command?And can we use INSERT with COPY?Thanks,AngshuOn 1/5/06, 
Jim C. Nasby [EMAIL PROTECTED] wrote:
On Thu, Jan 05, 2006 at 10:51:55PM -0600, Angshu Kar wrote: Hi Pgsql, I've a table schema in pg say as: (A B C D E) where none of these fields allow null.
 Now, I've a flat file that has got tab-delimited data for B,C and E fields. And A,B come from sequences. Could anyone please let me know how I can upload the complete dataset into the table?
COPY table (b, c, e) FROM file?--Jim C. Nasby, Sr. Engineering Consultant[EMAIL PROTECTED]Pervasive Softwarehttp://pervasive.com
work: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461-- Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

Could you please tell me what's the syntax with INSERT for this?On 1/5/06, Bruno Wolff III [EMAIL PROTECTED] wrote:
On Thu, Jan 05, 2006 at 22:51:55 -0600,Angshu Kar 
[EMAIL PROTECTED] wrote: Hi Pgsql, I've a table schema in pg say as: (A B C D E) where none of these fields allow null. Now, I've a flat file that has got tab-delimited data for B,C and E fields.
 And A,B come from sequences. Could anyone please let me know how I can upload the complete dataset into the table?Use COPY or INSERT with explicitly named columns. (I think this is a
relatively new feature for COPY, but you can use it for INSERT in any version.)-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

On Thu, Jan 05, 2006 at 10:51:55PM -0600, Angshu Kar wrote:
 I've a table schema in pg say as:
 
 (A B C D E)
 
 where none of these fields allow null.
 
 Now, I've a flat file that has got tab-delimited data for B,C and E fields.
 And A,B come from sequences.
 
 Could anyone please let me know how I can upload the complete dataset into
 the table?

See the documentation for COPY (or \copy in psql).  If you need to
adjust sequence values after the load you can use ALTER SEQUENCE
or the setval() function.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Adding another primary key to a populated table

2006-01-05 Thread Andrew - Supernews

On 2006-01-06, Daniel Kunkel [EMAIL PROTECTED] wrote:
 Hi

 I'm trying to add another primary key to a table populated with data and
 a number of foreign key constraints.

You can only have one primary key on a table.

You can add additional unique constraints to get the same effect. (A
primary key constraint is just a unique constraint that is also not null,
and is the default target for REFERENCES constraints referring to the table -
this last factor is why there can be only one...)

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Data loading from a flat file...

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
 Thanks Jim. the statement is running without any error but nothing is
 getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return?  How much data
is there?  What version of PostgreSQL are you using and on what
platform?

 Also, my data file is showing some ^M chars like
 
 B1^M   C1^M   E1
 B2^M   C2^M   E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters?  Are they between fields
as shown or only at the ends of lines?

 Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get COPY
to read it.  That should be an easy job for a script (somebody here
can probably help).

 And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the appropriate
INSERT commands; that's another scripting job.

-- 
Michael Fuhr

---(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] Adding another primary key to a populated table

2006-01-05 Thread Daniel Kunkel

Hi

It makes sense that I can't have more than 1 primary key.

Postgres was trying to create another primary key instead of modify the
existing primary key.

So...

As I understand it, a table does not always have to have a primary key
defined.

Would it work to first delete/drop the primary key, then recreate the
primary key on all 6 columns.

ALTER TABLE product_price DROP CONSTRAINT  product_price_pkey;

I tried this, but it doesn't seem to work...  If I look at the table
from pgAdmin, it is still there, reindexable, I can't add a new primary
key, etc.  But if I try to run the above command twice, it says it's
already been removed.

--

Just for the record...  the error message I got was:

ERROR:  ALTER TABLE / PRIMARY KEY multiple primary keys for table
'product_price' are not allowed


On Fri, 2006-01-06 at 05:19 +, Andrew - Supernews wrote:
 On 2006-01-06, Daniel Kunkel [EMAIL PROTECTED] wrote:
  Hi
 
  I'm trying to add another primary key to a table populated with data and
  a number of foreign key constraints.
 
 You can only have one primary key on a table.
 
 You can add additional unique constraints to get the same effect. (A
 primary key constraint is just a unique constraint that is also not null,
 and is the default target for REFERENCES constraints referring to the table -
 this last factor is why there can be only one...)
 


---(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] Data loading from a flat file...

Thanks Michael.I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux m/c! The file has about 2GB data.It returns back to the prompt very soon.I'm using less
 or vi command to view the file and getting those ^M as mentioned (i.e. between fields). Any clue how I can massage the data? If you suggest I can try and write the script.
Also, now I'm facing another permission related problem!It's throwing the error:ERROR: could not open file /home/akar/final.out for reading: Permission denied
I've changed the file owner to postgres but without any avail!Also do I need to change the permission to akar directory? How(I'm a linux freshie)?Thanks,Angshu
On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:
On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote: Thanks Jim. the statement is running without any error but nothing is getting copied into the table!What client are you using and what's the exact command you ran?
Does the command finish or does it never return?How much datais there?What version of PostgreSQL are you using and on whatplatform? Also, my data file is showing some ^M chars like B1^M C1^M E1
 B2^M C2^M E2The ^M sequence might represent a carriage return -- how are youviewing the file to see these characters?Are they between fieldsas shown or only at the ends of lines? Is it creating any trouble for the COPY command?
Possibly; you might need to massage the data if you can't get COPYto read it.That should be an easy job for a script (somebody herecan probably help). And can we use INSERT with COPY?
To use INSERT you'd need to read the data and generate the appropriateINSERT commands; that's another scripting job.--Michael Fuhr-- Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

I've solved the permission issue but now the error is:ERROR: missing data for column subject_entry_id
CONTEXT: COPY distance, line 1: 107128I feel the ^M is creating the problem! Any means to remove that? I mean using the delimiters option?
On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
Thanks Michael.I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux m/c! The file has about 2GB data.It returns back to the prompt very soon.I'm using less
 or vi command to view the file and getting those ^M as mentioned (i.e. between fields). Any clue how I can massage the data? If you suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's throwing the error:ERROR: could not open file /home/akar/final.out for reading: Permission denied

I've changed the file owner to postgres but without any avail!Also do I need to change the permission to akar directory? How(I'm a linux freshie)?Thanks,Angshu

On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote: Thanks Jim. the statement is running without any error but nothing is getting copied into the table!What client are you using and what's the exact command you ran?
Does the command finish or does it never return?How much datais there?What version of PostgreSQL are you using and on whatplatform? Also, my data file is showing some ^M chars like
 B1^M C1^M E1
 B2^M C2^M E2The ^M sequence might represent a carriage return -- how are youviewing the file to see these characters?Are they between fieldsas shown or only at the ends of lines? Is it creating any trouble for the COPY command?
Possibly; you might need to massage the data if you can't get COPYto read it.That should be an easy job for a script (somebody herecan probably help). And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the appropriateINSERT commands; that's another scripting job.--Michael Fuhr
-- Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes first...

-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding command. But
^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx username.

For this command to succeed you need to execute this command as root
or the owner of the directory

On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
 Thanks Michael.

 I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux
 m/c! The file has about 2GB data.It returns back to the prompt very soon.
 I'm using less or vi command to view the file and getting those ^M as
 mentioned (i.e. between fields). Any clue how I can massage the data? If you
 suggest I can try and write the script.

 Also, now I'm facing another permission related problem!It's throwing the
 error:
 ERROR:  could not open file /home/akar/final.out for reading: Permission
 denied
  I've changed the file owner to postgres but without any avail!Also do I
 need to change the permission to akar directory? How(I'm a linux freshie)?

 Thanks,
 Angshu



 On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:
  On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
   Thanks Jim. the statement is running without any error but nothing is
   getting copied into the table!
 
  What client are you using and what's the exact command you ran?
  Does the command finish or does it never return?  How much data
  is there?  What version of PostgreSQL are you using and on what
  platform?
 
   Also, my data file is showing some ^M chars like
  
   B1^M   C1^M   E1
   B2^M   C2^M   E2
 
  The ^M sequence might represent a carriage return -- how are you
  viewing the file to see these characters?  Are they between fields
  as shown or only at the ends of lines?
 
   Is it creating any trouble for the COPY command?
 
  Possibly; you might need to massage the data if you can't get COPY
  to read it.  That should be an easy job for a script (somebody here
  can probably help).
 
   And can we use INSERT with COPY?
 
  To use INSERT you'd need to read the data and generate the appropriate
  INSERT commands; that's another scripting job.
 
  --
  Michael Fuhr
 



 --
 Ignore the impossible but honor it ...
 The only enviable second position is success, since failure always comes
 first...

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Data loading from a flat file...

Thanks for the chmod command Pandu but the cat command is not doing anything!And as Michael suggested that file has indeed carriage returns embedded in fields. I opened it in my windows m/c using textpad and got:
B1 C1 E1B2 C2 E2...Any more suggestions on how to solve this? :(On 1/6/06, Pandurangan R S
 [EMAIL PROTECTED] wrote:
To get rid of ^M characters you could usecat file | tr -d ^Myou need to type ^V before you type ^M in the preceeding command. But^V will not be displayed on the screen.You might need to change directory permission too.
use chmod +rx username.For this command to succeed you need to execute this command as rootor the owner of the directoryOn 1/6/06, Angshu Kar [EMAIL PROTECTED]
 wrote: Thanks Michael. I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux m/c! The file has about 2GB data.It returns back to the prompt very soon. I'm using less or vi command to view the file and getting those ^M as
 mentioned (i.e. between fields). Any clue how I can massage the data? If you suggest I can try and write the script. Also, now I'm facing another permission related problem!It's throwing the
 error: ERROR:could not open file /home/akar/final.out for reading: Permission deniedI've changed the file owner to postgres but without any avail!Also do I need to change the permission to akar directory? How(I'm a linux freshie)?
 Thanks, Angshu On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:  On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
   Thanks Jim. the statement is running without any error but nothing is   getting copied into the table!   What client are you using and what's the exact command you ran?
  Does the command finish or does it never return?How much data  is there?What version of PostgreSQL are you using and on what  platform?Also, my data file is showing some ^M chars like
 B1^M C1^M E1   B2^M C2^M E2   The ^M sequence might represent a carriage return -- how are you  viewing the file to see these characters?Are they between fields
  as shown or only at the ends of lines?Is it creating any trouble for the COPY command?   Possibly; you might need to massage the data if you can't get COPY
  to read it.That should be an easy job for a script (somebody here  can probably help).And can we use INSERT with COPY?   To use INSERT you'd need to read the data and generate the appropriate
  INSERT commands; that's another scripting job.   --  Michael Fuhr  -- Ignore the impossible but honor it ... The only enviable second position is success, since failure always comes
 first...-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Adding another primary key to a populated table

2006-01-05 Thread Aaron Koning

Are you trying to create a primary key composed of 6 fields? What is
the result you want to achieve with the constraint? If you just want
UNIQUE, NOT NULL values in a field, you can achieve that without
creating a primary key.

AaronOn 1/5/06, Daniel Kunkel [EMAIL PROTECTED] wrote:
HiIt makes sense that I can't have more than 1 primary key.Postgres was trying to create another primary key instead of modify theexisting primary key.So...As I understand it, a table does not always have to have a primary key
defined.Would it work to first delete/drop the primary key, then recreate theprimary key on all 6 columns.ALTER TABLE product_price DROP CONSTRAINTproduct_price_pkey;I tried this, but it doesn't seem to work...If I look at the table
from pgAdmin, it is still there, reindexable, I can't add a new primarykey, etc.But if I try to run the above command twice, it says it'salready been removed.--Just for the record...the error message I got was:
ERROR:ALTER TABLE / PRIMARY KEY multiple primary keys for table'product_price' are not allowedOn Fri, 2006-01-06 at 05:19 +, Andrew - Supernews wrote: On 2006-01-06, Daniel Kunkel 
[EMAIL PROTECTED] wrote:  Hi   I'm trying to add another primary key to a table populated with data and  a number of foreign key constraints.
 You can only have one primary key on a table. You can add additional unique constraints to get the same effect. (A primary key constraint is just a unique constraint that is also not null,
 and is the default target for REFERENCES constraints referring to the table - this last factor is why there can be only one...)---(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] Data loading from a flat file...

More problems solved and created - Now I'm getting the error:null value in column subject_id violates not-null constraintand this is nothing but column A which I talked about in the very beginning! Since its not null how can I COPY to B C and E. The same problem will arise with field E too!
AKOn 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
Thanks for the chmod command Pandu but the cat command is not doing anything!And as Michael suggested that file has indeed carriage returns embedded in fields. I opened it in my windows m/c using textpad and got:

B1 C1 E1B2 C2 E2...Any more suggestions on how to solve this? :(
On 1/6/06, Pandurangan R S
 [EMAIL PROTECTED] wrote:

To get rid of ^M characters you could usecat file | tr -d ^Myou need to type ^V before you type ^M in the preceeding command. But^V will not be displayed on the screen.You might need to change directory permission too.
use chmod +rx username.For this command to succeed you need to execute this command as rootor the owner of the directoryOn 1/6/06, Angshu Kar 
[EMAIL PROTECTED]
 wrote: Thanks Michael. I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux m/c! The file has about 2GB data.It returns back to the prompt very soon. I'm using less or vi command to view the file and getting those ^M as
 mentioned (i.e. between fields). Any clue how I can massage the data? If you suggest I can try and write the script. Also, now I'm facing another permission related problem!It's throwing the
 error: ERROR:could not open file /home/akar/final.out for reading: Permission deniedI've changed the file owner to postgres but without any avail!Also do I need to change the permission to akar directory? How(I'm a linux freshie)?
 Thanks, Angshu On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:
  On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
   Thanks Jim. the statement is running without any error but nothing is   getting copied into the table!   What client are you using and what's the exact command you ran?
  Does the command finish or does it never return?How much data  is there?What version of PostgreSQL are you using and on what  platform?Also, my data file is showing some ^M chars like
 B1^M C1^M E1   B2^M C2^M E2   The ^M sequence might represent a carriage return -- how are you  viewing the file to see these characters?Are they between fields
  as shown or only at the ends of lines?Is it creating any trouble for the COPY command?   Possibly; you might need to massage the data if you can't get COPY
  to read it.That should be an easy job for a script (somebody here  can probably help).And can we use INSERT with COPY?   To use INSERT you'd need to read the data and generate the appropriate
  INSERT commands; that's another scripting job.   --  Michael Fuhr  -- Ignore the impossible but honor it ... The only enviable second position is success, since failure always comes
 first...-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

cat test | paste - - -

This will merge three consecutive lines into a single line. Note that
there is a space between each -.

On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
 More problems solved and created - Now I'm getting the error:

  null value in column subject_id violates not-null constraint

 and this is nothing but column A which I talked about in the very beginning!
 Since its not null how can I COPY to B C and E. The same problem will arise
 with field E too!

 AK



 On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
  Thanks for the chmod command Pandu but the cat command is not doing
 anything!
 
  And as Michael suggested that file has indeed carriage returns embedded in
 fields. I opened it in my windows m/c using textpad and got:
 
  B1
  C1
  E1
  B2
  C2
  E2
  .
  .
  .
 
 
 
  Any more suggestions on how to solve this? :(
 
 
 
 
 
  On 1/6/06, Pandurangan R S [EMAIL PROTECTED] wrote:
   To get rid of ^M characters you could use
  
   cat file | tr -d ^M
  
   you need to type ^V before you type ^M in the preceeding command. But
   ^V will not be displayed on the screen.
  
   You might need to change directory permission too.
  
   use chmod +rx username.
  
   For this command to succeed you need to execute this command as root
   or the owner of the directory
  
   On 1/6/06, Angshu Kar  [EMAIL PROTECTED]  wrote:
Thanks Michael.
   
I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my
 linux
m/c! The file has about 2GB data.It returns back to the prompt very
 soon.
I'm using less or vi command to view the file and getting those ^M as
mentioned (i.e. between fields). Any clue how I can massage the data?
 If you
suggest I can try and write the script.
   
Also, now I'm facing another permission related problem!It's throwing
 the
error:
ERROR:  could not open file /home/akar/final.out for reading:
 Permission
denied
 I've changed the file owner to postgres but without any avail!Also do
 I
need to change the permission to akar directory? How(I'm a linux
 freshie)?
   
Thanks,
Angshu
   
   
   
On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:
 On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
  Thanks Jim. the statement is running without any error but nothing
 is
  getting copied into the table!

 What client are you using and what's the exact command you ran?
 Does the command finish or does it never return?  How much data
 is there?  What version of PostgreSQL are you using and on what
 platform?

  Also, my data file is showing some ^M chars like
 
  B1^M   C1^M   E1
  B2^M   C2^M   E2

 The ^M sequence might represent a carriage return -- how are you
 viewing the file to see these characters?  Are they between fields
 as shown or only at the ends of lines?

  Is it creating any trouble for the COPY command?

 Possibly; you might need to massage the data if you can't get COPY
 to read it.  That should be an easy job for a script (somebody here
 can probably help).

  And can we use INSERT with COPY?

 To use INSERT you'd need to read the data and generate the
 appropriate
 INSERT commands; that's another scripting job.

 --
 Michael Fuhr

   
   
   
--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always
 comes
first...
  
 
 
 
  --
 
  Ignore the impossible but honor it ...
  The only enviable second position is success, since failure always comes
 first...



 --

 Ignore the impossible but honor it ...
 The only enviable second position is success, since failure always comes
 first...

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Adding another primary key to a populated table

2006-01-05 Thread Daniel Kunkel

Why do I want to include 6 fields in the primary key?  

Good question...  I don't know. It's a requirement of OFBiz, a really
awesome ERP/CRM/Accounting/ECommerce system.

I'm upgrading the software which requires it, and need to upgrade the
database to match.

Once I find out, I'll publish the solution in the OFBiz forums and Wiki
so others won't come knocking.

Thanks

Daniel

On Thu, 2006-01-05 at 22:44 -0800, Aaron Koning wrote:
 Are you trying to create a primary key composed of 6 fields? What is
 the result you want to achieve with the constraint? If you just want
 UNIQUE, NOT NULL values in a field, you can achieve that without
 creating a primary key.
 
 Aaron
 
 On 1/5/06, Daniel Kunkel [EMAIL PROTECTED] wrote:
 Hi
 
 It makes sense that I can't have more than 1 primary key.
 
 Postgres was trying to create another primary key instead of
 modify the
 existing primary key.
 
 So...
 
 As I understand it, a table does not always have to have a
 primary key 
 defined.
 
 Would it work to first delete/drop the primary key, then
 recreate the
 primary key on all 6 columns.
 
 ALTER TABLE product_price DROP CONSTRAINT  product_price_pkey;
 
 I tried this, but it doesn't seem to work...  If I look at the
 table 
 from pgAdmin, it is still there, reindexable, I can't add a
 new primary
 key, etc.  But if I try to run the above command twice, it
 says it's
 already been removed.
 
 --
 
 Just for the record...  the error message I got was: 
 
 ERROR:  ALTER TABLE / PRIMARY KEY multiple primary keys for
 table
 'product_price' are not allowed
 
 
 On Fri, 2006-01-06 at 05:19 +, Andrew - Supernews wrote:
  On 2006-01-06, Daniel Kunkel  [EMAIL PROTECTED]
 wrote:
   Hi
  
   I'm trying to add another primary key to a table populated
 with data and
   a number of foreign key constraints. 
 
  You can only have one primary key on a table.
 
  You can add additional unique constraints to get the same
 effect. (A
  primary key constraint is just a unique constraint that is
 also not null, 
  and is the default target for REFERENCES constraints
 referring to the table -
  this last factor is why there can be only one...)
 
 
 
 ---(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
 
-- 
Daniel

*-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-
Have a GREAT Day!

Daniel Kunkel   [EMAIL PROTECTED]
BioWaves, LLC   http://www.BioWaves.com
14150 NE 20th St. Suite F1
Bellevue, WA 98007
800-734-3588425-895-0050
http://www.WizCity.com  http://www.Illusion-Optical.com
*-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-


---(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] Data loading from a flat file...

Please show the output of describe command of the table

On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
 More problems solved and created - Now I'm getting the error:

  null value in column subject_id violates not-null constraint

 and this is nothing but column A which I talked about in the very beginning!
 Since its not null how can I COPY to B C and E. The same problem will arise
 with field E too!

 AK



 On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
  Thanks for the chmod command Pandu but the cat command is not doing
 anything!
 
  And as Michael suggested that file has indeed carriage returns embedded in
 fields. I opened it in my windows m/c using textpad and got:
 
  B1
  C1
  E1
  B2
  C2
  E2
  .
  .
  .
 
 
 
  Any more suggestions on how to solve this? :(
 
 
 
 
 
  On 1/6/06, Pandurangan R S [EMAIL PROTECTED] wrote:
   To get rid of ^M characters you could use
  
   cat file | tr -d ^M
  
   you need to type ^V before you type ^M in the preceeding command. But
   ^V will not be displayed on the screen.
  
   You might need to change directory permission too.
  
   use chmod +rx username.
  
   For this command to succeed you need to execute this command as root
   or the owner of the directory
  
   On 1/6/06, Angshu Kar  [EMAIL PROTECTED]  wrote:
Thanks Michael.
   
I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my
 linux
m/c! The file has about 2GB data.It returns back to the prompt very
 soon.
I'm using less or vi command to view the file and getting those ^M as
mentioned (i.e. between fields). Any clue how I can massage the data?
 If you
suggest I can try and write the script.
   
Also, now I'm facing another permission related problem!It's throwing
 the
error:
ERROR:  could not open file /home/akar/final.out for reading:
 Permission
denied
 I've changed the file owner to postgres but without any avail!Also do
 I
need to change the permission to akar directory? How(I'm a linux
 freshie)?
   
Thanks,
Angshu
   
   
   
On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote:
 On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
  Thanks Jim. the statement is running without any error but nothing
 is
  getting copied into the table!

 What client are you using and what's the exact command you ran?
 Does the command finish or does it never return?  How much data
 is there?  What version of PostgreSQL are you using and on what
 platform?

  Also, my data file is showing some ^M chars like
 
  B1^M   C1^M   E1
  B2^M   C2^M   E2

 The ^M sequence might represent a carriage return -- how are you
 viewing the file to see these characters?  Are they between fields
 as shown or only at the ends of lines?

  Is it creating any trouble for the COPY command?

 Possibly; you might need to massage the data if you can't get COPY
 to read it.  That should be an easy job for a script (somebody here
 can probably help).

  And can we use INSERT with COPY?

 To use INSERT you'd need to read the data and generate the
 appropriate
 INSERT commands; that's another scripting job.

 --
 Michael Fuhr

   
   
   
--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always
 comes
first...
  
 
 
 
  --
 
  Ignore the impossible but honor it ...
  The only enviable second position is success, since failure always comes
 first...



 --

 Ignore the impossible but honor it ...
 The only enviable second position is success, since failure always comes
 first...

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Data loading from a flat file...

here it is: Table public.distance Column | Type | Modifiers--+--+---distance_id | integer | not null
query_id | integer | not nullsubject_id | integer | not nulldistanceparameter_id | integer |pvalue | double precision | not nullIndexes:
distance_pkey primary key, btree (distance_id) distance_query_id_key unique, btree (query_id, subject_id, distanceparameter_id)Foreign-key constraints: distance_distanceparameter_id_fkey FOREIGN KEY (distanceparameter_id) REFERENCES distanceparameter(distanceparameter_id) ON UPDATE RESTRICT ON DELETE RESTRICT
distance_query_id_fkey FOREIGN KEY (query_id) REFERENCES entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT distance_subject_id_fkey FOREIGN KEY (subject_id) REFERENCES entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT
On 1/6/06, Pandurangan R S [EMAIL PROTECTED] wrote:
Please show the output of describe command of the tableOn 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote: More problems solved and created - Now I'm getting the error:
null value in column subject_id violates not-null constraint and this is nothing but column A which I talked about in the very beginning! Since its not null how can I COPY to B C and E. The same problem will arise
with field E too! AK On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote: Thanks for the chmod command Pandu but the cat command is not doing
anything! And as Michael suggested that file has indeed carriage returns embedded in fields. I opened it in my windows m/c using textpad and got: B1
C1 E1 B2 C2 E2 . . . Any more suggestions on how to solve this? :(
On 1/6/06, Pandurangan R S [EMAIL PROTECTED] wrote: To get rid of ^M characters you could use
cat file | tr -d ^M you need to type ^V before you type ^M in the preceeding command. But ^V will not be displayed on the screen.
You might need to change directory permission too. use chmod +rx username. For this command to succeed you need to execute this command as root
or the owner of the directory On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:Thanks Michael.
I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linuxm/c! The file has about 2GB data.It returns back to the prompt very
soon.I'm using less or vi command to view the file and getting those ^M asmentioned (i.e. between fields). Any clue how I can massage the data? If yousuggest I can try and write the script.
Also, now I'm facing another permission related problem!It's throwing theerror:ERROR:could not open file /home/akar/final.out for reading:
Permissiondenied I've changed the file owner to postgres but without any avail!Also do Ineed to change the permission to akar directory? How(I'm a linux
freshie)? Thanks,Angshu On 1/5/06, Michael Fuhr
[EMAIL PROTECTED] wrote: On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote: Thanks Jim. the statement is running without any error but nothing
is getting copied into the table! What client are you using and what's the exact command you ran? Does the command finish or does it never return?How much data
is there?What version of PostgreSQL are you using and on what platform? Also, my data file is showing some ^M chars like
B1^M C1^M E1 B2^M C2^M E2 The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters?Are they between fields as shown or only at the ends of lines? Is it creating any trouble for the COPY command?
Possibly; you might need to massage the data if you can't get COPY to read it.That should be an easy job for a script (somebody here
can probably help). And can we use INSERT with COPY? To use INSERT you'd need to read the data and generate the
appropriate INSERT commands; that's another scripting job. -- Michael Fuhr
--Ignore the impossible but honor it ...The only enviable second position is success, since failure always
comesfirst... -- Ignore the impossible but honor it ... The only enviable second position is success, since failure always comes
first... -- Ignore the impossible but honor it ... The only enviable second position is success, since failure always comes first...
-- Ignore the impossible but honor it ...The only enviable second position is success, since failure always comes first...

Re: [GENERAL] Data loading from a flat file...

Issue the following command before you execute copy

ALTER TABLE DISTANCE ALTER COLUMN column name which should use seq
SET DEFAULT nextval('sequence to be used')

Btw, what version of postgres are you using?

On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
 here it is:


Table public.distance
 Column|   Type   | Modifiers
 --+--+---
  distance_id  | integer  | not null
  query_id  | integer  | not null
  subject_id| integer  | not null
  distanceparameter_id | integer  |
  pvalue   | double precision | not null
 Indexes:
 distance_pkey primary key, btree (distance_id)
 distance_query_id_key unique, btree (query_id, subject_id,
 distanceparameter_id)
 Foreign-key constraints:
 distance_distanceparameter_id_fkey FOREIGN KEY
 (distanceparameter_id) REFERENCES
 distanceparameter(distanceparameter_id) ON UPDATE RESTRICT
 ON DELETE RESTRICT
 distance_query_id_fkey FOREIGN KEY (query_id) REFERENCES
 entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT
 distance_subject_id_fkey FOREIGN KEY (subject_id) REFERENCES
 entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT



 On 1/6/06, Pandurangan R S [EMAIL PROTECTED] wrote:
  Please show the output of describe command of the table
 
  On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
   More problems solved and created - Now I'm getting the error:
  
null value in column subject_id violates not-null constraint
  
   and this is nothing but column A which I talked about in the very
 beginning!
   Since its not null how can I COPY to B C and E. The same problem will
 arise
   with field E too!
  
   AK
  
  
  
   On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote:
Thanks for the chmod command Pandu but the cat command is not doing
   anything!
   
And as Michael suggested that file has indeed carriage returns
 embedded in
   fields. I opened it in my windows m/c using textpad and got:
   
B1
C1
E1
B2
C2
E2
.
.
.
   
   
   
Any more suggestions on how to solve this? :(
   
   
   
   
   
On 1/6/06, Pandurangan R S [EMAIL PROTECTED] wrote:
 To get rid of ^M characters you could use

 cat file | tr -d ^M

 you need to type ^V before you type ^M in the preceeding command.
 But
 ^V will not be displayed on the screen.

 You might need to change directory permission too.

 use chmod +rx username.

 For this command to succeed you need to execute this command as root
 or the owner of the directory

 On 1/6/06, Angshu Kar  [EMAIL PROTECTED]  wrote:
  Thanks Michael.
 
  I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in
 my
   linux
  m/c! The file has about 2GB data.It returns back to the prompt
 very
   soon.
  I'm using less or vi command to view the file and getting those ^M
 as
  mentioned (i.e. between fields). Any clue how I can massage the
 data?
   If you
  suggest I can try and write the script.
 
  Also, now I'm facing another permission related problem!It's
 throwing
   the
  error:
  ERROR:  could not open file /home/akar/final.out for reading:
   Permission
  denied
   I've changed the file owner to postgres but without any
 avail!Also do
   I
  need to change the permission to akar directory? How(I'm a linux
   freshie)?
 
  Thanks,
  Angshu
 
 
 
  On 1/5/06, Michael Fuhr  [EMAIL PROTECTED] wrote:
   On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
Thanks Jim. the statement is running without any error but
 nothing
   is
getting copied into the table!
  
   What client are you using and what's the exact command you ran?
   Does the command finish or does it never return?  How much data
   is there?  What version of PostgreSQL are you using and on what
   platform?
  
Also, my data file is showing some ^M chars like
   
B1^M   C1^M   E1
B2^M   C2^M   E2
  
   The ^M sequence might represent a carriage return -- how are you
   viewing the file to see these characters?  Are they between
 fields
   as shown or only at the ends of lines?
  
Is it creating any trouble for the COPY command?
  
   Possibly; you might need to massage the data if you can't get
 COPY
   to read it.  That should be an easy job for a script (somebody
 here
   can probably help).
  
And can we use INSERT with COPY?
  
   To use INSERT you'd need to read the data and generate the
   appropriate
   INSERT commands; that's another scripting job.
  
   --
   Michael Fuhr
  
 
 
 
  --
  Ignore the impossible but honor it ...
  The only enviable second position is success, since failure always
   comes

Re: [GENERAL] Data loading from a flat file...