[GENERAL] ODBC linked table in access on second pc

[Carsten Huettl]

Hello,

I have a pgsql db (v.6.5.3) with some tables.

On my pc1 I have created a MS access front end (with ODBC) with these
tables linked.
When I start the MS access front end from my pc2 (same ODBC
driver/setup) I get access denied while trying to open the linked
tables. I can link the tables again from pc2 and open them within the
same front end.
This let me open the tables on pc2 but not on pc1. There only works the
first links.
In summary: 
The links I created (in the same Access front end) works only on the pc
where they have been created.

Where am I wrong?
How can I correct this behaviour?

TIA
C.

[GENERAL] ALTER TABLE speed (adding foreign keys)

[Shaw Terwilliger]

I have a table, contacts, with two fields, id (int), contactid (int).
I have another table, users, with a bunch of fields, but one important
one, id (SERIAL), which is the table key.

I needed to test my database setup (and software) with 1.5 million users,
each of whom will have (on average), 10 contacts.  So for each user in
the users table, I'll need ten records in the contacts table.

I wrote a little script to spew some sample data into a file for 
COPY into the tables.  Since 1.5 million * 10 contacts is 15 million
rows, I created my tables without foreign keys (both id and contactid
will eventually be foreign keys into users, since I often select on both
of them) and constraints (CHECK id <> contactid).

The COPY goes much, much faster with these constraints absent from my tables.
So I go to ALTER TABLE contacts, to add these foreign keys.  One backend
fires up, allocates a few MB of RAM, and takes a long, long time to check
all the existing (15 million) rows.  At least, that's what I think it's doing.

postgres   394 98.1  1.6  7752 4300 pts/0R<   Feb14 632:41 
/usr/lib/postgresql/bin/postgres localhost sterwill im ALTER 

The process has taken approximately 10 hours of CPU time so far.
I understand placing a foreign key constraint on an existing table can be
a very expensive operation, but I just wanted to make sure I won't be 
"waiting forever" on this process.  I've got more tables to ALTER, and if
they all take this long, I'll just wipe the database, load the schema
again (_with_ the constraints), and just let the COPY take a day or two.

I'm using PostgreSQL 7.0.3 on an AMD Athlon 650 with 256 MB RAM, 500 MB 
swap, Linux 2.2.18.

-- 
Shaw Terwilliger <[EMAIL PROTECTED]>
SourceGear Corporation
217.356.0105 x 641

 PGP signature

[GENERAL] Max query limit in 7.1? With TOAST?

[Lincoln Yeoh]

Hi, 

What is the max query size for 7.1?

For 6.5.x it's about 16383 bytes. 

The reason why I'm wondering is because I'm thinking of using 7.1's TOAST
to insert large amounts of text into a table (e.g. > 1MB). And I'd like to
confirm that it's possible using the usual database interfaces and methods
e.g. INSERT/UPDATE over ODBC/DBI/PHP-PG. Because if it's not possible yet,
then I'll store the stuff on the filesystem.

Thanks,
Link.

Re: [GENERAL] Case insensitive selects?

[Michael Fork]

Indexes *can* and *will* be used if you create the appropiate
functional indexes, i.e:

CREATE INDEX idx_table_field_upper ON table(upper(field));

SELECT field FROM table WHERE upper(field) LIKE upper('some string');

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Thu, 15 Feb 2001, Anand Raman wrote:

> Hi
> use it if u absolutly need it.. Using a function on a column name
> doesnt use the index associated with that column,.. So exercise this
> option with some amount of thinking..
> 
> Anand
> On Wed, Feb 14, 2001 at 11:39:47AM -0500, Adam Lang wrote:
> >It is in the list archives several times.
> >
> >All you need is to use some basic SQL.
> >
> >select * from mytable where upper('my criteria') = upper(mytable.info);
> >
> >Adam Lang
> >Systems Engineer
> >Rutgers Casualty Insurance Company
> >http://www.rutgersinsurance.com
> >- Original Message - 
> >From: "David Reid" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Wednesday, February 14, 2001 9:58 AM
> >Subject: [GENERAL] Case insensitive selects?
> >
> >
> >> Does pgsql support this and how would I do it?
> >> 
> >> david
> 

Re: [GENERAL] Tuple is to big

[Anand Raman]

