Re: [HACKERS] On file locking
That same documentation mentions that locks acquired using flock() will *not* invoke the mandatory lock semantics even if on a file marked for it, so I guess flock() isn't implemented on top of fcntl() in Linux. They're not. And there's another difference between fcntl and flock in Linux: although fork(2) states that file locks are not inherited, locks made by flock are inherited to children and they keep the lock even when the parent process is killed with SIGKILL. Tested this. Just see man syscall, there exists both flock(2) and fcntl(2) -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] On file locking
But this only wins if a child process inheriting an open file also inherits copies of any locks held by the parent. If not, then the issue is moot. Anybody have any idea if file locks work that way? Is it portable?? From RedHat 8.0 manages fork(2): SYNOPSIS #include sys/types.h #include unistd.h pid_t fork(void); DESCRIPTION fork creates a child process that differs from the parent process only in its PID and PPID, and in the fact that resource utilizations are set to 0. File locks and pending signals are not inherited. ^^ ^^ And from SunOS 5.8 flock Locks are on files, not file descriptors. That is, file descriptors duplicated through dup(2) or fork(2) do not result in multiple instances of a lock, but rather multiple references to a single lock. If a process holding a lock on a file forks and the child explicitly unlocks the file, the parent will lose its lock. Locks are not inherited by a child process. If I understand correctly it says that if parent dies, file is unlocked no matter if there's children still running? -- Antti Haapala ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Switching connection on the fly
On Mon, 27 Jan 2003, Shridhar Daithankar wrote: I went thr http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-session- authorization.html to get what it is. I didn't have an idea of such thing. Back to the topic, yes, pretty much except for few differences. 1) It says 'The session user identifier may be changed only if the initial session user (the authenticated user) had the superuser privilege. Otherwise, the command is accepted only if it specifies the authenticated user name.' That mean an ordinary user can not set session to any other authorised user. It is like running setuid program with input accessible to any user. 2) Where do I specify password? I mean I take a password and start a connection to database. But when it comes to switching connection, there is no password. Probably because only superuser can switch connection? If there is a password clause there and if any user can switch to any user, then it is the thing I am looking for. Probably even excluding switching to superuser as a security measure. I need this feature also. The problem with set session authorization is that you can always change back so it's not that secure. Actually I wanted to have a function that could augment the privileges of user if supplied the right password, which in turn had nothing to do with original password. I believe it could be easy to implement such a function in C. But it could be better and easier to have pl/pgsql function that could set the session authorization. So, could it be made possible that pl/pgsql functions created by superuser could set session authorization even when not called by superuser (or user logged in as superuser)? -- Antti Haapala ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE
Ross, you make some powerful arguments here. Probably the most
significant was the idea that you need a unique identifier for every
row, and it should be of a consistent type, which primary key is not.
I don't see why you need a unqiue identifier per row, nor do I see why,
if you are going to have one, it needs to be the same type across all
tables.
If i had table with multi col primary key like...
create table devices (
major int4,
minor int4,
primary key (major, minor)
);
... and do this:
insert into devices (major, minor values (224, find_free_minor_for(224))
should the database report something like
INSERT '{([\'224\', \'89\'])}' 1
which I could then parse in my client program and try to recover my
fresh brand new primary key from it? No thanks...
Anyways, I've got an idea: what about having option that INSERTs return
oid_status in form
major = '224' and minor = '10'
or
state = 'ca'
?
Then you could just throw this expression into a select query after where
;P And tables would never need row oids...
--
Antti Haapala
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 7.4 Wishlist
SHOW
I think 7.4 could and really should implement SHOW command similar to
MySQL. Listing tables/foreign keys/views and so isn't just psql problem,
but common to many interface implementations and maintenance tools. I
think it's wrong to rely on pg_* system tables on these.
If you think of some probable new user (changing from MySQL) who asks how
to query list of tables in PostgreSQL and gets the answer (from psql -E)
that is:
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as Type,
u.usename as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
opposed to something like
SHOW TABLES_AND_VIEWS
as in MySQL, which DBMS would this user prefer?
I further suggest that these SHOW command parameters (like tables and
views) could be views in special system schema, so it could be easy to
update them just by changing templates. Maybe 7.5 (if it had introduced
new features) could provide downward compatibility to 7.4 ready clients by
allowing the use of SHOW-views from different schema (like
pg_show_743_compat)
:)
Stored procedures used in implementing new syntax
-
This is an implementation detail suggestion. Would it be possible that
new syntax in SQL could be implemented in different languages than C.
We
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 Wishlist
Ups... i sent an early draft of my post by accident, sorry... -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
SHOW I think 7.4 could and really should implement SHOW command similar to MySQL. Listing tables/foreign keys/views and so isn't just psql problem, Actually, in 7.4 I'd tell them to: select * from information_schema.tables; This is a far more portable method. Yep. You know it was just a draft which wasn't actually meant to be sent... I think the contents of that information schema could and should be user modifiable views... I needed to administer one of my dbs on 7.3 from another computer with psql 7.2.3 -- for the computer had PG 7.2.3 running, and \d on for example views failed gratuituosly with error relation pg_relcheck not found. Of course the dumps, sql commands and tsv data etc. I needed were only on that 7.2.3 machine... Many interfaces need to enumerate tables databases etc. I'm not interested in having different versions of for example DBD::Pg... one, the most up-to-date, version should do the job. Against whatever version of postmaster I want to use it. So maybe backwards compatibility could be introduced also (older clients applications against newer DBMS) by having different information schemas for different client versions... don't know, how it should be actually done. Maybe there would be schemas info_compat_74, info_compat_75 and information_schema_80 in PostgreSQL 8.0 :P and information_schema could be alias to most appropriate of these, depending on client version :) (dunno). -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] MySQL vs PostgreSQL.
Check out: http://www.mysql.com/doc/en/MySQL-PostgreSQL_features.html MySQL AB compares MySQL with PostgreSQL. Quoted from one page Because we couldn't get vacuum() to work reliable with PostgreSQL 7.1.1, we haven't been able to generate a --fast version of the benchmarks yet (where we would have done a vacuum() at critical places in the benchmark to get better performance for PostgreSQL). We will do a new run of the benchmarks as soon as the PostgreSQL developers can point out what we have done wrong or have fixed vacuum() so that it works again. and from another. Drawbacks with PostgreSQL compared to MySQL Server: VACUUM makes PostgreSQL hard to use in a 24/7 environment. They also state that they have more sophisticated ALTER TABLE... Only usable feature in their ALTER TABLE that doesn't (yet) exist in PostgreSQL was changing column order (ok, the order by in table creation could be nice), and that's still almost purely cosmetic. Anyway, I could have used that command yesterday. Could this be added to pgsql. MySQL supports data compression between front and back ends. This could be easily implemented, or is it already supported? I think all the other statements were misleading in the sense, that they compared their newest product with PostgreSQL 7.1.1. There's also following line: PostgreSQL currently offers the following advantages over MySQL Server: After which there's only one empty line. Note that because we know the MySQL road map, we have included in the following table the version when MySQL Server should support this feature. Unfortunately we couldn't do this for previous comparisons, because we don't know the PostgreSQL roadmap. They could be provided one... ;-) Upgrading MySQL Server is painless. When you are upgrading MySQL Server, you don't need to dump/restore your data, as you have to do with most PostgreSQL upgrades. Ok... this is true, but not so hard - yesterday I installed 7.3b2 onto my linux box. Of course PostgreSQL isn't yet as fast as it could be. ;) -- Antti Haapala ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposed LogWriter Scheme, WAS: Potential Large
On 6 Oct 2002, Greg Copeland wrote: On Sat, 2002-10-05 at 14:46, Curtis Faith wrote: 2) aio_write vs. normal write. Since as you and others have pointed out aio_write and write are both asynchronous, the issue becomes one of whether or not the copies to the file system buffers happen synchronously or not. Actually, I believe that write will be *mostly* asynchronous while aio_write will always be asynchronous. In a buffer poor environment, I believe write will degrade into a synchronous operation. In an ideal situation, I think they will prove to be on par with one another with a slight bias toward aio_write. In less than ideal situations where buffer space is at a premium, I think aio_write will get the leg up. Browsed web and came across this piece of text regarding a Linux-KAIO patch by Silicon Graphics... The asynchronous I/O (AIO) facility implements interfaces defined by the POSIX standard, although it has not been through formal compliance certification. This version of AIO is implemented with support from kernel modifications, and hence will be called KAIO to distinguish it from AIO facilities available from newer versions of glibc/librt. Because of the kernel support, KAIO is able to perform split-phase I/O to maximize concurrency of I/O at the device. With split-phase I/O, the initiating request (such as an aio_read) truly queues the I/O at the device as the first phase of the I/O request; a second phase of the I/O request, performed as part of the I/O completion, propagates results of the request. The results may include the contents of the I/O buffer on a read, the number of bytes read or written, and any error status. Preliminary experience with KAIO have shown over 35% improvement in database performance tests. Unit tests (which only perform I/O) using KAIO and Raw I/O have been successful in achieving 93% saturation with 12 disks hung off 2 X 40 MB/s Ultra-Wide SCSI channels. We believe that these encouraging results are a direct result of implementing a significant part of KAIO in the kernel using split-phase I/O while avoiding or minimizing the use of any globally contented locks. Well... In a worse case scenario, it seems that aio_write does get a win. I personally would at least like to see an aio implementation and would be willing to even help benchmark it to benchmark/validate any returns in performance. Surely if testing reflected a performance boost it would be considered for baseline inclusion? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Multicolumn foreign keys need useless unique indices?
There is a misfeature in 7.2.2 that appears when I have a foreign key that references two columns of a table. Consider following simplified example: CREATE TABLE a ( a int PRIMARY KEY, b int ); CREATE TABLE b ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES a(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); I get an error UNIQUE constraint matching given keys for referenced table a not found. because I have unique constraint only on the first field (which is still enough to make the whole combination unique. (b is not even unique))... So I need to add an useless(?) UNIQUE constraint to (a, b) for table a just to allow creation of multicol FOREIGN KEYs for table b. And I get NOTICE: CREATE TABLE / UNIQUE will create implicit index 'a_a_key' for table. AFAIK, the extra index only slows down my inserts - it basically contains no usable information... shouldn't the presence of _primary_key_ in multicol foreign key be enough to decide whether the whole key is unique or not? And shouldn't it be enough to find out the tuple in table 'a' corresponding newly inserted tuple in b? Or should I just write my own triggers for checking the integrity of b/bref column pair to avoid needless index creation? -- Antti Haapala ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multicolumn foreign keys need useless unique indices?
AFAIK, the extra index only slows down my inserts - it basically contains no usable information... Not 100% true. It will speed up cascade delete and update... To clarify things: CREATE TABLE original ( a int PRIMARY KEY, b int ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'original_pkey' for table 'original' CREATE CREATE TABLE referencer ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES original(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table original not found CREATE TABLE original ( a int PRIMARY KEY, b int, UNIQUE (a,b) ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'original_pkey' for table 'original' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'original_a_key' for table 'original' CREATE CREATE TABLE referencer ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES original(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE ilmo=# \d original Table original Column | Type | Modifiers +-+--- a | integer | not null b | integer | Primary key: a_pkey Unique keys: a_a_key Triggers: RI_ConstraintTrigger_41250, RI_ConstraintTrigger_41252 ilmo=# \d referencer Table referencer Column | Type | Modifiers +-+--- aref | integer | bref | integer | Triggers: RI_ConstraintTrigger_41248 Actually nothing changes. The unique constraint doesn't add anything new - it allows NULLs in column b and requires that combination (a, b) is unique... and it definitely is because column 'a' is unique (primary key). It just creates a multicol index and adds an useless extra constraint check, while almost the same data is available in index original_a_pkey. -- Antti Haapala ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
