[SQL] foreign key on pg_shadow
Dear list, Upon designing our application we thought that the following setup would be a good idea to implement security: Using the pg_shadow table as it is: | pg_shadow | +-+ | usename | | usesysid| | usecreatedb | | usesuper| | usecatupd | | passwd | | valuntil| | useconfig | We also wanted an extension on it tblusersettings: | tblusersettings | +-+ | userid | | language| | address | | birthdate | | department | | etc... | Where userid should reference to pg_shadow.usesysid. Making it so, that the usersettings for a user would be deleted on a DROP USER. So I tried to create a foreign key constraint with ON DELETE CASCADE. No matter what ON DELETE constraint I created, the system will not allow me to create a foreign key, as pg_shadow is a system catalog. Yet using the database user with this extention would be awesome. I could try to inherit the table, altough I am not certain if that would be allowed... Anyway: is there a way to get this setup working, or should I give up and try it completely different? I am using PostgreSQL 8.0.3 TIA, Michiel ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SELECT very slow
The problem is, that a SELECT * FROM foobar; takes ages (roughly 3
minutes) to return the first row. I played around with the fetchSize()
to disable the result set caching in the Java program first (before I
tried psql) but that did not change anything.
Hello,
Yours seemed strange so I tried this :
Created a table with 128K lines, 4 TEXT columns containing about 70 chars
each...
-
\d bigtest;
Colonne | Type | Modificateurs
-+-+-
id | integer | not null default
nextval('public.bigtest_id_seq'::text)
data1 | text|
data2 | text|
data3 | text|
data4 | text|
Index :
«bigtest_pkey» PRIMARY KEY, btree (id)
-
SELECT count(*) from bigtest;
count
131072
-
explain analyze select * from bigtest;
QUERY PLAN
--
Seq Scan on bigtest (cost=0.00..7001.72 rows=131072 width=308) (actual
time=0.035..484.249 rows=131072 loops=1)
Total runtime: 875.095 ms
So grabbing the data takes 0.875 seconds.
-
SELECT avg(length(data1)),
avg(length(data2)),avg(length(data3)),avg(length(data3)) from bigtest;
avg | avg | avg |
avg
-+-+-+-
72.1629180908203125 | 72.2342376708984375 | 72.3680572509765625 |
72.3680572509765625
Here you see the average data sizes.
-
Now I fire up python, do a SELECT * from the table and retrieve all the
data as native objects... Hm, it takes about 1.3 seconds... on my
Pentium-M 1600 laptop...
I was about to suggest you use a less slow and bloated language than
Java, but then on my machine psql takes about 5 seconds to display the
results, so it looks like it ain't Java. psql is slow because it has to
format the result and compute the column widths.
Don't you have a problem somewhere ? Are you sure it's not swapping ? did
you check memory ? Are you transferring all this data over the network ?
Might an obscure cabling problem have reverted your connection to 10 Mbps ?
I'm using pg 8.0.something on Linux.
Ouch. I saw you're on Windows so I tried it on the windows machine there
which has a postgres installed, over a 100Mbps network, querying from my
linux laptop. The windows machine is a piece of crap, Pentium-II 300 and
256 MB Ram, it takes 7 seconds to retrieve the whole table in a python
native object.
So...
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] foreign key on pg_shadow
"M.D.G. Lange" <[EMAIL PROTECTED]> writes: > No matter what ON DELETE constraint I created, the system will not allow > me to create a foreign key, as pg_shadow is a system catalog. We do not support foreign keys (or indeed triggers of any kind) on system catalogs. I don't foresee that happening in the near future either, though I think there is something about it on the TODO list. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] getting details about integrity constraint violation
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
>> Simply name the table constraints yourself with a descriptive name, so you
>> always know exactly what is going on:
> And then I keep a list of all the constraint names and scan the error
> message for it?
Don't keep a list: just come up with a standard naming scheme, such as:
"tablename|colname|is_not_unique"
which should be human and machine parseable (perl example):
if ($error =~ m#^(.+)\|(.+)\|is_not_unique$#o) {
die qq{Whoops : looks like column "$2" of table "$1" needs to be
unique\n};
}
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506142204
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCr4zivJuQZxSWSsgRAgGPAJ0awkoBmus6z1cLBRpsR5xmQPTfiACgpJxG
Ld90hEGDPrebBE3JGGL11L4=
=smQJ
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