hi Endre
U can go upto 32 k on 7.0.x and there is no such limit on 7.1
Try to upgrade..
Regards
Anand
On Tue, Feb 13, 2001 at 04:11:59PM -0500, Bodoni Endre wrote:
>I have a little trouble with my PostgreSQL 6.5.1. I use a table with
>text character type.
>When I try to insert about 1 character long string into it I've got
>an error message like:
>ERROR:  Tuple is too big: size 10172
>Is there any limitation for a variable length text type?
>If any, how long is it?
>
>Regards,
>Endre Bodoni

Re: [GENERAL] Case insensitive selects?

[Anand Raman]

Hi
use it if u absolutly need it.. Using a function on a column name
doesnt use the index associated with that column,.. So exercise this
option with some amount of thinking..

Anand
On Wed, Feb 14, 2001 at 11:39:47AM -0500, Adam Lang wrote:
>It is in the list archives several times.
>
>All you need is to use some basic SQL.
>
>select * from mytable where upper('my criteria') = upper(mytable.info);
>
>Adam Lang
>Systems Engineer
>Rutgers Casualty Insurance Company
>http://www.rutgersinsurance.com
>- Original Message - 
>From: "David Reid" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Wednesday, February 14, 2001 9:58 AM
>Subject: [GENERAL] Case insensitive selects?
>
>
>> Does pgsql support this and how would I do it?
>> 
>> david

Re: [GENERAL] temp table creation

[Tom Lane]

Alfonso Peniche <[EMAIL PROTECTED]> writes:
> What's wrong with this function? The syntax is supposed to be right,
> isn't it?

No, it isn't right, not for plpgsql.  SELECT INTO means something
different to plpgsql than it does in normal Postgres SQL.

regards, tom lane

[GENERAL] temp table creation

[Alfonso Peniche]

What's wrong with this function? The syntax is supposed to be right,
isn't it?

Create function UsaTablaTemporal()
RETURNS integer
AS '
BEGIN

 SELECT p.apellidos, p.nombres, u.username
INTO TEMP TABLE mitabla
FROM persona p, usuario u
WHERE p.idpersona = u.idusuario
  AND p.idpersona = 3278;

RETURN 1;

END;'
LANGUAGE 'plpgsql';

If I run:
select usatablatemporal();

I get the message:
ERROR:  parser: parse error at or near "temp"

Thanx

Re: [GENERAL] Postmaster crashes

[Tom Lane]

"Mark Alliban" <[EMAIL PROTECTED]> writes:
> pq_recvbuf: unexpected EOF on client connection
> Server process (pid 2087) exited with status 139 at Tue Feb 13 15:38:08 2001
> Terminating any active server processes...
> Server processes were terminated at Tue Feb 13 15:38:08 2001
> Reinitializing shared memory and semaphores

> The first line appears many times (once every few minutes) but doesn't cause
> a problem. However the server then exits and the database is down.

The unexpected-EOF messages are probably unrelated to the crash.  What
Postgres version are you running?  There should be a corefile left from
the crashed backend --- can you get a stack trace from it?  How about
running the postmaster with -d2 to log queries, so that you can see what
queries were being executed at the time of the crash?

regards, tom lane

Re: [GENERAL] transaction safety

[Jan Wieck]

DaVinci wrote:
> On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote:
> > The problem is, that a referential integrity constraint needs
> > to ensure that from the moment  the  constraint  got  checked
> > until  your  INSERT got made persistent (COMMIT), nobody else
> > has a chance to kick out the referenced key.
> >
> > Due to the lack of the ability to put a shared row lock  with
> > a  SELECT,  we currently use FOR UPDATE, placing an exclusive
> > lock onto the referenced key.
>
>  I understand this but, why then other changes on "aviso" get locked?. My
>  first impression is that only referenced keys should be, but not a table
>  that references them.

You're right, at the time two FK rows referencing the same PK
get inserted, there is no need to block one  of  them.  Thus,
PostgreSQL *shouldn't* block.

But at the time beeing, the foreign key triggers issue a

SELECT oid FROM  WHERE  = 
FOR UPDATE;

Since  both  INSERT  operations  on  the  FK table do it, the
second one is blocked until COMMIT of  the  first,  releasing
the lock.

What we need is something like

SELECT ...
WITH LOCK;

applying  a  shared (read) lock of the PK row. In this state,
UPDATE/DELETE to the PK row in question  or  SELECT  ...  FOR
UPDATE  of  that  will  block,  but more SELECT ... WITH LOCK
would be allowed and pass through without blocking.

I don't really want that language construct, since  it's  not
SQL  standard.   Maybe  it's  possible  to  achieve  the same
behaviour with dirty reads or the  like.  However  it's  done
finally, it should behave like the above.

>
>  If this works so for now, is any plan to change in future?.
>
> > In your case it might help to make the constraints  INITIALLY
> > DEFERRED.  That  causes  that  the  checks  are delayed until
> > COMMIT, so it shortens the time the lock is present.
>
>  Thanks!. That works fine.

You're welcome.


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

RE: [GENERAL] using tables as types in other tables

[Michael Ansley]

Title: RE: [GENERAL] using tables as types in other tables





I went over this ground about six months ago, and the bottom line is: until somebody picks this up and sorts out the code (which suffers from bitrot) it's not going to work like you described (believe me, I tried).  Put the addresses in a single table, with foreign keys from each other table that you need.  What you may decide to do is forward- rather than reverse-reference, possibly even with a resolution table between the address table, and the actual entity tables:

client >-|
 |
 |
vendor >< address_mem >-< address
 |
 |
employee >---|
 
This uses sub-typing, which SQL doesn't deal with very well, but it's an idea.  Of course, if you carry it a step further, and use PGs inheritance, then you can make life a little easier for yourself:


 /--*client
    /
   /
address >-< address_ent-*vendor
   \
    \   
 \--*employee



where address carries a foreign key to addressable_entity, or something.  I haven't thought this one through particularly hard, but this is what these facilities are for ;-)

Cheers...



MikeA


 


-Original Message-
From: Louis-David Mitterrand
To: [EMAIL PROTECTED]
Sent: 2-14-01 6:35 PM
Subject: [GENERAL] using tables as types in other tables



In the app we are developing the concept of an address will occur very
often on many tables (vendor, clients, employees, etc.) so we are
looking to avoid code duplication by centralizing the addresses in one
table. However I once read on one of the pgsql- lists that one could use
a table name as a type:


create table address(street text, zip text, city text, country text);


create table employee(emp_addr address, emp_id int);


This is accepted by Postgres but the question is how to insert into the
employee table? What syntax should be used? I tried:


insert into employee values (('mystreet','myzip','mycity','mycountry'),
1);


But it doesn't work.


Should I proceed along that path or would I be better off using a
central address table with keys to the other tables?


Thanks in advance for your insight,



-- 
    THESEE: D'un perfide ennemi j'ai purgé la nature ;
    A ses monstres lui-même a servi de pâture ;
  (Phèdre, J-B Racine, acte 3,
scène 5)




**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

Re: [GENERAL] News server not working?

[The Hermit Hacker]

had so software instability on the news server the past couple of days,
the developers exorcised the demons and all appears to be well right now
*cross fingers*


On Tue, 13 Feb 2001, PM wrote:

> I haven't been able to connect to news.postgresql.org for a few days now. I
> can ping the address...
>
> Is it just me?
>
>
>

Marc G. Fournier   ICQ#7615664   IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED]   secondary: scrappy@{freebsd|postgresql}.org

[GENERAL] Re: tmp_pg_shadow

[Robert Dege]

I am attempting to upgrade to 7.0.3 -- hence the dumpall command.

There was a zero size file in template1 that was causing the problem. 
Thanks for the help.

<>

Thanks
-Rob

> Robert Dege <[EMAIL PROTECTED]> writes:
> > I'm trying to do a pg_dumpall & receive this error message:
> > ERROR:  cannot create tmp_pg_shadow
> 
> Is it possible there is a physical file tmp_pg_shadow in your database
> directory?  If so, try removing it.
> 
> BTW, I'd recommend an update to 7.0.3 at your earliest convenience...
> 
> regards, tom lane

Re: [GENERAL] tmp_pg_shadow

[Tom Lane]

Robert Dege <[EMAIL PROTECTED]> writes:
> I'm trying to do a pg_dumpall & receive this error message:
> ERROR:  cannot create tmp_pg_shadow

Is it possible there is a physical file tmp_pg_shadow in your database
directory?  If so, try removing it.

BTW, I'd recommend an update to 7.0.3 at your earliest convenience...

regards, tom lane

Re: [GENERAL] Backend sent D message without prior T

[Tom Lane]

Antonis Antoniou <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> IIRC, this is a fairly common symptom of the frontend app running out
>> of memory --- libpq loses sync with what the backend is sending, and
>> it doesn't handle that very gracefully.

> This is strange because the machine has 1GB of memory.  Using the same
> database on PostgreSQL 7.0.2  and on a machine that has 500MB of memory
> the above query works fine.

But what do you have the per-process memory limit set to on this
machine?  (ulimit, kernel MAXDSIZ parameter, or swap space limits might
be preventing your frontend from using all available memory.)

regards, tom lane

[GENERAL] using tables as types in other tables

[Louis-David Mitterrand]

In the app we are developing the concept of an address will occur very
often on many tables (vendor, clients, employees, etc.) so we are
looking to avoid code duplication by centralizing the addresses in one
table. However I once read on one of the pgsql- lists that one could use
a table name as a type:

create table address(street text, zip text, city text, country text);

create table employee(emp_addr address, emp_id int);

This is accepted by Postgres but the question is how to insert into the
employee table? What syntax should be used? I tried:

insert into employee values (('mystreet','myzip','mycity','mycountry'), 1);

But it doesn't work.

Should I proceed along that path or would I be better off using a
central address table with keys to the other tables?

Thanks in advance for your insight,


-- 
THESEE: D'un perfide ennemi j'ai purgé la nature ;
A ses monstres lui-même a servi de pâture ;
  (Phèdre, J-B Racine, acte 3, scène 5)

[GENERAL] tmp_pg_shadow

[Robert Dege]

I'm trying to do a pg_dumpall & receive this error message:

ERROR:  cannot create tmp_pg_shadow
ERROR:  COPY command failed.  Class tmp_pg_shadow does not exist.
ERROR:  Relation 'tmp_pg_shadow' does not exist

I'm running postgres 6.5.2.  Does anybody have any insight as to why
this is occuring?


Thank you 
-Rob

Re: [GENERAL] Backend sent D message without prior T

[Antonis Antoniou]

Tom Lane wrote:

> Antonis Antoniou <[EMAIL PROTECTED]> writes:
> > I get the following message:
> > Backend sent D message without prior T
>
> > Does anyone knows about this message? I am expecting to retrieve
> > 2818510  records and am using  PostgreSQL 7.1beta4.
>
> IIRC, this is a fairly common symptom of the frontend app running out
> of memory --- libpq loses sync with what the backend is sending, and
> it doesn't handle that very gracefully.
>
> I'd suggest using a cursor to retrieve the data in more manageable
> chunks.
>
> regards, tom lane

This is strange because the machine has 1GB of memory.  Using the same
database on PostgreSQL 7.0.2  and on a machine that has 500MB of memory
the above query works fine.

Regards
 Antonis

Re: [GENERAL] Bad book review

[Vince Vielhaber]

On Wed, 14 Feb 2001, Bruce Momjian wrote:

> I am not sure how many people have looked at my book on Amazon.com, but
> I have received my first negative book review:
>
>   
>http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1/ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907
>
> I guess everyone isn't going to like my book.  :-)

Did you read any of his other reviews?  He doesn't strike me as a happy
person.   Someone musta moved his cheese.

Vince.
-- 
==
Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directoryhttp://www.camping-usa.com
   Online Giftshop Superstorehttp://www.cloudninegifts.com
==

[GENERAL] Is it useful to benchmark MySQL against PostgreSQL? (was RE: Crash-me of PostgreSQL)

[Riebs, Andy]

>  I still not understand what all compare, ... compare full 
> dymamic/modular 
> PostgreSQL with static features (types, functions ..) in MySQL?
> 
>  The MySQL must be always faster - if not - MySQL developers 
> are greenhorns -
> but it isn't feasible.
> 
>  May be compare availability for some specific usage -- an example web
> application, multi-user assecc vs. locks  ..etc., but some 
> common tests 
> say nothing. 

Every database system has a unique combination of capabilities and
performance. In selecting a database system, the user must answer the
question "Will this product meet my unique set of requirements for my
application?" Those requirements typically include...

o Capabilities
o Purchase price
o Total cost of ownership over time
o Performance
o Capacity
o Scalability
o Reliability
o etc.

As you have observed, it is important to know something about the
application to understand what you will need from the database. Some
database systems will perform well in one type of environment, and totally
fail in another. With osdb, we are trying to provide a tool kit which will
let one answer questions about performance and scalability such as

1. How does database X perform compared to database Y when a single
   application is generating a very high rate of queries?
2. How does X compare to Y when a large number of users are executing
queries?
3. How does database X on platform A compare to X on platform B?

We started osdb with MySQL and PostgreSQL because they are well-known,
respected, and easy to purchase :), not because we wish to engage in
arguments suggesting that one is better than another.  Over time, we hope to
extend osdb to cover both closed and open source products.

As they say, "your mileage may vary," but we give you a way to generate some
mileage!

/andy

---
Andy Riebs, [EMAIL PROTECTED]  Alpha Technology Solutions Group
978-506-6628, fax 978-506-6524 Compaq Computer Corporation
(h) [EMAIL PROTECTED]   

Re: [GENERAL] Case insensitive selects?

[Adam Lang]

It is in the list archives several times.

All you need is to use some basic SQL.

select * from mytable where upper('my criteria') = upper(mytable.info);

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
- Original Message - 
From: "David Reid" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 14, 2001 9:58 AM
Subject: [GENERAL] Case insensitive selects?


> Does pgsql support this and how would I do it?
> 
> david

[GENERAL] Re: Tuple is to big

[Dan McGrath]

Actually, you should take a look in the documentation. It specifically
states that the maximum size for any tuple is 8k (unless compiled
otherwise). If you want to store something larger, consider using the large
objects feature.

Dan

Bodoni Endre wrote:

> I have a little trouble with my PostgreSQL 6.5.1. I use a table with
> text character type.
> When I try to insert about 1 character long string into it I've got
> an error message like:
> ERROR:  Tuple is too big: size 10172
> Is there any limitation for a variable length text type?
> If any, how long is it?
>
> Regards,
> Endre Bodoni

[GENERAL] Re: How to make PostgreSQL JDBC drive get PGTZ?

[Raymond Chui]

I solved this problem by execute a SQL statement below after JDBC
connection.

SET TIME ZONE 'GMT';

--Raymond



begin:vcard 
n:Chui;Raymond
tel;fax:(301)713-0963
tel;work:(301)713-0624 Ext. 168
x-mozilla-html:TRUE
url:http://members.xoom.com/rchui/
org:NWS, NOAA
version:2.1
email;internet:[EMAIL PROTECTED]
title:SA, DBA
note:ICQ #: 16722494
adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A.
x-mozilla-cpt:;-6384
fn:Raymond Chui
end:vcard

[GENERAL] List set bag support

[Mourad EL HADJ MIMOUNE]

 Hello,
My name is Mourad. I have just started to use 
Postgres.
 
I seen that postgres support variable-length multi-dimensional arrays, whit 
built-in type or user-defined type.
I want know if Postgres permits the use of 
set, list, and bag concepts.
 
Mourad,

[GENERAL] Parameters if trigger...

[Mikhail V. Majorov]

Hi all!

May I use function with parameters with triggers?
When I try it I have error: "Can't find function ..".
Is it normal?

Postre can't find my function. Why?

create function mytest(text) returns opaque as '
declare 
parma1 like as $1;
begin
return NEW;
end;
' language 'pgsql';

Best regards,
Mik.

[GENERAL] Re: Bad book review

[mike]

I got the book when it was first made availiable, and its been a damn good
reference, though I do admit it doesn't go into using Large Objects well
enough. as for installation, the INSTALL file pretty much explains what
needs to be done. All in all its one of my most frequently used references.
=)

Mike

- Original Message -
From: "Bruce Momjian" <[EMAIL PROTECTED]>
To: "PostgreSQL-general" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 11:06 PM
Subject: Bad book review


> I am not sure how many people have looked at my book on Amazon.com, but
> I have received my first negative book review:
>
>
http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1
/ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907
>
> I guess everyone isn't going to like my book.  :-)
>
> --
>   Bruce Momjian|  http://candle.pha.pa.us
>   [EMAIL PROTECTED]   |  (610) 853-3000
>   +  If your life is a hard drive, |  830 Blythe Avenue
>   +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026
>

Re: [GENERAL] transaction safety

[DaVinci]

On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote:
> DaVinci wrote:
> >  Problem is with referential integrity like you say. But I don't understand
> >  well reason. Interlocks should appear only when values of referenced tables
> >  are changed, added or removed, shouldn't they?. But that is not the case. I
> >  only insert in table "aviso".
> >
> >  Is this normal?
> 
> Maybe.
> 
> The problem is, that a referential integrity constraint needs
> to ensure that from the moment  the  constraint  got  checked
> until  your  INSERT got made persistent (COMMIT), nobody else
> has a chance to kick out the referenced key.
> 
> Due to the lack of the ability to put a shared row lock  with
> a  SELECT,  we currently use FOR UPDATE, placing an exclusive
> lock onto the referenced key.

 I understand this but, why then other changes on "aviso" get locked?. My
 first impression is that only referenced keys should be, but not a table
 that references them.

 If this works so for now, is any plan to change in future?.

> In your case it might help to make the constraints  INITIALLY
> DEFERRED.  That  causes  that  the  checks  are delayed until
> COMMIT, so it shortens the time the lock is present.

 Thanks!. That works fine.

   
 David 

Re: [GENERAL] Bad book review

[Jan Wieck]

Bruce Momjian wrote:
> I am not sure how many people have looked at my book on Amazon.com, but
> I have received my first negative book review:
>
>   
>http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1/ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907
>
> I guess everyone isn't going to like my book.  :-)

Give yourself an amusement and lookup this:

http://www.petdance.com/andy/resume/

After   15   years  of  *professional*  ColdFusion  and  Perl
development, 7 years of OO-Perl (interfacing  MySQL)  plus  a
couple of other high-level skills, he got the enlightment and
learns VB 6.

On Word: If your only tool is MSWord, everything looks like a
.doc :-)


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: [GENERAL] transaction safety

[Jan Wieck]

DaVinci wrote:
> On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> > DaVinci <[EMAIL PROTECTED]> writes:
> > > procedenciaint2 references procedencia,
> > > línea  int2 references línea,
> > > empresaint2 references empresa,
> >
> > Depending on the data values you are working with, it could be that the
> > foreign key references cause interlocks --- ie, one transaction has to
> > wait to see if the other commits to know if it has a valid foreign key.
>
>  Problem is with referential integrity like you say. But I don't understand
>  well reason. Interlocks should appear only when values of referenced tables
>  are changed, added or removed, shouldn't they?. But that is not the case. I
>  only insert in table "aviso".
>
>  Is this normal?

Maybe.

The problem is, that a referential integrity constraint needs
to ensure that from the moment  the  constraint  got  checked
until  your  INSERT got made persistent (COMMIT), nobody else
has a chance to kick out the referenced key.

Due to the lack of the ability to put a shared row lock  with
a  SELECT,  we currently use FOR UPDATE, placing an exclusive
lock onto the referenced key.

In your case it might help to make the constraints  INITIALLY
DEFERRED.  That  causes  that  the  checks  are delayed until
COMMIT, so it shortens the time the lock is present.


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

[GENERAL] Crash-me of PostgreSQL

[Uro Gruber]

Hi!

Does anybody have chrach-me (mysql) results for PostgreSQL 7.1beta, because
in dont have module DBI for perl so i can't run it. I wan't to
comprebe to version 7.0 to see the difference.



-- 
lp,
 Uroš  mailto:[EMAIL PROTECTED]

Re: [GENERAL] transaction safety

[DaVinci]

On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> DaVinci <[EMAIL PROTECTED]> writes:
> > procedencia int2 references procedencia,
> > línea   int2 references línea,
> > empresa int2 references empresa,
> 
> Depending on the data values you are working with, it could be that the
> foreign key references cause interlocks --- ie, one transaction has to
> wait to see if the other commits to know if it has a valid foreign key.

 Problem is with referential integrity like you say. But I don't understand
 well reason. Interlocks should appear only when values of referenced tables
 are changed, added or removed, shouldn't they?. But that is not the case. I
 only insert in table "aviso".

 Is this normal?

 Greets.

   
 David
 

Re: [GENERAL] Re: Bad book review

[Mikhail V. Majorov]

Matt Friedman wrote:
> 
> I have it and have found it very useful.

I think charachter about Functions and Triggers too small.
Server-side functions are very impotant for check input information and
integrity of database.

Best regards,
Mik